MySQLをアップデートしたらレプリケーションにエラーが出て動かなくなった。

現象

MySQL 5.7.13から5.7.14にアップデートしたところ、次のようなエラーが出てレプリケーションが動かなくなった。

2016-09-17T05:06:04.346580Z 0 [ERROR] Info table has a problem with its key field(s). Table 'mysql.slave_master_info' expected field #23 to be 'Channel_name' but found 'Tls_version' instead.
2016-09-17T05:06:04.346679Z 0 [ERROR] Error in checking mysql.slave_master_info repository info type of TABLE.
2016-09-17T05:06:04.346798Z 0 [ERROR] Error creating master info: Error checking repositories.
2016-09-17T05:06:04.346869Z 0 [ERROR] Failed to create or recover replication info repository.
2016-09-17T05:06:04.346902Z 0 [ERROR] Failed to create or recover replication info repositories.

リスタートしても一緒。mysql_upgradeを実行しても変わらず。

原因

MySQL 5.7.14のmysql_upgradeの不具合により、mysql.slave_master_infoテーブルが適切に更新されないため。

対策

mysqlを管理者権限で起動し、次のようにテーブルをリファインする。

USE mysql;

ALTER TABLE `mysql`.`slave_master_info`
CHANGE COLUMN `Channel_name` `Channel_name` CHAR(64) NOT NULL DEFAULT '' COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication' AFTER `Enabled_auto_position`;

CREATE TABLE `new_slave_master_info` (
 `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
 `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
 `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
 `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'The host name of the master.',
 `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
 `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
 `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
 `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
 `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
 `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
 `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
 `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
 `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
 `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
 `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
 `Heartbeat` float NOT NULL,
 `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
 `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
 `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
 `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
 `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
 `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
 `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
 `Channel_name` char(64) NOT NULL COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication',
 `Tls_version` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Tls version',
 PRIMARY KEY (`Channel_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

INSERT INTO `new_slave_master_info` SELECT * FROM slave_master_info;

DROP TABLE `slave_master_info`;

RENAME TABLE `new_slave_master_info` to `slave_master_info`;

cf. mysql_upgrade of PS 5.7.14 doesn’t update the “slave_master_info” schema (Percona Server bug report)

おまけのトラブル

上記の解決策を試す前に、不具合報告ページの最後にある「mysql.slave_master_infoテーブルを削除してmysql_upgradeを実行すれば良い」という安易な解法を試してしまい、その結果、マスター側で次のようなエラーが出るように。

2016-11-29T11:47:50.604968Z 1 [ERROR] Error reading packet from server for channel '': 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. (server_errno=1236)
2016-11-29T11:47:50.605017Z 1 [ERROR] Slave I/O for channel '': 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.', Error_code: 1236

まあ、そうだよね…。

仕方がないので、レプリケーションを再構築。

マスター側でdumpファイルを作成。

# mysql -u root -p
Enter Password:
mysql> show master status\G
*************************** 1. row ***************************
 File: mysql-bin.000023
 Position: 1288527
 Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 3e7e83f4-14fb-11e6-9cd7-525406009008:1-1947619
1 row in set (0.00 sec)
mysql> quit;
# mysqldump --all-databases --single-transaction --flush-logs --triggers --routines --events --add-drop-table -p > ~/master.sql

スレーブ側でレプリケーションをいったん停止し、マスターをリセット。

# mysql -u root -p
Enter Password:
mysql> stop slave;
mysql> reset slave;
mysql> reset master;
mysql> quit;
#

スレーブ側にデータベースをリストア。
```
# mysql -p < master.sql
```

レプリケーション再同期。

# mysql -u root -p
Enter Password:
mysql> change master to
    -> master_host=’********’,
    -> master_user=’*********’,
    -> master_password=’*********’,
    -> master_log_file=’mysql-bin.000003”,
    -> master_log_pos=2376;

MASTER_LOG_FILE および MASTER_LOG_POS は、マスター側の「show master status」で確認した情報をもとに入力。

スレーブ側のレプリケーションを再開。
```
mysql> start slave
```
スレーブ側でレプリケーションが正常に動いているか確認。
```
mysql> show slave status\G
```
ここで、次のパラメータがふたつとも「Yes」なら問題なし。
- Slave_IO_Running
- Slave_SQL_Running

cf. MySQLレプリケーションをマスター無停止で再構築 (Skyarch Broadcasting)
cf. “@@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.”というエラーが出たら (Sawara.me)