mysql.gtid slave pos_MySQL主从报错解决：Table ‘mysql.gtid_slave_pos’ doesn’t exist

给内部一个数据库做异地热备，热备部分采用了 MariaDB 的 galera 集群模式。然后挑选其中一台作为 Slave 和深圳主集群做主从同步。

主集群是老环境，用的版本还是是 MySQL 5.5.13。用常规办法创建主从同步

MariaDB [(none)]>change master to master_host='192.168.1.100',master_user='rpl',master_password='rpl@201809',master_log_file='mysql-bin.001091',MASTER_LOG_POS=137962110,master_connect_retry=30;

MariaDB [(none)]>start slave;

结果有如下报错：

MariaDB [(none)]> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.100

Master_User: rpl

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: mysql-bin.001093

Read_Master_Log_Pos: 77139171

Relay_Log_File: udb158-relay-bin.000002

Relay_Log_Pos: 237764027

Relay_Master_Log_File: mysql-bin.001091

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1146

Last_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist

Skip_Counter: 0

Exec_Master_Log_Pos: 375725743

Relay_Log_Space: 2086663884

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 105914

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1146

Last_SQL_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist

Replicate_Ignore_Server_Ids:

Master_Server_Id: 15410

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

1 row in set (0.00 sec)

错误信息为：Last_SQL_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist

搜了下资料，大部分说是没有执行 mysql_upgrade 导致的，不过我们这边的 MariaDB 是 Docker 跑的，而且用了很长时间了，理论上应该是没问题的才对。

既然提示没有这个表：Table 'mysql.gtid_slave_pos' doesn't exist，那我就创建一个吧！

从网上找到这个建表语句：

CREATE TABLE `gtid_slave_pos` (

`domain_id` int(10) unsigned NOT NULL,

`sub_id` bigint(20) unsigned NOT NULL,

`server_id` int(10) unsigned NOT NULL,

`seq_no` bigint(20) unsigned NOT NULL,

PRIMARY KEY (`domain_id`,`sub_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';

在作为 Slave 的 MariaDB 上执行，然后重启 slave 后问题解决，过程如下：

MariaDB [(none)]> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.100

Master_User: rpl

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: mysql-bin.001093

Read_Master_Log_Pos: 77139171

Relay_Log_File: udb158-relay-bin.000002

Relay_Log_Pos: 237764027

Relay_Master_Log_File: mysql-bin.001091

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1146

Last_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist

Skip_Counter: 0

Exec_Master_Log_Pos: 375725743

Relay_Log_Space: 2086663884

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 105914

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1146

Last_SQL_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist

Replicate_Ignore_Server_Ids:

Master_Server_Id: 15410

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

1 row in set (0.00 sec)

MariaDB [(none)]> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [mysql]> CREATE TABLE `gtid_slave_pos` (

-> `domain_id` int(10) unsigned NOT NULL,

-> `sub_id` bigint(20) unsigned NOT NULL,

-> `server_id` int(10) unsigned NOT NULL,

-> `seq_no` bigint(20) unsigned NOT NULL,

-> PRIMARY KEY (`domain_id`,`sub_id`)

-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';

Query OK, 0 rows affected (0.01 sec)

MariaDB [mysql]> stop slave;

Query OK, 0 rows affected (0.04 sec)

MariaDB [mysql]> start slave;

Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Queueing master event to the relay log

Master_Host: 192.168.1.100

Master_User: rpl

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: mysql-bin.001093

Read_Master_Log_Pos: 1059879280

Relay_Log_File: udb158-relay-bin.000002

Relay_Log_Pos: 390833600

Relay_Master_Log_File: mysql-bin.001091

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 528795316

Relay_Log_Space: 3069404437

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 101616

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 15410

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

1 row in set (0.00 sec)

MariaDB [mysql]>