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