How to implement MySQL master-slave replication based on Docker

Preface

MySQL master-slave replication is the basis for achieving high performance and high availability of applications. For applications with intensive database read operations, database pressure can be effectively reduced by load balancing database requests to different MySQL servers. When encountering a single point failure in MySQL, failover can be achieved in a short time. This article explains the built-in replication function of MySQL.

Version

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

Overview

MySQL replication data process:

1. Before the master database commits a transaction after data update, it asynchronously records the event in the binlog binary log file. After the log record is completed, the storage engine commits the transaction.
2. The slave starts an I/O thread to establish a connection with the master to request the binlog to be updated in the master. At this time, the binlog dump thread created by the main library is a binary dump thread. If there is a new update event, it notifies the I/O thread. When the thread completes dumping the binary log and there is no new log, the thread enters the sleep state.
3. After receiving the new event log from the library's I/O thread, save it to its own relay log
4. The slave SQL thread reads events from the relay log and performs updates and saves them.

Configure the master-slave library

Main library my.cnf configuration

Enable binary logging in the master database's my.cnf file and set the service ID.

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

Note that server-id must be a unique number, must be different between the master and slave, and must be set for the master and slave databases.

Configure from library my.cnf

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

The slave library also opens log-bin and sets log-slave-updates to record the relay logs in its own binary log when the slave library replays them. This allows the slave library to serve as the master library for other servers and forward the binary logs to other slave libraries. This solution can be considered when doing a one-master-multiple-slave solution.

Dockerfile builds MySQL image

Build required files

Here, the master and slave files are saved separately and not shared. First create the folder /usr/local/mysql, then create two directories, master and slave, in the directory, and then create data folders for each.

• The data directory is used to store data files
• Dockerfile saves the Dockerfile content
• init.sql SQL to initialize the database
• my.cnf database configuration file, the configuration method has been mentioned above
• start.sh Dockerfile script for building MySQL

Dockerfile contents

# Create a new image using the mysql 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"]

The master and slave here are built based on the same image, and the storage engine and other components used should be the same, otherwise exceptions may occur during the replication process.

init.sql initializes 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;

Create the data_copy database and the person table.

start.sh script

#!/bin/sh
echo 'Start mysql'
service mysql start
sleep 5

echo 'Initialize database'
mysql -uroot -pytao < /mysql/init.sql
echo 'Initialization completed! '
tail -f /dev/null

Build the master and slave images and run the containers

Build the master image

docker build -t master/mysql .

Build slave image

docker build -t slave/mysql .

If the build is successful, Successfuly will be returned, or you can view the image through the docker images command

Run the container using the image you just built

# 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

Specify the master port as 3306, the slave port as 3307, and mount the data directory as the directory where the data is stored.

After connecting to the database, verify whether the database is initialized successfully

Check whether log-bin is enabled

Create a replication account

As mentioned earlier, the slave I/O thread needs to establish a connection with the master, so an account is needed for verification. In addition to the connection permission (REPLICATION CLIENT), the account must also have the replication permission (REPLICATION SLAVE).

GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO muser@'%' IDENTIFIED BY 'ytao';

The access address set here is open. For security reasons, you must specify the access address during actual use.

Start copying from the repository

Connect the slave database to the master database, obtain the binary log and replay it. Here we first need to configure the account created above to connect and use the command to make 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';

At this point, the copy has not started yet, so you need to start it from the library again

START SLAVE;

Use SHOW SLAVE STATUS\G; command to view the status after startup

The output information marked above Slave_IO_Running: Yes and Slave_SQL_Running: Yes shows that the I/O thread and SQL thread have been started and are running.

Test synchronization data

If a piece of data is added, updated or deleted in the master database, the slave database should also have corresponding data changes to the master database.

Add a piece of data to the main database

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

Query the slave database data, the data has been synchronized.

Summarize

The above is the simplest and most basic configuration. However, if you understand the above configuration process, you can customize different solutions according to your own situation, implement one master and multiple slaves, master-master replication (active-active or active-passive mode), etc. to meet your own needs.

Although MySQL replication is simple and convenient to use, it is also accompanied by some problems that we need to solve during use, such as: inability to recover from abnormal server shutdown, delays in data synchronization, etc. Fortunately, most of the problems encountered now have been solved accordingly in the industry. Those who are interested in this aspect can learn about the middleware implementation solutions that are currently solving these problems.

Well, that’s all for this article. I hope the content of this article will be of certain reference value to your study or work. Thank you for your support of 123WORDPRESS.COM.