mysql 冷备 搭建从库_MySQL数据库配置主从或线下冷备

环境

序号

主库IP

是否奇偶库

冷备主库IP

执行冷备状态

1

10.0.2.13/24

奇库

10.0.2.15/24

正常

2

10.0.2.14/24

偶库

10.0.2.16/24

正常

准备工作

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