Sample code for implementing mysql master-slave replication in docker

1. Overview

1. Principle
2. Implementation

3. Create a Slave instance

4. Master-slave configuration

Summarize:

5. Reference

1. Overview

1. Principle

The master server records data changes in a binary log. When data on the master changes, the changes are written to the binary log.
The slave server will detect whether the master binary log has changed at a certain time interval. If it has changed, it will start an I/OThread to request the master binary event
At the same time, the master node starts a dump thread for each I/O thread to send binary events to it and save them in the local relay log of the slave node. The slave node will start the SQL thread to read the binary log from the relay log and replay it locally to make its data consistent with that of the master node. Finally, the I/OThread and SQLThread will enter a sleep state and wait to be awakened next time.

Master-slave flow chart

2. Implementation

Master library: 192.168.3.13:3310 Slave library: 192.168.3.14:3310 2. Create the master master library Enter the server 192.168.3.13

1. Install the image

docker pull mysql:8.0.26

2. Create a new directory

mkdir -p /home/apps/mysql-master/{config,log,data}

3. Create and start

docker run -d --name mysql-master \
--restart=always \
--privileged=true \
-p 3310:3306 \
-v /home/apps/mysql-master/config:/etc/mysql/conf.d \
-v /home/apps/mysql-master/log:/var/log/mysql \
-v /home/apps/mysql-master/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:8.0.26

4. Add/modify master basic configuration

vim /home/apps/mysql-master/config/my.cnf

Add the following content

[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8
 
[mysqld]
init_connect = 'SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

3. Create a Slave instance

Enter the server 192.168.3.14

1. Same as above operation

# Create directory mkdir -p /home/apps/mysql-slave-01/{config,log,data}

# Start the container docker run -d --name mysql-slave-01 \
--restart=always \
--privileged=true \
-p 3310:3306 \
-v /home/apps/mysql-slave-01/config:/etc/mysql/conf.d \
-v /home/apps/mysql-slave-01/log:/var/log/mysql \
-v /home/apps/mysql-slave-01/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:8.0.26


# Modify the basic configuration of Slave vim /home/apps/mysql-slave-01/config/my.cnf

# Add the following content [client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8
 
[mysqld]
init_connect = 'SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

4. Master-slave configuration

1. Add master configuration

vim /home/apps/mysql-master/config/my.cnf
server_id=1

# Enable binary log log-bin=mysql-bin
read-only=0

# Database that needs to be synchronized binlog-do-db=rapid-cloud
binlog-do-db=rapid-cloud-test

# Database to be ignored replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

2. Restart the container

docker restart mysql-master

3. Add Slave configuration

vim /home/apps/mysql-slave-01/config/my.cnf

server_id=2
log-bin=mysql-bin
read-only=1
binlog-do-db=rapid-cloud
binlog-do-db=rapid-cloud-test

replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

4. Restart the container

docker restart mysql-slave-01

5. Add an account to synchronize users

# Enter the container docker exec -it mysql-master /bin/bash

# Enter the main mysql database mysql -u root -p

# Authorize root to access remotely (it has nothing to do with master-slave, just to facilitate our remote connection to mysql)

# Authorize remote ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

# flush privileges;


# Create backup user # You should create a new user first create user 'backup'@'%' identified by '123456';

# Execute authorization grant all privileges on *.* to 'backup'@'%';

# flush privileges;

# Authorize remote ALTER USER 'backup'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

# flush privileges;

# View the status of the master database show master status;

6. Set up the master database connection in the slave database

# Enter the container docker exec -it mysql-slave-01 /bin/bash

# Enter the main mysql database mysql -u root -p

change master to master_host='192.168.3.13',master_user='backup',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=0,master_port=3310;

7. Start slave synchronization

First copy the data of the master database to the slave database, including the table structure and data

Clear the main library binlog so that its position starts from 0

purge master logs to'mysql-bin.000001';

Turn on sync

# Start synchronization start slave;

# Stop synchronization # stop slave;

# Check the synchronization status show slave status\G;

8. Troubleshooting

If master-slave synchronization cannot be achieved, you can check the following

Summarize:

The master and slave databases declare in their configuration files which databases need to be synchronized and which databases to ignore. And the server-id cannot be the same as the master database authorizing a certain account and password to synchronize its own data. The slave database uses this account and password to connect to the master database to synchronize data.

5. Reference

https://www.cnblogs.com/heian99/p/12104189.html

https://blog.csdn.net/lilygg/article/details/98187015

Binlog clearing: https://www.cnblogs.com/kiko2014551511/p/11532426.html

This is the end of this article about the sample code for implementing MySQL master-slave replication in Docker. For more related MySQL master-slave replication content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

How to run MySQL in Docker environment and enable Binlog to configure master-slave synchronization
Realize MySQL real-time incremental data transmission based on Docker and Canal
How to deploy MySQL master and slave in Docker
Detailed explanation of building MySQL master-slave environment with Docker
Docker opens mysql binlog log to solve data volume problems

<<: 18 sets of exquisite Apple-style free icon materials to share

>>: Practical record of optimizing MySQL tables with tens of millions of data