"MySQL case" Error.log warning:if a crash happens thisconfiguration does not guarantee that the relay LO

1.1.1. If A crash happens thisconfiguration does not guarantee that the relay log info would be consistent

"Environment description"

msyql5.6.14

"Error Message"

When MySQL slave starts, a warning warning appears in Error.log:

[Warning] Slave sql:if A crash happensthis configuration does not guarantee that the relay log info would beconsistent, Error_code: 0

This warning information has no effect on MySQL and MySQL replication functionality.

"Cause of error"

The MySQL5.6 version began to support the writing of the contents of Master.info and relay-log.info to the table in the MySQL library.

Master.info--> Mysql.slave_master_info

relay-log.info--> MySQL. Slave_relay_log_info

At the same time in the MySQL5.6 version, added Slave crash-safe replication function, in order to ensure that MySQL replication can crash-safe,slave_master_ The info and Slave_relay_log_info tables must use the transactional storage engine (InnoDB), and do not attempt to manually modify the contents of the two tables. At the same time, slave also to turn on relay_log_recovery function.

"Workaround"

Set the value of Master_info_repository and relay_log_info_repository to table, while the Relay_log_recovery function is turned on.

Modify the/ETC/MY.CNF configuration file to add the following 3 items:

Master-info-repository=table # can be dynamically modified using set global

Relay-log-info-repository=table # can be modified dynamically using Setglobal

Relay-log-recovery=1 # read-only parameter, must be modified my.cnf restart MySQL

Then restart the MySQL instance.

Resources

L Master.info and Relay-log.info logs

Two logs are created on the replicated slave node, master.infor and Relay-log.info, respectively, in the DataDir directory. In MySQL5.6 and subsequent versions, you can specify the table to be written to MySQL or write to a file by setting the master-info-file and relay-log-info-file parameters.

These two files contain information similar to the Showslave status output, which reads the Master.info and Relay-log.info files when slave is started to determine the information that reads binary log from master and reads relay log.

The slave I/O thread is responsible for updating the maintenance Master.info file,

Master.info	Mysql.slave_master_info	SHOW SLAVE STATUS Column	Description
1	Number_of_lines	[None]	Number of lines in the file
2	Master_log_name	Master_log_file	The name of the master binary log currently being read from the master
3	Master_log_pos	Read_master_log_pos	The current position within the master binary log that has been read from the master
4	Host	Master_host	The host name of the master
5	User	Master_user	The user name used to connect to the master
6	User_password	Password (not shown by SHOW SLAVE STATUS)	The password used to connect to the master
7	Port	Master_port	The network port used to connect to the master
8	Connect_retry	Connect_retry	The period (in seconds) that the slave would wait before trying to reconnect to the master
9	Enabled_ssl	Master_ssl_allowed	Indicates whether the server supports SSL connections
10	Ssl_ca	Master_ssl_ca_file	The file used for the Certificate Authority (CA) Certificate
11	Ssl_capath	Master_ssl_ca_path	The path to the Certificate authority (CA) certificates
12	Ssl_cert	Master_ssl_cert	The name of the SSL certificate file
13	Ssl_cipher	Master_ssl_cipher	The list of possible ciphers used in the handshake for the SSL connection
14	Ssl_key	Master_ssl_key	The name of the SSL key file
15	Ssl_verify_server_cert	Master_ssl_verify_server_cert	Whether to verify the server certificate
16	Heartbeat	[None]	Interval between replication heartbeats, in seconds
17	Bind	Master_bind	Which of the slave ' s network interfaces should be used for connecting to the master
18	Ignored_server_ids	Replicate_ignore_server_ids	The number of server IDs to is ignored, followed by the actual server IDs
19	Uuid	Master_uuid	The master ' s unique ID
20	Retry_count	Master_retry_count	Maximum number of reconnection attempts permitted Added in MySQL 5.6.1)

The slave SQL thread is responsible for maintaining the Relay-log.info file, relay-log.info the number of records in the file and the number of seconds of replication delay in MySQL5.6.

Relaylog.info	Slave_relay_log_info	SHOW SLAVE STATUS	Description
1	Number_of_lines	[None]	Number of lines in the file or rows in the table
2	Relay_log_name	Relay_log_file	The name of the current relay log file
3	Relay_log_pos	Relay_log_pos	The current position within the relay log file; Events up to this position has been executed on the slave database
4	Master_log_name	Relay_master_log_file	The name of the master binary log file from which the events in the relay log file were read
5	Master_log_pos	Exec_master_log_pos	The equivalent position within the master ' s binary log file of events that has already been executed
6	Sql_delay	Sql_delay	The number of seconds that the slave must lag the master

Set the Master_info_repository and Relay_log_info_repository parameters:

sql> stop Slave;

sql> set global master_info_repository=table;

sql> set global relay_log_info_repository=table;

Sql> Show variables like '%repository ';

+--------------------------------------+---------+

| variable_name | Value |

+--------------------------------------+---------+

| Master_info_repository | TABLE |

| Relay_log_info_repository | TABLE |

+--------------------------------------+----------+

To view a log read to master:

Sql> select * from Slave_master_info;

To view slave's relay log application:

Sql> select * from Slave_relay_log_info;

Attention:
Master.info and Relay-log.info data will have a certain delay, depending on the MySQL to slave information to the hard disk time, if you want to get slave real-time information, you can query Slave_master_info and slave_ Relay_log_info the table, or perform a show slave status View.

L Master-info-repository

master-info-repository={FILE | TABLE}, the default value is file, which is used to set the slave node to write the master information to a physical file or to MySQL. The Slave_master_info table.

If set to file, which is also the default setting for MySQL, slave will record the master information in the Datadir/master.info file, starting with the MySQL5.6 version, we recommend using table mode.

L Relay-log-info-reposity

Relay-log-info-reposity=file|table, the default value is file, which is used to set the slave node to write relay-log.info information to the datadir/ Relay-log.info file or MySQL. slave_relay_log_info table.

If set to file, which is also the default setting for MySQL, slave will record the master information in the Datadir/master.info file, starting with the MySQL5.6 version, we recommend using table mode.

L Relay-log-recovery

Relay-log-recover=0|1, the default value is 0 does not turn on, this parameter is used to set whether to turn on the automatic recovery function of relay log. When the Relay_log_recovery function is turned on, the slave database will ignore the relay log that was not executed when it was started, and it will reconnect master to get relay log for recovery.

When the slave occurs, it is recommended to turn on this function, can effectively avoid slave executed relay log inside the corruption record.

If the Relay_log_recovery function is turned on, the relay_log_info_reposity must be set to table mode at the same time.