主从复制

主从复制前提

• 两台以上mysql实例 ,server_id,server_uuid不同
• 主库开启二进制日志
• 专用的复制用户
• 保证主从开启之前的某个时间点,从库数据是和主库一致(补课)
• 告知从库,复制user,passwd,IP port,以及复制起点(change master to)
• 线程(三个):Dump thread IO thread SQL thread 开启(start slave)

主从复制搭建

修改my.cnf ,开启二进制日志功能

sync_binlog=1
binlog_format=row

主库中创建复制用户

grant replication slave on *.* to repl@'10.0.0.%' identified by '123456';
select user,host from mysql.user;

备份主库并恢复到从库

mkdir /data/backup/
mysqldump -S /tmp/mysql.sock -A --master-data=2 --single-transaction -R -E --triggers > /data/backup/full.sql

#从库恢复
db02 [(none)]>source full.sql;
[root@linux-node1 ~]# grep -e '-- CHANGE MASTER' /data/backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=967;

告知从库关键复制信息

CHANGE MASTER TO
  MASTER_HOST='10.0.0.12',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000007',
  MASTER_LOG_POS=967,
  MASTER_CONNECT_RETRY=10;

db02 [test01]>start slave;

检查复制状态

db02 [test01]>show slave status\G;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes