Build MySQL master-slave replication based on Docker

This blog post is relatively simple, because it is the first time to use the master-slave copy of Docker,mysql in the CentOS environment, but also forget about the same, so this attempt to build in Docker.

According to the online tutorial walk or step on some pits, but fortunately finally build success, so record down, to avoid stepping on the repeat of the pit.

Build the Environment

Centos 7.2 64-bit

MySQL 5.7.13

Docker 1.13.1

Next, we'll install Docker on a single server and use Docker to run three MySQL containers, one for each master and two from the other.

Installing Docker

Execute command

If there is a hint, go all the way y

After the installation has successfully started Docker, review the version

Version information appears, the installation succeeds

Start Docker

Start Docker and set to boot from boot

install MySQL

Using Docker to pull MySQL images

running the main container

--name specify a name for the container, this is the master

-P maps the specified port of the container to the specified port of the host, which maps the container's 3306 port to the host's 3306 port

-e Sets the environment variable, which is the root password for the specified root account

-D background runs the container and returns the container ID

Mysql:5.7.13 specifying the MySQL version to run

Verify that the start is successful

Docker ps-a displays all the containers, including the non-running

[Email Protected]_0_17_centos ~]# Docker ps-a

Ee86c19336f8 mysql:5.7.13 "Docker-entrypoint ..." About a hour ago up about an hour 0.0.0.0:3306->3306/TCP master

Note that it is the up state, which indicates that it is running

Open 3306 Ports

--permanent permanently on to avoid the need to manually open the port again next time

Using the Navicat connection test

MySQL main container has started successfully

Create a copy account for the primary container

Execute SQL using the Navicat friendly graphical interface

The following message appears indicating a successful authorization

Modifying the MySQL configuration environment

To create a configuration file directory

The directory structure is as follows

/usr/local/mysql/master

/usr/local/mysql/slave1

/usr/local/mysql/slave2

Copy a MySQL configuration file

In the master directory, there is already a copy of the MY.CNF

Modify MY.CNF, save after the [Mysqld] node is added last

Log-bin=mysql-bin using binary logging,mysql-bin is the prefix of the log file name

server-id=1 unique server ID, not 0 integers, cannot be duplicated with Server-id of other servers

Overwrite the modified file with the configuration file in MySQL in Docker

Restart MySQL docker for the configuration to take effect

After starting, re-test the connection, the connection succeeds indicating that the primary container is configured successfully

run MySQL from the container

First run from container

Similar to the primary container, the copy configuration file is overwritten back to Docker after the SLAVE1 directory has been modified

Don't forget to restart the SLAVE1 container for the configuration to take effect

Configuring Master-slave replication

After you connect slave1 with Navicat, create a new query that executes the following SQL

Master_host fill Navicat The IP in the connection configuration should be able to

Master_port Port of the primary container

Master_user user name of the sync account

Master_password Password for sync account

Check if configuration is successful

Slave_io_state if it is waiting for master to send event, then half of the success, if it is connecting to master, basically is the configuration failed, it is recommended to recheck the configuration, Specific failure reasons can view log tracking

I met Master_user and master_password whether the hand is wrong, put out the error log

2018-05-10t02:57:00.688887z [ERROR] Slave I/O for Channel ": ERROR Connecting to master ' [email protected]:3306 '-ret Ry-time:60 Retries:2, error_code:1045

2018-05-10t02:58:00.690476z [ERROR] Slave I/O for Channel ": ERROR Connecting to master ' [email protected]:3306 '-ret Ry-time:60 Retries:3, error_code:1045

Take a look at the Bakcup in the log, the workaround is as follows

Then, we said that half of the success, and did not say success, then the other half is slave_io_running and slave_sql_running

If all is yes, then congratulations, you can test the master-slave copy of the effect, if there is a not yes, half is restarted from the container, the transaction rollback caused by, then the solution is as follows

After execution, observe the three key fields again it should all be okay.

At this point, a master from the already completed, and then add from the instance of the same way as above, here is not to repeat.

test Master-slave replication

First, create a test database in the master instance

Open (refresh) from instance, visible test library already exists

Create a table t_test in the test library, add an ID test field

Add a few data to the table

Refresh from library, visible t_test table and where 1, 2, 3, 4 data already exists

At this point, a master and slave copy of the main two from the MySQL has been built.

Source: Akita June

my.oschina.net/u/3773384/blog/1810111

Build MySQL master-slave replication based on Docker