因为是使用bin-log进行备份，所以主库要进行如下配置

1

vi /etc/mysql/my.cnf

添加两行

1
2

server-id  = 1
log_bin    = mysql-bin

bin log会存放在data dir下

重启mysql

创建用于备份的用户

1
2
3

CREATE USER 'sync_user'@'%' IDENTIFIED BY 'sync_password';
GRANT REPLICATION SLAVE ON *.* TO 'sync_user'@'%';
flush privileges;

导出老数据

1

mysqldump -uroot -p123456 test --single-transaction --master-data=2 > master-data.sql

过滤出change master to信息

1
2
3

grep -i "change master to" master-data.sql 

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=900012;

这里不使用

show master status;

是因为主库一直在变化，和导出的master-data不匹配。

用docker创建备份库

创建容器，因为主库是5.5所以用5.5的镜像

1

docker run --name mysql -p 3306:3306 -v /data/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.5

修改配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

docker exec -it mysql bash

echo '[mysqld]' > /etc/mysql/my.cnf
echo 'skip-host-cache' >> /etc/mysql/my.cnf
echo 'skip-name-resolve' >> /etc/mysql/my.cnf
echo 'datadir = /var/lib/mysql' >> /etc/mysql/my.cnf
echo '!includedir /etc/mysql/conf.d/' >> /etc/mysql/my.cnf
echo 'server-id = 2' >> /etc/mysql/my.cnf

exit

重启

1
2

docker stop mysql
docker start mysql

备份库设置主库信息

1
2

docker exec -it mysql bash
mysql -uroot -p123456

导入数据

1

source master-data.sql

修改change master to

1
2

change master to master_host='192.168.10.105', master_user='sync_user', master_password='sync_password', master_log_file='mysql-bin.000001', master_log_pos=900012;
start slave;

进行测试，完成

相关命令与SQL

1
2
3
4
5
6
7
8

#开启从库模式
start slave;

#停止从库模式
stop slave;

#查看从库状态
show slave status \G;