How to create a MySQL master-slave database using Docker on MacOS

1. Pull the 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 set of directories under the current user to store the MySQL container configuration files (this step can be omitted under Linux). Refer to the following figure:

Note: For MySQL version 8 and later, you need to add mysql-files to the mapping file, otherwise the MySQL database container will fail to be created.

Because MacOS does not support vi/vim to directly modify the my.cnf file, nor does it support apt-get to install vim, you need to create two new my.cnf mapping files locally. (Under Linux, you can directly modify the configuration file through vim)

The my.cnf configuration file corresponding to the master database 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

The my.cnf configuration file corresponding to the slave library 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 the 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 a slave 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

As shown in the following figure, two MySQL containers were created successfully.

At this point we open the Docker dashboard and can see that the two containers are already running. And the port is the corresponding port we created before

When we connect through Navicat, we will get an error 1130 because the connected user account does not have permission to connect remotely. You need to change the host item 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 database configuration:

//Enter the master data container docker exec -it mysql-master mysql -uroot -p123456
//Create a user to synchronize data. Each slave uses a standard MySQL username and password to connect to the master. The user who performs replication operations is granted the REPLICATION SLAVE privilege. The encryption rule in versions before mysql8 is mysql_native_password, and after mysql8, the encryption rule is caching_sha2_password
CREATE USER 'slave'@'%' IDENTIFIED BY '123456'; (This may cause an error when the slave creates a connection with the master)
or CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
//Authorize the user GRANT REPLICATION SLAVE ON *.* to 'slave'@'%';
// Check the status and remember the values ​​of File and Position. Show master status will be used in Slave;
//Query the IP of the master container, which will be used when the slave sets up the master library connection docker inspect mysql-master | grep IPA;

Status of master, File mysql-bin.000003 Position 661

Slave slave database configuration:

//Enter the slave data container docker exec -it mysql-slave mysql -uroot -p123456
//Set the master 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 slave synchronization start slave;
//Check the status show slave status\G;
//If the show slave status\G command result shows:
//Slave_IO_Running: Yes
//Slave_SQL_Running: Yes
//If both of the above two items are Yes, then there is no problem.
//Otherwise, reconfigure the slave data stop slave;
reset slave all;

Start slave synchronization successfully

5. Master-slave verification

We create a database on the master, then create a table, and insert a piece of data, and the corresponding slave will also 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 and slave databases is the same;

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

It can be found that the newly added data in the main database has been synchronized, and the master-slave replication of MySQL has been set up. (Test environment, MacOS M1 ARM64 machine, Docker, MySQL 8.0.27)

This is the end of this article about using Docker to create a MySQL master-slave database on MacOS. For more information about using Docker to create a MySQL master-slave database, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!