Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what is the use of relay log and binlog in mysql". It is easy to understand and clear. I hope it can help you solve your doubts. Let me lead you to study and learn this article "what is the use of relay log and binlog in mysql".

Suddenly, there is much less data in a table in the mysql database. I want to check the log to determine what operations have been done on this table, and then recover it. However, if I look at binlog (binary form) directly, I find that it is very messy and I can't see it clearly, so I know that I need to format it through mysqlbinlog first, as shown below:

Main library binlog: mysqlbinlog-- base64-output=decode-rows-v-v mysql-bin.000058 > binlog

From the library relaylog: mysqlbinlog-- base64-output=decode-rows-v-v mysql-relay-bin.000031 > relaylog

Then look at it in a very regular form: just some specific sql statements.

[root@S243 mybinlog] # more binlog28

/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=1*/

/ *! 40019 SET @ @ session.max_insert_delayed_threads=0*/

/ *! 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/

DELIMITER / *! * /

# at 4

# 161125 22:17:23 server id 20 end_log_pos 120 Start: binlog v 4, server v 5.6.18-enterprise-commercial-advanced-log created 161125 22:17:23

# at 120

# 161125 22:17:23 server id 20 end_log_pos 204 Query thread_id=3498328 exec_time=0 error_code=0

SET timestamp 1480083443

SET @ @ session.pseudoclinic thread readership idling 3498328pencils /

.

.

SET timestamp 1480083443

Insert into mailer.khgz_coreseek_result (info_id, khgz_id, last_modify, result, status) values ('1th,' 1th, '2016-11-25 2222, 17-24th,' 29803096:8d78ac82-e746-423a-9615-971485e8, 0)

/ *! * /

# at 487

# 161125 22:17:23 server id 20 end_log_pos 572 Query thread_id=3498328 exec_time=0 error_code=0

SET timestamp 1480083443

COMMIT

/ *! * /

# at 572

On the rotate mechanism of binlog and relay log:

Binary Log rotate mechanism:

Rotate: after each binary log is written, it will determine whether the current file exceeds max_binlog_size (the default is 1g), and if so, a binlog file is automatically generated

Delete:expire-logs-days only determines whether there is an expired binlog file when the instance is started and flush logs, and whether the file file exceeds max_binlog_size. If the file access time is earlier than the set value, then purge file

Relay Log rotate mechanism:

Rotate: after each events from Master fetch, determine whether the current file exceeds max_relay_log_size (default 1g). If so, a new relay-log-file is automatically generated.

Delete:purge-relay-log judges every time the SQL Thread executes an events, and automatically deletes the relay-log if it is no longer needed

Delete:expire-logs-days only judges when the instance is started and flush logs. If the file access time is earlier than the set value, purge file (with Binlog file) (updated: expire-logs-days and relaylog's purge has nothing to do)

Parameters for binlog and relay log:

Parameters related to binlog:

Mysql > show variables like'% bin%'

+-+

| | Variable_name | Value |

+-+

| | bind_address | * | |

| | binlog_cache_size | 1048576 | |

| | binlog_checksum | NONE |

| | binlog_direct_non_transactional_updates | OFF |

| | binlog_format | MIXED |

| | binlog_max_flush_queue_time | 0 | |

| | binlog_order_commits | ON |

| | binlog_row_image | FULL |

| | binlog_rows_query_log_events | OFF |

| | binlog_stmt_cache_size | 32768 | |

| | innodb_api_enable_binlog | OFF |

| | innodb_locks_unsafe_for_binlog | OFF |

| | log_bin | ON |

| | log_bin_basename | / mysql/mybinlog/mysql-bin |

| | log_bin_index | / mysql/mybinlog/mysql-bin.index |

| | log_bin_trust_function_creators | OFF |

| | log_bin_use_v1_row_events | OFF |

| | max_binlog_cache_size | 18446744073709547520 | |

| | max_binlog_size | 1073741824 | |

| | max_binlog_stmt_cache_size | 18446744073709547520 | |

| | sql_log_bin | ON |

| | sync_binlog | 0 | |

+-+

22 rows in set (0.00 sec)

Log_bin

Setting this parameter enables the binlog feature and specifies the path name

Log_bin_index

Set this parameter to specify the path and name of the binary index file

Binlog_do_db

This parameter indicates that only the binary log of the specified database is recorded.

Binlog_ignore_db

This parameter indicates that the binary log of the specified database is not recorded.

Max_binlog_cache_size

This parameter represents the maximum size of memory used by binlog

Binlog_cache_size

This parameter represents the amount of memory used by binlog and can be tested through the state variables binlog_cache_use and binlog_cache_disk_use.

Binlog_cache_use: number of transactions that use binary log caching

Binlog_cache_disk_use: the number of transactions that use binary log caching but exceed the binlog_cache_ size value and use temporary files to save statements in the transaction

Max_binlog_size

The maximum, maximum and default value of Binlog is 1GB. This setting does not strictly control the size of Binlog, especially when Binlog is close to the maximum and encounters a larger transaction. In order to ensure the integrity of the transaction, it is impossible to switch logs, so all SQL of the transaction can only be recorded in the current log until the end of the transaction.

Sync_binlog

Sync_binlog ": this parameter is very important to the MySQL system. It not only affects the performance loss caused by Binlog to MySQL, but also affects the integrity of data in MySQL. The various settings for the" sync_binlog "parameter are described as follows:

Sync_binlog=0, when a transaction is committed, MySQL does not do disk synchronization instructions such as fsync to refresh the information in binlog_cache to disk, but let Filesystem decide when to synchronize, or synchronize to disk after the cache is full.

Sync_binlog=n, after every n transaction commits, MySQL will issue a disk synchronization instruction such as fsync to force the data in binlog_cache to be written to disk.

In MySQL, the default setting of the system is sync_binlog=0, that is, do not do any mandatory disk refresh instructions, this time the performance is the best, but the risk is also the greatest. Because once the system Crash, all binlog information in binlog_cache will be lost. When set to "1", it is the safest setting with the greatest performance loss. Because when set to 1, even if the system Crash, at most one outstanding transaction in the binlog_cache is lost, without any material impact on the actual data.

From past experience and related tests, for systems with high concurrent transactions, the write performance gap between systems with "sync_binlog" set to 0 and 1 may be as high as 5 times or more.

Parameters related to relay log:

View all the relevant parameters of the relay of the predecessor through the statement: show variables like'% 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 allowed value for relay log, if the value is 0, the default value is max_binlog_size (1G), if not 0, max_relay_log_size is the maximum 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, which is named host_name-relay-bin.nnnnnn (By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory).

2.3relay_log_index: same as relay_log, define the location and name of the relay_log

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

2.5relay_log_purge: whether to automatically empty when relay logs are no longer needed. The default value is 1 (enabled).

2.6relay_log_recovery: when the slave is down from the library, if the relay-log is corrupted and some of the relay logs are not processed, all outstanding relay-log will be discarded automatically and the logs will be retrieved from the master, thus ensuring the integrity of the relay-log. This feature is off by default. If you set the value of relay_log_recovery to 1, you can enable this feature on the slave slave library. It is recommended to enable it.

2.7relay_log_space_limit: prevent relay logs from being written to disk. Set the maximum limit for relay logs here. However, it is not recommended to use this setting if the master database crashes and the relay log from the library is incomplete.

2.8sync_relay_log: this parameter is the same as sync_binlog, when set to 1, every time the slave I / O thread receives the binlog log sent by master, it will be written to the system buffer, and then brushed into the relay log relay log, which is the safest, because in the event of a crash, you will lose at most one transaction, but will result in a large number of disk Ihand O. When set to 0, it is not immediately brushed into the relay log, but it is up to the operating system to decide when to write, although the security is reduced, but a large number of disk Ihop O operations are reduced. This value defaults to 0 and can be modified dynamically. It is recommended to use the default value.

2.9sync_relay_log_info: this parameter is the same as the sync_relay_log parameter, when set to 1, every time the slave I / O thread receives the binlog log sent by master, it will be written to the system buffer and then brushed into the relay-log.info, which is the safest, because in the event of a crash, you will lose at most one transaction, but will result in a large number of binlog O on the disk. When set to 0, it is not immediately brushed into the relay-log.info, but is decided by the operating system when to write, although the security is reduced, but a large number of disk Igamo operations are reduced. This value defaults to 0 and can be modified dynamically. It is recommended to use the default value.

About the deletion method of binlog and relay log:

Too many solutions to binlog are as follows:

PURGE MASTER LOGS manually deletes usage and examples. MASTER and BINARY are synonyms.

> PURGE {MASTER | BINARY} LOGS TO 'log_name'

> PURGE {MASTER | BINARY} LOGS BEFORE 'date'

Example:

> PURGE MASTER LOGS TO 'MySQL-bin.010'; / / clear the MySQL-bin.010 log

> PURGE MASTER LOGS BEFORE '2008-06-22 1300 PURGE MASTER LOGS BEFORE DATE_SUB PURGE MASTER LOGS BEFORE DATE_SUB (NOW (), INTERVAL 3 DAY) before 13:00:00; / / clear the BEFORE of the binlog log 3 days ago. The date argument of the variable can be in' YYYY-MM-DD hh:mm:ss' format.

Or directly:

Go to the database and check which binlog log is currently in use. Except for this, you can delete everything else using rm-rf!

Relay log can be deleted automatically by modifying a parameter, which has been described earlier: relay_log_purge=on. Automatic emptying no longer requires relay logs.

Summary:

The binlog and relay log logs of the mysql database play an important role, and relay log only exists in the slave library of mysql. Its function is to record the binlog received by the io process in the slave library from the master library, and then wait for the sql process of the slave library to read and apply to ensure master-slave synchronization, but both the binlog master library and the slave library (slave) can exist to record SQL statements that occur or potentially change the data. And saved in binary form on disk, so you can use binlog to back up and restore the database in real time.

These are all the contents of the article "what are relay log and binlog in mysql?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.