Building mysql master-slave structure with docker-compose

Posted by grantson on Mon, 21 Feb 2022 18:37:01 +0100

Using docker-compose to build mysql master-slave structure

A few days ago, I tried to learn the online tutorial. Using docker-compose, I set up a simple master-slave mysql for learning purposes. Next, I will show you the pure dry goods step by step in the following section. Roll up!!!

First, the basic environment and software used by the author are introduced.

Centos 7.6
Windows 10
Docker version 20.10.12
Docker Compose version v2.2.2
Navicat Premium 15

docker-compose.yml configuration and description

The author built a new docker-compose in the / home/docker directory. YML Execution Profile

#docker-compse corresponding version docker version
version: '3'
services:
  # mysql configuration (primary)
  mysql-master:
    # Use mysql5. Version 7 Mirror 
    image: mysql:5.7
    # Set Self-Start
    restart: always
    # Container name
    container_name: mysql-master
    # mysql root user password configuration
    environment:
      MYSQL_ROOT_PASSWORD: 123456
    # mysql main library configuration, see below for details
    command:
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
      --server-id=1
      --log-bin=mysql-bin
      --binlog-do-db=Database Name
      --innodb_flush_log_at_trx_commit=2
      --sync_binlog=1
      --max_allowed_packet=128M;
    # Port Binding
    ports:
      - 3306:3306
    # Data Volume (new corresponding empty file needs to be created on the host)
    volumes:
      - "/home/docker/mysql/master/my.cnf:/etc/mysql/my.cnf"
      - "/home/docker/mysql/master/conf:/etc/mysql/conf.d"
      - "/home/docker/mysql/master/data:/var/lib/mysql"
      - "/home/docker/mysql/master/error.log:/var/log/mysql/error.log"
  # mysql configuration (from)
  mysql-slave-3307:
    # Use mysql5. Version 7 Mirror 
    image: mysql:5.7
    # Set Self-Start
    restart: always
    # Container name
    container_name: mysql-slave-3307
    # mysql root user password configuration
    environment:
      MYSQL_ROOT_PASSWORD: 123456
    # MySQL main library configuration, see/home/docker/mysql/master/my for details. CNF
    command:
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
      --server-id=428
      --max_allowed_packet=128M;

#Configuration Instructions

Only parameters involving master-slave configuration are described here

server-id sets the ID of the service (id can be set freely by itself, but to make sure it's different from slave's id, configure this from the library only)
log-bin=mysql-bin Enables binary log files (master-slave core, don't know what's recommended to do to understand the execution process of master-slave database, I won't introduce it here)
binlog-do-db=database name This means that only one library is synchronized (if not, all libraries are synchronized)

innodb_flush_log_at_trx_commit=2

0: When a transaction commits, instead of writing the redo log buffer to disk, it relies on InnoDB The main thread executes a refresh to disk every second. So if MySQL If a downtime occurs, a portion of the transaction may be lost.

1: When a transaction commits, the redo log buffer is written to disk and refreshed immediately ( fsync()). Note that because of the "delayed write" feature of the operating system, the flush is only written to the operating system's buffer at this time, so synchronization can be performed to ensure that it is persisted to the hard disk.

2: When a transaction commits, the redo log buffer is written to disk, but no refresh operation occurs immediately, so it is only written to the operating system's buffer. If the operating system goes down without instant synchronization, some data may also be lost.

You can see that only one can really guarantee the persistence of a transaction, but because of the refresh operation fsync() It's blocked and doesn't return until it's finished. We know that writing to disk is slow, so MySQL Performance will be significantly degraded. If you don't care about transaction loss, 0 and 2 will achieve better performance.

In terms of efficiency:
Data volume: 4 w
Temporary change:set global innodb_flush_log_at_trx_commit=2;Failure after restarting database
Query statement: SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';

Time to set 0: about 1 minute
Default 1 time: about 20 minutes
Set time for 2: 8 Minutes or so
--------
Reference blog documentation link: https://blog.csdn.net/qq_31984879/article/details/103952183

sync_binlog=1 Turns on binlog log synchronization

At this point, the preparation is complete and the docker container is run in this directory using the'docker-compose up --build-d'command.

Here, for convenience, the author uses Navicat Premium 15 to build the master-slave mysql work.

First, connect the two mysql services.

On Main Database

# Query log_bin state, log_bin for ON docker initialization built successfully
show variables like '%log_bin%';
# Prepare to create a primary library user and grant permissions (here is the sql statement for the author to graphically create a new account using Navicat Premium 15)
CREATE USER `User name`@`%` IDENTIFIED WITH mysql_native_password BY '123456';
GRANT Alter, Alter Routine, Create, Create Routine, Create Temporary Tables, Create View, Delete, Drop, Event, Execute, Grant Option, Index, Insert, Lock Tables, References, Select, Show View, Trigger, Update ON `Master-Slave Database Name`.* TO `User name`@`%`;
# Authorized main library account
GRANT REPLICATION SLAVE ON *.* TO 'User name'@'%';
# Query the state of the primary library, where the File and Position columns are used after the library
SHOW MASTER STATUS;

From the library database

# Create a primary library connection
change master to
    master_host=''  ,#Primary Library host
    master_user=''  ,#Authorized main library account
    master_port=    ,#Primary Library Port
    master_password=''  ,#Authorized main library account password
    master_log_file=''  ,#Query Primary Library status File column (check once before creating a primary library connection)
    master_log_pos=     ;#Query the primary library status Position column (check once before creating a primary library connection to get the latest value)
# Start Slave Library Service
start slave;
# View From Status
show slave status;
# If Slave_IO_Running and lave_ SQL_ When Running is Yes, the master and slave Mysql are built successfully

Up to this point, the master has been successfully built from Mysql. If you change the master library, the slave library will synchronize the changes.

#Note:

Note that do not change the slave library, otherwise Slave_IO_Running will become No, you have to reconfigure master-slave mysql
It is recommended that you create a new read-only account from the library, where you can do read-write separation

#Exception Solution

# Exception Solution
# mysql master-slave replication Slave_ SQL_ Running: Solutions for No
stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;
show slave status;
# mysql master-slave replication Slave_ IO_ Running: The solution for No did not start successfully.
# Synchronize databases with data transfers using toolbars first, then restart master slave sql
# Use when startup from library service is unsuccessful, reset from state
reset slave;

So far in this tutorial, what's wrong, what's wrong, what's wrong, what's worth optimizing. Welcome to teach ~I'm Xiaolu, I like to drill into Internet technology, roll up together~

Topics: Database MySQL Docker

Programmer Think