- Windows 11 Enterprise Edition
- mysql8.0+
- docker
- vm virtual machine
1, Construction of Mysql master-slave replication
1. Pull the image
docker pull mysql
2, Configuration of MYSQL main service
Because I already have local mysql in 3306, the mysql port in the container is set to 33306
docker run --name mysql_master -p 33306:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql 0ad6b529bfbceda875b4af88788c1b6d4d29916095203755294d60cd922dda7a
| parameter | explain |
|---|---|
| –name | Specify a name for the container. Here is master |
| -p | Map the specified port of the container to the specified port of the host. Here, map the 3306 port of the container to the 3306 port of the host |
| -e | Set the environment variable, where the password of the specified root account is root |
| -d | Run the container in the background and return the container ID |
| mysql | Specify the version of mysql to run |
1. Query container operation status
As shown in the figure below, it indicates successful operation
docker ps -a
2. Use Navicat connection test
3. Create the replication account of the master database
3.1 creating users
//Create user create user 'slave'@'%' identified with mysql_native_password by 'Mslave#q818';
3.2 granting user rights
grant replication slave on *.* to 'slave'@'%'; //Click the authorization form after authorization flush privileges;
3.3 viewing user permissions
show grants for 'slave'@'%';
The following represents success
3.4 modify MySQL configuration file
Enter mysql master server
docker exec -it 8b71301f25ed /bin/bash //8b71301f25ed is the container ID docker exec -it mysql-master /bin/bash //MySQL master is the container name
Switch to / etc/mysql directory
cd /etc/mysql
Install vim
apt-get update apt-get install vim
Modify my CNF file, add the following two lines in [mysqld]
# vim /etc/my.cnf [mysqld] log-bin=mysql-bin server-id=1
Add description:
- Log bin = MySQL bin uses binary logging, and MySQL bin is the prefix of the log file name
- Server id = 1, unique server ID, non-zero integer, cannot be the same as the server ID of other servers
After the configuration is completed, you need to restart the MySQL service to make the modified configuration file take effect. Use the following command to restart mysql
service mysql restart
Finally, restart the service: docker start mysql_master
3, MYSQL slave service configuration
Set the MySQL service port to 33307 from the MySQL container
docker run --name mysql_slave -p 33307:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql
The same configuration as the main service:
server-id=2
Restart the service: docker restart mysql_slave
4, Configure MYSQL master-slave
After using Navicat to connect to the slave, create a new query and execute the following SQL
CHANGE MASTER TO MASTER_HOST='192.168.3.38', MASTER_PORT=33306, MASTER_USER='slave', MASTER_PASSWORD='Mslave#q818';
START SLAVE;
- MASTER_HOST: fill in the ip address in the Navicat connection configuration
- MASTER_PORT: port of the main container
- MASTER_USER: the user name of the synchronization account
- MASTER_PASSWORD: the password of the synchronization account
Since then, MYSQL master-slave configuration has been completed and all docker containers have been restarted
5, Master slave replication test
There are many ways to perform master-slave replication of mysql. Here we conduct a simple test.
1. Create a new library in Master
mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec)
At this time, the test library will be available in Slave:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec)
To create a table and insert data in the Master:
mysql> create table mytbl (id varchar(16),name varchar(32)); mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | mytbl | +----------------+ mysql> insert into mytbl values (1,'zhangsan'); Query OK, 1 row affected (0.01 sec) mysql> select * from mytbl; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | +------+----------+ 1 row in set (0.00 sec)
Check whether there is data for synchronization in Slave:
mysql> use test; 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> show tables; +----------------+ | Tables_in_test | +----------------+ | mytbl | +----------------+ 1 row in set (0.00 sec) mysql> select * from mytbl; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | +------+----------+ 1 row in set (0.00 sec)
6, Other operations of master and slave
How to stop copying from a service
stop slave
How to reconfigure master-slave
stop slave; reset master;