MySQL master-slave replication construction and principle based on Docker

Why build it based on Docker?

• Limited resources
• Building a virtual machine requires machine configuration, and the steps to install mysql are cumbersome
• One machine can run multiple Docker containers
• Docker containers are independent of each other, have independent IPs, and do not conflict with each other
• The steps to use Docker are simple and the container can be started in seconds

Use Docker to build a master-slave server

First pull the docker image, we use the 5.7 version of mysql here:
docker pull mysql:5.7
Then use this image to start the container, here you need to start the master and slave respectively Container

Master (master):

docker run -p 3339:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

Slave (from)

docker run -p 3340:3306 --name mysql-slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
Master’s externally mapped port It is 3339, and the port mapped externally by Slave is 3340. Because docker containers are independent of each other, each container has its own independent IP, so there will be no conflict between different containers using the same port. Here we should try to use the default 3306 port of mysql, otherwise there may be a problem of being unable to connect to mysql in the docker container through IP.
Use thedocker pscommand to view the running container:

At this time, you can use tools such as Navicat to test the connection to mysql

Configure Master (main)

Enter the Master container through thedocker exec -it 5ddad8c2f368 /bin/bashcommand, or you can also usedocker Enter the exec -it mysql-master /bin/bashcommand. 5ddad8c2f368 is the id of the container, and mysql-master is the name of the container.
cd /etc/mysql/mysql.conf.dSwitch to the /etc/mysql/mysql.conf.d directory, and thenvim mysqld.cnfperform my.cnf edit. At this time,bash: vim: command not foundwill be reported, and we need to install vim ourselves inside the docker container. Using theapt-get install vimcommand to install vim
will cause the following problems:

Executeapt-get update, Then executeapt-get install vimagain to successfully install vim. Then we can use vim to edit my.cnf and add the following configuration to my.cnf:

[mysqld] ## 同一局域网内注意要唯一 server-id=100 ## 开启二进制日志功能，可以随便取（关键） log-bin=mysql-bin

After the configuration is completed, the mysql service needs to be restarted to make the configuration take effect. Useservice mysql restartto complete the restart. Restarting the mysql service will stop the docker container. We also need todocker start mysql-masterto start the container.
Next step, create a data synchronization user in the Master database and grant the userslave REPLICATION SLAVEpermissions andREPLICATION CLIENTpermissions to synchronize data between the master and slave databases.
First connect to the mysql database
root@5ddad8c2f368:/# mysql -uroot -p123456
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

Grant replication accountREPLICATION CLIENTPermissions, replication users can useSHOW MASTER STATUS, SHOW SLAVE STATUSandSHOW BINARY LOGSto determine replication status.
Grant the replication accountREPLICATION SLAVEpermission so that replication can really work.

Configuring Slave (slave)

is the same as configuring Master (master), add the following to the Slave configuration file my.cnf Configuration:

[mysqld] ## 设置server_id,注意要唯一 server-id=101 ## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用 log-bin=mysql-slave-bin ## relay_log配置中继日志 relay_log=edu-mysql-relay-bin

After the configuration is completed, you also need to restart the mysql service and docker container. The operation is consistent with the configuration Master (main).

Relay log is similar to binary log in many aspects. The difference is: the slave server I/O thread reads the binary log of the master server and records it to the local file of the slave server, and then the SQL thread reads the contents of the relay-log log and applies it to the slave server, thereby making the slave server and master server data remains consistent.

Link Master (master) and Slave (slave)

Enter mysql on Master and executeshow master status;

File和Position字段的值后面将会用到，在后面的操作完成之前，需要保证Master库不能做任何操作，否则将会引起状态变化，File和Position字段的值变化。
在Slave 中进入 mysql，执行

CHANGE MASTER TO master_host = '172.17.0.2', master_user = 'slave', master_password = '123456', master_port = 3306, master_log_file = 'mysql-bin.000001', master_log_pos = 617, master_connect_retry = 30;

命令说明：
master_host：Master的地址，指的是容器的独立ip,可以通过docker inspect --format='{{.NetworkSettings.IPAddress}}' 容器名称|容器id查询容器的ip

master_port：Master的端口号，指的是容器的端口号
master_user：用于数据同步的用户
master_password：用于同步的用户的密码
master_log_file：指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
master_log_pos：从哪个 Position 开始读，即上文中提到的 Position 字段的值
master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是60秒
在Slave 中的mysql终端执行show slave status \G;用于查看主从同步状态。

正常情况下，SlaveIORunning 和 SlaveSQLRunning 都是No，因为我们还没有开启主从复制过程。使用start slave开启主从复制过程，然后再次查询主从同步状态show slave status \G;。

SlaveIORunning 和 SlaveSQLRunning 都是Yes，说明主从复制已经开启。此时可以测试数据同步是否成功。
主从复制排错：

使用start slave开启主从复制过程后，如果SlaveIORunning一直是Connecting，则说明主从复制一直处于连接状态，这种情况一般是下面几种原因造成的，我们可以根据 Last_IO_Error提示予以排除。

• 网络不通
  检查ip,端口
• 密码不对
  检查是否创建用于同步的用户和用户密码是否正确
• pos不对
  检查Master的 Position

测试主从复制

测试主从复制方式就十分多了，最简单的是在Master创建一个数据库，然后检查Slave是否存在此数据库。
Master:

Slave:

完整的主备流程图

最后让我们来看一下，一个 update 语句在节点 A 执行，然后同步到节点 B的完整流程图。

可以看到：主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写入binlog。
备库B跟主库A之间维持了一个长连接。主库A内部有一个线程，专门用于服务备库B的这个长连接。
一个事务日志同步的完整过程是这样的：

• 1、在备库B上通过change master命令，设置主库A的IP、端口、用户名、密码、以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量。
• 2、在备库B上执行start slave命令，这时侯备库会启动两个线程，io_thread 和 sql_thread。其中， io_thread负责与主库建立连接。
• 3、主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B。
• 4、备库B拿到binlog后，写到本地文件，称为中转日志（relay log）。
• 5、sql_thread读取中转日志，解析日志里的命令，并执行。

The above is the detailed content of MySQL master-slave replication construction and principle based on Docker. For more information, please follow other related articles on the PHP Chinese website!