主从复制搭建

环境要求

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