Notes on master-slave replication of MySQL 8 in docker

Time:2021-7-27

In the actual development process, I have never had the actual combat experience of master-slave replication, but whenever I look for a job, I have to start building rockets. As a PHPer, I’m really in pain. None of the companies experienced has done business, let alone a large number of users. As an amateur webmaster, I have more traffic than those companies.

Too bad to criticize.

1. Docker create container

#Pull MySQL image
docker pull mysql

#View mirror list
docker images

#Create and run container without directory mapping set
docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql

#Create and run containers and set up directory mappings
docker run -p 3306:3306 --name mysql
-v /usr/local/docker/mysql/conf:/etc/mysql
-v /usr/local/docker/mysql/logs:/var/log/mysql
-v /usr/local/docker/mysql/data:/var/lib/mysql
-e MYSQL_ROOT_PASSWORD=123456
-d mysql

#- – Name: container name, named here ` mysql`
#- E: configuration information. Here, configure the login password of the root user of MySQL
#- P: port mapping, where host port 3306 is mapped to container port 3306
#- D: run the container in the background to ensure that the container continues to run after exiting the terminal
#- V: Directory mapping relationship between host and container. Before ":" is host directory, followed by container directory

#View container list
Docker PS - a # view a list of all containers
Docker PS # view the list of currently running containers

#Enter docker container
docker exec -it mymysql /bin/bash

#To exit the docker container, use the shortcut Ctrl + Q + P

#Enter MySQL
mysql -uroot -p123456

#If the MySQL instance cannot be linked remotely
#1. Check the host firewall port
#2. Check MySQL user permissions

#Enter docker container,设置所有客户端ip都能登录mysql
docker exec -it mysql  /bin/bash
mysql -uroot -p123456
mysql> grant all privileges on *.* to [email protected]'%' identified by "password";
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.24    |
+-----------+
1 row in set (0.02 sec)

Practical steps

1. Create two containers, mysql_ Master and MySQL_ The slave port is bound to the 3339 3340 ports of the host computer. Since it’s a test, you don’t bother to set up directory mapping.

docker run -p 3339:3306 --name mysql_master -e MYSQL_ROOT_PASSWORD=123456 -d mysql

docker run -p 3340:3306 --name mysql_slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql

2. Enter MySQL_ Master container, modify MySQL configuration file

docker exec -it mysql_master  /bin/bash

vim /etc/my.cnf

#If there is no VIM tool, install the VIM tool
apt-get update
apt-get install vim

In the [mysqld] node of / etc / my.cnf, add the following configuration information

[mysqld]
#Master slave configuration
Server id = 1 # server ID 
Log bin = MySQL bin # binary file storage path
Binlog do DB = Palan dev # database to be synchronized
Binlog ignore DB = MySQL # unsynchronized data

3. Authorize the account so that it can be copied from the database.

#In docker testing, IP is not limited for convenience

#Create user
#Create user 'rootslave' @ 'slave server IP address' identified with MySQL_ native_ password BY 'root123';

CREATE USER 'rootslave'@'%' IDENTIFIED WITH mysql_native_password BY 'root123';

#Authorize users
#Grant replication slave on *. * to 'rootslave' @ 'slave server IP address';

grant replication slave on *.* to 'rootslave'@'%';

4. Restart MySQL (exit the container and restart the container)

#Exit MySQL client
mysql> quit;

#CTRL + Q + P exit container

#Restart container
docker restart mysql_master

#Re enter the container
docker exec -id mysql_master /bin/bash

#Enter Mysql to view the main database status
mysql -uroot -p123456
mysql> show master status;

show master status; The implementation effect is as follows

Notes on master-slave replication of MySQL 8 in docker

5. Modify the slave library configuration and also add it in the configuration file / etc / my.cnf

**[mysqld]**
#Master slave configuration

Server id = 4 # server ID 
Log bin = MySQL bin # binary file storage path
Replicate do DB = Palan dev # database to be synchronized
Replicate ignore DB = MySQL # unsynchronized data

6. Restart the slave library
docker restart mysql_slave
7. Enter the container and log in to the MySQL client to realize master-slave synchronization (MySQL master-slave replication – change master to syntax is detailed at the end of the article)
master_ Host: corresponds to the IP of the master database. Here, because the master database and the slave database are on the same server, you can fill in the host IP
master_ Port: the port number of the host machine bound to the corresponding primary library
master_ User: corresponds to the user created in the main library
master_ Password: the corresponding user password
master_ log_ File: show master status of the corresponding master database; File value for
master_ log_ Pos: show master status of the corresponding master database; Position value of

mysql>stop slave; # Close slave Library
mysql> change master to master_ Host = 'host IP', master_ port=3339,master_ user='rootslave',master_ password='root123',master_ log_ file='mysql-bin.000002',master_ log_ pos=2374;

mysql> start slave; # Enable slave Library (stop Slave: close slave Library)
mysql> show slave status \G;  # Check server status
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.136.148
                  Master_User: rootslave
                  Master_Port: 3339
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 2374
               Relay_Log_File: dfeefafe7679-relay-bin.000003
                Relay_Log_Pos: 2542
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: palan-dev
          Replicate_Ignore_DB: mysql
           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: 2374
              Relay_Log_Space: 2758
              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: 100
                  Master_UUID: 703673b4-aa67-11eb-9570-0242ac110004
             Master_Info_File: mysql.slave_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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

The end can start the master-slave synchronization test on Navicat

This is just a simple simulation test of master-slave replication environment. The principle of master-slave replication I understand is to create an account in the master database, and then let the slave database read the binary log file of the master database through the account of the master database, so as to update the current operation of the slave database.

In practice, master-slave replication must have many needs for fine setting and optimization.

MySQL master-slave replication used by Alibaba and is easy to use. Why do many small companies like this and ask PHPer to master these skills? I don’t understand.

Thinking: how to analyze and solve the failure of master-slave replication???

Reference articles

I am also Xiaobai. All operations refer to other people’s blogs

  1. Using docker to build MySQL service
  2. Build master-slave database
  3. MySQL master-slave replication – change master to syntax explanation

This work adoptsCC agreement, reprint must indicate the author and the link to this article