MySQL 复制相关问题处理(细节)

                           

部分参数设置 my.cnf

  server-id = 1
  log_bin = mysql-bin
  binlog_format = row
  expire_logs_days = 7
  sync_binlog = 1
  sync_relay_log = 10000
  sync_relay_log_info = 10000
  relay_log_purge = 1
  relay_log_recovery = 1
  log_slave_updates = 1
  gtid_mode = on
  enforce_gtid_consistency = on #禁用create table...select 和 create temporarytable语句
  master_info_repository=TABLE
  relay_log_info_repository=TABLE
  #skip-slave-start=1
设置同步
#创建复制专用账号
   create user repluser@'192.168.1.%' IDENTIFIED by 'replpassword';
   grant replication slave on *.* to repluser@'192.168.1.%';
   flush privileges;
 
#slave中设置同步
change master to
    master_host='192.168.1.82',
    master_port =3400,
    master_user='repluser',
    master_password='replpassword',
    master_auto_position=1;
 
start slave;

相关信息说明

#信息查看
show master status\G;
show slave status\G;
show global variables like 'gtid%';
select * from mysql.gtid_executed;
 
#其他信息说明！
# reset master;     #删除binglog日志文件,如用于第一次主从搭建;slave执行不影响;
# reset slave;      #删除relaylog日志文件,删除master.info和relay-log.info文件信息(master同步位置将丢失)
# reset slave all;  #同 reset slave,此外change master 的连接信息也删除;
# flush logs;       #新增并使用新的binlog文件,编号增加;(也影响 relaylog、slow log、general log)
# show binary logs; #查看binlog文件数及大小
# purge master logs to 'mysql-bin.000004'; #删除指定binglog日志文件,日志编号不变

slave中查看：IO线程正常，SQL线程停止，SQL线程应用有误，Retrieved_Gtid_Set表示IO已同步事务，Executed_Gtid_Set表示SQL已执行的事务，可知道跳过下一个是哪个事务，Slave执行跳过下一个事务

stop slave;
#set global sql_slave_skip_counter = 1;
set gtid_next = '1d5d6816-1be2-11ea-b08e-00155d016616:2';
begin; commit;
set gtid_next = automatic;
start slave;
show slave status\G;

误操作对主实例执行 reset master 解决方法

#错误日志
[Note] Error reading relay log event for channel '': slave SQL thread was killed
[Note] Slave SQL thread for channel '' exiting, replication stopped in log 'FIRST' at position 0
[Warning] 
[Note] Slave I/O thread for channel '': connected to master 'repluser@192.168.1.82:3400',replication started in log 'FIRST' at position 4
[Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
[Note] Slave SQL thread for channel '' initialized, starting replication in log 'FIRST' at position 0, relay log './redhat82-relay-bin.000001' position: 4
[ERROR] Error reading packet from server for channel '':  (server_errno=1236)
[ERROR] Slave I/O for channel '': , Error_code: 1236
[Note] Slave I/O thread exiting for channel '', read up to log 'FIRST', position 4
 
解决:(master若有数据插入,也将重新开始同步)
reset master;
stop slave;
reset slave;
start slave;
 
或者：
reset master;
stop slave;
reset slave all;
change master to
    master_host='192.168.1.82',
    master_port =3400,
    master_user='repluser',
    master_password='replpassword',
    master_auto_position=1;
start slave;
并行复制设置

启用DB级别的并行复制(默认 slave_parallel_type = DATABASE)
show processlist;
show global variables like '%parallel%';
 
stop slave;
set global slave_parallel_workers=4;
start slave;
 
 
启用多线程复制：(组提交的信息存放在GTID中)
show processlist;
show global variables like '%parallel%';
 
stop slave;
set global slave_parallel_type = 'LOGICAL_CLOCK';
set global slave_parallel_workers = 8;
set global slave_preserve_commit_order = 0;
start slave;