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.
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;
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)
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 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 # 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~