MySQL 主从管理

log-bin=mysql-bin
binlog_format=row
log_slave_updates=1 --(可选)
gtid_mode=ON
enforce_gtid_consistency=1

create user 'slave'@'%' identified with mysql_native_password by 'password123';  

grant replication slave on *.* to slave@'%';

mysqldump -uroot -pxxx --socket=/data/mysql_3306/tmp/mysql.sock  -all-databases >all.sql 

# 加载主库备份sql(新建的2个等同节点可跳过此步)
mysql> source /root/all.sql 
mysql> stop slave;
mysql> change master to
master_host='192.168.xx.xx',master_user='slave',master_password='password123',master_port=3306,master_auto_position=1;
mysql> start slave;

#(1)停止slave进程
mysql> STOP SLAVE;
#(2)设置事务号，事务号从Retrieved_Gtid_Set获取
#在session里设置gtid_next，即跳过这个GTID 或者查看last_SQL_Error 里的信息
mysql> SET GTID_NEXT= '1d257f5b-5e6b-11e2-b668-5254003de1b6:N';
#(3)设置空事物
mysql> BEGIN; COMMIT;
#(4)恢复事物号
mysql> SET SESSION GTID_NEXT = AUTOMATIC;
#(5)启动slave进程
mysql> START SLAVE;
#跳过一个事务之后，重启slave，恢复正常

-- gtid 方式
change master to master_host='xx.124.67', master_user='', master_password='', master_port=3306, master_auto_position=1;

-- 偏移量方式
change master to master_host='',master_user=xxx,master_password=xxx,master_port=xxx,master_log_file='mysqlbin.000105',master_log_pos=xxx,master_auto_position=0;

# 查看gtid_purged='xxxx'值，后面用
show slave status;

stop slave;

reset master;

select * from gtid_executed;

set @@global.gtid_purged='xxxxx:1-N';--N是第一步查看slave status中retrieved_gtid_set的值

reset slave all;

change master to master_host='xx.124.67', master_user='', master_password='', master_port=3306, master_auto_position=1;

start slave;

set global slave_exec_mode=IDEMPOTENT;

set global slave_exec_mode=STRICT;

 #第一步：创建用户
GRANT select, process, super, replication slave on *.* to 'pt_checksum'@'%' identified with mysql_native_password by 'pt_checksum';
 #第二步：赋权限
 grant all on percona.* to 'pt_checksum'@'%';
 #第三步：检查一致性
 pt-table-checksum h='192.168.xx.xx', u='pt_checksum', p='pt_checksum',P=3306, -d backend --nocheck-replication-filters --replicate=percona.checksums --no-check-binlog-format
 # 修复  主从库信息输入正确
 pt-table-sync --print --replicate=percona.checksums \
 h=192.168.xx.xx, u=pt_checksum,p=pt_checksum,P=3306
 h=192.168.xx.xx, u=pt_checksum,p=pt_checksum,P=3306
 
# 注意权限
grant insert,delete,update,select on backend.* to 'pt_checksum'@'192.168.%';

# 临时修复用
sql_log_bin=0;

change master to master_user=xx, master_port=xx, master_host=xx,master_port=xx, master_log_file=xx,master_log_position=xx,relay_log_file=xx, master_auto_position=0;

change master master_auto_position=1;