Home >Database >Mysql Tutorial >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!