Shulou(Shulou.com)06/01 Report--

Written by small whirlwind

Many brothers in the industry are flocking to mysql and like it very much because it is cute (small and exquisite installation package), simple (easy to install and easy to use), sincere (open source), and considerate (powerful features and continuous improvement).

However, because you do not understand, you mistakenly think that she is "not single-minded" (the Lord is never consistent, in fact, you can be consistent with each other), and gradually abandon her. She looked up in the dim lights to look back on you suddenly, but you smiled coldly, she could only be sad.

Hurt the mind to say: "you do not understand me, I do not blame you." Actually, it's not your fault.

The editor will take you to know the inner world of mysql carsh_safe replication.

Crash-safe replication definition

When an unexpected situation such as downtime occurs on any of the master/salve nodes, the master/salve data remains consistent after the server is restarted.

Include

Master crash-safe replication

Slave crash-safe replication

Master crash-safe replication

Three parameters need to be configured.

Innodb-flush-log-at-trx-commit=1

Sync_binlog=1

Innodb-xa-support=1

Detailed explanation:

Master crash-safe replication ok as long as the transaction and its binary persistence are guaranteed.

To ensure persistence, you must ensure that each committed transaction persists redo log to redo log files and bin log to binary log files (ensuring data consistency between master and slave libraries).

And to ensure the atomicity of saving 2 files per commit, a distributed transaction needs to be opened because it is a different file.

Innodb-flush-log-at-trx-commit=1

Each commit transaction calls fsync to save the redo log information it produces to a redo log file on disk.

In order to ensure the persistence of the transaction. Restart mysql server in the event of a failure and recover through redo log.

Sync_binlog=1

One transaction per commit saves its binary log to binary file to ensure the consistency of master and slave data.

Innodb-xa-support=1

Open distributed transaction

Slave crash-safe replication

Three parameters need to be configured

Innodb-flush-log-at-trx-commit=1

Relay_log_info_repository=table

Relay_log_recovery=on

Problems that often occur when slave shuts down abnormally:

Constant 1062 primary key collision errors

Why?

Skip-slave-error=1062?

Inconsistency between master and slave data

Do you often encounter 1062 primary key conflicts after slave downtime? Maybe you will just execute SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 to skip an error. Why have you ever thought about it?

The editor has always thought that why is more important than what it is, and we should know why, so that we can avoid mistakes. Let's let me figure it out.

SQL thrend mainly does two things.

1: play back the transaction information in relay log

2: update the information in the relay_log.info file. Make sure to restart the service next time and let SQL thrend know that playback starts from that location of the relay log file.

Why:

The problem occurs in step 2, where the new relay_log.info file is cached to write, where the parameter sync_relay_log_info controls the timing of writing to the relay_log.info file.

The default value is 1000, which means that the disk is written once for every 10000 transactions in the relay log. Even with sync_relay_log_info=1, it is possible to repeat one thing.

And such frequent flushing will lead to a serious decline in system performance is not desirable.

Salve report 1062 scenario instance parsing:

Suppose that 95000 transactions have been played back by SQL thread on slave, and the location recorded by the relay_log.info file is 30500 [90000th transaction location], then salve is down.

After restarting, SQL thread reads relay_log.info to get the 30500 location that has been executed to a relay log file, that is, the 90000th transaction location, and then re-executes the transaction of 90000-95000, again because

If there is a primary key constraint, it will naturally report a primary key conflict.

Inconsistent master-slave data why

With the same problem as above, if there is no primary key constraint, the insert data will be executed repeatedly, and the slave library will have more insert data to be reexecuted.

Generally speaking, bin log is based on row, insert is not idempotent, update is idempotent. Idempotent: F (x) = f (f (x)), that is, what causes master/salve inconsistency is the insert statement and there is no primary key in the table.

Solution:

MySQL 5.6 crash safe: relay_log_info_repository=table

Relay-info.log 's information is stored in InnoDB's transaction table.

BEGIN

Apply log event

Apply log event

UPDATE mysql.slave_relay_log_info

SET Master_log_pos = Exec_Master_Log_Pos

Master_log_name = Relay_Master_Log_File

Relay_log_name = Relay_Log_File

Relay_log_pos = Relay_Log_Pos

COMMIT

This makes the execution of the transaction log event in relay log and the update relay_log.info atomicity.

IO thread

Receive binary log event

Update master-info.log

Cache write

Sync_master_info

Solution

Relay_log_recovery=on

Make sure binary log is still on the master server

This is the same as SQL thread, will repeatedly accept binary log event, the solution is relay_log_recovery=on with the previous relay_log_info_repository=table

That is, every time the service is restarted, IO thread will read the Master_log_name Master_log_pos in the mysql.slave_relay_log_ info table, that is, IO thread will go back to

Master continues to pull data from the specified location Master_log_pos of the specified binary file Master_log_name, as long as the corresponding binary file of master is still there.

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.