MySQL Master-slave replication implementation process

first, what is the master-slave replication

The DDL and DML operations in the primary database are transferred through the binary log (BINLOG) to the slave database, and then the logs are re-executed (redo) so that the data from the database is consistent with the primary database.

second, the role of master-slave replication

1. There is a problem with the primary database, you can switch to from the database.

2, can carry on the database level read and write separation,

3. Daily backups can be performed from the database

third, the replication process

Binary log: Binary logs for the primary database

Relay log: Relay logs from the server

The first step: Master writes the action record serially to the Binlog file before each transaction updates the data.

Step Two: Salve open an I/O thread, the thread opens a normal connection in master, and the main job is Binlog dump process. If the read progress has been followed by master, go to sleep and wait for master to generate a new event. The ultimate purpose of the I/O thread is to write these events to the relay log.

Step three: SQL thread reads the trunk log and executes the SQL events in the log sequentially, keeping the data in the primary database consistent.

Iv. Specific operations of master-slave replication

I am installing two MSYQL instances under different paths on the same windows. It is recommended that the installation version of the master-slave two MySQL is consistent, although my own is inconsistent.

1. Modify the configuration file of master/slave database separately My.ini

Master

3306 is the MySQL default port number, where the master instance can not be modified; Server-id is used to specify a unique ID, the different MySQL instances do not repeat, BINLOG-DO-DB specifies the database to be replicated; Log-bin is used to open the binary log file.

Salve

Because the master-slave database will be running on a computer, so the port needs to be set to a different, here is 3307

REPLICATE-DO-DB: The name of the database that needs to be synchronized, consistent with the configuration on master.

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

This new account can be queried in the table Mysql.user:

The first time I operated, I completed the creation of this account, but to the actual replication, but found that the replication did not succeed, troubleshooting the error when the master generated Binlong no problem, and then view the status of slave:

There is a line error at the end:

This account cannot be connected to master using Weidai, so it should not have been acquired to Master Binlog, which prevents the relay log from being generated.

I repeatedly checked the account and password did not find the problem, and then find the relevant information, only to find that because the master to create a new user when the one step less:

After setting up the user or changing the password, flush privileges is required to flush the MySQL system permission related table, otherwise access will be denied. This is why the previous error occurred. Another option is to restart the MySQL server for the new settings to take effect.

3, the main database to obtain the location of the data at this time, mainly for starting from the data, copying the starting position of the data, but before the acquisition of this state value, the primary database can no longer modify the data operation, so you need to set the read lock valid

4, the main library for data backup, backup means there are many kinds, here do not expand the introduction, you can refer to my previous article, after the end of the backup can release the read lock, the main library can write operations

5, start from the database, to restore the data just backed up, this time the master-slave database in the backup point of time data is consistent.

6. Configuration of replication behavior from the database

7, this time configuration is complete, but from the database can not be synchronized, need to start the slave thread

8. Create table and new data in master, observe in Slave:

As can be seen, I do in the master of the operation, can be reflected in the slave, this time slave as a master mirror.

Five, master-slave synchronization state interpretation

Use the commands on slave to view:

Because the typesetting is too ugly, I tidy up the following:

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

----------------------------------------------------------Gorgeous split Line-------------------------------------------

Slave_io_running:yes

Slave_sql_running:yes

These two threads are mentioned earlier and are two very important threads involved in the replication process on a slave. Yes indicates normal, no indicates an exception.

Slave_io thread is mainly to copy the contents of the Binlong Log on master to the Slave relay log (Relay_log), generally the probability of the problem is small, most of the problems are due to permissions or network problems, resulting in the connection is not master. As mentioned in the previous error.

The SLAVE_SQL thread is responsible for executing the SQL in the trunk log again, which is relatively more likely to fail. If someone manually inserts some records from the library, causing primary key conflicts when the master-slave synchronization occurs.

Slave_io_state:waiting for Master to send event

This status indicates that the trunk log synchronization is complete, waiting for the master to have a new event to produce.

MySQL Master-slave replication implementation process