MySQL Replication Report

Many people will build MySQL master-slave framework, but many people do not really understand the basic use of synchronization, synchronization of the basic principles, as well as when master and slave synchronous disconnection after the processing and causes the master and slave out of sync, and so on, when you know these things, For MySQL master and slave Some common problems, also can easily solve it, and the Database architecture optimization and transformation will be a great help. Now let's study MySQL replication, ^0^

Use of replication:

1, data distribution, scale out,sacle up, vertical division, Horizontal Division

2. Load Balancer Balance

3, backup, generally not used as a backup, once the delete operation, replication will not retain

4, achieve high availability of data

5. Different storage engines can be used on different master and slave libraries

6. Test MySQL Upgrade

The common MySQL replication architecture is:

MySQL a master multi-slave, implementation of read-write separation framework diagram:

Common load-balancing architectures:

Master2 Open log-slave-updates configuration when using too much slave to relieve Mater pressure Cascade Architecture

A master down-time redundancy architecture:

Replication different libraries to different hosts:

Principle:

1. Three processes:

MySQL replication (relication) is an asynchronous replication that replicates from one master to another slave. The implementation of the entire replication operation is mainly done by three processes, of which two processes are on slave (SQL process and IO process), another process on master (IO process), if replication is in progress, on Master can be run by running show Processlist view, on slave can perform show slave status to view, inside the Slave_io_running:no

Slave_sql_running:no

Is whether the state of the two processes is running.

2, three log files and two info files:

When replication is in progress, Statements executed on Master are recorded in Bin.log, the location and number of logs, and when there is a change, slave will read the binary log of master through the modern war IO process, and when it finds a change, it will copy the new changes to its relay.log (trunk log), which will record the location and data of the row A new file called Master.info, continue to check the master binary log, when the slave SQL process found in Relay.log changes will be executed, while slave also through the SQL process to compare the master and slave changes, if the contrast found inconsistencies, the replication process will Stop and count the error information into the slave error.log, if the result is correct, a new log location and number will be recorded to the Relay-log.info file, slave will wait for another change to the Relay.log file.

3, the basic process of replication is as follows:

The simple saying is that Mster records its changes to Binlog,slave received changes will be recorded to his relay Log,slave by replay Relay log, and then write into his own log

1), slave above the IO process connected to master, and request from the specified log file at the specified location (or from the beginning of the log) after the contents of the log;

2), master receives a request from the IO process from the slave, the IO process that is responsible for the replication reads the log information after the specified log's specified location according to the requested information, and returns the IO process to the slave. In addition to the information contained in the log, the returned information includes the name of the Bin-log file returned to the master side and the location of the Bin-log;

3), after the slave IO process receives the information, the received log content is added to the end of the Relay-log file on the slave side, and the file name and location of the Bin-log read to the master side are recorded in the Master-info file. In order to be able to tell Master clearly at the next reading, "I need to start from somewhere in the Bin-log log content, please send me";

4), slave's SQL process detects the new content in the Relay-log, will immediately parse the contents of Relay-log as the real execution of the master side of the executable content, and in its own execution;

4, replication two levels of replication:

Statement level MySQL after 3.23:

Each query that modifies the data is recorded in the binary log of master, and the SQL thread resolves to the same query that was executed on the original master side when the slave is copied.

The advantage is that you don't need to record every change, reduce log, and save Io.

The disadvantage is that you must have information about each statement, that is, contextual information.

After Row level 5.1:

The Binaty log is recorded as a modified form of each row of data, and then the same data is modified on the slave port, and the lock table operation is reduced.

Advantage: You do not need to record the context information that executes the query statement, just record that the article has been modified and what has been modified.

The disadvantage is that the resulting log record is larger

There is also a mixed level that is not commonly used.

5. Causes master and slave to be out of sync

1), delete Update does not use the limit statement when changing rows, no ORDER BY

2), some functions in the use of statements based replication when the following:

Load_file,user,found_rows,uuid,uuid_short,sysdate ()

3), do not use the temporary table, when the slave crash off or restart, after the loss of information

4), Slave down

5), using REPLICATE-IGNORE-DB and BINLOG-IGNORE-DB

6), the wrong binlog execution SQL led to Binlog plugging, the specific details can be consulted: http://dev.mysql.com/doc/refman/5.0/en/replication.html

6, the history of MySQL replication

MySQL version of 4.0-5.0:

MySQL 5.1

Summarize:

First, through the MySQL replication principle of learning, you can clearly understand how the data is synchronized from the master library to the Slave library, what will lead to synchronization disconnection and so on.

Second, through the MySQL replication architecture learning, can be based on the online and business needs to choose the right structure of their own business, improve data security and access efficiency.

For more information, refer to Daniel's blog: http://ourmysql.com/archives/876

MySQL Replication Report