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

This article mainly tells you how to solve the MySQL master-slave synchronization delay. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on how to solve the MySQL master-slave synchronization delay can bring you some practical help.

Mysql master-slave basic principle, main form and master-slave synchronization delay principle (read-write separation) lead to the problem of data inconsistency between master and slave database and its solution

I. the difference between master and slave databases

The slave database (Slave) is the backup of the master database, and the slave database is updated when the master database (Master) changes. These database software can design the update cycle. This is a means to improve information security. The master-slave database cloud server is not located in a geographical location, and the database can be saved in the event of an accident.

(1) Master-slave division of labor

The Master is responsible for the load of the write operation, that is, all the write operations are carried out on the Master, while the read operations are allocated to the Slave. In this way, the efficiency of reading can be greatly improved. In general Internet applications, after some data survey, it is concluded that the read / write ratio is about 10:1, that is to say, a large number of data operations are focused on read operations, which is why we have multiple Slave. But why separate reading from writing? Developers who are familiar with DB all know that write operations involve locks, whether row locks, table locks or block locks, which reduce the efficiency of system execution. Our separation is that the write operation is concentrated on one node, while the read operation is carried out on the other N nodes, which effectively improves the reading efficiency and ensures the high availability of the system.

(2) basic process

1) Master-slave synchronization of Mysql means that when data changes in master (master database), it will be synchronized to slave (slave library) in real time.

2) Master-slave replication can horizontally expand the load capacity of the database, fault tolerance, high availability, and data backup.

3), whether it is delete, update, insert, or creating functions or stored procedures, they are all on master. When master has operations, slave will quickly accept these operations and synchronize them.

(3) uses and conditions

1), mysql master-slave replication purpose

● real-time disaster recovery for failover

● separates reads and writes and provides query services.

● backup to avoid affecting the busin

2). Necessary conditions for master-slave deployment:

● master library opens binlog log (set log-bin parameter)

● master and slave server-id are different

● can connect to the master database from the database cloud server.

Second, the granularity, principle and form of master-slave synchronization:

(1), three main implementation granularities

There are three main forms of detailed master-slave synchronization: statement, row, and mixed.

1), statement: sql statements for database operations will be written to binlog

2), row: each change of data will be written to binlog.

3), mixed: the mixture of statement and row. Mysql decides when to write binlog in statement format and when to write binlog in row format.

(2), the main realization principle, concrete operation, schematic diagram

1) the operation on the master machine:

When the data on the master changes, the event change is written sequentially to the bin-log. When slave links to master, the master machine starts the binlog dump thread for slave. When the binlog of the master changes, the bin-log dump thread notifies the slave and sends the corresponding binlog content to the slave.

2) operate on the slave machine:

When master-slave synchronization is turned on, two threads are created on the slave: I\ O thread. The thread connects to the master machine, and the binlog dump thread on the master machine sends the contents of the binlog to the I\ O thread. The I / O thread receives the binlog content and then writes the content to the local relay log;sql thread. The thread reads the ralay log written by the I / O thread. And according to relay log. And do the corresponding operation to the slave database according to the content of relay log.

3). The master-slave replication schematic diagram of MySQL is as follows:

Two threads are generated from the library, one is an Icano thread and the other is a SQL thread

The iCompo thread requests the binlog of the main library and writes the resulting binlog log to the relay log (Relay Log) file.

The master library generates a log dump thread that is used to pass binlog to the slave library iUnix thread.

The SQL thread reads the log in the relay log file and parses it into specific operations to achieve the consistency of the master-slave operation and the final data consistency.

(2) Master-slave form

Mysql master-slave replication is flexible

● one Master and one Slave

● master master replication

● one-master, multi-slave-expands the performance of system reads because reads are read from the library

● supports multi-master, one-slave and-5.7,

● cascaded replication--

Third, the delay of master-slave synchronization and other problems, causes and solutions:

(1) delay in synchronization of mysql database from database

1) related parameters:

First, execute show slave satus; on the CVM and you can see many synchronization parameters:

Master_Log_File: the name of the primary CVM binary log file that is currently being read by the Ibank O thread in SLAVE

Read_Master_Log_Pos: in the current primary CVM binary log, the location that has been read by the Icano thread in SLAVE

Relay_Log_File: the name of the relay log file that the SQL thread is currently reading and executing

Relay_Log_Pos: the location in the current relay log where the SQL thread has read and executed

Relay_Master_Log_File: the name of the primary CVM binary log file that contains most recent events executed by the SQL thread

Slave_IO_Running: whether the Icano thread is started and successfully connected to the primary cloud server

Slave_SQL_Running: whether the SQL thread is started

Seconds_Behind_Master: the time gap between the slave CVM SQL thread and the slave CVM Imax O thread (in seconds).

The ● show slave status display parameter Seconds_Behind_Master that occurs from the library synchronization delay is not 0, which may be very large

The ● show slave status display parameters Relay_Master_Log_File and Master_Log_File show a great difference in the number of the bin-log, indicating that the bin-log is not synchronized in time on the slave library, so the recently executed bin-log is very different from the bin-log read by the current IO thread.

There are a large number of mysql-relay-log logs in the slave database data directory of ● mysql. The log will be automatically deleted by the system after the synchronization is completed, and there are a large number of logs, indicating that the master-slave synchronization delay is very serious.

(2) delay in synchronization of MySql database from database

1) MySQL Database Master-Slave synchronization delay principle mysql Master-Slave synchronization principle: master database writes binlog sequentially for write operations, reads "binlog of write operation" sequentially from single thread to master database, and executes locally as-is from library to binlog (random write) to ensure logical consistency of master-slave data. The master-slave replication of mysql is a single-threaded operation, and the master library writes binlog,binlog sequentially to all DDL and DML, so it is very efficient. The Slave_IO_Running thread of slave takes logs to the master database, which is relatively efficient. Next, the problem is that the Slave_SQL_ running thread of slave implements the DDL and DML operations of the master library in slave. The IO operation of DML and DDL is random, not sequential, and the cost is much higher, and other queries on slave may also generate lock contention. Because Slave_SQL_Running is also single-threaded, a DDL card owner needs to execute for 10 minutes, then all subsequent DDL will wait for the DDL to finish execution before continuing to execute, which leads to the delay. A friend will ask: "the same DDL on the main library also needs to execute 10 points. Why is the slave delayed?" The answer is that master can be concurrent, but Slave_SQL_ running threads cannot.

2). How is the master-slave synchronization delay in MySQL database generated? When the TPS concurrency of the main library is high, the number of DDL generated is more than a sql thread of slave can bear, then the delay occurs, and of course, lock waiting may occur with the large query statements of slave. The primary reason: database read and write pressure on the business is too large, CPU computing load, network card load, hard disk random IO is too high secondary reasons: read and write binlog performance impact, network transmission delay.

(3) delay solution for synchronization of MySql database from database

1), architecture

1. The implementation of the persistence layer of the business adopts a sub-library architecture, and mysql services can be expanded in parallel to disperse the pressure.

two。 A single library is separated from reading and writing, with one master and multiple followers, and the main writer is read from each other, dispersing the pressure. In this way, the pressure of the slave library is higher than that of the master library, which protects the master library.

3. The infrastructure of the service adds the cache layer of memcache or redis between the business and the mysql. Reduce the reading pressure of mysql.

4. The mysql of different businesses is physically placed on different machines to disperse the pressure.

5. Using a better hardware device than the main library as a slave summary, the mysql pressure is low, and the latency will naturally be reduced.

2), hardware

1. Using a good CVM, for example, the performance of 4U is better than that of 2U, and the performance of 2U is better than that of 1U.

two。 Storage uses ssd or disk array or san to improve the performance of random writes.

3. The master and slave are guaranteed to be under the same switch and in a 10-gigabit environment.

In conclusion, with strong hardware, latency will naturally be reduced. In a word, the solution to reducing delays is to spend money and time.

3), mysql master-slave synchronous acceleration

1. Sync_binlog is set to 0 on the server.

2.-the updates received by logs-slave-updates from the CVM and the primary CVM are not recorded in its binary log.

3. Disable binlog on Slave directly.

4. Server, if the storage engine used is innodb,innodb_flush_log_at_trx_commit = 2

4) optimize in terms of the attributes of the file system itself

The master side modifies the etime attributes of files in linux and Unix file systems. Since OS writes back the time of read operations to disk every time a file is read, this is not necessary for database files with frequent read operations. It will only increase the burden on the disk system and affect the performance of IBO. You can organize the operating system to write atime information by setting the mount property of the file system. The operation on linux is: open / etc/fstab, add the noatime parameter / dev/sdb1 / data reiserfs noatime 1 / 2, and then re-mount the file system # mount-oremount / data

5) the main library of synchronization parameter adjustment is write, which is highly secure for data. For example, setting sync_binlog=1,innodb_flush_log_at_trx_commit = 1 is required, while slave does not need such high data security. It is possible to set sync_binlog to 0 or turn off binlog,innodb_flushlog to improve the efficiency of sql execution.

1. Sync_binlog=1 oMySQL provides a sync_binlog parameter to control the binlog of the database to be flushed to disk. The default, sync_binlog=0, means that MySQL does not control the refresh of binlog, and the file system controls the refresh of its cache. At this time, the performance is the best, but the risk is also the greatest. Once the system Crash, all binlog information in the binlog_cache will be lost.

If sync_binlog > 0, it means that every sync_binlog transaction commits, the refresh operation of the file system called by MySQL brushes the cache. The safest is sync_binlog=1, which means that MySQL will brush down binlog every time a transaction is committed, which is the safest setting with the greatest performance loss. In this way, if the host operating system where the database is located is damaged or suddenly powered off, it is possible for the system to lose the data of a transaction. However, although binlog is a sequential IO, setting sync_binlog=1 and multiple transactions commit at the same time also greatly affect the performance of MySQL and IO. Although it can be mitigated by patches in group commit, the high frequency of refreshes can also have a great impact on IO.

For systems with highly concurrent transactions, the write performance gap between systems with "sync_binlog" set to 0 and 1 can be as high as five times or more. So the sync_binlog set by many MySQL DBA is not the safest 1, but 2 or 0. At the expense of a certain amount of consistency, higher concurrency and performance can be achieved. By default, the binlog is not synchronized with the hard drive every time you write. So if the operating system or machine (not just the MySQL cloud server) crashes, it is possible that the last statement in binlog is lost. To prevent this, you can use the sync_binlog global variable (1 is the safest value, but also the slowest) to synchronize binlog with the hard drive after every N binlog writes. Even if sync_binlog is set to 1, it is possible that there is an inconsistency between the table content and the binlog content in the event of a crash.

2. Innodb_flush_log_at_trx_commit complains that Innodb is 100 times slower than MyISAM. So you probably forgot to adjust this value. The default value of 1 means that every transaction commit or instruction outside a transaction needs to be written to (flush) the hard disk, which is time-consuming. Especially when using battery powered cache (Battery backed up cache). Setting to 2 is OK for many applications, especially those transferred from the MyISAM table, which means writing to the system cache instead of writing to the hard disk. Logs still flush to the hard disk per second, so you don't usually lose updates of more than 1-2 seconds. Setting it to 0 is faster, but the security aspect is poor, and even if the MySQL is down, the transaction data may be lost. A value of 2 will only lose data when the entire operating system is down.

3. The ls (1) command can be used to list the atime, ctime, and mtime of a file.

The access time of the atime file changes the create time of the ctime file when reading the file or executing the file when writing to the file, changing the owner, permission, or link settings, changing the modified time of the mtime file with the change of the content of the inode when writing to the file, changing the ls-lc filename lists the ctimels-lu filename of the file listing the file's atimels-l filename listing the file's mtimestat filename list atime,mtime Ctimeatime is not necessarily modified after accessing the file because: when using the ext3 file system, if the noatime parameter is used when mount, then the atime information will not be updated. All three time stamp are placed in inode. If the mtime,atime is modified, the inode will certainly change, and now that the inode has changed, so will the ctime. The reason why we use noatime in mount option is that we don't want file system to make too many changes and improve the performance of reading.

(4) other problems and solutions of synchronizing MySql database from database

1) problems with mysql master-slave replication: after the ● master library goes down, the data may be lost. There is only one sql Thread in the ● slave library, so the master database is under heavy writing pressure, and the replication is likely to be delayed. 2), solution: ● semi-synchronous replication-solve the problem of data loss-- ● parallel replication-solve the problem of replication delay in the slave library.

3) semi-synchronous replication mysql semi-sync (semi-synchronous replication) semi-synchronous replication: ● 5.5 is integrated into mysql and exists as a plug-in. ● needs to be installed separately to ensure that the binlog is transferred to at least one slave library after the transaction is committed. ● does not guarantee that the binlog ● performance of this transaction will be reduced to a certain extent, and the response time will be longer. ● network exception or slave library downtime, card master library Until timeout or slave library recovery 4), master-slave replication-asynchronous replication principle, semi-synchronous replication and parallel replication principle comparison

A. the principle of asynchronous replication:

B. the principle of semi-synchronous replication:

The transaction needs to be returned to the client after the master library has finished writing binlog, and it is integrated into mysql and exists as a plug-in, which needs to be installed separately to ensure that the binlog is transferred to at least one slave library after the transaction is committed. The binlog performance of this transaction is not guaranteed to be completed by the slave library application, which reduces the network exception or slave library downtime, card master library, until timeout or slave library recovery

C. Parallel replication mysql parallel replication ● Community Edition 5.6. the new ● parallelism refers to the parallel application of binlog from the library multi-thread apply binlog ● library level, and whether the data changes in the same library are serial (version 5.7 parallel replication is based on the transaction group). Set the number of set global slave_parallel_workers=10; threads to 10

Principle: from library multithreading apply binlog to add library level parallel application binlog in community 5.6, the same library data change or serial version 5.7 parallel replication is based on transaction group.

MySQL master-slave synchronization delay how to solve the first to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.