1, Pull MySQL image

Get the latest MySQL image through the terminal

docker pull mysql/mysql-server

2, Create the directory corresponding to the MySQL database container configuration file

We create a group of directories under the current user to store MySQL container configuration files (this step can be omitted under Linux). Refer to the following figure:

Note: after MySQL 8, MySQL files should be added to the mapping file, otherwise the creation of MySQL database container will fail.

Because vi/vim is not supported under MacOS to directly modify my CNF file, and apt get installation of vim is not supported, so you need to create two my CNF mapping file. (under Linux, you can modify the configuration file directly through VIM)

My. Corresponding to master master database The CNF configuration file is:

[mysqld]
server_id = 1
log-bin= mysql-bin
read-only=0
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

slave from the library corresponding to my The CNF configuration file is:

[mysqld]
server_id = 2
log-bin= mysql-bin
read-only=1
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

3, Create two MySQL database containers

Create master master database container

docker run --name mysql-master -d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /Users/yumaster/test/mysql_master_slave/master/data:/var/lib/mysql -v /Users/yumaster/test/mysql_master_slave/master/conf/my.cnf:/etc/mysql/my.cnf -v /Users/yumaster/test/mysql_master_slave/master/mysql-files:/var/lib/mysql-files mysql/mysql-server

Create slave from database container

docker run --name mysql-slave -d -p 3308:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /Users/yumaster/test/mysql_master_slave/slave/data:/var/lib/mysql -v /Users/yumaster/test/mysql_master_slave/slave/conf/my.cnf:/etc/mysql/my.cnf -v /Users/yumaster/test/mysql_master_slave/slave/mysql-files:/var/lib/mysql-files mysql/mysql-server

The following figure shows that the two MySQL containers were created successfully

 

At this point, when we open the Docker dashboard, we can see that the two containers have been running. And the port is the corresponding port we created before

We will report a 1130 error through Navicat connection because the connected user account does not have the permission to connect remotely. You need to change the host entry in the user table in the mysql database

Change localhost to%

Specific steps:

mysql> use mysql
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 host from user where user='root';
+-----------+
| host      |
+-----------+
| localhost |
+-----------+
1 row in set (0.01 sec)

mysql> update user set host='%' where user = 'root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select host from user where user='root';
+------+
| host |
+------+
| %    |
+------+
1 row in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

4, Master slave database configuration

Master master database configuration:

//get into master Master data container
docker exec -it mysql-master mysql -uroot -p123456
//Create a user to synchronize data, each slave Use standard MySQL User name and password connection master. The user who performs the copy operation grants REPLICATION SLAVE jurisdiction. mysql8 In previous versions, the encryption rule was mysql_native_password,And in mysql8 after,The encryption rule is caching_sha2_password
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';(It's possible that slave Create and master Connection error)
or
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
//Authorize users
GRANT REPLICATION SLAVE ON *.* to 'slave'@'%';
//Check the status and remember File,Position Value of, in Slave Will be used
show master status;
//query master Container IP，Will be slave Used when setting up the main library connection
docker inspect monemysql | grep IPA;

mster status, file MySQL bin 000003  Position 661

 

slave Slave database configuration:

//get into slave From data container
docker exec -it mysql-slave mysql -uroot -p123456
//Set main library link change master to
change master to master_host='172.17.0.2',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=661,master_port=3306;
//Start synchronization from library
start slave;
//View status
show slave status\G;
//If show slave status\G The following appears in the command result:
//Slave_IO_Running: Yes
//Slave_SQL_Running: Yes
//Both of the above are Yes，That means there's no problem.
//Otherwise, reconfigure the data
stop slave;
reset slave all;

Start slave library synchronization succeeded

5, Master-slave verification

We create a database on the master, then create a table, and then insert a piece of data, and the corresponding slave will increase;

create database master_slave_demo;
use master_slave_demo;
create table userinfo(username varchar(50),age int);
insert into userinfo values('Toulon',25);
select * from userinfo;

Before executing the command, the number of master-slave databases is the same;

After the master executes the command, the slave adds the corresponding data

It can be found that the new data in the master database has been synchronized, and the master-slave replication of MySQL has been set. (test environment, MacOS M1, arm64 machine, Docker, MySQL 8.0.27)