Description:
I found that master-info-repository & relay-log-info-repository are dynamic variables i.e. you can change those variables online on replication slave to either FILE or TABLE dynamically. I believe documentation needs to be updated to avoid this confusion here https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_maste... & https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_relay...
How to repeat:
I reproduced it on replication sandbox.
slave1 [localhost] {msandbox} (test) > SHOW GLOBAL VARIABLES LIKE '%version%';
+-------------------------+------------------------------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------------------------------+
| innodb_version | 5.6.29-rel76.2 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.29-76.2-log |
| version_comment | Percona Server (GPL), Release 76.2, Revision ddf26fe |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------------------------------+
7 rows in set (0.00 sec)
slave1 [localhost] {msandbox} (test) > SHOW GLOBAL VARIABLES LIKE '%repository%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | FILE |
| relay_log_info_repository | FILE |
+---------------------------+-------+
2 rows in set (0.00 sec)
slave1 [localhost] {msandbox} (test) > SET GLOBAL master_info_repository='TABLE';
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
slave1 [localhost] {msandbox} (test) > STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)
slave1 [localhost] {msandbox} (test) > SET GLOBAL master_info_repository='TABLE';
Query OK, 0 rows affected (0.00 sec)
slave1 [localhost] {msandbox} (test) > SET GLOBAL relay_log_info_repository='TABLE';
Query OK, 0 rows affected (0.00 sec)
slave1 [localhost] {msandbox} (test) > SHOW GLOBAL VARIABLES LIKE '%repository%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+
2 rows in set (0.00 sec)
slave1 [localhost] {msandbox} (test) > START SLAVE;
Query OK, 0 rows affected (0.02 sec)
While during change of master_info_repository and relay_log_info_repository master server continuously inserting records on test table.
slave1 [localhost] {msandbox} (test) > SELECT * FROM test.test ORDER BY id DESC LIMIT 5;
+------+
| id |
+------+
| 173 |
| 172 |
| 171 |
| 170 |
| 169 |
+------+
5 rows in set (0.00 sec)
slave1 [localhost] {msandbox} (test) > SELECT * FROM test.test ORDER BY id DESC LIMIT 5;
+------+
| id |
+------+
| 174 |
| 173 |
| 172 |
| 171 |
| 170 |
+------+
5 rows in set (0.00 sec)
slave1 [localhost] {msandbox} (test) > SELECT * FROM test.test ORDER BY id DESC LIMIT 5;
+------+
| id |
+------+
| 175 |
| 174 |
| 173 |
| 172 |
| 171 |
+------+
5 rows in set (0.00 sec)
slave1 [localhost] {msandbox} (test) > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rsandbox
Master_Port: 22494
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 86769
Relay_Log_File: mysql-relay.000005
Relay_Log_Pos: 11413
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.
.
slave1 [localhost] {msandbox} (test) > SELECT * FROM test.test ORDER BY id DESC LIMIT 5;
+------+
| id |
+------+
| 200 |
| 199 |
| 198 |
| 197 |
| 196 |
+------+
5 rows in set (0.00 sec)
slave1 [localhost] {msandbox} (test) > SELECT * FROM mysql.slave_relay_log_info;
+-----------------+----------------------+---------------+------------------+----------------+-----------+-------------------+----+
| Number_of_lines | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos | Sql_delay | Number_of_workers | Id |
+-----------------+----------------------+---------------+------------------+----------------+-----------+-------------------+----+
| 7 | ./mysql-relay.000005 | 11623 | mysql-bin.000001 | 86979 | 0 | 0 | 1 |
+-----------------+----------------------+---------------+------------------+----------------+-----------+-------------------+----+
1 row in set (0.00 sec)
slave1 [localhost] {msandbox} (test) > SELECT * FROM mysql.slave_master_info;
+-----------------+------------------+----------------+-----------+-----------+---------------+-------+---------------+-------------+--------+------------+----------+------------+---------+------------------------+-----------+------+--------------------+--------------------------------------+-------------+---------+-------------+-----------------------+
| Number_of_lines | Master_log_name | Master_log_pos | Host | User_name | User_password | Port | Connect_retry | Enabled_ssl | Ssl_ca | Ssl_capath | Ssl_cert | Ssl_cipher | Ssl_key | Ssl_verify_server_cert | Heartbeat | Bind | Ignored_server_ids | Uuid | Retry_count | Ssl_crl | Ssl_crlpath | Enabled_auto_position |
+-----------------+------------------+----------------+-----------+-----------+---------------+-------+---------------+-------------+--------+------------+----------+------------+---------+------------------------+-----------+------+--------------------+--------------------------------------+-------------+---------+-------------+-----------------------+
| 23 | mysql-bin.000001 | 75639 | 127.0.0.1 | rsandbox | rsandbox | 22494 | 60 | 0 | | | | | | 0 | 1800 | | 0 | 00022494-1111-1111-1111-111111111111 | 86400 | | | 0 |
+-----------------+------------------+----------------+-----------+-----------+---------------+-------+---------------+-------------+--------+------------+----------+------------+---------+------------------------+-----------+------+--------------------+--------------------------------------+-------------+---------+-------------+-----------------------+
1 row in set (0.00 sec)
