Detailed description of Mysql master-slave synchronization configuration practices, detailed description of mysql master-slave practices

Detailed description of Mysql master-slave synchronization configuration practices, detailed description of mysql master-slave practices

1. Introduction

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

I believe that all the shoes I have read in this article are in full swing. Have you tried it?

Today, we have a master-slave mysql synchronization practice!

2. Environment Description

OS: ubuntu16.04

Mysql: 5.7.17

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

3. Go to practice

Tools

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 found the file /etc/mysql/mysql.conf.d/mysqld.cnf.

The configuration is as follows:

Bind-address = 192.168.33.22 # your master ipserver-id = 1 # In the master-slave architecture, each machine node needs a unique server-idlog_bin =/var/log/mysql/mysql-bin.log # enable 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-pPassword: # create an slave1 user and specify that the user can only log on to the host 192.168.33.33. Mysql> create user 'slave1 '@ '192. 168.33.33' identified by 'slavepass'; Query OK, 0 rows affected (192 sec) # assign REPLICATION SLAVE permissions to slave1. Mysql> grant replication slave on *. * TO 'slave1 '@ '192. 168.33.33'; Query OK, 0 rows affected (192 sec)

4. Apply a read lock to MYSQL

To ensure the consistency between the master database and 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 in the master database

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

7. Access the read lock of the master database

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 server Load balancer instance

1. Change the configuration file

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

The configuration change is as follows:

Bind-address = 192.168.33.33 # your slave ipserver-id = 2 # unique server-idlog_bin in master-slave structure =/var/log/mysql/mysql-bin.log # enable binlog

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

sudo systemctl restart mysql

3. import data from the master database. Export the dbdump. SQL file to make master-slave data consistent

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

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

$ mysql -u root -pPassword: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 MASTER_LOG_FILE = 'mysql-bin.000001 'and MASTER_LOG_POS = 613 values are obtained from the show master status above.

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

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.