This article uses Docker to achieve mysql master/slave synchronization/read-write separation, of course, you can also open two virtual machines to build, the method is the same. It’s just easy to compare prices with Docker.
In order to ensure data integrity and security, mysql design master/slave synchronization, one failure can be used by the other. Reconfiguration forum recently, want to change master from it. If you’re worried about making mistakes, you practice with a Docker. If you have any mistakes or incomprehension, please contact me at [email protected].
You can actually do it through this article.
- Primary/secondary synchronization of mysql
- Docker image and container creation
- Data transfer between Docker containers
- Introduction to mycat
The following requires an IQ greater than 100 and mysql foundation Docker foundation, Linux foundation.
No, I’m fine. It’s just a little hard to watch.
The environment is Ubuntu16.04, mycat is 1.6. Docker is 1.12.6
Download the mysql image.
Docker pull mysql:5.7 #ps: if you do not need to create an update file, enter sudo vim /etc/docker/daemon.json to create a new update file. [" https://pcpvmdkk.mirror.aliyuncs.com "]} # : wq exit sudo systemctl daemon - reload sudo systemctl restart docker Of course you can also use tencent's source [anyway, now all the big bosses in the server, the source can be used] {" registry - mirrors ": /" https://mirror.ccs.tencentyun.com "}Copy the codeDocker images | docker images | docker images | You can interpret it as Ghos in Windows when you reinstall the system and docker after you install the systemCopy the code
This image indicates that you have downloaded the image file for mysql :5.7
So let’s start with our mysql image file ps: create two new images for master/slave synchronization.
The server we’re currently on is called the host server,
We want to use Docker virtual two Docker container servers, a master server, a slave server.
Create a folder of your own. For example, if I use virtual machine as the environment, /home/usr/mysql.master /home/usr/mysql.slave under/MNT/HGFSCopy the codeCreate a Dockerfile in the master and slave folders
CNF /etc/mysql/ EXPOSE 3306 CMD ["mysqld"]Copy the codeCreate my.cnf in the master folder
[mysqld]
log-bin=mysql-bin
server-id=1
Copy the codeCreate my.cnf in the slave folder
[mysqld]
log-bin=mysql-bin
server-id=2
Copy the codeThen we go under the folder to create the image
docker build -t master/mysql .
docker build -t slave/mysql .
Copy the code
Just look at the top two
Create containers with images
docker run -p 3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=mysql -d master/mysql docker run -p 3306 --name Mysql -slave -e MYSQL_ROOT_PASSWORD=mysql -d slave/mysql # -e MYSQL_ROOT_PASSWORD= database password docker ps -aCopy the code
Two containers are created
From here, it is recommended to open two terminal Windows for easy operation.
Master execution
Docker exec it mysql-master bash mysql -uroot -pCopy the codeSlave Terminal Execution
Docker exec it mysql-slave bash mysql -uroot -p enter the password mysql to enter the mysql environmentCopy the codeMysql > configure primary/secondary mysql
GRANT REPLICATION SLAVE ON *.* TO 'user'@'192.168.99.100' IDENTIFIED BY 'mysql'; GRANT REPLICATION SLAVE ON *.* to 'user'@'%' identified by 'mysql'; (All IP addresses)Copy the codeThen check the primary container database state:
show master status;
Copy the code
Record the value of File and Position.
The next thing I’m going to do is, at this point, the main library should never do anything else to prevent state changes.
Check the master/mysql external port number
0.0.0.0:32785-> 3306/TCP, where 32785 is the master port
mysql>change master to master_host='x.x.x.x', master_user='user', master_log_file='mysql-bin.000003', master_log_pos=437, master_port=32785, master_password='mysql'; Query OK, 0 rows affected, 2 warnings (0.03 SEC) mysql> start slave; Query OK, 0 rows affected (0.01sec)Copy the codeMaster_log_file = ‘mysql-bin.000003’, Master_log_pos =1338 and Position =1338. mysql> start slave; // Start the slave replication function if the configuration is incorrect, enter mysql> stop slave; Then type it again
As shown in figure
Check the primary/secondary connection status
show slave status\G
Copy the codeThe two must be either Yes for No or connect to indicate No connection
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Testing the master/slave connection Notice After the master/slave connection is configured, operations can only be performed on the master terminal. Operations on the slave terminal are not synchronized to the master terminal. Log in to the master terminal,
mysql> create database sunhao; Query OK, 1 row affected (0.01sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | Sunhao | | sys | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 5 rows in the set (0.00 SEC) mysql > use sunhao Database changed mysql > create table sunhao(id int(3),name char(10)); Query OK, 0 rows affected (0.04 SEC) mysql> insert into SUNhao values(1,'xiaoshuai'); Query OK, 1 row affected (0.01sec) mysql> select * from sunhao; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | 1 | xiaoshuai | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the codeLog in to the Slave server
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | Sunhao | | sys | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 5 rows in the set (0.00 SEC) mysql > use sunhao Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from sunhao; +------+-----------+ | id | name | +------+-----------+ | 1 | xiaoshuai | +------+-----------+Copy the codeIf application data already exists on the primary server, the replication process must be performed separately. (Note that this operation will overwrite the data entered separately on the secondary server.) :
Let’s do a full test here
(1) Insert new data into the database of the master server and lock the table to prevent data from being written
mysql> insert into sunhao values(2,'xiaoshuai'); Query OK, 1 row affected (0.01sec) mysql> select * from sunhao; +------+-----------+ | id | name | +------+-----------+ | 1 | xiaoshuai | | 2 | xiaoshuai | +------+-----------+ 2 rows In set (0.00 SEC) mysql> FLUSH TABLES WITH READ LOCK; mysql> show full processlist;Copy the codeDump backup files to /var/lib with mysqldump, and tar them with tar. Ha ha.
mysql> exit
Bye
root@980e5ea48152:/var/lib/mysql# mysqldump -u root -p sunhao > /var/lib/sunhao.dump
Enter password:
root@980e5ea48152:/var/lib# tar -zcvf ./sunhao.dump .tar ./sunhao.dump
Copy the codeOpen the host server and copy the mysql master server file sunhao.dump.tar. To host server
[root]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 8d1e3b87d499 slave/mysql "docker-entrypoint.sh" 4 hours ago Up 4 hours 0.0.0.0:32769->3306/ TCP mysql-slave 980e5ea48152 master/mysql "docker-entrypoint.sh" 4 hours ago Up to 4 hours 0.0.0.0:32768-3306 / TCP mysql - > master mkdir -p/var/mydata docker cp 980 e: / var/lib/sunhao. Dump. The tar /var/mydata/ cd /var/mydata [root@VM_128_230_centos mydata]# ls sunhao.dump.tarCopy the code