Master-slave replication of MySQL based on docker

Time:2020-6-2

Master-slave replication of MySQL based on docker

preface

MySQLMaster-slave replication is the foundation of high performance and high availability. For applications with intensive database read operations, the load balance of database requests is distributed to differentMySQLServer can effectively reduce the pressure of database. WhenMySQLIn the single point fault, it can also realize the failover in a short time. This article is aboutMySQLThe built-in replication function of is described.

edition

  • MySQl: 5.7.17
  • CentOS: 7.4.1708
  • Docker: 1.13.1

summary

MySQLReplication data process:

  1. The main database records the event to the binlog binary log file asynchronously before the data update is submitted. After the logging is completed, the storage engine submits the transaction
  2. Start an I / O thread from the library to establish a connection with the main library to request the binlog to be updated in the main library. At this time, the binlog dump thread created by the main database, which is a binary dump thread, will notify the I / O thread if there is a new update event; when the thread dumps the binary log and there is no new log, the thread will enter the sleep state.
  3. After receiving the new event log from the I / O thread of the library, save it to its own relay log
  4. Read the events in the relay log from the SQL thread of the library and perform an update save.

Master-slave replication of MySQL based on docker

Configure master-slave Library

Main librarymy.cnfto configure

In the main librarymy.cnfOpen binary log in and set the service ID.

log-bin = mysql-bin
server-id = 1

be carefulserver-idMust be a unique number, must have inconsistent master-slave, and master-slave library must have items set.

From librarymy.cnfto configure

log-bin = mysql-bin
server-id = 2
log-slave-updates = 1
read-only = 1

From library also onlog-binlog-slave-updatesWhen the relay log is set to be replayed from the slave database and recorded in its own binary log, the slave database can be used as the master database of other servers to forward the binary log to other slave databases. This scheme can be considered when making a master multi slave scheme.

Dockerfile building MySQL image

Build required files

heremasterandslaveThe files are saved separately and not shared. Create a folder first/usr/local/mysqlThen create it in the directorymasterandslaveTwo directories, and create them separatelydatafolder
Master-slave replication of MySQL based on docker

  • The directory where data files are stored
  • Dockerfile save dockerfile content
  • init.sql Initialize SQL for database
  • my.cnf Database configuration file, configuration mode mentioned above
  • start.sh Script when dockerfile builds MySQL

Dockerfile content

#Using MySQL image to create a new image
FROM mysql:5.7.17

ENV MYSQL_ROOT_PASSWORD ytao

COPY start.sh /mysql/start.sh
COPY my.cnf /etc/mysql/my.cnf  
COPY init.sql /mysql/init.sql

EXPOSE 3306
CMD ["sh", "/mysql/start.sh"]

theremasterandslaveThey are all built on the same image. It is better to use the same storage engine and other components. Otherwise, exceptions may occur during the replication process.

init.sql Initialization data

--Create data_ Copy database
DROP DATABASE IF EXISTS `data_copy`;
CREATE DATABASE `data_copy` /*!40100 DEFAULT CHARACTER SET utf8mb4 collate utf8mb4_general_ci */;

--Create person table
USE `data_copy`;
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(32) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

establishdata_copyDatabase andpersonTable.

start.sh script

#!/bin/sh
Echo 'start MySQL'
service mysql start
sleep 5

Echo 'initialize database'
mysql -uroot -pytao < /mysql/init.sql
Echo 'initialization complete! '
tail -f /dev/null

structuremasterandslaveMirror and run container

structuremasterimage

docker build -t master/mysql .

structureslaveimage

docker build -t slave/mysql .

Build success will returnSuccessfuly, or bydocker imagesCommand view image
Master-slave replication of MySQL based on docker

Use the image you just built to run the container

#Master container
docker run --name master -p 3306:3306 -v /usr/local/mysql/master/data/:/var/lib/mysql -d master/mysql

#Slave container
docker run --name slave -p 3307:3306 -v /usr/local/mysql/slave/data/:/var/lib/mysql -d slave/mysql

appointmasterPort is3306,slavePort is3307, the mount data directory is the directory where the data is stored.

Verify that the database is initialized successfully after connecting to the database
Master-slave replication of MySQL based on docker

seelog-binOpen or not
Master-slave replication of MySQL based on docker

Create a copy account

As mentioned earlier, the slave I / O thread needs to establish a connection with the main database, so it needs to use an account for verification. In addition to the connection client, the account should also have the replication slave.

GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO [email protected]'%' IDENTIFIED BY 'ytao';   

The access address set here is open, and it must be specified for the sake of security in actual use.

Start replication from library

Connect from the library to the main library, get the binary log and replay it. First of all, configure the account created above to connect, and use the command to set the corresponding settings.

CHANGE MASTER TO 
MASTER_HOST = '47.107.xx.xxx',
MASTER_PORT = 3306,
MASTER_USER = 'muser',
MASTER_PASSWORD = 'ytao',
MASTER_LOG_FILE = 'mysql-bin.000006';

The replication has not started yet. You need to start from the library again

START SLAVE;

useSHOW SLAVE STATUS\G;Command to view the situation after startup
Master-slave replication of MySQL based on docker

Output information marked aboveSlave_IO_Running: YesandSlave_SQL_Running: YesYou can see that the I / O thread and the SQL thread are running.

Test synchronization data

If you add, update or delete a data in the master database, there should be data changes corresponding to the master database in the slave database.
Add a piece of data to the main library

INSERT INTO `data_copy`.`person` (`id`, `name`) VALUES ('1', 'ytao');

Query the data from the database. The data has been synchronized.
Master-slave replication of MySQL based on docker

summary

The above is the simplest and most basic configuration, but understanding the above configuration process, you can customize different schemes according to your own situation, realize one master and many slaves, master master copy (active or active passive mode), etc. to meet your own needs.
MySQLAlthough the replication of is simple and convenient to use, there are also some problems that need to be solved in use, such as: can’t recover from abnormal stop of server, delay of data synchronization, etc. fortunately, most of the problems encountered now have been solved in the industry. Interested in this aspect, you can learn about the middleware implementation solutions to solve these problems.

Personal blog: https://ytao.top
My official account ytao
Master-slave replication of MySQL based on docker