Using docker to build MySQL master-slave replication on CentOS

Time:2021-6-13

What is MySQL master-slave

The master-slave database includes one master database and one or more slave databases. The data in the master database will be synchronized to each slave database.

Principle of master-slave replication

  • 1. When the master node performs insert, update, and delete operations, it will be written to the binlog in order.
  • 2. If the slave is connected to the master main library, the number of slave threads in the master will create the number of binlog dump threads.
  • 3. When the binlog of the master node changes, the binlog dump thread will notify all the salve nodes and push the corresponding binlog content to the slave node.
  • 4. After receiving the binlog content, the I / O thread writes it to the local relay log.
  • 5. The SQL thread reads the relay log written by the I / O thread, and performs corresponding operations on the slave database according to the content of the relay log.
    Using docker to build MySQL master-slave replication on CentOS

The role of master and slave

  • Realize the separation of reading and writing
  • Read write separation can improve the concurrency of database
  • When backing up data
  • High availability of database can be achieved by using appropriate middleware

Building a master-slave environment

Construction of main library

  • Pull the database image, here is the mysql5.7 image
  • Create master library container

    docker run -d --name master -p 3307:3306 -v /www/m2/master/conf/:/etc/mysql/conf.d -v /www/m2/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root mysql:5.7
  • Main library configuration my.conf

    #MySQL configuration item
    [client]
    port=3306
    user=mysql
    default-character-set=utf8mb4
    [mysql]
    default-character-set=utf8mb4
    [mysqld]
    port=3306
    user=mysql
    character-set-server = utf8mb4
    default-time_zone = '+8:00'
    server_id=1
    log_bin=mysql-bin
    binlog_format=ROW
    slow-query-log-file=/logs/slow-query.log
    long_query_time=1
  • Enter the MySQL on the container link and add a test user to synchronize from the database

    docker exec master -it /bin/bash
    mysql -uroot -proot
    #Configure test users and permissions (the password is also test)
    grant replication slave on *.* to 'test'@'%' identified by 'test';
    flush privileges;
    #Here, grant replication slave is a command format, which means that the following accounts are given permission to copy.
  • View the binlog status of the master node

    show master status\G;
    #Remember the values of file and position, which will be used in slave library configuration.

    Using docker to build MySQL master-slave replication on CentOS

Build from library

  • create mirror

    docker run -d --name slave --privileged=true --link master:master -p 3308:3306 -v /www/m2/salve/conf/:/etc/mysql/conf.d -v /www/m2/salve/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root mysql:5.7
  • Configure my.conf from library

    #MySQL configuration item
    [client]
    port=3306
    user=mysql
    default-character-set=utf8mb4
    [mysql]
    default-character-set=utf8mb4
    [mysqld]
    port=3306
    user=mysql
    character-set-server = utf8mb4
    default-time_zone = '+8:00'
    server_id=2
    log-bin=mysql-slave-bin
    relay_log=mysql-relay-bin
  • Enter the container and link to the main library

    docker exec slave -it /bin/bash
    mysql -uroot -proot
    #MySQL command
    #Link master configuration
    change master to master_host='172.17.0.4', master_user='test', master_password='test', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=313, master_connect_retry=30;
    #Start slave Library
    start slave;
    #View slave library status
    show slave status\G;
    • In the above change command, master_ user,master_ Password is the account number and password set for the slave database when configuring the master database; master_ Port is the port of MySQL in the main database container. Don’t write it as the port mapped from the host to the main database container; master_ log_ file,master_ log_ POS is the query value when the main database is configured, which specifies the synchronization location.
    • Show slave status description

      mysql> show slave status\G;
      *************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.4
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 622
               Relay_Log_File: edu-mysql-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: 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: 622
              Relay_Log_Space: 1006
              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: 1
                  Master_UUID: 1cdd11f9-a256-11eb-8d55-0242ac110004
             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: 
      

      When slave_ IO_ Running , Slave_ SQL_ When running is all yes, it means the startup is successful. Operations in the master library can be successfully synchronized to the slave library.

Problems encountered

  • Failed to link master library from library
    Show slave status prompt:

    Last_IO_Error: error connecting to master '[email protected]:3307' - retry-time: 30  retries: 1

    Main reasons: host error; Account password error; Port error. Before, 3307 host mapping port was written, which led to link failure;

  • Slave_ SQL_ Running: no synchronous data interrupt

    Method 1: skip the error that causes the interrupt
    mysql> stop slave ;
    mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
    mysql> start slave ;
    
    Method 2: reset the synchronization position
    First, stop the slave service: MySQL > slave stop;
    Check the host status on the master server and record the corresponding values of file and position;
    Set the synchronization position from the library;
    Start synchronization.