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

This article introduces the knowledge of "mysql5.7 master-slave configuration based on docker". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Establish the master library to prepare the configuration file of the master library

The mysql5.7 database configuration file under docker exists under multiple files, the entry file is / etc/mysql/my.cnf, and the other files are entered through include.

In order to view the configuration file more intuitively, the configuration file of the main library is placed in / etc/mysql/mysql.conf.d/master.cnf

# vi $PWD/ master.cnf [mysqld] server-id=100log-bin=mysql-bin# vi $PWD/master.sqlCREATE USER 'slave'@'%' IDENTIFIED BY' 123456 prepare the storage directory of Grant REPLICATION SLAVE, REPLICATION CLIENT ON *. * TO 'slave'@'%'; to prepare the main library (may be omitted)

It is not difficult to understand that every piece of data recorded in the mysql database is eventually stored in a few files on the hard disk in the form of files.

In the mysql5.7 environment in the docker image, those important data files are stored in the / var/lib/mysql directory, of course, in the container environment.

Therefore, it is recommended that these files be mapped from the inside of the container to the outside of the container, so that they can be backed up and saved more easily. In this column, I map the / var/lib/mysql inside the container to the master/db directory of my host. This directory does not need to be established. As long as the path is specified, docker will help us set up the ps: your host can use the network disk path, so that the files of the database actually exist at the remote end.

Use docker to run mysql main library docker run-p 5001docker run 3306\-name mymaster\-v $PWD/master.cnf:/etc/mysql/mysql.conf.d/master.cnf\-v $PWD/masterdb:/var/lib/mysql\-v $PWD/master.sql:/docker-entrypoint-initdb.d/master.sql\-e MYSQL_ROOT_PASSWORD=111111\-e MYSQL_DATABASE=waynedb\-e MYSQL_USER=wayne\-e MYSQL_PASSWORD=wayne\-d mysql:5.7

Explain line by line:

Map port 3306 in docker to port 5001 outside

Map the external configuration file to the internal

Map the database file to an external directory (can be omitted)

Set root password

Set up an initial database (which can be omitted)

Set the user, who has full permissions on the above initial database by default (can be omitted)

Set the above user password (can be omitted)

Use image for version 5.7 of mysql

Configure the main database to enter into the main database

Use powerful docker to simulate a mysql client, directly connect to the running master library, and fill in the real master library ip and port number

Docker run-- rm-it-- name mysql-client mysql:5.7 mysql- h20.2.11.97-- port=5001-uroot-p configure synchronization account and view synchronization information (obsolete)

After entering, create an account slave for synchronization with a password of 123456 and grant synchronization permission

CREATE USER 'slave'@'%' IDENTIFIED BY' 123456 victory Grant REPLICATION SLAVE, REPLICATION CLIENT ON *. * TO 'slave'@'%'

Then query the status to prepare for synchronization from the library

Show master status\ G Establish the slave library prepare to enable the binary log function from the configuration file # vi $PWD/ slave.cnf [mysqld] server-id=101## Use log-bin=mysql-slave-bin## relay_log to configure relay log relay _ log=edu-mysql-relay-bin when using Slave as Master for other Slave. Use docker to run mysql slave library docker run-p 5002Master 3306\-name myslave\-v $PWD/slave.cnf:/etc/mysql/mysql.conf.d/slave.cnf\-v $PWD/slavedb:/var/lib/mysql\-v $PWD/a.sql:/docker-entrypoint-initdb.d/a.sql\-e MYSQL_ROOT_PASSWORD=111111\-e MYSQL_DATABASE=waynedb\-e MYSQL_USER=wayne\-e MYSQL_PASSWORD=wayne\-d mysql:5.7 configuration from the library to the internal database of the library

Use powerful docker to simulate a mysql client, directly connect to the running master library, and fill in the real slave library ip and port number

Docker run-- rm-it-- name mysql-client mysql:5.7 mysql- h20.2.11.97-- port=5002-uroot-p configuration synchronization

Configure the slave library to connect to the main library

Please fill in the real main library ip and port number.

Please note to fill in the real master_log_file and master_log_pos, which is obtained from the main library using show master status\ G;

Mysql > change master to master_host='10.2.11.97', master_user='slave', master_password='123456', master_port=5001, master_log_file='mysql-bin.000001', master_log_pos= 2830, master_connect_retry=30 Explanation: master_host: the address of Master, which refers to the independent ip of the container. You can use docker inspect-- format=' {{.NetworkSettings.IPAddress}} 'container name | Container id queries the port number of container ipmaster_port:Master, which refers to the container port number master_user: user for data synchronization master_password: user password for synchronization master_log_file: specify the log file from which Slave starts copying data That is, the value master_log_pos of the File field mentioned above: which Position to start reading, that is, the value master_connect_retry of the Position field mentioned above: if the connection fails, the retry interval is in seconds. The default is 60 seconds to start the copy function start slave from. Use docker's mysql to act as a client

Use docker's mysql as the client, exit (--rm) and enter interactive mode (- it) after one run. Boldface is the mysql connection command docker run-- rm-it-- name mysql-client mysql:5.7 mysql- h227.0.0.1-- port=3339-uroot-p

One-click script

This script needs a docker-based environment. Please configure docker first.

Just modify the #-- the part above

The real ipmaster_port= main library of the host where the master_ip= master library is located the listening port of the host master_MYSQL_ROOT_PASSWORD= account password of the master_docker_name= main library the name of the master_docker_name= main library under docker the data file of the master_root_path= main library is mapped to the host directory script as follows: #! / bin/bash# master_ip and slave_ip if the master_port and slave_port cannot be the same Master_docker_name and slave_docker_name cannot be the same # before setting the port Note whether the port is occupied # master_ip and slave_ip should be able to access each other # master library related settings master_ip=10.2.11.97master_port=5010master_MYSQL_ROOT_PASSWORD=111111master_docker_name=mymaster_newmaster_root_path=/home/wayne/newnew# slave library related settings slave_ip=10.2.11.97slave_port=5011slave_MYSQL_ROOT_PASSWORD=111111slave_docker_name=myslave_newslave_root_path=$master_root_path# slave library use the following users to connect to the master library Step sync_user=slavesync_pass=123456#---reset () {docker rm-f $master_docker_name docker rm-f $slave_docker_name} master () {if [!-d $master_root_path] Then mkdir-p $master_root_path fi cd $master_root_path cat master.sqlCREATE USER'${sync_user}'@% 'IDENTIFIED BY' ${sync_pass}'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *. * TO'${sync_user}'@'%' EOF cat master.cnf [mysqld] server-id=100log-bin=mysql-binEOF docker run-p ${master_port}: 3306\-- name ${master_docker_name}\-v $PWD/master.cnf:/etc/mysql/mysql.conf.d/master.cnf\-v $PWD/masterdb:/var/lib/mysql\-v $PWD/master.sql:/docker-entrypoint- Initdb.d/master.sql\-e MYSQL_ROOT_PASSWORD=$ {master_MYSQL_ROOT_PASSWORD}\-e MYSQL_DATABASE=waynedb\-e MYSQL_USER=wayne\-e MYSQL_PASSWORD=wayne\-d mysql:5.7# rm master.sql# rm master.cnf} slave () {item1 file= `docker run-- rm-it-- name mysql-client mysql:5.7 mysql -h ${master_ip}-- port=$ {master_port}-uroot-p$ {master_MYSQL_ROOT_PASSWORD}-e "show master status\ G "2 > / dev/null | grep File | cut-f2-d: | awk'$1clients while [- z $file] do echo $i times sleep 5 sleep 5 I = `expr ${I} + 1`if [$I-gt 10] Then echo "failed to connect to the master library, but the slave library did not establish and exit" exit 0 fi file= `docker run-- rm-it-- name mysql-client mysql:5.7 mysql- h ${master_ip}-- port=$ {master_port}-uroot-p$ {master_MYSQL_ROOT_PASSWORD}-e "show master status\ G" "2 > / dev/null | grep File | cut-f2-d: | awk'$1clients 1``done pos= `docker run-- rm-it-- name mysql-client mysql:5.7 mysql- h ${master_ip}-- port=$ {master_port}-uroot-p$ {master_MYSQL_ROOT_PASSWORD}-e" show master status\ G; "2 > / dev/null | grep Pos | cut-f2-d: | awk'$1clients 1' `echo $pos if [!-d $slave_root_path] Then mkdir-p $slave_root_path fi cd $slave_root_path cat slavetmp.sqlstop slave;change master to master_host='$ {master_ip}', master_user='$ {sync_user}', master_password='$ {sync_pass}', master_port=$ {master_port}, master_log_file='$file', master_log_pos= $pos, master_connect_retry=30;start slave EOF cat-v slavetmp.sql | tr-d "^ M" > slave.sql cat slave.cnf [mysqld] server-id=101## enable binary log function Use log-bin=mysql-slave-bin## relay_log to configure relay log relay _ log=edu-mysql-relay-binEOF docker run-p ${slave_port}: 3306\-- name ${slave_docker_name}\-v $PWD/slave.cnf:/etc/mysql/mysql.conf.d/slave.cnf\-v $PWD/slavedb:/var/lib/mysql\ when Slave is used as the Master for other Slave -v $PWD/slave.sql:/docker-entrypoint-initdb.d/slave.sql\-e MYSQL_ROOT_PASSWORD=$ {slave_MYSQL_ROOT_PASSWORD}\-e MYSQL_DATABASE=waynedb\-e MYSQL_USER=wayne\-e MYSQL_PASSWORD=wayne\-d mysql:5.7# rm slave.sql# rm slavetmp.sql# rm slave.cnf} masterslave#reset "mysql5 based on docker. 7 Master-Slave configuration "ends here. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.