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

Master_info and relay_info on Mysql database what impact, for this problem, this article details the corresponding analysis and solution, hoping to help more want to solve this problem of small partners to find a simpler and easier way.

Prior to MySQL 5.6.2, master information for slave records and binlog information for slave applications were stored in files, master.info and relay-log.info. After version 5.6.2, it is allowed to record in table, and the parameters are set as follows:

master-info-repository = TABLE ---FILE means in file format

relay-log-info-repository = TABLE --FILE means in file form

The corresponding tables are mysql.slave_master_info and mysql.slave_relay_log_info, respectively, and these two tables are innodb engine tables.

Master info and relay info also have 3 parameters to control refresh:

The default is 10000, which means every 10000 sync_relay_log events are flushed to disk.

If the value>0, MySQL SERVER synchronizes its relay log to disk (writes relay log, using fdatasyc ()) in every sync_relay_log events are written to the relay log.)

When set to 1, slave I/O threads write binlog logs to the system buffer every time they receive them from master, and then brush them into relay logs. This is the safest way, because in a crash, you will lose at most one transaction, but it will cause

A lot of I/O to disk.

When set to 0, it is not immediately flushed into the relay log, but the operating system decides when to write, although security is reduced, but a large number of disk I/O operations are reduced.

sync_master_info: Controls the updating of master_info information

If master-info-repository is FILE, when it is set to 0, it will be refreshed to disk every time sync_master_info event, and the default is 10000 times to refresh to disk;

If master-info-repository is TABLE, when set to 0, the table does not do any updates, when set to 1, the table #is updated every event Default is 10000

sync_relay_log_info: Controls the updating of relay_log_info information

If relay_log_info_repository is FILE, when it is set to 0, the OS will refresh the disk, and the default is 10000 times to refresh the disk;

If relay_log_info_repository is TABLE and INNODB storage, then evnet updates the table every time, regardless of the value.

Data loss caused by failure to recover in time after master outage

When the master fails, binlogs are not transmitted to slaves in time, or binlogs received by slaves are inconsistent. And the master cannot recover at the first time. What should I do at this time?

If the master does not switch, the entire database can only be read-only, affecting the operation of the application.

If you promote another slave to the new master, then the data that the original master did not have time to transfer to the slave's binlog will be lost, and the following two problems are involved.

1. The binlogs received by slaves are inconsistent. If a slave is forcibly pulled, the data among slaves will be inconsistent.

2. After the original master is restored to normal, since the new master log discards part of the original master binlog, how to deal with these extra binlog logs and rebuild the environment?

One way to deal with the above problem is to ensure that binlogs are passed to the slave library, or that there are multiple copies of binlogs in the master library. The second approach is to allow data loss and develop policies to minimize data loss.

1. Make sure binlogs are all passed to slave libraries

Solution 1: Semi sync (semi-synchronous) mode is used. After the transaction is submitted, it must be transmitted to slave before the transaction can be considered finished. It has a large impact on performance, and the dependent network is suitable for small tps systems.

Solution 2: Double write binlog, copy to standby through DBDR OS layer file system, or use shared disk to save binlog log.

Solution 3: Make an issue of the data layer, for example, after ensuring that the database is successfully written, write another asynchronous queue, and some services can be solved by design and data flow.

2. Guaranteed minimum data loss

The above scheme design and architecture is more complex, if you can tolerate data loss, you can consider using Taobao TMHA replication management tool.

When the master goes down, TMHA selects a binlog to receive the largest slave as the master. When the original master is restored, the transactions executed on the original master are rolled back through the reverse application of binlog.

3. summary

Through the above summary analysis, MySQL lost data scenarios are varied, involving single database lost data scenarios, master-slave lost data scenarios and MySQL internal XA transaction principles, etc., which are relatively complex and a bit difficult to understand.

Only when we understand these lost data scenarios can we better learn and solve these problems.

About master_info and relay_info on Mysql database what impact the answer to the question to share here, I hope the above content can be of some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.

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.