Pay attention to the wechat public number [programmer Bai Ze], enter bai Ze’s knowledge sharing planet 🌍

preface

As the third article in this series, we will use bin log to implement master/slave replication. Master/slave replication is also the cornerstone of MySQL cluster to achieve high availability and separation of database reads and writes. Since this is a series of articles, in the last article (Portal) we introduced the case of checking the status and file information of bin log in MySQL and using bin log to restore data. Therefore, if relevant knowledge is involved in this article, it will not be repeated.

Data recovery and master/slave replication are two of the most important bin log functions

Master slave replication architecture

One master and one slave/One master and many slave/Cascading

Here is a diagram of the two basic master-slave replication architectures, with changes to the database being synchronized from one master to three slaves on the left (we mentioned in the previous article that bin logs only record changes to the database). On the right-hand side, the slave to which the data is synchronized continues to synchronize database changes as the “master” of the later slave, but the basic principles of these master slave replication modes are the same. For learning purposes, the demos explained later in this article will be based on the simplest model of one master, one slave.

Principle of master-slave replication

There are three threads involved in primary/secondary MySQL replication:

  • Master log dump thread:

    After connecting from the slave node to the master node, the master node creates a log dump thread for each connection from the slave node to communicate with it. The log dump thread reads the master bin log data file. Send it to the receiving thread on the corresponding slave side.

  • Slave I/O thread and SQL execution thread:

    Among the two threads on the slave side, the I/O thread is the bin log change log that connects to the master node and continuously requests new records on the master side. After receiving bin log changes from the log dump thread, the I/O thread saves them in the local slave relay log file (asynchronous buffering). Then the SQL thread reads the contents of the relay log file and converts them into SQL for execution on the slave end. The bin log is used to record changes to the database. The bin log is used to record changes to the database. The bin log is used to record changes to the database.

Master slave replication demo

Next, I will use my local MySQL database as the slave database, and the MySQL of my cloud server as the master node, and synchronize the data in the cloud database through the master/slave replication. And make sure that the server ids of the master and slave libraries are different before starting the next two steps.

  • Set the server id of the secondary library to 2. This value can be set by modifying the mysql configuration file and restarting it, or by using the set global parameter. For details, you can set the server id to 2 by using Google ~

  • The primary library server-id is set to 1

Configuring the Master Node

My cloud database is deployed using Docker images. If you are not familiar with container technology, you can also read a Docker introductory article I wrote (Portal). The following will cover some Docker operation commands, but if you just want to understand the master/slave replication process, there is no problem to continue to read.

First check the running Docker container through docker PS to find the ID of MySQL container

Enter the docker image of your MySQL container with the container ID.

Then log in to MySQL from within the MySQL container

Ensure that bin log is enabled on the master node

Create user test_copy on the master node, set password to 123456, and set REPLICATION SLAVE privileges.

A database transaction will be executed from the current position until the end of the next position. The next transaction is recorded from the position at the end of the previous transaction, so the execution steps of a transaction are stored between the two positions. The core principle is to convert the data files between the two POS points into SQL transactions and execute the SQL transactions again, no matter whether the data is recovered or replicated by master or slave.)

In order to facilitate the database management tool directly create a copy table, set the ID and USERNAME two fields, insert 3 data, let us see now the bin log data file pos point is pushed back, indicating that changes to the database are recorded in the bin log data file.

At this point, the master node is configured, users are created for the slave node, and data is prepared for synchronization.

Configuring the Slave Node

Now we use the local MySQL database as the secondary node, execute the following command, using the test_copy user registered on the primary node above to obtain the change data in the bin log of the primary node. Where master_host is the IP address of the server corresponding to your cloud database: xx.xx.xx.xx.

Run the start slave command to synchronize the changed data of the master node from the node.

Finally in the local database (query from the database to get copy database was successfully synchronized down! Error: error: error: error: error: error: error: error: error: error: error: error: error: error: error: error: error: error There is also a way to synchronize data through the global transaction ID instead of the POS point, which needs additional learning and will not be described in this article.

conclusion

This article explains the basic principle of using bin log for master-slave replication. Combined with the data recovery in the last article, the two functions of binary log have been explained completely. I believe that you can answer the relevant knowledge points fluently in the interview.

I am Bai Ze, a programmer/student party who is keen on knowledge sharing. I follow the wechat public account [programmer Bai Ze]. I will synchronize my blog articles, reply to my resume, and also get the resume template I am using.

P3-juejin.byteimg.com/tos-cn-i-k3…


\