SoFunction
Updated on 2025-04-11

How to implement Mysql8 based on binlog deployed using Docker

concept

MySQL is based onBinlogMaster-Slave Replication is a mechanism for implementing data replication in MySQL databases. In this replication mode, the master library records all modification operations to the database (such as INSERT, UPDATE, DELETE, etc.) toBinlog, the slave library reads these logs and performs the same operations, thus maintaining data consistency with the master library.

1. Basic concepts:

  • Master library: All data modification operations are executed on the main library, and the main library will record these modifications to the Binlog.
  • From the library (Slave): The slave library connects to the main library, reads the Binlog of the main library and applies the operations to its own data, achieving the purpose of being consistent with the main library.

2. Binlog (binary log)

Binlog is a log file used by MySQL to record all events that modify the database, including:

  • Data modification events: For example, INSERT, UPDATE, DELETE, etc.
  • DDL Events: For example, CREATE, ALTER and other operations.
  • Log files:Binlog is a sequence of files, each file has a unique name. It is the core of logging all database changes.

Binlog stores the operation log rather than the data itself, so the library needs to update its own data based on these operations.

3. Workflow:

The Binlog-based replication mode can be roughly divided into the following steps:

1. Main library record Binlog

  • When any changes are performed in the main library, these operations are logged into the Binlog file of the main library.
  • These operations are recorded sequentially and arranged in chronological order.

2. Connect to the main library by slave library

  • Binlog data is obtained by connecting to the main library from the library. This connection passesIO threadTo implement it, the slave library will periodically request new Binlog events from the main library.
  • The slave library will record the current one of the main libraryBinlog Location, and requests to start synchronizing data from that location.

3. Read Binlog from the library

  • The IO thread from the library gets the latest Binlog events from the main library and stores these events into the local Binlog file of the slave library.

4. Execute Binlog events from the library

  • From the librarySQL threadsThe locally stored Binlog will be read and the corresponding SQL statement will be executed according to the operations in the log. In this way, the slave library can be consistent with the data in the master library.

5. Stay synchronous

  • The main and slave libraries are consistent through Binlog continuous synchronization. Each time the master library has new data updates, these updates are propagated to the slave library through Binlog.

4. Key components of master-slave replication:

  • Binlog（Binary Log）: Record all operations to change data, and the main library passes the changed content through Binlog.
  • I/O threads: The I/O thread of the slave library is responsible for reading the Binlog event from the master library and writing it to the slave library's local Binlog.
  • SQL threads: The SQL thread of the slave library is responsible for reading the local Binlog and performing operations to keep the data of the slave library synchronized.

5. Types of copy mode:

Master-slave replication based on Binlog can be divided into different replication modes, mainly as follows:

  • Asynchronous replication：

    • In this mode, after the main library performs an operation, it returns without waiting for the slave library to confirm. The master library will not wait for the slave library to have completed the data synchronization.
    • advantage: High performance, no delay is added because of waiting for confirmation from the library.
    • shortcoming: If the main library fails, unsynced data may be lost.

  • Semi-Synchronous Replication：

    • In this mode, after performing an operation, the master library waits for at least one slave library to confirm that the data has been received and written to the local Binlog before returning. This is slightly safer than fully asynchronous replication, but there is still a latency.
    • advantage: More secure than asynchronous replication, at least one slave library can confirm synchronization.
    • shortcoming: Will increase a certain delay and may affect performance.

  • Synchronous Replication：

    • In this mode, after performing an operation, the master must wait for all slaves to confirm that the data has been synchronized before returning.
    • advantage: It can ensure that the data of the master library and all slave libraries are completely consistent.
    • shortcoming: Large performance overhead, high latency, and requires more resources.

6. Replication delay and fault tolerance:

  • Replication delay: Since the master library and the slave library are asynchronously synchronized, in high concurrency scenarios, the slave library may experience delays, resulting in inconsistent data between the master library and the slave library. This delay is usually caused by slow processing speeds in the library or network problems.

  • Fault tolerance: Binlog-based master-slave replication. When the master library fails, it needs to be switched manually or automatically to the slave library. Although the slave library can keep a copy of the master library, a certain transaction may be lost in the event of a master library failure, so the demand for high availability needs to be combined with other technologies (such asMHA、ProxySQL、Group Replicationetc.) to improve fault tolerance.

7. Pros and cons:

advantage:

  • Simplicity: Binlog-based copy settings are relatively simple, easy to understand and implement.
  • performance: Compared with GTID replication mode, the performance overhead of Binlog replication mode is smaller.
  • Strong compatibility:Binlog replication is the standard replication method of MySQL and is supported in almost all versions.
  • Widely applicable: Suitable for most scenarios where master-slave synchronization is required, especially read-write separation and load balancing.

shortcoming:

  • Slow recovery: If the master library fails, manual intervention may be required to restore the master-slave replication relationship, and unsynchronized data may be lost during the switching process.
  • Data inconsistency may occur: In the case of large network delay or replication delay, the master and slave data may be temporarily inconsistent.
  • Replication delay: At high load, there may be delay in master-slave replication, causing the slave database to lag behind the master database.

Summarize:

Master-slave replication based on Binlog is a common way to implement data replication in MySQL. It maintains data consistency by recording the binary log of the master library and syncing the logs to the slave library. This method runs stably and has good performance in most applications, but needs to pay attention to problems such as failure recovery and replication delay. It is suitable for scenarios such as read and write separation, load balancing, etc. in high-availability architectures.

The binlog binary log file records changes to all databases on the main server

Practical operation, master-slave copying between two slave libraries of one master library

refer to:/2401_85648342/article/details/139765433

Data persistence management-path planning

.
├── 
├── master1
│   ├── conf
│   ├── data
│   └── logs
├── slave1
│   ├── conf
│   ├── data
│   └── logs
└── slave2
    ├── conf
    ├── data
    └── logs

Create related folders

# Create a persistent directorymkdir -p /opt/mysql-compose/{master1/{data,logs,conf},slave1/{data,logs,conf},slave2/{data,logs,conf}}
# Modify permissionschmod -R 777 /opt/mysql-compose/{master1/{data,logs},slave1/{data,logs},slave2/{data,logs}}
# Temporary test-delete persisted datarm -rf /opt/mysql-compose/{master1/{data/*,logs/*},slave1/{data/*,logs/*},slave2/{data/*,logs/*}}
#rm -rf /opt/mysql-compose/{master1/data/*,slave1/data/*,slave2/data/*}

Upload configuration files () to the conf directory separately

Master1 configuration file is as follows

[mysqld]
# The server's unique id, default value 1server-id=11
# Set log format, default value ROWbinlog_format=STATEMENT
# Binary log name, default binlog# log-bin=binlog
# Set the databases that need to be copied, and all databases are copied by defaultbinlog-do-db=testdb
# Set up a database that does not require replication#binlog-ignore-db=mysql
#binlog-ignore-db=infomation_schema
#binlog-ignore-db=sys
#binlog-ignore-db=performance_schema

slave1 configuration file is as follows

# The server has a unique id. The id of each server must be different. If you configure other slaves, please make sure to modify the id.server-id=12
# Relay log name, default xxxxxxxxxxxx-relay-bin#relay-log=relay-bin

The slave2 configuration file is as follows

# The server has a unique id. The id of each server must be different. If you configure other slaves, please make sure to modify the id.server-id=13
# Relay log name, default xxxxxxxxxxxx-relay-bin#relay-log=relay-bin

The content is as follows

#version: "3.5"
services:
    #mysql:
    #    image: /multiway/mysql:8.0.29
    #    container_name: mysql8
    #    ports:
    #      - "13306:3306"
    #    restart: always
    #    environment:
    #      - MYSQL_ROOT_PASSWORD=123456
    #      - TZ=Asia/Shanghai
    #    volumes:
    #      - /opt/mysql-compose/master1/conf:/etc/mysql/
    #      - /opt/mysql-compose/master1/logs:/var/log/mysql
    #      - /opt/mysql-compose/master1/data:/var/lib/mysql
    mysql_master1:
        image: /multiway/mysql:8.0.29
        container_name: mysql_master1
        ports:
          - "13306:3306"
        restart: always
        environment:
          - MYSQL_ROOT_PASSWORD=123456
          - TZ=Asia/Shanghai
        volumes:
          - ./master1/mysql:/etc/mysql
          - ./master1/logs:/var/log/mysql
          - ./master1/data:/var/lib/mysql
    mysql_slave1:
        image: /multiway/mysql:8.0.29
        container_name: mysql_slave1
        ports:
          - "13307:3306"
        restart: always
        environment:
          - MYSQL_ROOT_PASSWORD=123456
          - TZ=Asia/Shanghai
        volumes:
          - ./slave1/mysql:/etc/mysql
          - ./slave1/logs:/var/log/mysql
          - ./slave1/data:/var/lib/mysql
    mysql_slave2:
        image: /multiway/mysql:8.0.29
        container_name: mysql_slave2
        ports:
          - "13308:3306"
        restart: always
        environment:
          - MYSQL_ROOT_PASSWORD=123456
          - TZ=Asia/Shanghai
        volumes:
          - ./slave2/mysql:/etc/mysql
          - ./slave2/logs:/var/log/mysql
          - ./slave2/data:/var/lib/mysql

Note: The server-uuid in the /var/lib/mysql/ file is the unique identifier (UUID) of the MySQL database server. This identifier is used to identify MySQL instances, especially in Replication settings, which can help distinguish different database instances. In MySQL, /var/lib/mysql/ is an automatically generated configuration file that usually contains UUID information for MySQL instances. You can view the UUID of the MySQL server through this file. It is automatically generated when MySQL starts and usually does not require manual modification. If docker-compose mounts the local directory, please check if there is any duplicate modification of server-uuid or delete this file and restart the mysql service.

[auto]
server-uuid=bc8c658e-ce63-11ef-89ae-0242ac130004

Run the docker-compose command to start the service

# Enter the folder at the levelcd /opt/mysql-compose
# Run the docker compose container servicedocker compose up -d
#Stop docker compose container servicedocker compose down
#View docker compose container service statusdocker compose ps

Use the database management tool to connect the master and slave databases

Check the status of the main library, connect to the master1 database and execute the following SQL statement

SHOW MASTER STATUS;

View the results

File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set
binlog.000005 157 testdb

Note: If it is a specified database such as testdb, first create the database in master1, create the table and add data, export the script, and then create the database slave1 and slave2 from the databases testdb to import and execute the SQL script to make the data of the master and slave database consistent. Before performing the master and slave copy operation, stop reading and writing operations of other services to the master and slave database, otherwise it will cause data loss and other problems. Simply put, ensure that the master and slave database data is consistent before the master and slave copy operation begins.

Connect slave1 and slave2 databases respectively to execute the following SQL statements, set or repair the master-slave replication relationship of MySQL

#1. Reset the replication settings from the server.#Function: Clear all replication settings currently from the server#Function: Reset the replication status from the server, including clearing the MASTER_* configuration, copy-related files, status tags, etc.  If the replication task was already running from the server before, executing this command will stop the replication process and clear all replication status information.#Usage scenario: This is usually used when configuring a new replication relationship, or when replication needs to be reset.RESET SLAVE;
#2. Configure the slave server to connect to the specified master server (192.168.137.2) and set the start point of replication.#Function: Set the master server connection information and replication location of the slave server.#Function: Configure how the slave server connects to the master server and from which binary log file and location to start copying.CHANGE MASTER TO 
MASTER_HOST='192.168.137.2',   # Specify the IP address or host name of the master server, indicating that the slave server will connect to the hostMASTER_PORT=13306,             # Specify the port number of the main server. Usually, the default port of MySQL is 3306, modified according to the actual situation.MASTER_USER='root',            # Specify the username used for connection on the main server, usually a user with replication permissionsMASTER_PASSWORD='123456',      # Specify the password of the above user to authenticate the connectionMASTER_LOG_FILE='binlog.000005',# Specify the binary log file name of the master server and start copying data from the specified location of the file.MASTER_LOG_POS=157; #Specify where to start copying from the binary log file of the primary server.  The position is a number that represents the log entry starting from that position#3. Start the replication process from the server.#Function: Start the replication process from the server#Function: After executing CHANGE MASTER TO, start the replication task of the slave server, so that the slave server starts to connect to the master server and starts to copy data from the specified binary log file location.START SLAVE;
#4. Check the replication status from the server.#Function: Display the replication status from the server#Function: Check the current replication status of the slave server, including whether the connection to the master server successfully, whether the replication is going on normally, and any possible errors.This command returns a result containing multiple fields，Commonly used fields are Slave_IO_Running and Slave_SQL_Running，The values ​​of both areyes，Expressed separately I/O 线程and SQL Is the thread running?，Last_Error Show the last error message, etc.。
SHOW SLAVE STATUS;

This is the end of this article about implementing Mysql8 based on binlog deployment using Docker. For more related content on implementing Mysql8 based on binlog deployment using Docker, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!