[系统运维]基于Docker 安装 Mysql5.7 主从同步环境

1. 选取合适的镜像

首先是需要确定安装的MySQL版本，这里镜像我选择的版本是MySQL 5.7
然后去docker hub里面找到适合自己架构的镜像，这里用的是ARM架构服务器构建的。

找镜像直接去 https://hub.docker.com/ 里面搜 biarms/mysql ，地址是： https://hub.docker.com/r/biarms/mysql，接着在 Tags里面找到自己需要的版本和符合架构的镜像。

这里可以看到，5.7.30版本的镜像有三个，对应的架构分别为：linux/amd64、linux/arm、linux/arm64。这里我这边可以直接拉取5.7.30-linux-arm64v8镜像，或者直接拉取5.7.30镜像。

拉取使用了：docker pull biarms/mysql:5.7.30-linux-arm64v8

2. 创建挂载文件夹及配置文件

这里使用一主一从来搭建主从环境，其中主库为：192.168.104.68 从库为：192.168.104.69，下面创建文件夹要在每个机器上都要创建。

新建文件夹： mkdir -p /usr/soft/mysql/{etc,data,log}

修改log文件夹权限：chown 999 /usr/soft/mysql/log

3. 主库安装MySQL

在68机器上，创建主库的配置文件：/usr/soft/mysql/etc/my.cnf，直接使用下面命令即可。

cat >/usr/soft/mysql/etc/my.cnf << 'EOF'
[mysqld]
skip-host-cache
skip-name-resolve
skip_ssl
datadir=/var/lib/mysql
secure-file-priv=/var/lib/mysql-files
collation-server=utf8_bin
character-set-server=utf8
init_connect='set names utf8'
user=mysql
port=3306
max_connections=2000
group_concat_max_len=102400
max_allowed_packet=256M
#symbolic-links=0
log-bin=mysql-bin
server-id=1                 #主库server-id为1,备库server-id为2
binlog_format=row
relay-log-purge=0
#read_only=1                #备库配置
#log-slave-updates=true     #备库配置
#skip-slave-start=1         #备库配置  
binlog-ignore-db=mysql
replicate-ignore-db=mysql
####半自动同步+GTID
gtid-mode=on
enforce-gtid-consistency=1
plugin-load=rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_master_enabled = 1         
rpl_semi_sync_master_timeout = 2000      
rpl_semi_sync_slave_enabled = 1         
###################     Slow Log   ######################
slow_query_log = ON
# 开启慢查询日志
slow_query_log_file = /var/log/mysql/slow.log
# 慢查询日志存放路径
long_query_time = 10
# 超过10秒的查询，记录到慢查询日志，默认值10
log_queries_not_using_indexes = ON
# 没有使用索引的查询，记录到慢查询日志，可能引起慢查询日志快速增长
log_slow_admin_statements = ON
# 执行缓慢的管理语句，记录到慢查询日志
# 例如 ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.
###################     Error Log   ####################
log_error = /var/log/mysql/error.log
# 错误日志存放路径
log_error_verbosity = 2
# 全局动态变量，默认3，范围：1～3
# 表示错误日志记录的信息，1：只记录error信息；2：记录error和warnings信息；3：记录error、warnings和普通的notes信息
EOF

然后启动主库MySQL容器：

docker  run -itd \
-e MYSQL_ROOT_PASSWORD=abc123456 \
-v /usr/soft/mysql/data:/var/lib/mysql \
-v /usr/soft/mysql/log:/var/log/mysql \
-v /usr/soft/mysql/etc/my.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf \
-v /etc/localtime:/etc/localtime:ro \
--name mysql-server \
--network host \
--restart=always \
--privileged=true \
biarms/mysql:5.7.30-linux-arm64v8

4. 从库安装MySQL

cat >/usr/soft/mysql/etc/my.cnf << 'EOF'
[mysqld]
skip-host-cache
skip-name-resolve
skip_ssl
datadir=/var/lib/mysql
secure-file-priv=/var/lib/mysql-files
collation-server=utf8_bin
character-set-server=utf8
init_connect='set names utf8'
user=mysql
port=3306
max_connections=2000
group_concat_max_len=102400
max_allowed_packet=256M
#symbolic-links=0
log-bin=mysql-bin
server-id=2                 #主库server-id为1,备库server-id为2
binlog_format=row
relay-log-purge=0
read_only=1                #备库配置
log-slave-updates=true     #备库配置
skip-slave-start=1         #备库配置  
binlog-ignore-db=mysql
replicate-ignore-db=mysql
####半自动同步+GTID
gtid-mode=on
enforce-gtid-consistency=1
plugin-load=rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_master_enabled = 1         
rpl_semi_sync_master_timeout = 2000      
rpl_semi_sync_slave_enabled = 1         
###################     Slow Log   ######################
slow_query_log = ON
# 开启慢查询日志
slow_query_log_file = /var/log/mysql/slow.log
# 慢查询日志存放路径
long_query_time = 10
# 超过10秒的查询，记录到慢查询日志，默认值10
log_queries_not_using_indexes = ON
# 没有使用索引的查询，记录到慢查询日志，可能引起慢查询日志快速增长
log_slow_admin_statements = ON
# 执行缓慢的管理语句，记录到慢查询日志
# 例如 ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.
###################     Error Log   ####################
log_error = /var/log/mysql/error.log
# 错误日志存放路径
log_error_verbosity = 2
# 全局动态变量，默认3，范围：1～3
# 表示错误日志记录的信息，1：只记录error信息；2：记录error和warnings信息；3：记录error、warnings和普通的notes信息
EOF`在这里插入代码片`

从库配置文件，相比主库，修改了server-id，打开了只读配置的注释。

然后启动从库MySQL容器：

docker  run -itd \
-e MYSQL_ROOT_PASSWORD=abc123456 \
-v /usr/soft/mysql/data:/var/lib/mysql \
-v /usr/soft/mysql/log:/var/log/mysql \
-v /usr/soft/mysql/etc/my.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf \
-v /etc/localtime:/etc/localtime:ro \
--name mysql-server \
--network host \
--restart=always \
--privileged=true \
biarms/mysql:5.7.30-linux-arm64v8

如果一主多从环境，其他从库配置文件同上面从库配置文件，只需保证server-id依次递增即可。

5. 配置主从同步

进入主节点MySQL容器中：docker exec -it mysql-server mysql -uroot -pabc123456

设置主备同步的专用帐号repl_user，使用命令：GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'abc123456';

刷新：flush privileges;

查询主库状态：show master status;

mysql>  show master status
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |      757 |              | mysql            | 39f9df46-b88c-11eb-af08-000c29514619:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+

这里可以看到主库的Position 的值为757 ,后面核对从库状态需要用到。

进入从节点MySQL容器中：docker exec -it mysql-server mysql -uroot -pabc123456

停止主从同步 stop slave;

设置主节点

CHANGE MASTER TO 
MASTER_HOST='192.168.104.68',  
MASTER_PORT=3306,
MASTER_USER='repl_user',
MASTER_PASSWORD='abc123456',
MASTER_AUTO_POSITION=1;

启动主从同步：start slave;

显示主从同步状态：show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.104.68
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 757
               Relay_Log_File: ecs-d777-0002-relay-bin.000002
                Relay_Log_Pos: 807
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes      #状态正常
            Slave_SQL_Running: Yes      #状态正常
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           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: 757
              Relay_Log_Space: 1022
              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: 1
                  Master_UUID: 52301e53-6857-11ec-8958-fa163e71c74a
             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: 52301e53-6857-11ec-8958-fa163e71c74a:1-3
            Executed_Gtid_Set: 52301e53-6857-11ec-8958-fa163e71c74a:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 

核对上面信息：

Slave_IO_Running: Yes       #状态正常
Slave_SQL_Running: Yes      #状态正常
Seconds_Behind_Master: 0          #主从同步时间差
Read_Master_Log_Pos: 757	# 值是否是主库的Position

到这里主从同步环境已经搭好了。接着创建从库的只读用户，防止从库数据修改，如果修改了从库数据，则上面状态就会变成：

Slave_IO_Running: Yes      
Slave_SQL_Running: NO   

然后，自动停止主从同步。

从库新增只读用户：

创建用户 用户名：readonly_user 密码：abc123456可自行更改 –

CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'abc123456';

赋予用户只读权限 库名：spring_boot_plus 用户：readonly_user

GRANT SELECT ON spring_boot_plus.* TO 'readonly_user'@'%';

刷新权限 ： FLUSH PRIVILEGES;

查看用户权限：SHOW GRANTS FOR 'readonly_user'@'%';

主从同步至此搭建完成，在使用时，需要注意，从库要使用只读用户进行操作，否则从库一旦数据有自行修改的操作，主从同步里面中断。

主从同步问题一：Slave_SQL_Running：No

这种问题我这里遇到是因为修改了从库的数据，导致后面再次同步主库数据时，执行主库的SQL报错了。

解决办法：停止主从同步，将主库数据导入从库中，设置同步节点，再次开启主从同步。

从库进入MySQL中，停止同步：stop slave;

进入主库服务器，导出MySQL脚本，这里导出的数据库为：spring_boot_plus ：

docker exec -it mysql-server bash
mysqldump -uroot -pabc123456 --opt -R spring_boot_plus > /var/lib/mysql/2022_03_09_spring_boot_plus.sql

导出脚本的路径为： /var/lib/mysql/ 放到这个文件夹是因为，启动MySQL容器的时候，此文件夹已经挂载到宿主机上了，等下可以直接拷贝到从库机器上。无需单独从容器中拷贝出来了。

拷贝上面数据库脚本至从库的服务器上，位置为：/usr/soft/mysql/data，这样从库MySQL容器可以直接读取到该脚本。

从库操作：进入MySQL容器，设置数据库、导入SQL脚本。

docker exec -it mysql-server mysql -uroot -pabc123456
mysql> use spring_boot_plus;
mysql> source /var/lib/mysql/2022_03_09_spring_boot_plus.sql;

重置同步：

mysql> reset slave;

重新设置同步信息：

CHANGE MASTER TO 
MASTER_HOST='172.16.101.30',  
MASTER_PORT=3306,
MASTER_USER='repl_user',
MASTER_PASSWORD='abc123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=1881802;

host，port，user，password请根据你的主库设置相应修改，log_file 和 log_pos 根据主库中 master status 相应修改。

这里有两个地方需要注意，首先是MASTER_LOG_FILE、MASTER_LOG_POS这两个的值，需要在主库上查询得到，并且一定要确保MASTER_LOG_POS是最新的值，否则启动主从同步后，还会出问题。

如果报错：ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.

执行：change master to master_auto_position=0; 后，再次执行上面命令。

在主库上执行：

mysql>  show master status
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |   1881802|              | mysql            | 39f9df46-b88c-11eb-af08-000c29514619:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+

开启slave：start slave;

查看slave状态 show slave status\G; 这时，两个状态都应该是YES了。

            Slave_IO_Running: Yes      #状态正常
            Slave_SQL_Running: Yes      #状态正常

6. 数据库备份与恢复

6.1 创建备份文件

创建备份文件夹：mkdir -p /usr/soft/mysql/backup/auto_backup

cat > /usr/soft/mysql/backup/backup.sh << 'EOF'
#!/bin/bash
#########mysql backup####################
db_user="root" 
db_passwd="abc123456" 
db_host="192.168.104.68" 
db_port="3306"
MYSQL="docker exec -i mysql-server mysql" 
MYSQLDUMP="docker exec -i mysql-server mysqldump" 

##########code & res backup####################
backup_dir="/usr/soft/mysql/backup/auto_backup"
time="$(date +"%d-%m-%Y")" 
MKDIR="/bin/mkdir" 
RM="/bin/rm" 
MV="/bin/mv" 
GZIP="/bin/gzip" 

# the directory for story the newest backup 
test ! -d "$backup_dir/backup.0/" && $MKDIR -p "$backup_dir/backup.0/" 

# check the directory for store backup is writeable 
test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0 

backup()
{
# get all databases 
databases=(gdkf
message
nsfx) 

for db in ${databases[*]}
  do
    #备份指定库
    $MYSQLDUMP  -u$db_user -h$db_host -p$db_passwd -P$db_port $db --single-transaction --set-gtid-purged=OFF | $GZIP -9 > "$backup_dir/backup.0/$time.$db.gz"   
    #备份存储过程、触发器、函数等
    $MYSQLDUMP -u$db_user -h$db_host -p$db_passwd -P$db_port -n -t -d -R $db --single-transaction | $GZIP -9 > "$backup_dir/backup.0/$time.proc_$db.gz"    
done     
}

backup_all()
{
 $MYSQLDUMP  -u$db_user -h$db_host -p$db_passwd -P$db_port --all-databases --set-gtid-purged=OFF | $GZIP -9 > "$backup_dir/backup.0/mysql_$time.gz"   
}

#备份所有数据库
backup_all
####滚动清理历史备份
test -d "$backup_dir/backup.5/" && $RM -rf "$backup_dir/backup.5" 
for int in 4 3 2 1 0 
do 
if(test -d "$backup_dir"/backup."$int") 
then 
next_int=`expr $int + 1` 
$MV "$backup_dir"/backup."$int" "$backup_dir"/backup."$next_int" 
fi 
done 

exit 0;
EOF

6.2 创建恢复脚本

恢复脚本：

cat > /usr/soft/mysql/backup/restore.sh << 'EOF'
#!/bin/bash
#########mysql backup####################
db_user="root" 
db_passwd="abc123456" 
db_host="192.168.104.68" 
db_port="3306"
MYSQL="docker exec -i mysql-server mysql" 
MYSQLDUMP="docker exec -i mysql-server mysqldump" 

##########code & res backup####################
backup_dir="/usr/soft/mysql/backup/auto_backup"
time="$(date +"%d-%m-%Y")" 
MKDIR="/bin/mkdir" 
RM="/bin/rm" 
MV="/bin/mv" 
GZIP="/bin/gzip" 

restore_all()
{
 zcat $backup_dir/backup.1/mysql*.gz|$MYSQL  -u$db_user -h$db_host -p$db_passwd -P$db_port
}

restore_all

exit 0;
EOF

6.3 测试备份与恢复

此时 /usr/soft/mysql/backup 文件夹下 包含 auto_backup backup.sh restore.sh

给脚本新增执行权限：

chmod u+x  backup.sh
chmod u+x  restore.sh

测试备份：sh backup.sh

正常会打印警告信息：mysql: [Warning] Using a password on the command line interface can be insecure.

auto_backup 文件夹下会生成第一次备份的文件夹 backup.1， 再次执行备份生成 backup.2 最多保留五次最近备份记录

测试恢复备份：sh restore.sh

正常会打印警告信息：mysql: [Warning] Using a password on the command line interface can be insecure.