Use Docker to build a “one master and one slave” MySQL read-write separation cluster (super detailed steps)

Directory

      • 1. Premise
      • 2. MySQL production installation
        • 1. Pull mysql
        • 2. View the mysql image
        • 3. Start the mysql container
        • 4. Modify the Chinese encoding of mysql
        • 5. View and verify the Chinese encoding of mysql
      • 3. Installation and configuration of Mysql host mysql_master
        • 1. Copy the master container
        • 2. Start the master container
        • 3. Create new my.cnf
        • 3. Restart the master container
        • 4. Enter the container to connect to mysql
        • 5. Create user
        • 6. Authorized users
      • 4. Installation and configuration of Mysql slave mysql_slave
        • 1. Copy the slave container
        • 2. Start the slave container
        • 3. Create new my.cnf
        • 3. Restart the slave container
        • 4. Enter the container to connect to mysql
      • 5. Configure master-slave replication
        • 1. Check master status
        • 2. slave specifies master
        • 3. Check slave status
        • 4. slave starts synchronization
      • 6. Test
        • 1. Write in master
        • 2. Read in slave
        • 3. Use navicat to connect the master-slave database

1. Premise

VMware Workstation installed
Install and create docker virtual machine
XShellTools


2. MySQL production installation

In order to ensure data security, the mysql container installed in the production environment will use data volumes to persist data when it is started.

1, pull mysql
 docker pull mysql:5.7

2, view mysql mirror
docker images

3. Start the mysql container
docker run --name mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /root/mysql/data:/var/lib/mysql \
-v /root/mysql/log:/var/log/mysql \
-v /root/mysql/conf:/etc/mysql/conf.d \
-dp 3306:3306 \
mysql:5.7

Three data volumes are specified here:

  • /root/mysql/log:/var/log/mysql
  • /root/mysql/data:/var/lib/mysql
  • /root/mysql/conf:/etc/mysql/conf.d
4, modify the Chinese encoding of mysql

Create a new my.cnf file in the /root/mysql/conf directory (data volume directory) of the host machine and type the following content in it:
[client]
default_character_set=utf8
[mysql]
default_character_set=utf8
[mysqld]
character_set_server=utf8

5, check and verify the Chinese encoding of mysql

After modifying the configuration file, restart the mysql image:

docker restart mysql

Enter mysql:

docker exec -it mysql /bin/bash
mysql -uroot -p

View the formats supported by strings in the database:

show variables like 'character%';

3. Installation and configuration of Mysql host mysql_master

1, copy master container

Copy a copy of the mysql production image created in the previous step as the mysql-master container:

copy :

cp -r mysql mysql_master

Delete the data and log directories:

rm -rf data/ log/
2. Start the master container
docker run --name mysql_master \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /root/mysql_master/data:/var/lib/mysql \
-v /root/mysql_master/log:/var/log/mysql \
-v /root/mysql_master/conf:/etc/mysql/conf.d \
-dp 3316:3306 \
mysql:5.7
3, create new my.cnf

Create a new my.cnf file in the /root/mysql_master/conf directory of the host machine and type the following content in it:

[client]
default_character_set=utf8
[mysql]
default_character_set=utf8
[mysqld]
character_set_server=utf8
server_id=01
binlog-ignore-db=mysql
log-bin=master-log-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
3, restart the master container

Since the mysql configuration has been modified, the master container needs to be restarted for the new configuration to take effect.

docker restart mysql_master

4, enter the container connection mysql

After entering the container and connecting to mysql, check its character encoding and you can see that it supports Chinese.

5, create user

Create a user for the current MySQL:

create user 'slave'@'%' identified by '123456';

6, authorized users

Authorize the newly created user:

grant replication slave,replication client on *.* to 'slave'@'%';

4. Installation and configuration of Mysql slave mysql_slave

Open another session window

1, copy slave container

Copy a copy of the mysql production image created in the previous step as the mysql-slave container:

copy :

cp -r mysql mysql_slave

Delete the data and log directories:

rm -rf data/ log/
2. Start the slave container
docker run --name mysql_slave \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /root/mysql_slave/data:/var/lib/mysql \
-v /root/mysql_slave/log:/var/log/mysql \
-v /root/mysql_slave/conf:/etc/mysql/conf.d \
-dp 3326:3306 \
mysql:5.7
3, create new my.cnf

Create a new my.cnf file in the /root/mysql_slave/conf directory of the host machine and type the following content in it:

[client]
default_character_set=utf8
[mysql]
default_character_set=utf8
[mysqld]
character_set_server=utf8

server_id=02
binlog-ignore-db=mysql
log-bin=slave-log-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=relay-log-bin
log_slave_updates=1
read_only=1
3, restart the slave container

Since the mysql configuration has been modified, the slave container needs to be restarted for the new configuration to take effect.

docker restart mysql_slave
4, enter the container connection mysql

After entering the container and connecting to mysql, check its character encoding and you can see that it supports Chinese.

show grants for 'slave'@'%';
docker exec -it mysql /bin/bash
mysql -uroot -p

5. Configure master-slave replication

1, check master status

Run the show master status command on the master to view the binary log file name and where to start.

show master status;

2, slave specifies master

In the slave, run the change master to command to specify the master information to be connected to.

First check the ip:

ifconfig


Then, enter the following command to configure:

change master to master_host='192.168.162.105' , master_user='slave' ,master_password='123456' ,master_port=3316 ,master_log_file='master-log-bin.000002' ,master_log_pos=154 ,master_connect_retry=30 , master_retry_count=3;
3, check slave status

Checking the slave status in the slave shows that the current synchronous replication between the slave and the master has not started yet.

show slave status \G;

4, slave starts synchronization

Use the start slave command in the slave to enable data synchronization of the slave.

 start slave

At this time, check the status of the slave again and find that synchronization has started.


Note
If found Slave_SQL_Running: No
reason:
1. The program may have performed write operations on the slave
2. It may also be caused by transaction rollback after the slave machine is restarted.

Usually caused by transaction rollback:
Solution:

mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;

6. Test

At this point, the read-write separation cluster with one master and one slave is completed. Next, create a database and table in the master. If they can be viewed in the slave, it means the construction is successful.

1, write in master

2, read in slave

The data written in the master can be viewed in the slave, indicating that the cluster is successfully established.

3, use navicat to connect the master-slave database


Master-slave data synchronization changes! ! ! Done! ! ! !