环境

准备工作

1、位于10.0.2.13/24、10.0.2.14/24的两台奇偶库停止MyCat中间件等运行；
2、复制并传输 10.0.2.13/24、10.0.2.14/24的两台奇偶库的数据库配置（/etc/my.cnf）和数据文件到执行冷备的主库备份库10.0.2.15/24、10.0.2.16/24中；

[deployer@10-0-2-13 ~]# sudo scp -P 22443 -r /data/mysql_ssd/* 10.0.2.15:/data/mysql_ssd  
[deployer@10-0-2-14 ~]# sudo scp -P 22443 -r /data/mysql_ssd/* 10.0.2.16:/data/mysql_ssd

3、因尚未关闭MySQL，这里再次核实两台奇偶数据库的写入状态：

[deployer@10-0-2-13 ~]# sudo mysql -uzhu.pengfei -p

mysql > show master status;    #查看position字段是否变化,判断数据库此时是否正在写入

[deployer@10-0-2-14 ~]# sudo mysql -uzhu.pengfei -p

mysql > show master status;    #查看position字段是否变化,判断数据库此时是否正在写入

4、修改两台冷备主库的数据库配置文件：

[deployer@10-0-2-15 ~]# sudo vi /etc/my.cnf 
server-id=15 #一般修改为服务器IP 
log_bin = binlog #关闭binlog

& 

[root@10-0-2-16 ~]# sudo vi /etc/my.cnf 
server-id=16 #一般修改为服务器IP 
log_bin = binlog #关闭binlog

主从库同步

将冷备主库服务器上auto.cnf文件中uuid进行修改 使之与主库不一致

[deployer@10-0-2-15 ~]# sudo vi /data/mysql_ssd/auto.cnf
[deployer@10-0-2-16 ~]# sudo vi /data/mysql_ssd/auto.cnf

授予数据库权限

[deployer@10-0-2-15 ~]# sudo chown -R mysql.mysql /data/mysql_ssd
[deployer@10-0-2-16 ~]# sudo chown -R mysql.mysql /data/mysql_ssd

启动备份服务器mysql服务

[deployer@10-0-2-15 ~]# sudo systemctl start mysqld
[deployer@10-0-2-16 ~]# sudo systemctl start mysqld

两台奇偶数据库主库操作：

mysql > grant replication slave on *.* to 'zhu.pengfei'@'10.0.2.13' identified by "dbuser#mysql13";
mysql > grant replication slave on *.* to 'zhu.pengfei'@'10.0.2.13';
mysql > Show master status;

&

mysql > grant replication slave on *.* to 'zhu.pengfei'@'10.0.2.14' identified by "dbuser#mysql14";
mysql > grant replication slave on *.* to 'zhu.pengfei'@'10.0.2.14';
mysql > Show master status;

两台奇偶数据库主库备份库操作：

master_log_file : binlog名字格式可能发生变化,需进行比对.master_log_file

主库备份库10.0.2.15/24操作：

mysql > 
change master to
master_host='10.0.2.13',
master_user='zhu.pengfei',
master_password='dbuser#mysql13',
master_log_file='binlog.00011',  
master_log_pos=334374;

&

主库备份库10.0.2.16/24操作：

mysql > 
change master to
master_host='10.0.2.14',
master_user='zhu.pengfei',
master_password='dbuser#mysql14',
master_log_file='binlog.00022',  
master_log_pos=591356;

两台奇偶数据库主库备份库操作：

mysql > show slave status \G; #查看状态

冷备脚本

[deployer@10-0-2-16 ~]# sudo mkdir -p /data/scripts
[deployer@10-0-2-16 ~]# sudo vi /data/scripts/start_slave.sh
#!/bin/sh
systemctl restart mysqld
sleep 10s
CONNECT_MYSQL="mysql -h 10.0.2.14 -uzhu.pengfei -pdbuser#mysql14"
SQL="start slave;"
echo "${SQL}" | ${CONNECT_MYSQL}

[deployer@10-0-2-16 ~]# sudo vi /data/scripts/stop_slave.sh
#!/bin/sh
CONNECT_MYSQL="mysql -h 10.0.2.14 -uzhu.pengfei -pdbuser#mysql14"
SQL="stop slave;"
echo "${SQL}" | ${CONNECT_MYSQL}
sleep 10s
systemctl stop mysqld

&

[deployer@10-0-2-15 ~]# sudo mkdir -p /data/scripts
[deployer@10-0-2-15 ~]# sudo vi /data/scripts/start_slave.sh
#!/bin/sh
systemctl restart mysqld
sleep 10s
CONNECT_MYSQL="mysql -h 10.0.2.13 -uzhu.pengfei -pdbuser#mysql13"
SQL="start slave;"
echo "${SQL}" | ${CONNECT_MYSQL}

[deployer@10-0-2-15 ~]# sudo vi /data/scripts/stop_slave.sh
#!/bin/sh
CONNECT_MYSQL="mysql -h 10.0.2.13 -uzhu.pengfei -pdbuser#mysql13"
SQL="stop slave;"
echo "${SQL}" | ${CONNECT_MYSQL}
sleep 10s
systemctl stop mysqld

定时任务

[deployer@10-0-2-15 ~]# sudo crontab -e
00 01 * * * /bin/bash /data/scripts/start_slave.sh
00 07 * * * /bin/bash /data/scripts/stop_slave.sh
&
[deployer@10-0-2-16 ~]# sudo crontab -e
00 01 * * * /bin/bash /data/scripts/start_slave.sh
00 07 * * * /bin/bash /data/scripts/stop_slave.sh