Docker MySQL master-slave installation

Time:2022-5-13

1. First, remove the image

docker pull mysql:5.7

2. Run image

It mainly maps log storage files and configuration files to the host

docker run -p 13307:3306 --name mysql-master \
-v /mydata/mysql-master/log:/var/log/mysql \
-v /mydata/mysql-master/data:/var/lib/mysql \
-v /mydata/mysql-master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \-d mysql:5.7

[[email protected]node ~]# docker run -p 13307:3306 --name mysql-master \
> -v /mydata/mysql-master/log:/var/log/mysql \
> -v /mydata/mysql-master/data:/var/lib/mysql \
> -v /mydata/mysql-master/conf:/etc/mysql \
> -e MYSQL_ROOT_PASSWORD=123456 \-d mysql:5.7
76176e5ae29317ea207080be62aeffadaada4c82c03753d2c500916449d6476e
[[email protected] ~]# 


3. The master node creates a configuration file and adds the following contents

1. Contents:
/mydata/mysql-master/cnf
[[email protected] conf]# vi my.cnf

[mysqld]
##Set up server_ ID, which needs to be unique in the same LAN
server_id=101
##Specify the name of the database that does not need to be synchronized
binlog-ignore-db=mysql
##Enable binary log function
log-bin=mall-mysql-bin
##Set the memory size used by binary log (transaction)
binlog_cache_size=1M
##Use binary format (mixed, row, state)
binlog_format=mixed
##Binary log expiration cleanup time. The default value is 0, which means no automatic cleaning.
expire_logs_days=7
##Skip all errors encountered in master-slave replication or specified types of errors to avoid the interruption of slave side replication.
##For example, the 1062 error refers to the duplication of some primary keys, and the 1032 error is due to the inconsistency of data between the master and slave databases
slave_skip_errors=1062

4. Restart MySQL after adding the configuration

[[email protected] conf]# docker restart mysql-master
mysql-master
[[email protected] conf]# 

5. Enter the MySQL container

#Enter into container
docker exec -it mysql-master /bin/bash
#Login to MySQL
mysql -uroot -p123456

6. Create synchronization user

6.1 create user command:

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> 

7. Create slave server

[[email protected] ~]# docker run -p 13308:3306 --name mysql-slave \
> -v /mydata/mysql-slave/log:/var/log/mysql \
> -v /mydata/mysql-slave/data:/var/lib/mysql \
> -v /mydata/mysql-slave/conf:/etc/mysql \
> -e MYSQL_ROOT_PASSWORD=123456  \
> -d mysql:5.7
230880d2ab8c53351656ceb3998e28595dcd5af28879de3755cf6c6993b0879d
[[email protected] ~]# 

8. Create a configuration file from the node and add the following contents

[[email protected] ~]# cd /mydata/mysql-slave/cnf
[[email protected] conf]# vi my.cnf
[mysqld]
##Set up server_ ID, which needs to be unique in the same LAN
server_id=102
##Specify the name of the database that does not need to be synchronized
binlog-ignore-db=mysql
##Enable the binary log function for use when slave is the master of other database instances
log-bin=mall-mysql-slave1-bin
##Set the memory size used by binary log (transaction)
binlog_cache_size=1M
##Use binary format (mixed, row, state)
binlog_format=mixed
##Binary log expiration cleanup time. The default value is 0, which means no automatic cleaning.
expire_logs_days=7
##Skip all errors encountered in master-slave replication or specified types of errors to avoid the interruption of slave side replication.
##For example, the 1062 error refers to the duplication of some primary keys, and the 1032 error is due to the inconsistency of data between the master and slave databases
slave_skip_errors=1062
## relay_ Log configure relay log
relay_log=mall-mysql-relay-bin
## log_ slave_ Updates indicates that the slave writes the replication event to its binary log
log_slave_updates=1
##Slave is set to read-only (except for users with super permission)
read_only=1

9. Restart the slave node

[[email protected] conf]# docker restart mysql-slave
mysql-slave
[[email protected] conf]# 

10. View the master-slave synchronization status in the master database

mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mall-mysql-bin.000001 |      154 |              | mysql            |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

11. Enter the slave database

[[email protected] conf]# docker  exec -it mysql-slave /bin/bash
[email protected]:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

12. Execute the synchronization command on the slave node

change master to master_ Host = 'host IP', master_ user='slave', master_ password='123456', master_ port=3307, master_ log_ file='mall-mysql-bin. 000001', master_ log_ pos=617, master_ connect_ retry=30;

mysql> change master to master_host='192.168.1.180', master_user='slave', master_password='123456', master_port=13307, master_log_file='mall-mysql-bin.000001', master_log_pos=154, master_connect_retry=30;  
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> 

13. Enable master-slave synchronization on the slave node

mysql> start slave;

14 view the status of the slave node

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.180
                  Master_User: slave
                  Master_Port: 13307
                Connect_Retry: 30
              Master_Log_File: mall-mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mall-mysql-relay-bin.000002
                Relay_Log_Pos: 325
        Relay_Master_Log_File: mall-mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 537
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 101
                  Master_UUID: 9eedd539-7601-11ec-93ac-0242ac110005
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 

There are mainly two bits. Yes means success
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

15 test successful

Docker MySQL master-slave installation

Docker MySQL master-slave png