MySQL Master-Slave Configuration

I: Introduction

Master-slave synchronization allows data to be copied from one database server to another, where one database server acts as the master and the rest acts as the slave.Replication is asynchronous, so the server does not need to be connected to the primary server all the time, and the secondary server is also connected to the primary server intermittently and by dial-up. The configuration file allows you to specify that all databases, or a database, or even a table on a database be replicated.

Two: Advantages

1) If the primary library has problems, you can quickly switch to the slave library to provide services.
2) You can perform queries from the library and update them in the main library to reduce the pressure on the main library.
3) Separate reading from writing and perform backups from libraries to avoid affecting the services of the primary library during backups.

3: Process (master-slave configuration of mysql under docker)

1. Install mysql in docker

1.1. Query mysql from warehouse

[root@localhost ~]# docker search mysql


1.2. Download mysql image

[root@localhost ~]# docker pull docker.io/mysql


1.3.Query whether the image was downloaded successfully

[root@localhost ~]# docker image ls


1.4.Run docker container (container, master)

docker run -itd --name=mysql_master -p 3310:3306 -e MYSQL_ROOT_PASSWORD=123 docker.io/mysql


1.5.Query whether mysql process starts

[root@localhost ~]# docker ps

1.6. Download a container the same way as above

1.7. Run from container

[root@localhost ~]# docker run -itd --name=mysql_slave -p 3311:3306 -e MYSQL_ROOT_PASSWORD=123 docker.io/mysql

1.8 View Started Containers

Both databases can be started.

2.Configure master server

2.1 Enter master server container

[root@localhost ~]# docker exec -it mysql_master /bin/bash

2.2 Enter/etc/mysql folder

root@4abf554ea1e4:/# cd etc/ 
root@4abf554ea1e4:/etc# cd mysql 
root@4abf554ea1e4:/etc/mysql# 
root@4abf554ea1e4:/etc/mysql# ls 
conf.d my.cnf my.cnf.fallback

2.3 Use vi/vim to edit my.cnf file

Display the information above to verify that vim is not installed, vim needs to be installed
2.3.1. Perform apt-get update

root@4abf554ea1e4:/etc/mysql# apt-get update

2.3.2. Perform apt-get install vim installation vim

root@4abf554ea1e4:/etc/mysql# apt-get install vim

2.3.3. Use vi to edit my.cnf

root@4abf554ea1e4:/etc/mysql# vi my.cnf


2.3.4. Add code to my.cnf (use the "i" key to enter edit mode)

Save Changes

press Enter to continue

Restart container for configuration to take effect

2.4. Create a slave for master-slave synchronization
2.4.1. Log in to mysql using the root account

[root@localhost ~]# docker exec -it mysql_master /bin/bash
 root@4abf554ea1e4:/# mysql -u root -p 
 Enter password:

2.4.2. Create a dedicated synchronization account (slave)

3. Configure slave containers

3.1. Enter slave server container

3.2. Enter the / etc/mysql folder

3.3. Use vi/vim to edit my.cnf file
Not installed the same way vim is installed in the main container above
After installation, enter:

4. Master-Slave Replication

4.1. View master status
4.4.1. Enter master mysql container

[root@localhost ~]# docker exec -it mysql_master /bin/bash 
[root@4abf554ea1e4:/# mysql -u slave -p

4.4.2. Display master server status

mysql> show master status;


4.2. Executing commands in slave
4.2.1. Query the master container's stand-alone IP

[root@localhost ~]# docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql_master 
## The name of the mysql_master--master container


4.2.2. Enter slave container

[root@localhost ~]# docker exec -it mysql_slave /bin/bash


4.2.3. Executing SQL statements

mysql> change master to 
master_host='172.17.0.2',  
master_user='slave', 
master_password='123', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=1327; 

Query OK, 0 rows affected, 8 warnings (0.03 sec)


4.2.4. Open master-slave replication process

mysql> start slave;
 Query OK, 0 rows affected, 1 warning (0.00 sec)



Configuration ends here

5.Problems and Solutions

First error case

error connecting to master 'slave@172.17.0.2:3306' - retry-time: 60 retries: 2 message:
Authentication plugin 'caching_sha2_password' reported error: Authentication requires
secure connection.
Solution: Modify user password information at master server

ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123';
FLUSH PRIVILEGES;

Unlock Table

unlock tables

Second error case

Slave failed to initialize relay log info structure from the repository, Error_code: 1872
Solution:
stop slave; //Close slave
Reset slave; //reset slave
Rerun change master to:

mysql> change master to 
master_host='172.17.0.2', 
master_user='slave', 
master_password='123', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=1327; 

Query OK, 0 rows affected, 8 warnings (0.03 sec)

start slave;

Tags: Database MySQL Docker

Posted on Sun, 12 Sep 2021 00:54:05 -0400 by sguy