Detailed explanation of Mysql master slave synchronous configuration actual combat

1. Introduction

I have written an article before: the principle of Mysql master-slave synchronization.

I believe that all the children's shoes who have read this article are gearing up and jumping to try?

Today, we will have an mysql master-slave synchronous actual combat!

2. Environmental description

os:ubuntu16.04

mysql:5.7.17

The following actual combat drills are all based on the above environment. Of course, other environments are similar.

3. Enter actual combat

Tools

2 machines:

master IP:192.168.33.22

slave IP:192.168.33.33

Operation on master Machine

1. Change the configuration file

We found the file /etc/mysql/mysql.conf.d/mysqld.cnf .

The configuration is as follows:

bind-address = 192.168.33.22 #your master ip
server-id = 1 # In master-slave In the architecture, each machine node needs to have only 1 Adj. server-id
log_bin = /var/log/mysql/mysql-bin.log # Open binlog

2. Restart mysql for the configuration file to take effect.

sudo systemctl restart mysql

3. Create mysql user with master-slave synchronization.

$ mysql -u root -p
Password:

## Create slave1 User and specifies that the user can only be used on the host 192.168.33.33 Log in on. 
mysql> CREATE USER 'slave1'@'192.168.33.33' IDENTIFIED BY 'slavepass';
Query OK, 0 rows affected (0.00 sec)

## For slave1 Endowed with REPLICATION SLAVE Permission. 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'192.168.33.33';
Query OK, 0 rows affected (0.00 sec)

4. Add a read lock to MYSQL

In order to keep the data of the master library and the slave library identical, we first added a read lock to mysql to make it read-only.

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

5. Record the position of MASTER REPLICATION LOG

This information will be used later.

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |  613 |    |     |     |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

6. Export the existing data information in master DB

$ mysqldump -u root -p --all-databases --master-data > dbdump.sql

7. Contact the read lock of master DB

mysql> UNLOCK TABLES;

8. Add the dbdump. sql file copy to slave from Step 6

scp dbdump.sql ubuntu@192.168.33.33:/home/ubuntu

Operation on slave Machine

1. Change the configuration file

We found the file /etc/mysql/mysql.conf.d/mysqld.cnf .

Change the configuration as follows:

bind-address = 192.168.33.33 #your slave ip
server-id = 2 #master-slave In the structure, only 1 Adj. server-id
log_bin = /var/log/mysql/mysql-bin.log # Open binlog

2. Restart mysql for the configuration file to take effect

sudo systemctl restart mysql

3. Import from master DB. Export the dbdump. sql file to make master-slave data 1

sudo systemctl restart mysql

0

4. Make the connection between slave and master, so as to synchronize

sudo systemctl restart mysql

1

The values of MASTER_LOG_FILE= 'mysql-bin. 000001' and MASTER_LOG_POS=613 are obtained from SHOW MASTER STATUS above.

After this setting, master-slave synchronization can be performed ~