MySQL Master-slave replication

(1) Replication of MySQL data

mysq1l master-slave replication

The MySQL database supports synchronous replication, one-way, asynchronous replication, one server acting as the primary server during replication, and one or more servers acting as slave servers. The primary server writes the update to the binary log file and maintains an index of the file to track the log loop. These logs can record updates that are sent to the slave server. When a primary server is connected from the server, it notifies the primary server where the last successful update was read from the server in the log. Receive any updates from the server from then on, and then block and wait for the primary server to notify the new updates.

Note that when you make a copy, all updates to the tables in the replication must be made on the primary server. Otherwise, you must be careful to avoid conflicts between updates to tables on the primary server and updates made to tables on the server.

(2) How MySQL data is replicated

MySQL replication tracks all changes to the database (updates, deletions, and so on) based on the primary server in the binary log. Therefore, to replicate, you must enable binary logging on the primary server.

Each slave server receives a saved update from the primary server that the primary server has logged to the binary log so that the server can perform the same updates to its copy of the data.

It is important to recognize that the binary log is only a record that starts at a fixed point in time when binary logs are enabled. Any settings from the server require a copy of the database on the primary server when the binary log is enabled on the primary. If you start from the server, its database is different from the state of the boot binary log on the primary server, which is likely to fail from the server.

The replication of the MySQL database requires three threads to be started to implement:

1 of these are on the primary server and two on the slave server. When the start slave is emitted, an I/O thread is created from the server to connect to the primary server and let it send statements that are recorded in the binary log. The primary server creates a thread that sends the contents of the binary log to the slave server. The thread can identify the Binlog dump thread in the output of show processlist on the primary server. Reads the contents of the primary server Binlog dump thread from the server I/O thread and copies the data to a local file from the server data directory, which is the relay log. The 3rd thread is a SQL thread that is created from the server to read the trunk log and perform the updates contained in the log.

In the preceding description, each slave server has 3 threads. There are multiple primary servers created from the server to create a thread from the server for each current connection, and each slave server has its own I/O and SQL threads.

This way the read and execute statements are divided into two separate tasks. If the statement execution is slow, the statement read task does not slow down. For example, if the server has not been running for some time, when booting from the server, its I/O thread can quickly request all binary log content from the master server, even if the SQL thread lags far behind. If the server stops before the SQL thread executes all the requested statements, the I/O thread has at least claimed everything so that a secure copy of the statement is saved to the local relay log from the server for execution from the next boot of the server. This allows the binary log on the primary server to be emptied, since it is no longer necessary to wait for its contents to be requested from the server.

(3) Problems that may exist

Latency issues--a new version of MySQL will roughly solve the problem; change the model to reduce latency

Error during replication--semi-sync is used to avoid

Problems with HA cluster switching--Available lock Master

This article from "Huangguanhua" blog, reproduced please contact the author!

MySQL Master-slave replication