环境要求
2台以上的MySQL实例(同版本、同平台),具备不同的server_id,server_uuid
3307:主库
3308:从库
3309:从库
检查server_id
[root@db01 3308]# mysql -S /tmp/mysql3307.sock -e "select @@server_id ;"
[root@db01 3308]# mysql -S /tmp/mysql3308.sock -e "select @@server_id ;"
[root@db01 3308]# mysql -S /tmp/mysql3309.sock -e "select @@server_id ;"
检查 uuid
[root@db01 3308]# mysql -S /tmp/mysql3307.sock -e "select @@server_uuid ;"
[root@db01 3308]# mysql -S /tmp/mysql3308.sock -e "select @@server_uuid ;"
[root@db01 3308]# mysql -S /tmp/mysql3309.sock -e "select @@server_uuid ;"
1主库: 开启binlog, 创建复制用户。
vim /data/3307/my.cnf
server_id=7
log_bin=/data/3307/mysql-bin
[root@db01 3308]# systemctl restart mysqld3307
[root@db01 3308]# mysql -S /tmp/mysql3307.sock -e "select @@log_bin ;"
[root@db01 3308]# mysql -S /tmp/mysql3307.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
[root@db01 3308]# mysql -S /tmp/mysql3307.sock -e "select user,host ,plugin from mysql.user;"
2备份主库数据,恢复到从库
[root@db01 3308]# mysqldump -S /tmp/mysql3307.sock -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/data/full.sql
[root@db01 3308]# mysql -S /tmp/mysql3308.sock</data/full.sql
[root@db01 3308]# mysql -S /tmp/mysql3309.sock</data/full.sql
3告知从库信息
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1074,
MASTER_CONNECT_RETRY=10;
vim /data/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1043;
4 开启从库线程
start slave;
5 查看复制状态
mysql3308 >show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql3309 >show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6检查同步情况
1.在从库检查主库数据有没有同步过来
2.在主库创建数据,看从库能不能显示
如果出现如下情况
show slave status\G
请做如下操作
stop slave
reset slave all
change master to (从新告知从库主库信息)
start slave