What is mysql master-slave

In mysql, master-slave refers to the establishment of two identical databases, one of which is the main database and the other is the secondary database, which can prevent data loss due to database server downtime. It can also provide failover, read-write separation and backup functions.

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

What is mysql master-slave

Master-slave introduction

The so-called mysql master-slave is to create two identical databases, one of which is the main use database, and the other is a secondary database. Generally in enterprises, the database server that stores more important data needs to be configured with a master-slave configuration. This can prevent data loss due to database server downtime, and also ensure that too much business volume and data The quality of service (server response speed) when there are too many users and too many visitors, and it can also provide functions such as failover, read-write separation, and backup.

Master-slave form

One master and one slave

Master-master replication: used as backup. When the master server fails, another master server It will automatically top up.

One master and multiple slaves: Used to achieve separation of reading and writing. It is used when there are fewer write operations and more read operations. The master server is used to implement write operations, and the slave server is used to implement read operations.

Multiple masters and one slave: Used to achieve separation of reading and writing. It is used when there are many write operations and few read operations. The master server is used to implement write operations, and the slave server is used to implement read operations.

Traditional master-slave

Traditional master-slave replication is mainly replication based on the location of the binary log file, so the master must start binary logging and establish a unique server ID, replication Each server in the group must be configured with a unique server ID. If you omit server-id (or explicitly set it to its default value of 0), the master will reject any connections from the slave.

gtid master-slave

One of the new features of MySQL 5.6, the global transaction identifier (GTID) is a unique identifier created and associated with the source (master) associated with each transaction committed on the server. This identifier is not only unique, but also unique across all servers in a given replication setup. There is a one-to-one mapping between all transactions and all GTIDs. It is composed of the server ID and transaction ID. This global transaction ID is not only unique on the original server, but also unique on all mysql servers that have a master-slave relationship. It is precisely because of this feature that mysql's master-slave replication becomes simpler and database consistency is more reliable. A GTID is only executed once on a server to avoid data confusion or master-slave inconsistency caused by repeated execution.

The principle of master-slave replication

The workflow of master-slave replication: The master database records all user write operations (except additions, deletions, modifications, and checks) into the binlog log And a log dump thread is generated, and the slave library generates I/O and SQL threads. The I/O thread of the slave library sends a request in the form of an I/O stream to the log dump thread of the main library. The log dump thread of the main library receives the request from the slave library. After the library I/O thread requests, the binlog log is sent to the slave library. After the slave library I/O thread receives the binlog log, it writes it into the relay log (relay log) file, and then the SQL thread of the slave library relays it. The logs in the log file are parsed into sql scripts, and finally the generated sql script files are executed to achieve consistent master-slave operations and achieve final data consistency.

Recommended learning: mysql video tutorial

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