Meet MySQL Replication

MySQL Replication is a very distinctive feature of MySQL, and he is able to copy the data from one MySQL server Instance to the Instance of another MySQL server. Although the replication process is not real-time but asynchronous, due to its efficient performance design, latency is very small.

MySQL Replication is an asynchronous copy process, copied from one MySQL instace (we call Master) to another MySQL instance (we call it Slave). The entire replication process between Master and Slave is done primarily by three threads , of which two threads (SQL thread and IO thread ) are on the Slave side , and another thread (IO Thread ) on the Master side .

The Binary Log (mysqlbin.xxxxxx) feature of the Master side must be turned on in two ways, namely:

1. Use the "-log-bin" parameter option in the process of starting MySQL Server;

2. Add the "Log-bin" parameter to the MYSQLD parameter group ([Mysqld] in the MY.CNF configuration file).

The basic process for MySQL replication is as follows:

1. Slave the above IO line thread attached the Master, and requests the log content from the specified location (or from the beginning of the log) to the designated log file;

2. When Master receives a request from an IO thread from Slave, the IO thread that is responsible for the replication reads the log information from the specified log at the specified location based on the requested information and returns the IO thread to the Slave side. Returns information in addition to

The information contained in the log, including the name of the binary log file on the Master side of the returned information and its location in binary log;

3. After the Slave IO thread receives the information, it writes the received log contents to the end of the Relay log file (mysql-relay-bin.xxxxxx) on the Slave side, and reads the binlog text of the Master end

The name and location are recorded in the Master-info file so that the next read can be clearly high-speed master "I need to start from somewhere in the Bin-log log content, please send me"

4. Slave's SQL thread detects that the contents of the Relay log are newly added, it will parse the log file immediately into those executable Query statements at the real execution time on the Master side, and perform these

Query. In this way, the same Query is actually executed on the Master and Slave ends, so the data on both ends is exactly the same.

Replication Implementation Level :

MySQL replication can be based on a single statement (Statement level), or based on a record (Row level), you can set the configuration parameters in the MySQL replication levels, different replication level settings will affect the Master side of the Binary Log Recorded in different forms.

1. Row level:binary log is recorded in the form of each row of data being modified, and then the same data is modified on the Slave side.

2. Statement level: Each Query that modifies the data is recorded in the Binary log of Master. Slave the SQL thread will parse the same Query executed by the Sing Woo original Master at the time of replication.

3.Mixed level, in fact, is the combination of the first two modes. In Mixed mode, MySQL differentiates the log form of the treated record according to each specific Query statement executed, that is, choosing between Statement and Row.

Replication Common Architecture:

1.master-slaves

2. Master-master

3. Cascade Replication

4. Dual Master combined with cascading replication architecture

Meet MySQL Replication