Sample code for implementing mysql master-slave replication in docker

docker pull mysql:8.0.26

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

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

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

[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

# 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

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

docker restart mysql-master

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

docker restart mysql-slave-01

# 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; 

# 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;

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

# Start synchronization start slave;

# Stop synchronization # stop slave;

# Check the synchronization status show slave status\G;