[MySQL Case] Warning of error. log: 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 will be consistent

[Environment description]

Msyql5.6.14

[Error message]

When mysql slave is started, the following Warning occurs in error. log:

[Warning] Slave SQL: If a crash happensthis configuration does not guarantee that the relay log info will beconsistent, Error_code: 0

This Warning information has no impact on the Mysql and MySQL replication functions.

[Error cause]

MySQL5.6 began to support writing master.info and relay-log.info content to the mysql database table,

Master.info --> mysql. slave_master_info

Relay-log.info --> mysql. slave_relay_log_info

In MySQL5.6Slave crash-safe replicationFunction. To ensure that mysql replication can be crash-safe, the slave_master_info and slave_relay_log_info tables must use the transaction storage engine (InnoDB). Do not manually modify the content of the two tables. In addition, Slave must enable the relay_log_recovery function.

[Solution]

Set master_info_repository and relay_log_info_repository to TABLE, and enable relay_log_recovery.

Modify the/etc/my. cnf configuration file and add the following three items:

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

Relay-log-info-repository = table # Can be dynamically modified using setglobal

Relay-log-recovery = 1 # Read-Only parameter. You must modify my. cnf to restart mysql.

Then restart the mysql instance.

 

[References]

LMaster.info and relay-log.info logs

Two logs, master. infor and relay-log.info, are created on the replicated Slave node in the datadir directory. In MySQL5.6 and later versions, you can set the master-info-file and relay-log-info-file parameters to specify the tables to be written to mysql or the files to be written.

These two files contain information similar to the showslave status output, when slave is started, it will read master.info and the relay-log.info file to determine to read binary log from the master and read relay log information.

The Slave I/O thread is responsible for updating and maintaining the 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 have 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 will 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 shocould be used for connecting to the master
18	Ignored_server_ids	Replicate_Ignore_Server_Ids	The number of server IDs to be 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 SQL thread of the Slave is responsible for maintaining the relay-log.info file, relay-log.info in MySQL5.6 contains the number of records in the file and the number of seconds of replication latency.

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 have 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 have already been executed
6	SQL _delay	SQL _Delay	The number of seconds that the slave must lag the master

 

Set 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 |

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

 

View the logs read from the Master:

SQL> select * from slave_master_info;

View the Relay log application of Slave:

SQL> select * from slave_relay_log_info;

 

Note:
Master.info and relay-log.info data will have a certain delay, depending on the mysql flave slave information to the hard disk time, if you want to obtain the real-time slave information, you can query the slave_master_info and slave_relay_log_info table, or run show slave status to view the status.

 

L master-info-repository

Master-info-repository = {FILE | TABLE}. The default value is FILE. This parameter is used to set whether to write master information to the physical FILE or to the mysql. slave_master_info TABLE on the Slave node.

If it is set to FILE, which is also the default setting of mysql, Slave will record the master information in the datadir/master.info FILE, starting from mysql, we recommend that you use TABLE mode.

 

L relay-log-info-reposity

Relay-log-info-reposity = file | table, default value is FILE, this parameter 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 it is set to FILE, which is also the default setting of mysql, Slave will record the master information in the datadir/master.info FILE, starting from mysql, we recommend that you use TABLE mode.

 

L relay-log-recovery

Relay-log-recover = 0 | 1. The default value is 0. This parameter is used to set whether to enable automatic recovery of relay log. When the relay_log_recovery function is enabled, when the slave database is started, it will ignore the unexecuted relay log and reconnect to the master to obtain the relay log for recovery.

When a Server Load balancer instance goes down, we recommend that you enable this function to effectively prevent the Server Load balancer from executing the commit error records in the relay log.

If relay_log_recovery is enabled, you must set relay_log_info_reposity to TABLE mode.