Docker practice: mysql Master/Slave backup read/write splitting, dockermysql

Docker practice: mysql Master/Slave backup read/write splitting, dockermysql

1) download the msql Image File

docker pull mysql/mysql-server

You can also specify the mysql version.

docker pull mysql/mysql-server:5.7

View image files
Docker images

2) set the Directory
To keep MySql Data on the host machine, create several directories first.
Create a database storage directory
Mkdir-pv/root/docker/mysql/data
Create the master and slave directories under the data Directory to store database files.

Create a configuration directory for the master server
Mkdir-pv/root/docker/mysql/master

Create a configuration directory for the slave server
Mkdir-pv/root/docker/mysql/slave

3) configure the Master/Slave Server
Vi./master. cnf
The Code is as follows:

[mysqld]log-bin=mysql-binserver-id=101

Vi./slave. cnf
The Code is as follows:

[mysqld]log-bin=mysql-binserver-id=102

Note: The server-id in the slave database must be a number.

4) create a master-slave server container

docker create --name master -v /root/docker/mysql/data/master:/var/lib/mysql -v /root/docker/mysql/master:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 mysql:5.7docker create --name slave -v /root/docker/mysql/data/slave:/var/lib/mysql -v /root/docker/mysql/slave:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -p 3316:3306 mysql:5.7

Start container

docker start masterdocker start slave 

5) log on to mysql on the master server and query the master status.
Enter the msql container

docker exec -it cdd15f7b189b /bin/bash

Log on to the msql Service

myql -u root -p

Query ip addresses on master msql

ip addr

show master status;

Record ip, File, Position

Run the command on the master database to create a data synchronization user.
SET SQL _mode = (select replace (@ SQL _mode, 'only _ FULL_GROUP_BY ',"));
GRANT REPLICATION SLAVE ON.To backup @ '%' identified by '123 ';

6) log on to mysql on the slave server and set configuration parameters related to the master server.

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));change master to master_host='172.17.0.2',master_user='backup',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=154;

Note:
Master_host is the address in the mysql container master and cannot be 127.0.0.1
Master_user is the user created in the master database.
Master_log_pos is the master database show master status; the Position queried

Start the slave Service

start slave;

View the Master/slave status on the slave Database

show slave status;

If Slave_IO_State is Waiting for master to send event, it is successful. If yes
Most of the connections from Connecting to master are disconnected. Check whether the configuration is correct. If the connection is ip, the File and Position are incorrectly configured.

7) Create a database and a table on the master database to check whether the slave database is synchronized.
View IP addresses on the host machine

Use the mysql client Navicat to connect the master database and slave database. The IP address is the IP address of the host machine. The port is mapped to 3306 and 3316 when the container is created. The user name is admin and the password is 123456, after logon, you can create databases and tables in the master database. After refreshing the slave database, you can see the databases and tables created in the master database.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.