Home >Database >Mysql Tutorial >Detailed explanation of Mysql master-slave synchronization configuration sample code

Detailed explanation of Mysql master-slave synchronization configuration sample code

黄舟
黄舟Original
2017-03-16 14:00:581126browse

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=&#39;192.168.33.22&#39;,
 -> MASTER_USER=&#39;slave1&#39;,
 -> MASTER_PASSWORD=&#39;slavepass&#39;,
 -> MASTER_LOG_FILE=&#39;mysql-bin.000001&#39;,
 -> 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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn