A brief analysis of MySQL replication

MySQL replication is a great feature of MySQL, which copies data from one MySQL instance to another MySQL instance. The entire process is asynchronous, but due to its efficient performance design, the latency of replication is very small. MySQL replication is widely used in real-world application scenarios to ensure the security and extensible design of data system data.

1, the meaning of MySQL replication function

Internet Application System, a properly designed Web application server In most cases is stateless (except session, session sharing can be resolved through the Web container), the Web Application Server extension and cluster is relatively simple. But the clustering and replication of the database is not so easy. Database vendors have also been trying to make their products easy to replicate and cluster like Web application servers.

The advent of mysqlreplication enables us to easily replicate data from one database to multiple servers for data backup, master-slave sparing, and DB clustering. This effectively improves the processing ability of the database, improves the data security and so on.

2, Mysqlreplication realization principle

MySQL Replication (replication) is an asynchronous copy, copied from one mysqlinstace (called master) to another mysqlinstance (called slave). The entire replication operation is mainly done by three processes, of which two processes are in slave (SQL process and IO process), and the other process is on master (IO process).

To implement replication, you must first turn on the Binarylog (bin-log) function on the master side, otherwise it cannot be implemented. Because the entire replication process is actually a variety of operations that are logged in the execution log that slave obtains the log from the master side and then executes it in its own full sequence. The basic process for replication is as follows:

(1) Slave The above IO process connects to master, and requests the log content from the specified location (or from the beginning of the log) to the designated log file;

(2) master receives a request from the IO process from the slave, the IO process that is responsible for copying the log information after the specified log is read from the requested information and returned to the slave IO process. 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 clearly tell the master "I need to start from a certain location in a bin-log in the next log content, please send me";

(4) Slave's SQL process detects new additions to the relay-log and immediately resolves the contents of Relay-log as executable content at the real execution time on the master side and executes on its own.

3. Replication Implementation Level

There are three modes of MySQL replication: Statement level, Row level, Mixed level. Different levels of replication can result in a different form of the master-side binary log file generation.

3.1 Statement level replication

This mode is the earliest replication mode, the main process is the master side will each change the data to record the query, the slave side at the time of copying according to the binary file re-execute the same query. The advantage of this mode is that the master side does not need to record the change of each row of data, the binary log file size is small, the IO cost is low, the speed is fast.

Accordingly, the disadvantage of this pattern is as follows: Because the execution statement is logged, additional contextual information is required to know the execution of each statement to ensure that the same operation is performed at the slave end with the same results as the master. However, due to the increasing number of MySQL functions, this mode of replication needs to be considered more and more, the probability of the occurrence of a bug is also large. Starting with MySQL 5.0, MySQL replication solves a number of problems with the inability to copy or copy errors that occurred in previous releases, but as MySQL progresses, the challenge will become more severe.

3.2 Row Level replication

MySQL developers are aware of the problem with the statement level and start offering the row level mode at 5.1.5. The main process of this pattern is that the MySQL two-level log file will record every row of data modifications and then make the same changes on the slave side. The advantage of this pattern is that the log file does not record the context of the SQL statement execution, but only records which data has been modified and what it looks like, which avoids replication problems such as stored procedures, trigger calls, and triggers that are not executed correctly in certain situations.

Similarly, the model also has drawbacks: the daily mass multiplied. For example, when executing a statement such as ALTER TABLE, each record in the table is recorded in the log because of changes in the tables structure. This greatly increases the IO cost of the replication process, resulting in decreased speed and degraded performance.

3.3 Mixed Level replication

MySQL starts with 5.1.8 and offers mixed level. This model combines the advantages of the previous two modes and avoids the disadvantages of both. In this mode, MySQL distinguishes the format of the log file according to each statement executed. For example, when it comes to complex stored procedures, the row level is used to circumvent the problems that some scenes in the statement are not able to replicate, and when it comes to operations such as ALTER TABLE, use statement to circumvent row Level brings a huge amount of log issues.

4. MySQL replication Detailed Configuration 4.1 Environment Introduction 4.1.1 Master Environment

1) Operating system: Ubuntu12.04 32-bit

2) MySQL version: 5.5.40-0ubuntu0.12.04.1-log (Ubuntu)

3) ip:192.168.245.140

4.1.2 Slave Environment Introduction:

1) Operating system: Ubuntu12.04 32-bit

2) MySQL version: 5.5.40-0ubuntu0.12.04.1-log (Ubuntu)

3) ip:192.168.245.139

4.2 Configuring the 4.2.1 master configuration

1) MY.CNF Configuration

#VI /etc/mysql/my.cnf[mysqld]log-bin=mysql-bin   //[must] enable binary logging server-  ID=+/       /[must] server unique ID, default is 1, usually take IP last paragraph

2) Restart MySQL

sudo /etc/init.d/mysql restart

3) Create an account on the primary server and authorize slave

123456 MySQL ' Mysync '@'%'123456'//  Generally do not use the root account, "%" means that all clients may be connected, as long as the account, the password is correct, here can be specific client IP instead, such as 192.168.245.139, enhance security. 

4) Log in to MySQL and query the status of master

Mysql>Show master status; +------------------+----------+--------------+------------------+| File             | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| Mysql-bin. 000004 |      308 |              |                  | +------------------+----------+--------------+------------------+1 in Set (0.00 sec)

Note: Do not re-operate the master server MySQL after performing this step to prevent changes in the primary server state value.

4.2.2 Slave configuration

1) MY.CNF Configuration

#VI /etc/mysql/my.cnf[mysqld]log-bin=mysql-bin   //[must] enable binary logging server-  ID=139       //[must] server unique ID, default is 1, usually take IP last paragraph

2) Restart MySQL

sudo /etc/init.d/mysql restart

3) Configure the slave from the server:

Mysql>change Master to master_host='192.168.245.140', master_user='  Mysync', master_password='123456', master_log_file='  mysql-bin.000004', master_log_pos=308;   // Note Do not disconnect, "308" without single quotation marks. MYsql>start slave;    // To start the Copy from Server feature

4) Check the status of the replication function from the server:

Mysql>Show Slave Status\g***************************1. Row ***************************slave_io_state:waiting forMaster to send event Master_host:192.168.245.140master_user:root Master_port:3306Connect_retry: -Master_log_file:mysql-bin.000003Read_master_log_pos:5669Relay_log_file:mysqld-relay-bin.000002Relay_log_pos:5482Relay_master_log_file:mysql-bin.000003Slave_io_running:yes Slave_sql_running:yes replicate_do_db:replicate _ignore_db:replicate_do_table:replicate_ignore_table:replicate_wild_do_table:replicate_wild_ Ignore_table:last_errno:0Last_error:skip_counter:0Exec_master_log_pos:5669Relay_log_space:5639until_condition:none Until_log_file:until_log_pos:0Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SS L_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0master_ssl_verify_server_cert:no Last_io_errno:0Last_io_error:last_sql_errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: $1RowinchSet (0.00Sec

Note: The slave_io and slave_sql processes must be running normally, that is, the Yes state, otherwise it is the wrong state.

4.3 Master-Slave Server testing

master server MySQL, set up a database, and build a table in this library to insert a piece of data, to see whether from the library has also increased the corresponding database, data tables, data.

5. MySQL Replication Common Architecture Summary 5.1 master-slave backup architecture Design

Description: Two MySQL servers if one of the MySQL servers hangs up, the other one can immediately take over the job. So we have to make sure that the data of the two MySQL databases is exactly the same, and that when a reboot is stopped, it will not be accessed by the client, but will be used as a standby to synchronize the data with the MySQL that is currently working, until the service has been hung up and replaced by its work. This round-the-cycle realization of MySQL's high availability. Note: Slave does not provide services; slave and master are in the same LAN to ensure the speed of master-slave replication and the stability of the connection.

5.2 Primary Master Backup architecture design a

Summary: MySQL Master backup architecture a--Two servers, that is, a write data can be synchronized to B, b write data can be synchronized to a. The proxy server is responsible for load balancing reads and writes.

Disadvantage: The conflict problem of the self-increment primary key cannot be resolved, and when the write operation is frequent, it causes concurrency problems.

Application scenario: Not much write operation, no self-increment primary key, master, standby machine simultaneously undertake read and write tasks, save the machine, suitable for the machine tense scene.

5.3 Primary master backup architecture design B

MySQL Master backup schema b--two servers, that is, a write data can be synchronized to B, B-write data can be synchronized to a. However, the application server uses keepalived to write to master only, which is one of them, and the proxy server is responsible for load balancing the read operations.

Cons: Requires additional troubleshooting of read and write separations

Pros: No additional scripting is required to control the conversion of primary and standby roles; Data consistency assurance

A brief analysis of MySQL replication