Detailed explanation of Mysql master-slave synchronization configuration sample code

This article mainly introduces the detailed explanationMysqlThe actual practice of master-slave synchronization. It analyzes the principle and implementation of Mysql master-slave synchronization with examples. It is of great practical value and friends in need can refer to it.

1. Introduction

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

I believe that children’s shoes who have read this article are eager to give it a try, right?

Today we will have a practical mysql master-slave synchronization!

2. Environment description

os:ubuntu16.04

mysql:5.7.17

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

3. Enter actual combat

Tools

2 machines:

master IP: 192.168. 33.22

slave IP:192.168.33.33

Operation on the master machine

1. ChangeConfiguration 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 #在master-slave架构中，每台机器节点都需要有唯一的server-id
log_bin = /var/log/mysql/mysql-bin.log #开启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:

##创建slave1用户，并指定该用户只能在主机192.168.33.33上登录。
mysql> CREATE USER 'slave1'@'192.168.33.33' IDENTIFIED BY 'slavepass';
Query OK, 0 rows affected (0.00 sec)

##为slave1赋予REPLICATION SLAVE权限。
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 main database and the slave database consistent, we first add a read lock to mysql to make it Become 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. Contact the master DB Read lock

mysql> UNLOCK TABLES;

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

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

Operations on the 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结构中，唯一的server-id
log_bin = /var/log/mysql/mysql-bin.log #开启binlog

2. Restart mysql to make the configuration file take effect

sudo systemctl restart mysql

3. Import from master DB. The exported dbdump.sql file to make the master-slave data consistent

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

4. Make the slave establish a connection with the master to synchronize

$ 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 above SHOW MASTER STATUS.

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

The above is the detailed content of Detailed explanation of Mysql master-slave synchronization configuration sample code. For more information, please follow other related articles on the PHP Chinese website!