MySQL 主从同步问题

MySQL 主从同步问题

建立主从的命令：

change master to master_host='192.168.1.150',master_user='admin',master_password='123.com',master_port=3306,master_log_file='binlog.000002',master_log_pos=120;
# 或者
change master to master_host='192.168.1.150',master_user='admin',master_password='123.com',master_port=3306,master_auto_position=1;

问题1：

授权主从同步的用户，报错如下：

mysql>grant select ,replication slave,replication client on . to ‘admin’@‘192.168.1.151’ identified by ‘123.com’;
ERROR 1805 (HY000): Column count of mysql.user is wrong. Expected 45, found 50. The table is probably

原因：

表结构不一致。
因为建立主从前，手动使用mysqldump方式，将8.0的MySQL库的数据备份&数据恢复到5.7上。
5.7和8.0的user表，表结构不一致，导致授权新用户，接入数据报错。

解决办法：

导入之前备份的5.7的mysql库的数据，恢复正常。
ps：
做一些数据库操作前，一定要做备份！

报错1

mysql>show slave status\G
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 12 failed executing transaction ‘53ec1d72-4962-11e9-927c-000c299fe024:293’ at master log binlog.000002, end_log_pos 2717711. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:

查看错误日志，如下：

……
2019-03-19T10:06:24.692279Z 64 [ERROR] [MY-010584] [Repl] Slave SQL for channel ‘’: Worker 11 failed executing transaction ‘53ec1d72-4962-11e9-927c-000c299fe024:294’ at master log binlog.000002, end_log_pos 2717871; Error ‘Can’t drop database ‘seiki’; database doesn’t exist’ on query. Default database: ‘seiki’. Query: ‘drop database seiki’, Error_code: MY-001008
……
2019-03-19T10:06:24.702483Z 65 [ERROR] [MY-010584] [Repl] Slave SQL for channel ‘’: Worker 12 failed executing transaction ‘53ec1d72-4962-11e9-927c-000c299fe024:293’ at master log binlog.000002, end_log_pos 2717711; Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IDENTIFIED WITH ‘mysql_native_password’ AS ‘*A424E797037BF97C19A2E88CF7891C5C203’ at line 1’ on query. Default database: ‘mysql’. Query: ‘GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘repl’@‘192.168.23.222’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘*A424E797037BF97C19A2E88CF7891C5C2038C039’’, Error_code: MY-001064

原因：

新增Mysql服务器做主库，有进行过8.0的dump文件的恢复操作，这些操作被记录到binlog上，从库拉取主库binlog，并执行的时候发生报错。

解决办法：

跳过之前相关操作：

mysql>stop slave；
mysql>reset master;              #清空从库的gtid_executed和binlog信息
mysql>SET global gtid_purged= '53ec1d72-4962-11e9-927c-000c299fe024:294';   #手动让从库认为000c299fe024:294已经执行
mysql>START SLAVE;  
或者
#直接指定主从建立的起始日志文件和pos
mysql>change master to master_host='192.168.1.150',master_user='admin',master_password='123.com',master_port=3306,master_log_file='binlog.000002',master_log_pos=120;

报错2

mysql>change master to master_host=‘192.168.1.150’,
master_user=‘admin’,
master_password=‘123.com’,
master_port=3306,
master_log_file=‘binlog.000002’,
master_log_pos=120;
ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.

原因：

建立主从连接、启用gtid状况下：
如果使用master_auto_position，就无需指定master_log_file和master_log_pos，
如果通过file和pos指定主从的起始位置，就需带上master_auto_position=0

解决办法：

使用如下命令：

mysql>change master to 
master_host='192.168.1.150',
master_user='admin',
master_password='123.com',
master_port=3306,
master_log_file='binlog.000002',
master_log_pos=120,
master_auto_position=0;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

主从建立完成。

报错3

show salve status\G，报错如下：从库请求的gtids不存在，master上purge 了相关日志：

Last_IO_Error: 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.’
Last_SQL_Errno: 0
……

原因：

主从虽然建立连接，但是从库请求的包含 GTIDS 信息的日志没了，因为主库删除了对应的binlog文件：

[root@localhost log]# ll
total 2680
drwxr-xr-x 2 mysql mysql 6 Mar 18 02:43 binlog
-rw-r----- 1 mysql mysql 177 Mar 18 02:43 binlog.000001
-rw-r----- 1 mysql mysql 2718457 Mar 19 18:38 binlog.000002
-rw-r----- 1 mysql mysql 1555 Mar 19 23:52 binlog.000003
-rw-r----- 1 mysql mysql 2008 Mar 19 23:58 binlog.000004
-rw-r----- 1 mysql mysql 525 Mar 20 00:11 binlog.000005
-rw-r----- 1 mysql mysql 150 Mar 19 23:58 binlog.index
[root@localhost log]# rm -rf ./binlog.000001 binlog.000002 binlog.000003 binlog.000004
[root@localhost log]# ll
total 8
drwxr-xr-x 2 mysql mysql 6 Mar 18 02:43 binlog
-rw-r----- 1 mysql mysql 525 Mar 20 00:11 binlog.000005
-rw-r----- 1 mysql mysql 150 Mar 19 23:58 binlog.index

解决办法：

在 master 上查看gtid信息

root@localhost|seiki_2>show global variables like ‘%gtid%’;
±---------------------------------±-------------------------------------------+
| Variable_name | Value |
±---------------------------------±-------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 53ec1d72-4962-11e9-927c-000c299fe024:1-315 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
±---------------------------------±-------------------------------------------+
8 rows in set (0.00 sec)

从库执行：

root@localhost|iris>set global gtid_purged ='53ec1d72-4962-11e9-927c-000c299fe024:1-315'; # 告诉从库  000c299fe024:1-315 已经执行过了，继续后面的。
 
Query OK, 0 rows affected (0.00 sec)