mysql5.7 GTID错误Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

mysql数据库技巧

这个办法会让从库重新执行所以主库的binlog,是个笨办法,但是保障了数据是完整的。


主库查看master gtid_purged
show global variables like '%gtid%';
 找到gtid_purged的值
从库执行:
复制代码
mysql> stop slave;
Query OK, 0 rows affected (0.18 sec)
mysql> reset slave;
Query OK, 0 rows affected (1.29 sec)
mysql> reset master;
Query OK, 0 rows affected (1.18 sec)
mysql>  set @@global.gtid_purged='gtid_purged的值';
Query OK, 0 rows affected (2.12 sec)
mysql> change master to master_host='MASTER_IP',master_port=PORT,master_user='USERNAME',master_password='PASSWORD',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (5.55 sec)
mysql> start slave;
Query OK, 0 rows affected (0.40 sec)
mysql> show slave status \G;

下面是my.cnf的配置,有时候需要把一些错误掠过:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
server-id = 14

gtid_mode = on
enforce_gtid_consistency = on
slave-skip-errors = 1062,1053,1146,1213,1264,1205,1396,1032


datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

innodb_buffer_pool_size=32G
innodb_buffer_pool_instances=16
innodb_flush_log_at_trx_commit = 2

binlog-do-db = youdatabase


binlog-ignore-db = mysql
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys

character-set-server=utf8
max_connections=2048

validate_password_policy=0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
default-character-set=utf8