Explain how MySQL implements master-slave replication, and explain how mysql implements master-slave replication.

Explain how MySQL implements master-slave replication, and explain how mysql implements master-slave replication.

1. What is master-slave replication?

The DDL and DML operations in the primary database are transmitted to the slave database through binary logs, and these logs are re-executed (redone ); so that the data in the slave database is consistent with that in the master database.

Ii. Role of master-slave Replication

1. If there is a problem with the primary database, you can switch to the slave database.

2. You can perform read/write splitting at the database level,

3. Routine backup can be performed on the slave Database

Iii. Replication Process

Binary log: Binary log of the primary database

Relay log: Relay log of the slave server

Step 1:The master writes the operation records to the binlog file serially before each transaction updates data.

Step 2:Enable an I/O Thread in salve. This Thread opens a normal connection in the master, mainly working on binlog dump process. If the read progress keeps up with the master node, it goes to sleep and waits for the master node to generate a new event. The objective of the I/O thread is to write these events to the relay log.

Step 3:SQL Thread reads the relay log and executes the SQL events in the log sequentially to maintain consistency with the data in the primary database.

4. Specific operations on master-slave Replication

I installed two msyql instances in different windows paths. It is recommended that the master and slave mysql installation versions are the same here, although my own is inconsistent.

1. modify the configuration file my. ini of the master and slave databases respectively.

Master

3306 is the default port number of mysql. You do not need to modify it in the master instance. server-id is used to specify a unique id. Different mysql instances do not need to be repeated; binlog-do-db specifies the database to be copied; log-bin is used to enable binary log files.

Salve

Because the master and slave databases will run on a single computer, the port needs to be set to different. Here is 3307

Replicate-do-db: name of the database to be synchronized, consistent with the configuration on the master.

2. Create an account dedicated to replication on the master: weidai/123456

The newly added account can be queried in the mysql. user table:

During my first operation, the account was created here, but it was found that the account was not copied successfully, check whether the binlong generated by the master node is correct during troubleshooting. Then, check the slave status:

There is such an error at the end:

The account weidai cannot be used to connect to the master node. Therefore, the binlog of the master node is not obtained, and the relay log cannot be generated.

I checked my account and password repeatedly and found no problems. Then I found out that I did not perform any operations when I created a new user on the master node:

After a new user is set or the password is changed, flush privileges is required to refresh the MySQL system permission table. Otherwise, access is denied. This is why the preceding error occurs. Another way is to restart the mysql server to make the new settings take effect.

3. Obtain the data location in the primary database at the moment. It is mainly used to copy the data starting position after the data is started, but before obtaining the status value, the primary database cannot modify data any more. Therefore, you need to set the read lock to be valid.

4. There are many backup methods for data backup in the master database. I will not introduce them here. refer to my previous article. After the backup is complete, the read lock can be released and the master database can perform write operations.

5. Start the slave database and restore the backup data. At this time, the data of the master and slave databases at the backup time is consistent.

6. Configure replication behavior on the slave Database

7. The configuration is complete at this time, but the slave database cannot be synchronized. You need to start the slave thread.

8. Create a table and add data in the master. Observe the following in slave:

As you can see, all the operations I perform in the master can be reflected in the slave. At this time, the slave is like a mirror of the master.

5. Explanation of master-slave synchronization status

Run the following command on slave:

Because the layout is too ugly, I will organize it as follows:

Slave_IO_STATE: Waiting for master to send event

Master_host: 127.0.0.1

Master_user: weidai

Master_port: 3306

Connnect_retry: 60

Master_log_file: mysql-bin.000005

Read_Master_log_pos: 1662

Relay_log_file: AE6Z *****-relay-bin.000002

Relay_log_pos: 1415

Slave_IO_Running: yes

Slave_ SQL _Running: yes

------------------------------------------------------------ Lili split line -------------------------------------------

Slave_IO_Running: yes

Slave_ SQL _Running: yes

The two threads mentioned above are two very important threads involved in the replication process on slave. YES indicates normal, and NO indicates exception.

The Slave_IO thread mainly copies the binlong log Content on the master to the slave relay log (Relay_log). Generally, there is little probability of a problem. Most of the problems are caused by permissions or network problems, as a result, the master cannot be connected. As mentioned above.

The Slave_ SQL thread executes the SQL statement in the relay log, which is more likely to cause errors. If some records are manually inserted from the database, a primary key conflict occurs during master-slave synchronization.

Slave_IO_STATE: Waiting for master to send event

This status indicates that the relay log synchronization is complete, waiting for a new event to be generated by the master.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.