Master-slave replication based on Docker MySQL

Docker MySQL implements master-slave replication (1 Master + 2 Slave)

1. Requirements:
The three versions of mysql are identical
Initialize the table and start mysql in the background
Modify the root password

Modify the configuration file:
Start a docker mysql container and enter it, copy its configuration file / etc/mysql/my.cnf to local as the basic configuration.

docker run --name db1 -p 30000:3306 -d -e MYSQL_ROOT_PASSWORD='root' mysql:5.6
docker exec -it 757b /bin/bash
cat /etc/mysql/my.cnf

Copy the content to / home/jayhe/jay/config/mysql/master_2slave/my-m.cnf as the basic configuration. The following modifications are based on this basic configuration

2. Modify master server:
my.cnf

[mysqld]
#[Must] Start Binary Logs
log-bin=mysql-bin
#[Must] Set the unique ID of the server, default 1, usually the last segment of IP
server-id=3001

3. Modify slave from server:
my.cnf

[mysqld]
#Start binary logs [not required]
log-bin=mysql-bin
#[Must] Set the unique ID of the server, default 1, usually the last segment of IP
server-id=3002

my.cnf

[mysqld]
#Start binary logs [not required]
log-bin=mysql-bin
#[Must] Set the unique ID of the server, default 1, usually the last segment of IP
server-id=3003

Start three mysql:
Master server: mount / XX / XX / my. CNF (master server configuration) to mysql container

docker run --name mysql1 -p 3001:3306 -d -v /home/jayhe/jay/config/mysql/master_2slave/my-m.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD='root' mysql:5.6

From server: mount / XX / XX / my. CNF (from server configuration) to mysql container

docker run --name mysql2 -p 3002:3306 -d -v /home/jayhe/jay/config/mysql/master_2slave/my-s1.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD='root' mysql:5.6

docker run --name mysql3 -p 3003:3306 -d -v /home/jayhe/jay/config/mysql/master_2slave/my-s2.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD='root' mysql:5.6

Connect mysql in the container -- Even if the local mysql is started, it can still connect to the container's mysql service
Syntax:

mysql -h IP -P port -uroot -proot

##Connect to the primary server
mysql -h 127.0.0.1 -P 3001 -uroot -proot     
##Log on to the main server, create an account, and authorize
grant replication slave on *.* to 'mysync'@'%' identified by 'root';
##View the status of the primary server
show master status;

 mysql -h 127.0.0.1 -P 3002 -uroot -proot

Configure the slave server:
Here master_log_file='mysql-bin.000004', master_log_pos is consistent with master status in the master server

change master to master_host='172.17.64.106',master_port=3001,master_user='mysync',master_password='root',master_log_file='mysql-bin.000004', master_log_pos=312;

Start the replication function from the server

start slave;

View the status of the replication function from the server:

   show slave status;

+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+

Be careful:
Slave_IO_State = Waiting for master to send event
Slave_IO and Slave_SQL are both YES
Represents a successful master-slave replication configuration
Testing master-slave replication:
Enter the main server and create a database
mysql -h 127.0.0.1 -P 3001 -uroot -proot
create database test1;
Go to the slave server, look at the database, and find that test1 already exists -- synchronized with the primary server
mysql -h 127.0.0.1 -P 3001 -uroot -proot
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test1 |
+--------------------+

Special attention should be paid to:
1. In the primary server, create an account and authorize
grant replication slave on *.* to 'mysync'@'%' identified by 'root';
Usually no root account is used.
* * Represents that all clients can be connected. As long as the account and password are correct, specific IP can be used instead to enhance security eg: 192.168.145.222.

2. From the server, when configuring the slave server:
master_host is the host address of docker, unable to write 127.0.0.1
master_port as host server mysql maps to host port 3001
User created in master_user as master server mysync
File queried by master_log_file as master server show master status
Position queried by master_log_pos as master server show master status

3. Configuration file other content configuration items:
# If additional Slave libraries are needed, the id is deferred later;
server-id = 2
log-bin=mysql-bin
# host
master-host = 192.168.168.253
# Users established in the primary database server for backup use from the slave server
master-user = forslave
master-password = ******
master-port = 3306
# If the main server is disconnected, the time difference of reconnection is found.
master-connect-retry=60
# A database that does not need to be backed up;
replicate-ignore-db=mysql
# Database to be backed up
replicate-do-db=minishop
log-slave-update

4. from MySQL my.cnf Basic Configuration Obtained in Mirror Container

# Copyright (c) 2015, 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 Community Server configuration file.  
#  
# For explanations see  
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html  
  
[client]  
port        = 3306  
socket      = /var/run/mysqld/mysqld.sock  
  
[mysqld_safe]  
pid-file    = /var/run/mysqld/mysqld.pid  
socket      = /var/run/mysqld/mysqld.sock  
nice        = 0  
  
[mysqld]  
user        = mysql  
pid-file    = /var/run/mysqld/mysqld.pid  
socket      = /var/run/mysqld/mysqld.sock  
port        = 3306  
basedir     = /usr  
datadir     = /var/lib/mysql  
tmpdir      = /tmp  
lc-messages-dir = /usr/share/mysql  
explicit_defaults_for_timestamp  
log-bin=mysql-bin  
server-id=3001  
# Instead of skip-networking the default is now to listen only on  
# localhost which is more compatible and is not less secure.  
#bind-address   = 127.0.0.1  
  
#log-error  = /var/log/mysql/error.log  
  
# Recommended in standard MySQL setup  
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  
  
# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0  
  
# * IMPORTANT: Additional settings that can override those from this file!  
#   The files must end with '.cnf', otherwise they'll be ignored.  
#  
!includedir /etc/mysql/conf.d/

Posted by asanvicente on Tue, 04 Jun 2019 11:29:54 -0700

Programmer Group

Master-slave replication based on Docker MySQL

Docker MySQL implements master-slave replication (1 Master + 2 Slave)

Hot Keywords