Shulou(Shulou.com)06/02 Report--

Preface

The master-slave replication of MySQL is the basis of high performance and high availability of applications. For the applications with intensive database read operations, the database pressure can be effectively reduced by balancing the load of database requests to different MySQL servers. When a single point of failure of MySQL is encountered, the failover can also be realized in a short time. This paper describes the built-in replication function of MySQL.

Version

MySQl: 5.7.17CentOS: 7.4.1708Docker: 1.13.1

Overview

MySQL replication data flow:

Before the data update commits the transaction, the master database asynchronously records the event to the binlog binary log file. After the log recording is completed, the storage engine commits this transaction and starts an I / O thread to establish a connection with the master database to request the binlog to be updated in the master database. At this point, the binlog dump thread created by the main library, which is the binary dump thread, notifies 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 enters the sleep state. After receiving the new event log from the I / O thread of the library, save it to its own relay log (Relay Log) to read the events in the relay log from the SQL thread of the library, and perform an update save.

Configure master-slave library

Main library my.cnf configuration

Open the binary log in the my.cnf of the main library and set the service Id.

Log-bin = mysql-binserver-id = 1

Note that server-id must be a unique number, the master must be inconsistent, and the master-slave library must set items.

From the library my.cnf configuration

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

When the slave library also enables log-bin,log-slave-updates to replay the relay log from the slave library, record it in your own binary log. You can use the slave database as the master library of other servers and forward the binary log to other slave libraries. You can consider this solution when working with one master and multiple slaves.

Dockerfile to build MySQL image

Build the required files

Here, master and slave files are not shared separately. Create a folder / usr/local/mysql, then create two directories in the directory, master and slave, and then create a data folder.

Data directory the directory used to save data files Dockerfile saves the Dockerfile content SQLmy.cnf database configuration file for the init.sql initialization database, in the configuration manner mentioned above, the script for start.sh Dockerfile to build MySQL

Dockerfile content

# create a new image FROM mysql:5.7.17ENV MYSQL_ROOT_PASSWORD ytaoCOPY start.sh / mysql/start.shCOPY my.cnf / etc/mysql/my.cnf COPY init.sql / mysql/init.sqlEXPOSE 3306CMD using mysql image ["sh", "/ mysql/start.sh"]

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

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

Create a data_copy database and person table.

Start.sh script

#! / bin/shecho 'start mysql'service mysql startsleep 5echo' initialize the database 'mysql-uroot-pytao < / mysql/init.sqlecho' initialization complete! Tail-f / dev/null

Build master and slave images and run the container

Build a master image

Docker build-t master/mysql.

Build a slave image

Docker build-t slave/mysql.

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

Use the image you just built to run the container

# master Container docker run-- name master-p 3306 master/mysql# slave 3306-v / usr/local/mysql/master/data/:/var/lib/mysql-d master/mysql# slave Container docker run-- name slave-p 3307 Freight 3306-v / usr/local/mysql/slave/data/:/var/lib/mysql-d slave/mysql

Specify that the master port is 3306 and the port is 3307 and the mount data directory is the directory where the data is stored.

Verify that the database is initialized successfully after connecting to the database

Check to see if log-bin is on

Create a copy account

It was mentioned earlier that the slave library Ibind O thread needs to establish a connection with the main library, so the account needs to be used for verification. The account should not only have connection permission (REPLICATION CLIENT), but also copy permission (REPLICATION SLAVE).

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

The access address set here is open, and you must specify the access address for security reasons in the actual use.

Start replication from the library

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

CHANGE MASTER TO MASTER_HOST = '47.107.xx.xxxrecording Magazine master master Port = 3306 Masters user =' muser',MASTER_PASSWORD = 'ytao',MASTER_LOG_FILE =' mysql-bin.000006'

Replication has not been started yet. You need to start it again from the library.

START SLAVE

Use the SHOW SLAVE STATUS\ G; command to see what happens after startup

The output information marked above, Slave_IO_Running: Yes and Slave_SQL_Running: Yes, can see that the I / O thread and SQL thread are up and running.

Test synchronization data

If you add, update or delete a data in the master library, then there should also be data changes corresponding to the master library in the slave library.

Add a piece of data to the main library

INSERT INTO `data_ copy`.`person` (`id`, `name`) VALUES ('1customers,' ytao')

Query data from the library, the data has been synchronized.

Summary

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

Although the replication of MySQL is simple and convenient to use, it is also accompanied by some problems that need to be solved in use, such as the inability to recover from the abnormal stop of the server, the delay of data synchronization and so on. Fortunately, most of the problems encountered now have been solved in the industry. Those who are interested in this area can learn about the current middleware implementation to solve these problems.

Well, the above is the whole content of this article. I hope the content of this article has a certain reference and learning value for your study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.