2015-09-16 MySQL master-slave principle, synchronous common anomaly and recovery method

1. Principle

MySQL replication technology has some of the following features:
(1) Data distribution (distribution)

(2) Load balancing (load Balancing)
(3) Backup (Backups)
(4) High-availability and fault-tolerant lines of availability and failover

As a whole, there are 3 steps to replication:

(1) Master changes the record to binary log (these are called binary log events, binary logs event);
(2) Slave copies the binary log events of master to its trunk logs (relay log);
(3) Slave redo the event in the trunk log and change the data to reflect its own.

Describes the process of replication:

650) this.width=650; "title=" 18.gif "alt=" Wkiol1x5r0raaasjaaburjwr9ok644.gif "src=" http://s3.51cto.com/wyfs02/M01/ 73/48/wkiol1x5r0raaasjaaburjwr9ok644.gif "/>

2. Delay

Cause: When the TPS for the main library is high, the number of DDL generated exceeds the range that slave a SQL thread can withstand, and the delay is generated, and of course there is the possibility of a lock wait with a large query statement from slave.

Workaround: The simplest solution to reduce the slave synchronization delay is to optimize the architecture and try to get the DDL of the main library to execute quickly. There is the main library is written, the data security is high, such as sync_binlog=1 (, Innodb_flush_log_at_trx_commit = 1, and so on, and slave does not need such a high data security, can speak sync_ Binlog is set to 0 or off Binlog,innodb_flushlog can also be set to zero to increase the efficiency of SQL execution. The other is to use a better hardware device than the main library as a slave.

Sync_binlog

Using the Sync_binlog global variable 1 is the safest value, but also the slowest, so that Binlog synchronizes with the hard disk after every n binlog write

Innodb_flush_log_at_trx_commit

The default value of 1 means that each transaction commit or out-of-transaction instruction requires the log to be written to (flush) the hard disk, which is time-consuming, set to 0 faster, but less secure, even if MySQL hangs may lose transactional data. A value of 2 will only lose data if the entire operating system is hung.

3. mysql master-slave synchronization common anomaly and recovery method

A. General exceptions only need to skip one step to recover

>slave stop;

>set GLOBAL sql_slave_skip_counter = 1;

>slave start;

B. The last Bin-log log of the master library is restored when a power outage causes the master/slave to fail to synchronize

On the main library server, Mysqlbinlog mysql-bin.xxxx > Binxxxx.txt

Tail-n 100000 binxxxx.txt > Tail-binxxxx.txt

Vim Tail-binxxxx.txt Open a tail-binxxxx.txt file to find the last Postion value

Then on the From library, change the host to the corresponding correct value

>slave stop;

>change Master to master_host= ' IP ', master_user= ' username ', master_password= ' password ', master_log_file= ' Mysql-bin.xxxx ', master_log_pos=xxxx;

>slave start;

>show slave status\g;

C. A primary key violation, a table already exists, and other error codes, such as 1062,1032,1060, can be specified in the MySQL master configuration file

Skipping this type of exception and continuing with the next SQL synchronization can also avoid many of the exception interrupts of master-slave synchronization

[Mysqld]

Slave-skip-errors = 1062,1032,1060

Reference

MySQL Master never sync delay principle
http://wxy021.blog.163.com/blog/static/170618669201298102732747/
MySQL master-Slave synchronization common exception and recovery method
Http://www.open-open.com/lib/view/open1409208341557.html

2015-09-16 MySQL Master-slave principle, synchronization common exceptions and recovery methods