第十五周

原创

wyy1991 2020-04-07 01:21:51 ©著作权

©著作权归作者所有：来自51CTO博客作者wyy1991的原创作品，谢绝转载，否则将追究法律责任

1、编写脚本，支持让用户自主选择，使用mysqldump还是xtraback全量备份。

#!/bin/bash

PS3="Please input a choice:" select menu in mysqldump xtrabackup; do case $REPLY in 1) mkdir /backup/dump/ &> /dev/null mysqldump -F -A --single-transaction --master-data=1 > /backup/dump/all.sql break ;; 2) mkdir /backup/test/ &> /dev/null xtrabackup --backup --target-dir=/backup/test/ &> /dev/null break ;; *) echo "please input again!" esac done

2、配置Mysql主从同步

环境：192.168.43.127 master 192.168.43.137 slave

1、master配置文件：
[root@centos7 backup]#cat /etc/my.cnf
[mysqld]
**server-id=1**
max_connections=2000
binlog_format=ROW
**log-bin=/data/mysql/logbin/mysql-bin**
transaction-isolation=SERIALIZABLE
datadir=/data/mysql/data
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
log-error=/data/mysql/log/mariadb/mariadb.log
pid-file=/data/mysql/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

2、在master上添加复制用户：

MariaDB [(none)]>** grant replication slave on . to 'repluser'@'192.168.43.%' identified by '123456';** Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec)

3、备份master数据库，并拷贝到slave上：

[root@centos7 mysql]#mysqldump -F -A --single-transaction --master-data=1 > /backup/all.sql [root@centos7 backup]#scp -r /backup/all.sql 192.168.43.137:/data/

4、slave节点配置：

[mysqld] socket = /data/mysql/mysql.sock datadir = /data/mysql server-id=2 read_only

（1）slave导入数据库之前数据：

[root@ka1 mysql]#mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.2.25-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec)

（2）导入数据库，与主服务器保持数据一致：

[root@ka1 mysql]#mysql < /data/all.sql MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)

（3）配置主从同步（slave上操作）

查看数据库备份文件主服务二进制日志位置：

设置之前，slave状态是空的 MariaDB [(none)]> show slave status\G Empty set (0.00 sec)

MariaDB [(none)]>** CHANGE MASTER TO -> MASTER_HOST='192.168.43.127', -> MASTER_USER='repluser', -> MASTER_PASSWORD='123456', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.000005', -> MASTER_LOG_POS=245;** Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec)

设置之后可以看到slave状态如下：

MariaDB [(none)]>** show slave status\G** *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event ** Master_Host: 192.168.43.127** Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 245 Relay_Log_File: ka1-relay-bin.000002 Relay_Log_Pos: 544 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: ………

5、主从同步测试：

（1） master删除数据：

MariaDB [(none)]> use hellodb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [hellodb]> select * from teachers -> ; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shiitai | 77 | F | +-----+---------------+-----+--------+ 3 rows in set (0.00 sec)

MariaDB [hellodb]> delete from teachers where Age=77; Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | +-----+---------------+-----+--------+ 2 rows in set (0.00 sec)

（2）从服务器实时同步数据：

MariaDB [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | +-----+---------------+-----+--------+ 2 rows in set (0.00 sec)

3、使用MHA实现Mysql高可用。

在主从复制基础之上，实现MHA高可用；节点要开启二进制日志及中继日志；从节点为 read_only；关闭从节点的 relay_log_purge 中继日志自动清理功能环境准备： master 192.168.43.127 slave 192.168.43.137 slave/manager 192.168.43.147

1、master的配置文件： [mysqld] server-id=1 skip_name_resolve=1 relay-log=relay-log log-bin=bin-log innodb-file-per-table=ON datadir=/data/mysql/data slave1配置文件： [mysqld] datadir=/data/mysql/data server-id=2 skip_name_resolve=1 read_only relay_log_purge=0 relay-log=relay-log log-bin=bin-log innodb-file-per-table=ON slave2配置文件： [mysqld] datadir=/data/mysql/data server-id=3 skip_name_resolve=1 read_only relay_log_purge=0 relay-log=relay-log log-bin=bin-log innodb-file-per-table=ON

2、各个节点上安装mha4mysql-node，manager节点上安装mha4mysql-manager

[root@]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm [root@]#yum install mha4mysql-manager-0.56-0.el6.noarch.rpm

3、master上创建MHA管理用户核复制用户，在主从同步正常情况下会同步给slave和slave2:

MariaDB [(none)]> grant replication slave on . to repluser@'192.168.43.%' identified by '123456'; Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on . to mhauser@'192.168.43.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec)

4、各节点之间免密登录：

（1）各节点上生成密钥： [root@]#ssh-keygen

（2）后用命令将公钥拷贝到其他节点 [root@]#ssh-copy-id 192.168.43.147 [root@]#ssh-copy-id 192.168.43.137

[root@]#ssh-copy-id 192.168.43.127 [root@]#ssh-copy-id 192.168.43.137

[root@]#ssh-copy-id 192.168.43.127 [root@]#ssh-copy-id 192.168.43.147

5、在manager节点生成mha配置文件：

[root@]#cat **/etc/mastermha/app1.cnf ** [server default]
user=mhauser password=123456 manager_workdir=/data/mastermha/mysql/ manager_log=/data/mastermha/mysql/manager.log remote_workdir=/data/mastermha/mysql/ ssh_user=root repl_user=repluser repl_password=123456 ping_interval=1 [server1] hostname=192.168.43.127 candidate_master=1 master_binlog_dir=/data/mysql/data/
[server2] hostname=192.168.43.137 candidate_master=1 master_binlog_dir=/data/mysql/data/ [server3] hostname=192.168.43.147 master_binlog_dir=/data/mysql/data/

注释：

master_binlog_dir #mysql的binlog日志目录没有在默认的/var/log/mysql下，需要配置此项，写明具体路径 candidate_master=1 #是否可以成为master，1是

6、检查

（1）检查ssh登录是否正常：

[root@]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf

（2）检查节点之间复制是否正常： [root@]# masterha_check_repl --conf=/etc/mastermha/app1.cnf