Detailed explanation of MySQL master-slave replication principle

Detailed explanation of the principle of MySQL master-slave replication

First of all, the database has a "bin-log" binary file that records all SQL statements; then Copy the SQL statement from the "bin-log" file of the master database; finally execute the copied SQL statement in the "relay-log" redo log file of the slave database.

Master-slave replication

Master-slave replication is used to establish a database environment that is exactly the same as the master database, which is called The slave database and the master database are generally quasi-real-time business databases. In the most commonly used MySQL database, single item and asynchronous assignment are supported. During the assignment process, one server acts as the master server and the other server acts as the slave server; at this time, the master server writes the update information to a specific binary file.

And will maintain an index of files to track log rotation. This log can be recorded and sent to the slave server for updates. When a slave server connects to the master server, the slave server notifies the master server of the location of the last successful update read from the slave server's log file. The slave server will then receive any updates that have occurred since that time, and then lock and wait until the master server notifies new updates.

The role of master-slave replication

1. Do hot backup of data. As a backup database, after the main database server fails, it can be switched to the slave database to continue working to avoid data lost.

2. 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.

3. Separation of reading and writing enables the database to support greater concurrency. Especially important in reports. Because some report SQL statements are very slow, they cause table locks and affect front-end services. If the front desk uses the master and the report uses the slave, then the report SQL will not cause a front desk lock, ensuring the front desk speed.

Recommended tutorial: "PHP"

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