mysql主从配置

--主从
1:
主库 创建复制账户
grant replication slave,replication client on *.* to 'replication'@'%' identified by 'replication';
grant replication slave,replication client on *.* to 'replication'@'172.16.4.%' identified by '2018';

2:
查看server_id要不同，修改从库的
set global server_id=102
永久修改就要/etc/my.cnf




3:主库做逻辑备份--主库导出，备库导入用master-data=2里面有备份包含的gtid
mysqldump --user=root -p --all-databases --flush-privileges --single-transaction --master-data=2 --flush-logs --triggers --routines --events --hex-blob > /opt/full_dump_bak_$(date +%F).sql


4.copy到备服务器

5.备服务器建库。并恢复。
恢复必须reset master

more xtrabackup_info 找处里面备份的GTID
6.
SET @@GLOBAL.GTID_PURGED='e4b9819d-5cc9-11e9-b672-005056ad12d0:1-6790734'
slave上执行
如果以前也是从库，要reset slave
注入空事务
Begin;
Commit;
Set @@SESSION.GTID_NEXT = AUTOMATIC;
--例子
change master to master_host='11.222.222.68',
master_port=3306,
master_user='replication',
master_password='123',
master_auto_position=1;


7.备库start slave

8.slave库设置read_only
show variable like '%read_only%'
set global read_only=on;