Shulou(Shulou.com)05/31 Report--

Most people do not understand the knowledge points of this article "what is the concept of mysql master and slave", so the editor summarizes the following content, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "what is the concept of mysql master and slave" article.

In mysql, master-slave refers to the establishment of two identical databases, one as the primary database and the other as the secondary database, which can prevent data loss caused by database server downtime and provide the functions of failover, read-write separation and backup.

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

What is the master and slave of mysql?

Master-slave introduction

The so-called mysql master-slave is to set up two identical databases, one of which is the primary database and the other is the secondary database. Generally in the enterprise, the database server that stores the more important data needs to be configured with the master-slave, which can prevent the data loss caused by the database server downtime. It can also guarantee the quality of service (server response speed) when there is too much business, too much data 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

Primary master replication: used as a backup, when the primary server fails, another primary server will be automatically topped up.

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

Multi-master and one slave: used to achieve the separation of read and write, when there are more write operations and less read operations, the master server is used to achieve write operations, and the slave server is used to achieve read operations.

Traditional master and subordinate

Traditional master-slave replication is mainly based on the location of binary log files, so the master must start binary logging and establish a unique server ID, and each server in the replication 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 device will reject any connections from the slave device.

Gtid master and slave

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

The principle of master-slave replication

The workflow of master-slave replication: the master database records all the user's write operations (addition, deletion and modification, except checking) in the binlog log and generates a log dump thread, which generates the binlog O and SQL threads from the library, and sends the request in the form of the I / O stream from the I / O thread of the library to the log dump thread of the master database. The log dump thread of the master database sends the SQL log to the slave database after receiving the request from the Iameo thread of the slave library. After receiving the binlog log from the library, the SQL thread writes it into the relay log (Relay Log) file, and then the SQL thread of the slave library parses the log in the relay log file into a sql script, and finally executes the generated sql script file to achieve the consistency of master and slave operations and the final data consistency.

The above is the content of this article on "what is the concept of mysql master and slave". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.