[MySQL master-slave synchronization – based on Docker container]

Version overview:

• MySQL version: 8.0.29
• Docker version: 20.10.18
• Linux: CentOS 7.x

Achieving goals:

Realize master-slave synchronization of MySQL database, one master and one slave.

Database related

Host IP	Container Name	Identifier	Description
172.17.0.1:33011	mysql-master	master	main library
172.17.0.1:33012	mysql-slave	slave	from library

Host IP Description:

**Situation 1: **The same server, different containers, just use the docker0 network card ip (usually 172.17.0.1), which can be viewed through ip addr
**Case 2: **Different servers, different containers, just use the server’s internal network ip/public network ip + port
**Note:** This is case 1

Preparation

docker installation

How to install docker:

• Install online
• Install offline

Image pull and container mount folder creation

# mysql8.0.29 image pull
docker pull mysql:8.0.29

# Create a mount directory (main library) on the host
mkdir -p /home/docker/mysql/33011/data \
/home/docker/mysql/33011/conf\
/home/docker/mysql/33011/tmp


# Create a mount directory on the host (from the library)
mkdir -p /home/docker/mysql/33012/data \
/home/docker/mysql/33012/conf \
/home/docker/mysql/33012/tmp


# Give the tmp folder 777 permissions, readable and writable (the original folder can be executed), if only 666 is given, it will become 777
chmod +777 /home/docker/mysql/33011/tmp
chmod +777 /home/docker/mysql/33012/tmp

Main library my.conf content

The file storage path: /home/docker/mysql/33011/conf

[mysqld]
# Prohibit domain name resolution (including host name), which can eliminate the time for MySQL to perform DNS resolution, but all connections must use IP addresses.
skip-name-resolve=1

# Data storage location (absolute path)
datadir=/var/lib/mysql

# port: service port number, the default is 3306
port=3306

# Database default character set
character_set_server=utf8mb4

# The database character set corresponds to some sorting rules, pay attention to correspond to character-set-server
collation-server=utf8mb4_general_ci

# The unique number of the MySQL service, the id of each MySQL service must be unique, and cannot be duplicated with the slave database.
server-id=11

# The following master-slave configuration

# start binary log
log-bin=master-bin

# Specify the path and name of the binary index file (only the name is specified here)
log_bin_index=master-bin.index

# Binary day expiration time, 7 days, in seconds, the default is 30 days (2592000 seconds)
 binlog_expire_logs_seconds=604800

# The library to be synchronized to the slave (if not written, all will be synchronized by default)
# binlog-do-db=db01

#The library that does not synchronize to the slave (multiple writes?)
#binlog-ignore-db=mysql
#binlog-ignore-db=information_schema
#binlog-ignore-db=performance_schema
#binlog-ignore-db=sys

The main library container starts

docker run -d -p 3306:3306 --name=mysql-master \
-v /home/docker/mysql/3306/data:/var/lib/mysql \
-v /home/docker/mysql/3306/conf/my.cnf:/etc/my.cnf \
-v /home/docker/mysql/3306/tmp:/tmp \
-e MYSQL_ROOT_PASSWORD=xiaolei@1 \
-e TZ=Asia/Shanghai \
mysql:8.0.33

From repository my.conf content

The file storage path: /home/docker/mysql/33012/conf

[mysqld]
# Prohibit domain name resolution (including host name), which can eliminate the time for MySQL to perform DNS resolution, but all connections must use IP addresses.
skip-name-resolve=1

# Data storage location (absolute path)
datadir=/var/lib/mysql

# port: service port number, the default is 3306
port=3306

# Database default character set
character_set_server=utf8mb4

# The database character set corresponds to some sorting rules, pay attention to correspond to character-set-server
collation-server=utf8mb4_general_ci

# The unique number of the MySQL service, the id of each MySQL service must be unique.
server-id=12

# The following master-slave configuration

# Read-only mode, will not affect the function of slave synchronous replication
read_only = 1
# Specify the path and name of the binary index file (only the name is specified here)
log_bin_index=master-bin.index

# The following parameters can avoid the update is not timely, the master-slave replication error caused by SLAVE restart

# Relay log name (giving a name is equivalent to enabling interrupt logging)
relay-log = slave-relay-bin
# Master server I/O? Log reading, recording and storage index
relay-log-index = slave-relay-bin.index

# Where is the synchronization position information of the slave synchronization master, and the mysql.slave_relay_info table is used to record the synchronization position information
# mysql8 does not need these two settings. These two configurations have been removed from mysql8. The default is TABLE, plus there will be warnings
# master_info_repository=TABLE
# slave records the status of the master, mysql.slave_master_info table
# relay_log_info_repository=TABLE

Launch from library container

docker run -d -p 3306:3306 --name=mysql-slave \
-v /home/docker/mysql/3306/data:/var/lib/mysql \
-v /home/docker/mysql/3306/conf/my.cnf:/etc/my.cnf \
-v /home/docker/mysql/3306/tmp:/tmp \
-e MYSQL_ROOT_PASSWORD=xiaolei@1 \
-e TZ=Asia/Shanghai \
mysql:8.0.33

Error: [InnoDB] Unable to create temporary file inside “/tmp”; errno: 13

1. Clear the content of the host data file
2. Give the host tmp folder

• chmod +777 /home/docker/mysql/33011/tmp
• chmod +777 /home/docker/mysql/33012/tmp

Main library configuration

Be sure to distinguish between master and slave IP when stepping on the pit:
The main library ip is 192.168.10.107
The slave ip is 192.168.10.110
-- Note: All operations are performed under the mysql library
-- Create a login user from the slave library (slave) in the master library (master)
create user 'userSlave'@'192.168.10.110' identified by 'xiaolei@1';
select user,host from user;

-- Give the login user permission from the library (slave) (any table in any library)
grant all privileges on *.* to 'userSlave'@'192.168.10.110';

-- Refresh permission changes
flush privileges;

-- Check the status of the main library
show master status;

Note: (main library status)

• The file name in File will change once the mysql service is restarted. If the master has never taken effect, please check whether the file name is configured correctly in the slave library.
  • If it is incorrect, the slave library needs to stop synchronization and clear the synchronization configuration information.
• The number in Postition will change as long as there is an addition, deletion or modification of the main database. If there is no problem with the first connection from the library, this value can be changed without any problem. The word translates to the meaning of location. Guess which location in the log is recorded to start synchronization.

& height=190 & amp;id=u026b0294 & amp;name=image.png & amp;originHeight=238 & amp;originWidth=516 & amp;originalType=binary &ratio=1.25 &rotation=0 & showTitle=false &size=10500 &status=done &style=none &taskId=u8ac903f5-2681-45ea-9e66-2829e17e485 &title= &width=412.8″ alt=” image.png”>

Configuration from library

-- Note: all operations are performed under the mysql library
-- Connect to the main library (command before 8.0.23)
CHANGE MASTER TO
master_host = '192.168.10.107',
master_port = 33011,
master_user = 'userSlave',
master_password = '123456',
master_log_file = 'master-bin.000005',
master_log_pos = 157,
get_master_public_key=1; -- If it is a mysql8 database, adding this can prevent the connection from failing due to password policy issues,

-- Connect to the main library (command after 8.0.23)
CHANGE REPLICATION SOURCE TO
source_host='192.168.10.107',
source_port=33011,
source_user='userSlave',
source_password='123456',
source_log_file='master-bin.000005',
source_log_pos=157,
get_master_public_key=1;


-- Start master-slave replication
start slave;

-- View sync status
show slave status\G;

Detailed explanation of command parameters

CHANGE MASTER TO command

• master_host main library ip
• master_port master library port number
• master_user is the user name for logging in to the master library from the library
• master_password The password for logging in to the master library from the library
• master_log_file The binary log file name of the master library (value in File)
• master_log_pos The synchronization position of the binary log file of the main library (the value in Postition**)**
• get_master_public_key=1 Enable to obtain the master database connection public key

CHANGE REPLICATION SOURCE TO command

• source_host main library ip
• source_port main library port number
• source_user The user name for logging in to the main library from the library
• source_password The account password for logging in to the master library from the library
• source_log_file binary log file name of the main library (value in File)
• source_log_pos The synchronization position of the main library binary log file (the value in Postition**)**
• get_master_public_key=1 Enable to obtain the master database connection public key

Error: Last_Errno: 2061, Last_IO_Error field value: error connecting to master ‘[email protected]:33011’ – retry-time: 60 retries: 4 message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication require res secure connection.

Reason for problem:

The encryption policy of mysql8 start password is changed from mysql_native_password to caching_sha2_password.
In MySQL 8.0, caching_sha2_password is the default authentication plugin with higher security.
Connection failed because caching_sha2_password requires public key
So there are two ways to solve the problem:

1. Get the public key and save it in the corresponding directory of the local mysq
2. Change the encryption policy of the user logged in from the library

Solution 1 (recommended): Increase the connection master database parameter get_master_public_key=1

This parameter allows the slave library to get the public key of the master library server mysql when connecting to the server

-- The following operations are performed in the mysql library served by the library
-- Stop master-slave replication
stop slave;

-- Clear the previous master-slave replication configuration information
reset slave all;

-- Check the synchronization status (there should be nothing at this time)
show slave status;

-- Add parameter get_master_public_key=1
CHANGE REPLICATION SOURCE TO
source_host='172.17.0.1',
source_port=33011,
source_user='userSlave',
source_password='123456',
source_log_file='master-bin.000005',
source_log_pos=157,
get_master_public_key=1;

Solution 2: Modify the encryption policy of the login user from the library

-- The following operations are in the mysql library of the main library
-- Modify user encryption policy
ALTER USER 'userSlave'@'172.17.0.1' IDENTIFIED WITH mysql_native_password BY '123456';

-- Refresh permission changes
flush privileges;


-- Dividing line
-- The following operations are performed from the mysql library
-- Stop master-slave replication
stop slave;

-- Clear the previous master-slave replication configuration information
reset slave all;

-- Check the synchronization status (there should be nothing at this time)
show slave status;

-- just reconnect
CHANGE MASTER TO master_host = '172.17.0.1',
master_port = 33011,
master_user = 'userSlave',
master_password = '123456',
master_log_file = 'master-bin.000005',
master_log_pos = 157;

Master-slave status check

Main library

View in the master library, if the Binlog Dump character appears in the Command field as shown in the figure below, it means that the master-slave connection is successful

-- View user connection list
show processlist;

From library

To view the synchronization status, mainly check whether the four parameters in the picture are the same as the one in the picture below

-- slave status
show slave status;

Master-slave test

Create a test library and a test-table table in the main library, and view the slave library

Operation data validation

Additions, deletions and changes can be synchronized

Related commands

-- main library
-- Create a login user from the slave library (slave) in the master library (master)
create user 'userSlave'@'172.17.0.1' identified by '123456';

-- View user login connection
select user,host from user;

-- Give the login user permission from the library (slave) (any table in any library)
grant all privileges on *.* to 'userSlave'@'172.17.0.1';

-- Refresh permission changes
flush privileges;

-- Check the status of the main library
show master status;

-- View user connection list
show processlist;

-- Delete the user, and refresh the permissions after deleting
drop user 'userSlave'@'172.17.0.1';

-- from library
-- Connect to the main library (command before 8.0.23)
CHANGE MASTER TO master_host = '172.17.0.1',
master_port = 33011,
master_user = 'userSlave',
master_password = '123456',
master_log_file = 'master-bin.000005',
master_log_pos = 157,
get_master_public_key=1; -- If it is a mysql8 database, adding this can prevent the connection from failing due to password policy issues,

-- Connect to the main library (command after 8.0.23)
CHANGE REPLICATION SOURCE TO
source_host='172.17.0.1',
source_port=33011,
source_user='userSlave',
source_password='123456',
source_log_file='master-bin.000006',
source_log_pos=157,
get_master_public_key=1;


-- Start master-slave replication
start slave;

-- View sync status
show slave status;


-- Stop master-slave replication
stop slave;

-- Clear the previous master-slave replication configuration information
reset slave all;

How to reconnect from the slave library after the main library restarts

**Premise: **The two data must be consistent, otherwise you have to export the main library and start from scratch

-- main library
-- Check the status of the main library, get the file name and log pos location
show master status;

-- Handsome dividing line

-- from library
-- Stop master-slave replication
stop slave;
-- Clear the previous master-slave replication configuration information
reset slave all;

-- Connect to the main library
CHANGE REPLICATION SOURCE TO
source_host='172.17.0.1',
source_port=33011,
source_user='userSlave',
source_password='123456',
source_log_file='master-bin.000006',
source_log_pos=157,
get_master_public_key=1;

Other errors

**Note:** Once the master-slave reports an error, the synchronization will stop!

Error: Last_Errno: 1062, Last_IO_Error: Coordinator stopped because there were error(s) in the worker(s).The most recent failure being: Worker 1 failed executing transaction ‘ANONYMOUS’ at master log master-bin.000005, end_log_pos 4293. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

Reason for problem:

After the error is reported, a large part of the reason is due to the inconsistency of the master-slave data

Solution 1 (not recommended): clear the library (simple and rude)

Note: only applicable to the data in the library! ! !
This only needs to ensure that the data of the main library and the slave library are consistent, because it is a test, the current solution is to delete the data on both sides, and then reconnect the main library from the library, but pay attention to **Postition * in the main library *The value will change, you need to re-use the command show master status; to see the latest one when connecting.

Solution 2 (recommended):

Find a way to synchronize the database, for example: re-open a master-slave relationship, import the original master database data into the new master database

Quick check of error code

1062:

The primary key id conflicts. When inserting data from the database, a uniqueness conflict occurs. At this time, the slave database already has data with the same primary key, and if you insert data with the same primary key value, an error will be reported. You can check whether the line change data of the main library is consistent with the data to be inserted from the slave library. If they are consistent, skip the error and restore the SQL thread. .

2061:

Inconsistent encryption policy. The encryption policy of mysql8 start password is changed from mysql_native_password to caching_sha2_password.
In MySQL 8.0, caching_sha2_password is the default authentication plugin with higher security.
Connection failed because caching_sha2_password requires public key