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.