MySQL master-slave replication and mysql master-slave Replication

MySQL master-slave replication and mysql master-slave Replication

The principle of MySQL master-slave replication is the binlog log, so our master node is responsible for database write operations, while the slave node is responsible for read operations, so that transactions are not required on the slave node, it can greatly improve the database performance. The problem at this time is how to synchronize data from the master node. We have used the binlog we mentioned earlier. The slave node synchronizes data with the master node through binlog.

Http://www.roncoo.com/course/view/658088f6e77541f5835b61800314083e

Three nodes, one master node and two slave nodes are planned as follows:

Master192.168.153.201

Slave119425153.152

Slave2192.168.153.153

All three nodes use the CentOS6.9 operating system, and the database uses mysql5.7.

Configure master

1. Create and authorize a user. For example, if our user is called repl, the syntax for creating a user is as follows:

create user ‘repl'

2. Authorization. The user must have the replication slave permission, and no other permissions are required.

grant replication slave on *.* to 'repl'@'192.168.153.%' identified by 'repl';

3. Enable binlog

See the documentation for enabling binlog.

Now, the master server is configured. Configure the slave server

Slave1

1. Modify the my. inf configuration file

[mysqld]server-id=2relay-log-index=slave-relay-bin.indexrelay-log=slave-relay-bin

2. Connect to the Master

Change master to master_host = '2017. 168.0.104 ', // Master server Ipmaster_port = 3306, master_user = 'repl', master_password = 'mysql', master_log_file = 'master-bin.000001 ', // log generated by the Master server master_log_pos = 0; change master tomaster_host = '20180101. 168.153.201 ', master_port = 3306, master_user = 'repl', master_passwo rd = 'repl', master_log_file ='/var/lib/mysql/mysql-bin.000001 ', master_log_pos = 0;

 

3. view the status

show slaves status\G

 

Here we will focus on two areas: Slave_IO_Running and Slave_ SQL _Running. Both must be yes to start the slave node. So where is the problem? A log will be prompted later.

 

Here we mean that the uuid of the two nodes is the same, so we need to make the uuid of the two machines different. Where is the uuid? In our data directory, auto. in the cnf file, you only need to modify this file.

 

Check the status again, and then it is yes.

4. Start

start slave

5. Disable

stop slave

6. Reset

resetslave