Table of Contents
- Docker installs MySQL cluster (one master and one slave)
- Appetizer test
- MySQL cluster installation
-
- master container
- slave container
- master container
- Configure master-slave assignment
- test
docker installs MySQL cluster (one master and one slave)
Appetizer test
1. Pull the image of mysql5.7 to the local
[root@aliyun ~]# docker pull mysql:5.7
2. Start the mysql container and specify the password as 123456
[root@aliyun ~]# docker run --name mysql -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
3. Enter the mysql container
[root@aliyun ~]# docker exec -it mysql /bin/bash root@d9b75dcb7dae:/#
4. Enter password to access
root@d9b75dcb7dae:/# mysql -u root -p
MySQL cluster installation
Use Docker to build a “one master and one slave” MySQL read-write separation cluster.
master container
1. Start the master container and mount it with a volume to prevent data loss
[root@aliyun ~]# 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 \ -d -p 3316:3306 mysql:5.7
2. Create a new my.cnf file in the /root/mysql_master/conf directory of the host machine, and type the following content in it
# Set character set encoding [client] default_character_set=utf8 [mysql] default_character_set=utf8 [mysqld] character_set_server=utf8 server_id=01 # Specify the unique ID of mysql binlog-ignore-db=mysql # Ignore the operation records of the database named "mysql" in the binary log log-bin=master-log-bin # Enable binary log and specify the name of the binary log file as "master-log-bin" binlog_cache_size=1M # The size of the binary log cache is 1MB binlog_format=mixed # Set the binary log format to "mixed" expire_logs_days=7 # Set the number of days for binary log files to expire to 7 slave_skip_errors=1062 # Set when the slave server encounters an error code 1062 when performing replication, skip the error and continue to replicate
3. Restart the master container
[root@aliyun conf]# docker restart mysql_master
4. Enter the container to connect to mysql and check its character code
[root@aliyun conf]# docker exec -it mysql_master /bin/bash root@d61cb0fa6c50:/# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.36-log MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like 'character%'; + -------------------------- + ---------------------- ------+ | Variable_name | Value | + -------------------------- + ---------------------- ------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | + -------------------------- + ---------------------- ------+ 8 rows in set (0.00 sec)
5. Create a user for the current mysql, allowing to connect to the mysql server from any host (%’), and specify a password
mysql> create user 'slave'@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec)
6. Authorize, grant the user ‘slave’ permission related to replication.
mysql> grant replication slave, replication client on *.* to 'slave'@'%'; Query OK, 0 rows affected (0.00 sec)
The replication slave
permission allows the user to connect to the master server as a slave server and perform replication operations.
The replication client
permission allows users to view replication-related information.
*.*
means all libraries and tables
slave container
master container
1. Start the master container and mount it with a volume to prevent data loss
[root@aliyun ~]# 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 -d -p 3326:3306 mysql:5.7
2. 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=02 # Specify the unique ID of mysql 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 # Specify the name prefix of the relay log file. The relay log file is used to record the replication operations performed on the slave server, so that other slave servers can perform cascading replication by reading the relay log. log_slave_updates=1 # Enable the function of logging replication operations from the server to its own binary log read_only=1 # set the server to read-only mode
3. Restart the slave container
[root@aliyun conf]# docker restart mysql_slave
4. Enter the container to connect to mysql and check its character code
[root@aliyun conf]# docker exec -it mysql_slave /bin/bash root@72c2eed0b7ca:/# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.36-log MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like 'character%'; + -------------------------- + ---------------------- ------+ | Variable_name | Value | + -------------------------- + ---------------------- ------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | + -------------------------- + ---------------------- ------+ 8 rows in set (0.00 sec)
Configure master-slave assignment
1. View master status
Run the show master status command in master to see the binary log file name and where to start.
2. Run the change master to command in the slave to specify the relevant information of the master to be connected.
mysql> change master to master_host='172.29.121.66', #The host name or IP address of the master server master_user='slave', # The replication user specified by the master server master_password='123456', # Master server's replication user password master_port=3316, # The port of the master server master_log_file='master-log-bin.000001', # The binary log file name of the master server master_log_pos=617; # The binary log position of the master server
3. The slave starts synchronization
mysql> start slave; Query OK, 0 rows affected (0.00 sec)
Check slave status, synchronization has started
Test
1. Write in master
2. Read in slave