docker安装mysql5.7、单节点、主从复制(仅供测试使用)

前言

环境：Centos7.9 mysql5.7 Docker version 20.10.9
mysql数据库建议使用物理机安装，这里使用docker安装mysql仅供测试使用或其他不重要场景使用。

docker 安装mysql5.7-单机

#创建mysql的文件存放目录
mkdir -p  /usr/local/mysql/conf
mkdir -p /usr/local/mysql/logs
mkdir -p /usr/local/mysql/mysql
cd  /usr/local/mysql/conf
#创建mysql配置文件
cat > my.cnf <<'EOF'
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
default-time_zone = '+8:00'
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
EOF

#启动mysql5.7容器,并使用-v参数持久化存储mysql的数据
docker run --restart always \
-p 3306:3306 \
--name mysql5.7 \
-v /usr/local/mysql/conf/my.cnf:/etc/my.cnf \
-v /usr/local/mysql/logs:/var/log/mysql \
-v /usr/local/mysql/mysql:/var/lib/mysql  \
-e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

#查看mysql5.7容器,已经启动了
[root@soft mysql]# docker ps | grep  mysql5.7
1019a1f67f2d   mysql:5.7  "docker-entrypoint.s…"   30 minutes ago   Up 30 minutes   0.0.0.0:3306->3306/tcp, :::3306-
>3306/tcp, 33060/tcp   mysql5.7
[root@soft mysql]#

#进入容器查看mysql容器是否可用
[root@soft ~]# docker exec -it mysql5.7 mysql -uroot -p123456	#登录mysql				
mysql> show databases;											#执行命令,一切正常,说明MySQL可用
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
mysql> create database  test;
mysql> use test;
mysql> create table test(id long);
mysql> insert test(id) value(1);
mysql> select * from test;
+------+
| id   |
+------+
| 1    |
+------+
mysql> 

备注：因为我们在启动mysql5.7容器的时候做了主机端口映射,所以外部应用程序直接链接主机ip:3306即可使用容器的mysql数据库。

docker 安装mysql5.7-主从复制

创建mysql-master节点：

#创建mysql的文件存放目录
mkdir -p  /usr/local/mysql/conf
mkdir -p /usr/local/mysql/logs
mkdir -p /usr/local/mysql/mysql
cd  /usr/local/mysql/conf
#创建mysql配置文件
#server_id参数表示指定mysql的server ID
#log_bin参数表示开启mysql binlog日志，binlog日志前缀叫mysql-bin,这里配置的是相对路径,路径在/var/lib/mysql目录下
cat > my.cnf <<'EOF'
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
server_id = 1
log_bin = mysql-bin
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
default-time_zone = '+8:00'
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
EOF

#启动mysql-master容器,并使用-v参数持久化存储mysql的数据
docker run --restart always \
-p 3306:3306 \
--name mysql-master \
-v /usr/local/mysql/conf/my.cnf:/etc/my.cnf \
-v /usr/local/mysql/logs:/var/log/mysql \
-v /usr/local/mysql/mysql:/var/lib/mysql  \
-e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

#查看mysql-master,正常
docker exec -it mysql-master mysql -uroot -p123456 -e "show databases"

创建mysql-slave节点（另外一台服务器）：

mkdir -p  /usr/local/slave/conf
mkdir -p /usr/local/slave/logs
mkdir -p /usr/local/slave/mysql
cd  /usr/local/slave/conf
#创建mysql配置文件
cat > my.cnf <<'EOF'
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
server_id = 2
log_bin = mysql-bin
read-only = 1
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
default-time_zone = '+8:00'
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
EOF

#启动mysql-slave容器,并使用-v参数持久化存储mysql的数据
docker run --restart always \
-p 3306:3306 \
--name mysql-slave \
-v /usr/local/slave/conf/my.cnf:/etc/my.cnf \
-v /usr/local/slave/logs:/var/log/mysql \
-v /usr/local/slave/mysql:/var/lib/mysql  \
-e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

在这个命令中，当两个容器都在同一台主机上时可以加上 --link mysql-master:mysql-master 参数，表示将当前容器（mysql-slave）与名为
mysql-master 的容器进行链接。这个链接允许两个容器之间进行通信和数据交换。--link 参数后面跟着的是另一个容器的名称，冒号后面的是别名。
通过这种链接方式，mysql-slave 容器可以获取到 mysql-master 容器的 IP 地址和其他网络信息，从而与其进行通信。这对于一些需要跨容器通信的
场景非常有用，比如在一个主从复制架构中，从服务器需要连接到主服务器进行数据同步。

mysql-master节点创建主从复制的用户：

docker exec -it mysql-master bash
root@09c7a2106da2:/# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'rep'@'%' identified  by '123456';
mysql> exit;
docker restart  mysql-master
docker exec -it mysql-master bash
root@09c7a2106da2:/# mysql -uroot -p123456
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

mysql> 

配置从节点：

[root@dev-node01 conf]# docker exec -it mysql-slave bash
root@12ca632033a8:/# mysql -uroot -p123456
mysql> change master to master_host='192.168.244.20',master_port=3306,master_user='rep',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=154;

mysql>  start slave;
mysql> show slave status\G

测试主从同步：

# 在mysql-master节点创建一个test数据库，并创建表插入数据
[root@dev-master ~]# docker exec -it mysql-master bash
root@09c7a2106da2:/# mysql -uroot -p123456
mysql> create database test;
mysql> use test;
mysql> create table test(id int,name varchar(10));
mysql> insert into test(id,name) values(1,"lisi");
mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | lisi |
+------+------+
mysql> 
# 在mysql-slave上查看，数据已经同步过来了
[root@dev-node01 ~]# docker exec -it mysql-slave bash
root@12ca632033a8:/# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | lisi |
+------+------+