Detailed explanation of Mysql master-slave synchronization configuration practice

1. Introduction

I wrote an article before: The principle of MySQL master-slave synchronization.

I believe that everyone who has read this article is eager to give it a try, right?

Today we will have a practical experience of MySQL master-slave synchronization!

2. Environmental description

OS: Ubuntu 16.04

mysql:5.7.17

The following practical exercises are all based on the above environment. Of course, other environments are similar.

3. Entering actual combat

tool

2 machines:

master IP:192.168.33.22

slave IP:192.168.33.33

Operations on the master machine

1. Change the configuration file

We find 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 the master-slave architecture, each machine node needs to have a unique server-id
log_bin = /var/log/mysql/mysql-bin.log #Open binlog

2. Restart MySQL to make the configuration file take effect.

sudo systemctl restart mysql

3. Create a mysql user for master-slave synchronization.

$ mysql -u root -p
Password:

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

##Give slave1 the 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 database consistent with that of the slave database, we first add 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 location of the 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 the master DB

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

7. Access the read lock of the master DB

mysql> UNLOCK TABLES;

8. Copy the dbdump.sql file in step 6 to the slave

scp dbdump.sql [email protected]:/home/ubuntu

Operations on the slave machine

1. Change the configuration file

We find 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 #Unique server-id in the master-slave structure
log_bin = /var/log/mysql/mysql-bin.log #Open binlog

2. Restart MySQL to make the configuration file take effect

sudo systemctl restart mysql

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

$ mysql -u root -p < /home/ubuntu/dbdump.sql

4. Establish a connection between slave and master for synchronization

$ mysql -u root -p
Password:

mysql> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO
 -> MASTER_HOST='192.168.33.22',
 -> MASTER_USER='slave1',
 -> MASTER_PASSWORD='slavepass',
 -> MASTER_LOG_FILE='mysql-bin.000001',
 -> MASTER_LOG_POS=613;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

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

After this setting, you can perform master-slave synchronization~

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.