MySQL Relay log parameter summary

In the main master or master copy, MySQL will generate the corresponding relay log under the home directory, this document summarizes the definition and explanation of these related parameters

1. What is relay log

The relay log, like the binary log, consists of a set of numbered files containing events, describe database changes, and an index file, the contains the names of all used relay log files.

The term "Relay log file" generally denotes an individual numbered file containing database events. The term "relay log" collectively denotes the set of numbered relay log files plus the index file

Source:

Understand: Relay log many aspects are similar to binary log, the difference is: from the server I/O thread to read the primary server's binary log to the local file from the server, then the SQL thread will read the contents of the Relay-log log and apply to the slave server.

2, relay log of the relevant parameters,

Through the statement: Show variables like '%relay% ', see all the relevant parameters of the first spine relay

Mysql> Show variables like '%relay% ';

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

| variable_name | Value |

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

| Max_relay_log_size | 0 |

| Relay_log | |

| Relay_log_index | |

| Relay_log_info_file | Relay-log.info |

| Relay_log_purge | On |

| Relay_log_recovery | OFF |

| Relay_log_space_limit | 0 |

| Sync_relay_log | 0 |

| Sync_relay_log_info | 0 |

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

9 rows in Set (0.08 sec)

2.1 Max_relay_log_size: Marks the maximum allowable value of relay log, if the value is 0, the default value is Max_binlog_size (1G), and if not 0, Max_relay_log_size is the largest relay_ log file size;

2.2 Relay_log: Defines the location and name of the Relay_log, if the value is empty, the default location is in the directory of the data file, the file name is host_name-relay-bin.nnnnnn (by default, relay log file names The form host_name-relay-bin.nnnnnn in the data directory);

2.3 Relay_log_index: With Relay_log, define the location and name of the Relay_log;

2.4 Relay_log_info_file: Sets the location and name of the Relay-log.info (Relay-log.info records the recovery location of the binary_log of master and the location of Relay_log)

2.5 Relay_log_purge: If automatic emptying is no longer required when relaying logs. The default value is 1 (enabled).

2.6 Relay_log_recovery: When the slave from the library down, if the relay-log damage, causing some of the relay log is not processed, then automatically discard all the Relay-log, and re-get the log from master. This guarantees the integrity of the relay-log. By default, this feature is turned off and the value of Relay_log_recovery is set to 1 o'clock, and it is recommended to turn on the slave from the library.

2.7 Relay_log_space_limit: To prevent the trunk log from filling the disk, set the maximum trunk log limit here. But this setting exists the main library crashes, from the library trunk log is not the case, not the last resort, not recommended to use;

2.8 Sync_relay_log: This parameter is the same as Sync_binlog, when set to 1 o'clock, the slave I/O thread receives the Binlog log sent by master every time it is written to the system buffer, and then brushes in the relay log relay log. This is the safest, because at the time of the crash, you will lose at most one transaction, but it will cause a large amount of I/O to the disk. When set to 0 o'clock, it is not immediately brushed into the trunk log, but is determined by the operating system when to write, although the security is reduced, but reduced a lot of disk I/O operations. This value is 0 by default and can be modified dynamically, with default values recommended.

2.9 Sync_relay_log_info: This parameter is the same as the Sync_relay_log parameter, when set to 1 o'clock, the slave I/O thread receives the Binlog log sent by master every time it is written to the system buffer. Then swipe into the relay-log.info, which is the safest, because at the time of the crash, you will lose at most one transaction, but it will cause a large amount of I/O to the disk. When set to 0 o'clock, it is not immediately brushed into the relay-log.info, but is determined by the operating system when to write, although the security is reduced, but reduced a lot of disk I/O operations. This value is 0 by default and can be modified dynamically, with default values recommended.

3, Summary: The above is just a simple introduction of the role of each parameter, the specific settings of these parameters need to be set according to the actual system situation of each user;

=======================

MySQL Relay_log_info_repository and Master_info_repository

2. Relay_log_purge = 1
6. Relay_log_recovery = 1
10. These two are enabled Relaylog automatic repair function, avoid due to the network and other external causes log corruption, master and slave stop.
14. Master_info_repository = TABLE
18. Relay_log_info_repository = TABLE
22. These two parameters will save Master.info and relay.info in the table, the default is the MyISAM engine, the official recommended
26. 　　Alter table Slave_master_info Engine=innodb;
30. 　　Alter table Slave_relay_log_info Engine=innodb;
34. 　　Alter table Slave_worker_info Engine=innodb;
38. Change the InnoDB engine to prevent the table from being repaired after it is damaged.
44. 3. Set in MY.CNF
46. Relay_log_info_repository = TABLE
48. Master_info_repository = TABLE
50. Relay_log_recovery = on
54. Mysql> Show variables like '%relay_log_info_repository% ';
56. +---------------------------+-------+  
58. | variable_name | Value |
60. +---------------------------+-------+  
62. | Relay_log_info_repository | FILE |
64. +---------------------------+-------+  
66. 1 row in Set (0.00 sec)
70. Mysql> Show variables like '%master_info_repository% ';
72. +------------------------+-------+  
74. | variable_name | Value |
76. +------------------------+-------+  
78. | Master_info_repository | FILE |
80. +------------------------+-------+  
82. 1 row in Set (0.00 sec)
86. mysql> desc Mysql.slave_master_info

MySQL Relay log parameter summary