Old boy Education Daily-April 28, 2017-MySQL master-slave replication common faults and solutions?

MySQL Master-slave replication common failures and solutions?

1.1.1malfunction1: conflict with Main library from library data

show slave status; Error: and show slave status\gslave_i/o_running:yesslave_sql_running:noseconds_behind_master:null Last_error:Error ' Can ' t Create database ' Xiaoliu '; Database exists ' on query. Default database: ' Xiaoliu '. Query: ' Create DATABASE Xiaoliu '

Solution:

# # # Method 1:

Stop Slave;set Global sql_slave_skip_counter = 1; #将同步指针向下移动一个, you can repeat operations if you do not synchronize multiple times

# # # Method 2: Configure from the library configuration file to skip directly the error number that does not affect the business

grep slave-skip/etc/my.cnfslave-skip-errors = 1032,1062,1007

1.1.2malfunction2:Mysqlreason and solution of master-slave replication delay problem

issue 1: Too many libraries from the main library, resulting in replication delays

The number of 3-5 from the library is generally appropriate, too many nodes to replicate, resulting in replication delays.

Issue 2: The hardware configuration from the library is worse than the main library, resulting in delays

View The configuration of Master and Slave may cause replication delays due to improper configuration

issue 3: too many slow SQL statements

If a statement executes for more than 2 seconds, it needs to be tuned

issue 4: Master-slave replication design issues

Master -slave replication single-threaded, if the main library of write concurrency too large, too late to transfer to the slave library, will cause delay, later version of the MySQL can support multi-threaded replication, and the portal site will

Develop multi-threaded synchronization capabilities.

issue 5: Network latency between master and slave libraries

The master-slave library card, network cable, connected switches and other networking equipment can become a replication bottleneck, resulting in replication delays, in addition to cross-public network master-slave replication is prone to master-slave replication delay.

Issue 6: High read and write pressure on the main library, resulting in replication delay

The main library hardware is better, the schema front-end to buffer cache layer.

today is a day to accompany everyone's first day, look forward to your progress.

For questions and answers, please leave a comment in the blog comments section.

Index of the topic of the previous period

http://lidao.blog.51cto.com/3388056/1914205

This article is from the "Lee blog" blog, make sure to keep this source http://lidao.blog.51cto.com/3388056/1920297

Old boy Education daily-April 28, 2017-MySQL master-slave replication common faults and solutions?