存量数据导出

mysqldump -uuser -ppassword --single-transaction --master-data=2 --flush-logs -q database  > /dump.sql

--single-transaction设置隔离级别保证数据一致

--master-data将创建slave的语句包含在注释中，后续创建slave会用到

导出后

cat dump.sql |grep " CHANGE MASTER"

得到MASTER_LOG_FILE MASTER_LOG_POS

多源复制（多主一从）

实现从库C经由主库A同步databaseA，经由主库B同步databaseB。

配置文件

主库A

server_id=1 #设置id，不能重复
log-bin = log-bin 
binlog-format=mixed
expire_logs_days=60
binlog-do-db=databaseA #哪些数据库的变动需要写入到binlog
binlog-ignore-db = mysql  #这是不记录binlog，来达到从库不同步mysql库，以确保各自权限
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
binlog-ignore-db = sys

主库B

server_id=2 #设置id，不能重复
log-bin = log-bin 
binlog-format=mixed
expire_logs_days=60
binlog-do-db=databaseB #哪些数据库的变动需要写入到binlog
binlog-ignore-db = mysql  #这是不记录binlog，来达到从库不同步mysql库，以确保各自权限
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
binlog-ignore-db = sys

从库C

server_id=3
A.replicate-do-db = databaseA #设置从哪个主库同步哪些数据库
B.replicate-do-db = databaseB
slave-skip-errors=all  #忽略错误

执行语句

主库A

grant replication slave on *.* to slave@'%' identified by 'password';#创建同步账户并授权
flush privileges;

主库B

grant replication slave on *.* to slave@'%' identified by 'password';#创建同步账户并授权
flush privileges;

从库C

CHANGE MASTER 'A' TO
  MASTER_HOST='x.x.x.x',
  MASTER_USER='slave',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='log-bin.000001',#之前dump.sql中会提供
  MASTER_LOG_POS=0,#之前dump.sql中会提供
  MASTER_CONNECT_RETRY=10;

CHANGE MASTER 'B' TO
  MASTER_HOST='x.x.x.x',
  MASTER_USER='slave',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='log-bin.000001',#之前dump.sql中会提供
  MASTER_LOG_POS=0,#之前dump.sql中会提供
  MASTER_CONNECT_RETRY=10;

SATRT SLAVE 'A';#启动同步通道
SATRT SLAVE 'B';

SHOW SLAVE 'A' STATUS;#查看同步状态
SHOW SLAVE 'B' STATUS;

链式复制

实现从库D经由从库C同步databaseA与databaseB

配置文件

配置文件需要等待从库C把两个dump文件还原结束后，关闭binlog，再开启binlog后执行（保证binlog状态）

从库C

log-bin = log-bin
binlog-format=mixed
expire_logs_days=30
binlog-do-db=databaseA
binlog-do-db=databaseB
binlog-ignore-db = mysql  #这是不记录binlog，来达到从库不同步mysql库，以确保各自权限
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
binlog-ignore-db = sys
log_slave_updates = 1 #链式复制需要增加改行，传递修改到binlog

从库D

server_id=4
C.replicate-do-db = databaseA
C.replicate-do-db = databaseB

执行语句

从库C

grant replication slave on *.* to slave@'%' identified by 'password';#创建同步账户并授权
flush privileges;

从库D

CHANGE MASTER 'C' TO
  MASTER_HOST='x.x.x.x',
  MASTER_USER='slave',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='log-bin.000001',
  MASTER_LOG_POS=0,#设置为0使其自动调整
  MASTER_CONNECT_RETRY=10;