mysql 基于GTID复制

 1、主库创建同步账号

#创建复制账号
CREATE User 'sync1' @'%' identified by 'Sync1';
#授权复制账号权限
GRANT REPLICATION SLAVE ON *.* TO 'sync1'@'%' identified by 'Sync1';
#https://www.cnblogs.com/tianshupei88/p/5075367.html

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;

 删除用户修改密码

DROP USER '用户名1'@'%';
flush privileges;

update user set authentication_string = password("新密码") where user="用户名1";
flush privileges;

 

ssl

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

从库
stop slave;

change master to master_host='IP',
master_user='用户名',
master_password='密码',
master_port=端口,
master_connect_retry=60,
MASTER_AUTO_POSITION=1,
master_ssl=1,
master_ssl_ca='E:\\mysql-5.7.33-winx64\\sslClent\\ca.pem',
master_ssl_cert='E:\\mysql-5.7.33-winx64\\sslClent\\client-cert.pem',
master_ssl_key='E:\\mysql-5.7.33-winx64\\sslClent\\client-key.pem';

#master_log_pos=17338, #GTID复制不能设置这个选项

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

 

 

还原的两个问题：

1、ERROR 1418 (HY000) at line 1186: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决方法：

set global log_bin_trust_function_creators = 1;

2、

[SQL]

CREATE DEFINER=`root`@`%` FUNCTION `AddMinutes`(actualDateTime datetime, minutesToAdd int) RETURNS datetime
BEGIN
RETURN DATE_ADD(actualDateTime, INTERVAL minutesToAdd MINUTE);
END
;
[Err] 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决：

a、在 MySQL 数据库中执行以下语句，临时生效，重启后失效

set global log_bin_trust_function_creators=TRUE;

b、在配置文件 my.ini 的 [mysqld] 配置，永久生效

log_bin_trust_function_creators=1

 

3、The user specified as a definer ('root'@'%') does not exist

解决：grant all privileges on *.* to root@"%" identified by ".";

 

 

 

 

这个是主从复制中碰到的问题：

 

Last_IO_Errorno Last_IO_Error
2026 error connecting to master '' - retry-time: 30 retries: 1
原因: master_ssl_CA_File 路径不对window 设置时使用双斜杠\\

Last_IO_Errorno Last_IO_Error
1593 Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
原因：主从auto.cnf的有uuid一样，生成一个修改一下 select uuid() as uuid1

Last_IO_Errorno Last_IO_Error
1236 Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replicated to the slave. Suggest to replicate any transactions that master has rolled back from slave to master, and/or commit empty transactions on master to account for transactions that have been'
原因：

主库
show global variables like '%gtid%'

从库
show global variables like '%gtid%'
查询已经执行过的gtid即gtid_executed

stop slave;
reset slave;
reset master;

#在从库上设置gtid_purged

该值有两个来源，一是在主库上查询的gtid_purged，二是在从库上查询的已经执行过的gtid_executed值（本机的就不需要，主库上gtid）

注意：一定记得加上从库上已经执行过的gtid，若只设置了主库上的gtid_purged，此时从库会重新拉取主库上所有的二进制日志文件，同步过程会出现其他错误，导致同步无法进行
set @@global.gtid_purged='主库的gtid_executed';