1、环境说明
本次配置是在AlmaLinux下,mysql 采用 5.7.13,Docker 版本为3.3.1。主要目的是要实现在一台主机上两个Mysql数据库的主从复制,进而为SpringBoot读写分离作开发前准备。
2、文件挂载
根据《Docker最佳实践》的指导要求,数据库文件等不能放在容器里面,构建的镜像所启动的容器应该尽可能短暂(生命周期短)。「短暂」意味着可以停止和销毁容器,容器消亡时,容器存储层也随之消亡。容器不应该向其存储层内写入任何数据,容器存储层要保持无状态化。
1)、创建文件夹:data、conf、logs用于mysql数据库文件和日志文件以及配置文件挂载。数据库文件等不可能放在容器里面,如下:
mkdir -p /mysql/master/data #-p 确保目录名称存在,不存在的就建一个
mkdir -p /mysql/master/conf
mkdir -p /mysql/master/logs
mkdir -p /mysql/slave/data
mkdir -p /mysql/slave/conf
mkdir -p /mysql/slave/logs
2)、在conf创建master.cnf文件
vim /mysql/master/conf/master.cnf
文件内容如下:
# Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp
log-bin = mysql-bin
server-id = 1001
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
3、再创建slave.cnf文件
注意:server-id不能一样,是数字就可以 比如1和2 1001和1002这2个文件名随意,路径挂载正确即可(放入的文件夹是slave的conf)。
vim /mysql/slave/conf/slave.cnf
文件内容如下:
# Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp
log-bin = mysql-bin
server-id = 1002
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
4、运行容器,并挂载以上文件
Master:
docker run --name mastermysql --privileged=true -v /mysql/master/data:/var/lib/mysql -v /mysql/master/conf:/etc/mysql/conf.d -v /mysql/master/logs:/var/log/mysql -e MYSQL_ROOT_PASSWORD=admin -p 5306:3306 -d mysql:5.7.19
Slave:
docker run --name slavemysql --privileged=true -v /mysql/slave/data:/var/lib/mysql -v /mysql/slave/conf:/etc/mysql/conf.d -v /mysql/slave/logs:/var/log/mysql -e MYSQL_ROOT_PASSWORD=admin -p 5307:3306 -d mysql:5.7.19
5、查看容器运行情况
[root@localhost mysql]# docker ps
Emulate Docker CLI using podman. Create /etc/containers/nodocker to quiet msg.
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
933d18b697a8 docker.io/library/mysql:5.7.19 mysqld 7 minutes ago Up 7 minutes ago 0.0.0.0:5306->3306/tcp mastermysql
040641086a42 docker.io/library/mysql:5.7.19 mysqld 5 minutes ago Up 5 minutes ago 0.0.0.0:5307->3306/tcp slavemysql
注意:在安装docker mysql:5.7版本时,出现错误(docker logs XXID):
mysqld: Can't read dir of '/etc/mysql/conf.d/' (Errcode: 13 - Permission denied)
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!
ERROR: mysqld failed while attempting to check config
command was: "mysqld --verbose --help"
原因是:
因为Centos7安全Selinux禁止了一些安全权限,导致mysql和mariadb在进行挂载/var/lib/mysql的时候会提示如下信息
解决方法
在docker run中加入 --privileged=true 给容器加上特定权限,如下:
docker run --name mastermysql --privileged=true -v /mysql/master/data:/var/lib/mysql -v /mysql/master/conf:/etc/mysql/conf.d -v /mysql/master/logs:/var/log/mysql -e MYSQL_ROOT_PASSWORD=admin -p 5306:3306 -d mysql:5.7.19
6、主库(Master)设置
- 进入主库
docker exec -it mastermysql /bin/bash
- 进入主库数据库
root@933d18b697a8:/# mysql -uroot -padmin
- show master status;查看部分信息。主要是2个信息 File 和Position
mysql> show master status
;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 创建用户:
CREATE USER 'lichaoying'@'%' IDENTIFIED BY '123456';
格式:CREATE USER 'username'@'host' IDENTIFIED BY 'password';
username:你将创建的用户名
host:指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,如果想让该用户可以从任意远程主机登陆,可以使用通配符%
password:该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器
- 授权
GRANT ALL ON *.* TO 'lichaoying'@'%';
格式:GRANT privileges ON databasename.tablename TO 'username'@'host'
privileges:用户的操作权限,如SELECT,INSERT,UPDATE等,如果要授予所的权限则使用ALLdatabasename:数据库名tablename:表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示,如*.*
7、从库(Slave)设置
- 退回宿主机,查看主备的IP地址,如下可以看出Master IP 为10.88.0.11
[root@localhost ~]# docker ps
Emulate Docker CLI using podman. Create /etc/containers/nodocker to quiet msg.
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
933d18b697a8 docker.io/library/mysql:5.7.19 mysqld 4 hours ago Up 4 hours ago 0.0.0.0:5306->3306/tcp mastermysql
040641086a42 docker.io/library/mysql:5.7.19 mysqld 4 hours ago Up 4 hours ago 0.0.0.0:5307->3306/tcp slavemysql
[root@localhost ~]# docker inspect 933d18b697a8 | grep IPAddress
Emulate Docker CLI using podman. Create /etc/containers/nodocker to quiet msg.
"IPAddress": "10.88.0.11",
"IPAddress": "10.88.0.11",
[root@localhost ~]# docker inspect 040641086a42 | grep IPAddress
Emulate Docker CLI using podman. Create /etc/containers/nodocker to quiet msg.
"IPAddress": "10.88.0.12",
"IPAddress": "10.88.0.12",
[root@localhost ~]#
- 登录到从库(Slave)
docker exec -it slavemysql /bin/bash
- 进入从库mysql
root@040641086a42:/# mysql -uroot -padmin
- 从库中配置主库信息
change master to master_host='10.88.0.11', master_port=3306,master_user='lichaoying', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=154;
说明:
master_host:主库的IP地址/服务器地址
master_port:主库的端口
master_user:主库开放出来的用户
master_password:用户的密码
master_log_file:主库的日志文件,二进制日志文件,进行同步的。就是上面show master status 里面的File
master_log_pos:上面show master status 里面的Position
- 启动连接,并查看状态
mysql>start slave;
mysql>show slave status\G
出现 Slave_IO_Running: Yes Slave_SQL_Running: Yes 表示主从复制配置成功,如下:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.88.0.11
Master_User: lichaoying
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 756
Relay_Log_File: 040641086a42-relay-bin.000004
Relay_Log_Pos: 969
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 756
Relay_Log_Space: 3001411
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1001
Master_UUID: 603d29e7-7da6-11ec-8bc1-f26c6e020823
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
成功!以及关于成功。
后记:
快过年了! 从昨天早上到今早一直在研究这个课题,也许是因为昨晚领导说了一句“码农是公司里面最LOW的”,一直影响自己的心情。但是,我坚信在成功的路上,只要坚定自己的信念,一定会春暖花开。
春节到来,祝愿大家在新的一年里万事顺意,心想事成,梦想成真!
2022年1月28日于云南.蒙自