What is mysql master-slave replication

In mysql, master-slave replication means that data can be copied from a MySQL database server master node to one or more slave nodes. Asynchronous replication is used by default. The benefits of using master-slave replication: 1. Let the master database be responsible for writing and the slave database be responsible for reading. When the master database locks the table, the normal operation of the business can be ensured by reading from the slave database; 2. Hot backup of data can be done; 3. Expanding the architecture can reduce the frequency of disk I/O access and improve the I/O performance of a single machine.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

What is mysql master-slave replication?

MySQL master-slave replication means that data can be copied from a MySQL database server master node to one or more slave nodes. MySQL uses asynchronous replication by default, so that the slave node does not have to access the master server all the time to update its own data. Data updates can be performed on a remote connection. The slave node can copy all databases or specific databases in the master database, or specific tables. .

Why is master-slave replication needed?

1. In a system with complex business, there is a situation where a SQL statement needs to lock the table, resulting in the temporary inability to use the read service, which will greatly affect the running business. , use master-slave replication, let the main library be responsible for writing, and the slave library be responsible for reading. In this way, even if the main library locks the table, the normal operation of the business can be guaranteed by reading from the slave library.

2. Hot backup of data

3. Architecture expansion. The business volume is getting larger and larger, and the frequency of I/O access is too high, which cannot be satisfied by a single machine. At this time, multi-database storage is used to reduce the frequency of disk I/O access and improve the I/O performance of a single machine.

mysql replication principle

Principle:

(1) The master server will change the data Record the binary binlog log. When the data on the master changes, the changes are written to the binary log;

(2) The slave server will detect whether it occurs in the master binary log within a certain time interval. Change, if a change occurs, start an I/OThread to request the master binary event

(3) At the same time, the master node starts a dump thread for each I/O thread to send binary events to it and save it. To the local relay log of the slave node, the slave node will start the SQL thread to read the binary log from the relay log and replay it locally to make its data consistent with that of the master node. Finally, I/OThread and SQLThread will enter the sleep state. , waiting to be woken up next time.

In other words:

• The slave library will generate two threads, one I/O thread and one SQL thread;
• I The /O thread will request the binlog of the main library and write the obtained binlog to the local relay-log (relay log) file;
• The main library will generate a log dump thread to provide the slave with The library I/O thread passes binlog;
• SQL thread will read the log in the relay log file and parse it into sql statements to execute one by one;

##Note:

• – The master records the operation statement into the binlog log, and then grants the slave remote connection permission (the master must enable the binlog binary log function; usually for data security reasons, the slave also Turn on the binlog function).

• –slave opens two threads: IO thread and SQL thread. Among them: the IO thread is responsible for reading the binlog content of the master into the relay log; the SQL thread is responsible for reading the binlog content from the relay log and updating it to the slave database, so as to ensure that the slave data and the master data are maintained Consistent.

• –Mysql replication requires at least two Mysql services. Of course, Mysql services can be distributed on different servers, or multiple services can be started on one server.

• -Mysql replication is best to ensure that the Mysql versions on the master and slave servers are the same (if the versions cannot be consistent, then ensure that the version of the master node is lower than the version of the slave node)

• –The time between the master and slave nodes needs to be synchronized

Specific steps:

1. The slave library connects to the master library by manually executing the change master to statement, providing all the conditions for the connected user (user, password, port, ip), and letting the slave library know the starting point of the binary log Position (file name position number); start slave

2. Establish a connection between the IO thread of the slave library and the dump thread of the main library.

3. Based on the file name and position number provided by the change master to statement, the slave library initiates a binlog request to the master library from the IO thread.

4. The main library dump thread sends the local binlog to the slave library IO thread in the form of events according to the slave library's request.

5. Receive binlog events from the library IO thread and store them in the local relay-log. The transmitted information will be recorded in master.info

6. Apply relay-log from the database SQL thread, and record the applied information to relay-log.info. By default, the applied relay will be automatically cleaned up purge

mysql master-slave form

(1) One master and one slave

(2) Master-master replication

(3) One master and multiple slaves

(4) Multiple masters and one slave

(5) Cascade replication

mysql master-slave synchronization delay analysis

Mysql’s master-slave replication is a single-threaded operation, and the main library handles all DDL The logs generated by and DML are written into the binlog. Since the binlog is written sequentially, it is very efficient. The slave's sql thread thread replays the DDL and DML operation events of the main library in the slave. The IO operations of DML and DDL are random, not sequential, so the cost is much higher. On the other hand, since the sql thread is also single-threaded, when the concurrency of the main library is high, the number of DML generated exceeds the capacity of the slave's SQL thread. The processing speed, or when there is a large query statement in the slave that causes a lock wait, then the delay occurs.

Solution:

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

2. Separate reading and writing in a single library, one master and multiple slaves, master writes and slaves read, to spread the pressure. In this way, the pressure of the slave library is higher than that of the main library, protecting the main library.

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

4. MySQL for different businesses is physically placed on different machines to spread the pressure.

5. Use a better hardware device than the main database as the slave. Mysql will have less pressure and the delay will naturally become smaller.

6. Use more powerful hardware equipment

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is mysql master-slave replication. For more information, please follow other related articles on the PHP Chinese website!