Mysql Master-slave replication principle

Mysql Master-slave replication principle

With the continuous development of the website business, the increasing number of users, the volume of data continues to grow, the number of database access is correspondingly increased, to a certain time, the first bottleneck of the site is in the database layer (here does not add the cache), this time the database needs to be properly split, such as sub-library or sub-table, etc. If the database in the sub-Library, after the table is still a bottleneck, it is good to consider the database read and write separation, especially when read more write less time.

MySQL in the Read and write separation scheme is the master-slave replication, master server will update the record to binary log, this is called the binary logs event in MySQL, the slaver server will be the binary in master Events is copied to its own trunk log (relay log), the slave server listens to the relay log and logs changes to the database. Describes the process of replication:

Of these, MySQL supports approximately 3 types of replication types:

1. Statement-based replication (also known as logical replication, logical replication), the advantage is that the binary log based on the copy of the statement can be well compressed, and the amount of log data is also small, the disadvantage is that the statement-based replication must be serialized.

2. Record-based replication (row-based Replication), recorded in the binary log changes in the actual data, the advantage is that any statement can work correctly, some statements are more efficient, the disadvantage is that the binary log is very large, You cannot use Mysqlbinlog to view binary logs.

3. Mixed-type replication, which uses statement-based replication by default, can be used for row-based replication once a statement-based, inaccurate copy is found.

Some of the problems that replication can solve:

1 distribution (data distribution)

2. Load balancing can be achieved (load balancing), commonly referred to as read/write separation

3. Data backup (Backups) can be achieved, but it cannot be used as a real data backup

4. High availability and fault-tolerant rows (such as high availability in the master slave model)

However, the master-slave replication also brings a series of other problems, the typical is the main never synchronization, leading to the reason that the main is not synchronized: The server is generally multi-core multi-threading, resulting in the primary node can perform multiple read and write operations at the same time, and the record binary log must be sequentially sequential records, from the node in a single copy Generate a relay log, and then execute the statement, where replication latency occurs. The other problem is that the write fails to expand, and the lock example rises.

The architecture of replication has the following basic principles:

(1) Each slave can have only one master;

(2) Each slave can only have a unique server ID;

(3) Each master can have a lot of slave;

(4) If you set Log_slave_updates,slave can be the master of other slave, thus spreading the update of master

There are several common modes of MySQL replication:

1. A master more from

It is easiest to make a replication system from a master and a slave. The slave does not communicate with each other and only communicates with master. It is mainly used for the low-cost expansion solution of the database-side of applications with large reading pressure ratio.

2. Primary master replication

Master-master replicates two servers, both master and slave of the other server. In this way, changes made by either party are applied to the other party's database through replication. In this replication architecture, each running is not the same db, such as the left is the DB1, the right is the db2,db1 from on the right side of the DB2 from the left, the two are mainly from each other, and then assist some monitoring services can also achieve a certain degree of high can be used.

3. Active-Passive mode (HA)

This pattern is changed by master-master structure, it avoids the disadvantage of m-m, in fact, it is a kind of system with fault tolerance and high availability. The difference is that only one of the nodes is providing read and write services , and the other node is always ready when the primary node fails to replace the service immediately. For example, through the corosync+pacemaker+drbd+mysql can provide such a set of highly available services, master and Standby mode and then follow the slave server, can also achieve read and write separation.

Problems that occur in master-slave replication:

1. Restrict read-only from the server to ensure consistent master-slave data.

Show global variables like '%read% '

Change slave global server variable READ_ONLY to ON

Set global read_only on

Grant read-only set global READ_ONLY = 1 from the node, and if you permanently change MySQL My.ini or my.cnf, set read_only = 1 in MySQL

2. Ensure transaction security during master-slave replication

If MySQL is busy it will cache the binary log in memory and write it to disk when it is not busy, provided that MySQL buffers the binary log event data. Setting the following parameters on Master set global Sync_binlog = 1 When things are committed, you must synchronize the binary logs, which degrades performance, but the data is very important.

Reprint please indicate the source of the article

Mysql Master-slave replication principle