Master slave replication

working principle

Master slave replication is a high availability and high performance solution provided by MySQL.

The working principle is divided into the following three steps:

1. The master records the data changes in * * binlog * *.

2. The I/O thread from the slave saves the binary log read from the master library to its own * * relay log * *.

3. Execute the relay log from the SQL thread of the library and redo the log in the relay log, that is, execute SQL again and update your own database to achieve data consistency.

Key points:

Master-slave replication is not synchronous in full real time, but asynchronous in real time. The process of synchronizing the master database data from the slave database is serial, that is, parallel operations on the master database will be executed serially on the slave database. Due to the characteristics of copying logs from the slave database to the master database and serial execution of SQL, there will be master-slave synchronization delay. In the case of high concurrency, the problem is more obvious.

effect

Using master-slave replication can prevent a single point of failure of MySQ and back up data.

In high speed parallel delivery, the application needs to be read-write separated and configured with multiple data sources, that is, the write operation goes to the master library and the read operation goes to the slave library, so as to reduce lock contention. At this time, master-slave replication is also required.

Deploy database instance

1. Pull image

docker pull mysql:5.7

2. Run container

# Main library (port 3306)
docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 --name master_mysql mysql:5.7
# Slave Library (Port 3307)
docker run -d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 --name slave_mysql mysql:5.7

3. View status

docker ps	#Check whether it runs successfully

4. Connect using database management tools such as Navicat

Master slave replication configuration

1, Configure master library

• Enter the container to modify the configuration file

docker exec -it master_mysql /bin/bash

#Since there is no vim tool in the container, it needs to be installed
apt-get update
apt-get install vim

vim /etc/mysql/my.cnf

• Add the following code to my.cnf configuration file

[mysqld]
server-id=10
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1

• Enter the master database and create a user for the slave database to copy data. The slave database is connected to the master database through this user

# Login to mysql
mysql -u root -p

# Create user
mysql> CREATE user 'slave'@'%' identified by 'slave';
# Grant authority
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

• View main library status

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     617  |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

• Restart effective

docker restart master_mysql

2, Configure from library

• Similarly, enter the container to modify the configuration file

docker exec -it slave_mysql /bin/bash

#Since there is no vim tool in the container, it needs to be installed
apt-get update
apt-get install vim

vim /etc/mysql/my.cnf

• Add the following code to my.cnf configuration file

[mysqld]
server_id=11
log-bin=mysql-slave-bin
relay_log=edu-mysql-relay-bin

• Restart effective

docker restart slave_mysql

3, Bind master library

• docker to view the ip address of the main mysql container

docker inspect --format='{{.NetworkSettings.IPAddress}}' master_mysql

• Enter slave Library

docker exec -it slave_mysql /bin/bash
mysql -u root -p

• Bind master library

mysql> change master to master_host='172.17.0.2', master_user='slave', master_password='slave', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=617, master_connect_retry=30;

Master here_ Host is the ip of the main mysql container just found. It is a container independent ip

• View the status of the slave library

show slave status \G;
# It is found that the running status of IO thread and SQL thread is No. by default, they are not started and need to be started manually
Slave_IO_Running: No
Slave_SQL_Running: No

• Enable master-slave replication

start slave;