mysql 基于GTID复制

 1、主库创建同步账号

#创建复制账号
CREATE User 'sync1' @'%' identified by 'Sync1';
#授权复制账号权限
GRANT REPLICATION SLAVE ON *.* TO 'sync1'@'%' identified by 'Sync1';

2、主库 my.ini

bin_log=/log/mysql-bin
server_id=11
gtid_mode=on #启用gitd功能

enforce-gtid-consistency=1 #开启强制GTID一致性
log-slave-updates=on #5.7中没有5.6、5.5中配制

3、从库my.ini

server_id=12
relay_log=/log/relay_log
gtid_mode=on
enforce-gtid-consistency=on
#下面建议参数
log-slave-updates=on #5.7中没有5.6、5.5中配制
read_only=on
master_info_repository=TABLE
relay_log_info_repository=TABLE

4、初始化从服务器数据

主库备份

mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases -uroot -ptest1  --set-gtid-purged=off >all.sql

或xtarbackup --slave-info

从库恢复

mysql --port=4506 -uroot --password=test1  < D:\SOFTWARE\mysql-5.7.33-winx64\bin\all2.sql

5、启动基于GTID的复制

change master to master_host='localhost',
master_port=7506,
master_user='sync1',
master_password='Sync1',
master_auto_position=1;

注意：如果是slave 是启动则会报下面的错误

ERROR 3081 (HY000): This operation cannot be performed with running replication threads; run STOP SLAVE FOR CHANNEL '' first

解决方法：

stop slave;

启动复制

start slave;
show slave status;

ssl

grant replication slave,replication client on *.* to 'sync1'@'%' identified by 'Sync1' require ssl;
flush privileges;


stop slave;
change master to master_host='49.234.97.180',
master_user='scm1', master_password='Scm20191101!',
master_port=6549,
master_ssl_cert='D:\data1\180\client-cert.pem',
master_ssl_key='D:\data1\180\client-key.pem',
master_log_pos=17338,
master_connect_retry=30;

stop slave;
change master to master_host='localhost',
master_port=7506,
master_user='sync1',
master_password='Sync1',
master_auto_position=1,
master_ssl_cert='D:\data1\180\client-cert.pem',
master_ssl_key='D:\data1\180\client-key.pem',
master_log_pos=17338,
master_connect_retry=30;

start slave;
show slave status;


select version(), @@sql_mode;
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by


https://blog.csdn.net/zx1293406/article/details/103401803
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


[Err] 1872 - Slave failed to initialize relay log info structure from the repository
https://blog.51cto.com/yujianglei/1729129