docker mysql8 master-slave configuration

The mysql of this pull is version 8, which is used to build master-slave replication

 

With the upgrade of mysql image, the mount directory in the previous docker run command will be invalid. Make a record

The master and slave file directories are the same, so we need two copies of the following operations for conf configuration

master
  --data
  --conf
  --logs

slave
  --data
  --conf
  --logs

 

Step 1: temporarily start mysql to exit the container without generating container information

docker run --rm --name mysqltemp -it -v /usr/local/docker/mysql/slave/conf/:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=123456 mysql /bin/bash 

Mount the local directory where you want to put the configuration file in / var/lib/mysql of the container to copy the configuration file

Because I tried to write and copy by myself, I couldn't start the container, and I didn't know what was wrong, so I dug a hole for myself

 

Step 2 copy my.cnf and the dependency profile

cp /etc/mysql/my.cnf /var/lib/mysql
cp -R /etc/mysql/conf.d/ /var/lib/mysql/conf.d

It seems that mysql before version 8.0 can use my.cnf. Later, it needs to rely on the support of configuration files, otherwise the container's mount configuration file (my.cnf) is invalid

The master and slave will report that the server ID cannot be the same, etc

After executing the command, you can exit the temporary container to view the files and directories just cp down in / usr/local/docker/mysql/slave/conf /

 

Step 3

There may be several configuration files from the container cp, we just need to modify my.cnf file

[root@iZbp18caz0f7upvfdk2vzeZ conf]# cat my.cnf
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Edit add server ID and log bin
server-id=100
log-bin=mysql-bin

# Custom config should go here
!includedir /etc/mysql/conf.d/
[root@iZbp18caz0f7upvfdk2vzeZ conf]#

Edit add server ID and log bin

The server ID is unique. It is ok if it does not conflict with the master and slave. Remember to modify the value when copying

MySQL bin of log bin can be defined by itself. Here, MySQL bin is selected according to the original name of its master-slave copy

The principle of mysql replication

  • When the transaction is committed, the mysql main database will record the database changes as event Events in the binary binlog. The sys ﹣ binlog on the mysql main database controls the binlog to refresh to disk
  • The master database pushes the binary binlog to the relay log of the slave database, and then the slave database redoes the database change operation according to the relay log. Through logical replication, data consistency is achieved

Mysql completes the data replication between the master and slave libraries through 3 threads: the BinLog Dump thread runs on the master library, and the I/O thread and the SQL thread run on the slave library. When the start slave is started from the slave library, the I/O thread is first created to connect to the master library, and the master library then creates the Binlog Dump thread to read the database events and send them to the I/O thread, The I / O thread obtains the database event update to the relay log real log of the database, and then reads the updated database event in the relay log real log from the sqlthread of the database and applies it

 

Step 4 start the master-slave container

-- Main library
docker run --name master -p 3336:3306  
-v /usr/local/docker/mysql/master/data:/var/lib/mysql 
-v /usr/local/docker/mysql/master/conf/my.cnf:/etc/mysql/my.cnf 
-v /usr/local/docker/mysql/master/conf/conf.d:/etc/mysql/conf.d  
-v /usr/local/docker/mysql/master/logs:/var/log/mysql
-e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest

-- Slave Library
docker run --name slave -p 3337:3306  
-v /usr/local/docker/mysqlzc/slave/data:/var/lib/mysql 
-v /usr/local/docker/mysql/slave/conf/my.cnf:/etc/mysql/my.cnf 
-v /usr/local/docker/mysql/slave/conf/conf.d:/etc/mysql/conf.d  
-v /usr/local/docker/mysql/slave/logs:/var/log/mysql
-e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest

 

docker exec -it master /bin/bash

-- Create user to give permission
CREATE USER 'slaveuser'@'%' IDENTIFIED WITH mysql_native_password BY 'slaveuser0!';
GRANT ALL PRIVILEGES ON *.* TO 'slaveuser'@'%';
FLUSH PRIVILEGES;

-- Do this again sql View primary server bin Log information
show master status;

To view the bin information for configuration from the library, you can take a picture and record it first

 

Enter execute command from library container

change master to 
master_host='172.17.0.2',            -- Main library ip
master_user='slaveuser',             -- User name of main library
master_password='slaveuser0!',       -- Master database password
master_port=3306,                    -- Main library port
master_log_file='mysql-bin.000060',  -- Values in the screenshot of main database
master_log_pos= 248,                 -- Values in the screenshot of main database
master_connect_retry=30;             -- Number of seconds to sleep before trying to connect to the primary server again from the server thread,Default 60

The above is a line of command. Container ip view command: docker inspect -- format = '{. Networksettings. IPAddress}}' container name | container id

Finally, open the command start slave from the library;

View the message show slave status \G; if there is any abnormal error in the middle, you can view it in this message

  • slave-IO-running : yes
  • slave-sql-running: yes

Two parameters "yes" appear in the message, indicating that the building is successful

You can test that the main database creates a database and a table, and the same database table and data will appear in a while from the database

Tags: Programming MySQL Database Docker SQL

Posted on Thu, 07 May 2020 01:17:16 -0400 by aaronxbond