The same server uses Docker to simulate the master-slave synchronization settings of Mysql settings.
1. Run two Mysql: Master Mysql (master library) and slave Mysql (slave Library), database version 5.7.
#Pull image docker pull mysql:5.7 #y run master and slave containers docker run -p 13306:3306 --name slavemysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 docker run -p 13307:3306 --name mastermysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
After running, you can try to connect. Note that the service ports mapped to the host here are 13306 and 13307 respectively. For external connection, you need to ensure that the firewall and gateway settings are released.
2. View the used IP of the container (configuring the master and slave requires the use of IP addresses in the same intranet)
#View the IP address of the primary Library
docker inspect --format='{{.NetworkSettings.IPAddress}}' masterMysql
#172.18.0.2
3. Configure master-slave libraries.
(1) Enter the main library
docker exec -it masterMysql bash #masterMysql is the name of the run container.
(2) Configure configuration file
cd /etc/mysql&&ls
As you can see, multiple * cnf files, including in conf.d and mysql.com Conf.d folder. There is a loading order. The configuration here is all in my Operate on cnf.
(3) Set profile
Mode 1:
The normal operation is to enter the folder for editing, as follows:
vim ./my.cnf However, errors will be reported here: bash: vi: command not found
Pre installation vi
apt-get update #Run after success apt-get install vim
Mode 2 (suggestion):
After editing outside the container, copy it to the container. Here is the direct overwrite method.
Copy the host into the container. Syntax: docker cp [Host address] [container ID Or container name]:[Container file address] docker cp /home/mysql/my.cnf mysql:/etc/mysql/my.cnf
(4) Open Binlog configuration.
The same configuration: enter the master and slave libraries to set my CNF configuration file. Note that the server ID needs to be inconsistent.
character_set_server=utf8 init_connect='SET NAMES utf8' #These two are to set the utf-8 character format, and the configuration of the two hosts is the same ## Pay attention to uniqueness in the same LAN (if the two-way active and standby databases depend on this to distinguish the SQL statements executing Binlog) #You can take the last one of Ip. The master library is 2 and the slave library is 3. server-id=2 ## Enable the binary log function, and you can take any (key) #The demo sets the master library to master bin and the slave library to slave bin log-bin=master-bin log_bin_index = master-bin.index
Different configurations: Main Library:
#The configurable contents are sorted out here. Due to the test, comments are made first. #The library to be synchronized to the slave (if it is not written, all will be synchronized by default) #binlog-do-db=test #Libraries that are not synchronized to the slave (multiple writes and multiple lines) #binlog-ignore-db=mysql #Set replicated database #binlog-ignore-db=information_schema #Set ignore replicated databases #Automatically clean up log files 15 days ago expire_logs_days=15 #binlog_format=row #Set the SQL of the actual operation of Binlog records. #max_binlog_size=100m #set file size #replicate_do_table=test #Data table for replication #replicate_ignore_table=igoreTest #Ignore data tables for replication #replicate_wild_ignore_db=test # Same as Replicate_Do_DB can be wildcard #replicate_wild_ignore_db=igoreTest # Same as Replicate_Ignore_DB can be wildcard
From library:
#Set to avoid the master-slave database replication error caused by untimely update or restart. read_only = 1 master_info_repository=TABLE relay_log_info_repository=TABLE relay-log = slave-relay-bin #Log storage of main library. relay-log-index = slave-relay-bin.index
The configuration takes effect after restarting the container.
docker restart masterMysql docker restart slaveMysql
To view the container's log:
docker logs masterMysql
If the restart fails, you can use this operation to check whether there is an error in the configuration file just now. Use mode 2 (3) to modify the configuration file and then start the container.
(5) Check whether the bin log configuration is set successfully.
#Enter container docker exec -it masterMysql bash #Enter Mysql mysql -uroot -p123456 #View Binlog status. show variables like '%log_bin%'; #You can see the log_bin is the open state, bin_log is located in / var / lib / MySQL / Master bin 
(6) View the Binlog status of the main library's Matser node and get the position value configured here.
show master status;
If the previously opened Binlog is not used, it needs to be newly generated or reset.
#Generate a new Binlog log file flush logs; #Reset empty Binlog log file reset master;
The log configuration file you can view here is master bin 00000 1, Position 334.
4. The master database sets an account to synchronize data from the slave database. The account here is slaveMysql.
CREATE USER 'slaveMysql'@'%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slaveMysql'@'%';
5. Log in and execute Mysql from the library. It is recommended to connect to Navicat to run and view. The viewing format on the server is very messy.
(1) Perform master library configuration for synchronization. Note that here is the ending symbol.
change master to master_host='172.18.0.2', master_user='slaveMysql', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 334, master_connect_retry=30;
Parameter details:
change master to master_host=${Container used IP} Here, it needs to be the second income in the same LAN.
master_port Master The port number of the container refers to the port number of the running database of the container, not the port number mapped to the host. It is obtained in step 1.
master_user Set the account used for synchronization, as shown in Figure 4
master_password Set the account password used for synchronization, as shown in Figure 4
master_log_file Specifies which configuration file to read from the main library Binlog Log, from section 3 (6)
master_log_pos From which Position Start reading, as mentioned above Position The value of the field, obtained in Section 3 (6)
master_connect_retry If the connection fails, the time interval between retries. The unit is seconds. The default is 60 seconds
(2) Open the configuration from the library.
start slave; #Stop the slave. #stop slave;
(3) View the status of master-slave synchronization. Check the status of master-slave synchronization.
show slave status ;
(4) Check for errors.
You can view last by_ Io_ Error view the error of the connection. Check the following possible errors. 1> The network is blocked, the port and LAN IP 2 > the account password used for synchronization is normal 3 > the Binlog file name and Pos location of the master are wrong.
6. Test whether the master-slave is normal.
Create a database from the Master library and check whether the slave library has been successfully created. This is the end.
