Mysql relay log parameter Summary

Mysql relay log parameter Summary

MySQL generates a relay log under the home Directory when performing master-slave replication or master-slave replication. This document summarizes the definitions and explanations of these parameters.

1. What is relay log?

The relay log, like the binary log, consists of a set of numbered files containing events that describe database changes, and an index file that 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: http://dev.mysql.com/doc/refman/5.5/en/slave-logs-relaylog.html>

Understanding: relay log is similar to binary log in many aspects. The difference is that the binary log of the master server is read from the server I/O thread and recorded to the local file of the slave server, the SQL thread then reads the relay-log Content and applies it to the slave server.

2. relay log parameters,

Use the show variables like '% relay %' statement to view all relevant parameters of the first backbone 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: Mark the maximum value allowed by relay log. If this value is 0, the default value is max_binlog_size (1G). If it is not 0, max_relay_log_size is the maximum relay_log file size;

2.2 relay_log: defines the location and name of relay_log, if the value is null, 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 have the form host_name-relay-bin.nnnnnn in the data directory );

2.3 relay_log_index: Same as relay_log, it defines the location and name of relay_log;

2.4 relay_log_info_file: Set the location and name of the relay-log.info (the relay-log.info records the recovery location of the binary_log of the MASTER and the location of relay_log)

2.5 relay_log_purge: whether to automatically clear the relay logs that are no longer needed. The default value is 1 (Enabled ).

2.6 relay_log_recovery: when the slave database goes down, if the relay-log is damaged and some relay logs are not processed, all unexecuted relay-logs will be automatically abandoned, obtain logs from the master again to ensure the integrity of relay-log. By default, this function is disabled. If you set relay_log_recovery to 1, you can enable this function on the slave database. We recommend that you enable this function.

2.7 relay_log_space_limit: prevents relay logs from filling the disk. The maximum relay log quota is set here. However, the master database crashes and the slave database relay logs are incomplete. This setting is not recommended;

2.8 sync_relay_log: this parameter is the same as sync_binlog. When it is set to 1, the slave I/O thread writes the binlog sent from the master to the System Buffer every time, it is the safest way to relay log relay logs, because when a crash occurs, you will lose at most one transaction, but it will cause a large amount of I/O on the disk. When it is set to 0, it is not immediately flushed into the relay log, but the operating system determines when to write data. Although the security is reduced, however, this reduces disk I/O operations. The default value is 0, which can be dynamically modified. We recommend that you use the default value.

2.9 sync_relay_log_info: this parameter is the same as the sync_relay_log parameter. When it is set to 1, the slave I/O thread writes the binlog log sent from the master to the System Buffer every time, and then fl into the relay-log.info, this is the safest, because in the crash, you will lose a transaction at most, but will cause a lot of disk I/O. When it is set to 0, it is not immediately flushed into the relay-log.info, but determined by the operating system when to write, although the security is reduced, but a lot of disk I/O operations are reduced. The default value is 0, which can be dynamically modified. We recommend that you use the default value.

3. Conclusion: The preceding section briefly describes the functions of each parameter. The specific settings of these parameters must be set based on the actual system conditions of each user;

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization

MySQL 5.5 master-slave bidirectional Synchronization

MySQL 5.5 master-slave synchronization troubleshooting

MySQL master-slave replication asynchronous semi-sync instance

This article permanently updates the link address: