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

Source: Internet
Author: User

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?

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.