Docker部署Mysql主从

一、准备工作

软硬件准备

win11 + VM + CentOS 7 + docker [docker中启动mysql客户端]

docker pull mysql 拉取镜像

Mysql主从工作原理

MySQL主从同步是基于Bin Log实现的，而Bin Log记录的是原始SQL语句。

Bin Log共有三种日志格式，可以binlog_format配置参数指定。

Statement	记录原始SQL语句，会导致更新时间与原库不一致。比如 update_time=now()
Row			记录每行数据的变化，保证了数据与原库一致，缺点是数据量较大。
Mixed		Statement和Row的混合模式，默认采用Statement模式，涉及日期、函数相关的时候采用Row模式，既减少了数据量，又保证了数据一致性。

作用：
读写分离，提升数据库性能
容灾恢复，主服务器不可用时，从服务器提供服务，提高可用性
冗余备份，主服务器数据损坏丢失，从服务器保留备份
原理：
主库数据发生变更，写入本地binlog文件，从库的IO线程发起dump主库文件的请求，
主库IO线程推送binlog文件到从库，从库把binlog文件中内容写到从库的relaylog文件中，
从库使用sql线程读取relay文件内容，重新执行一遍sql语句。

二、实操

Mysql主机部署

1.创建Mysql配置文件

mkdir -p /usr/local/mysql/master/conf /usr/locl/mysql/master/data # 创建挂载目录
vim /usr/local/mysql/master/conf/my.cnf #创建配置文件
my.cnf
将下面的内容粘贴到my.cnf

# Copyright (c) 2017, 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  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
default_authentication_plugin=mysql_native_password

# Custom config should go here
!includedir /etc/mysql/conf.d/

server_id=1                       #服务器id
log_bin=mysql-bin             #binlog名称
binlog_format=row             #binlog的格式

2.启动主机容器

docker run -it \ # 交互模式运行容器
--name mysql_master \  # 容器名称
--privileged \ #  设置容器权限 默认为True
-p 3306:3306 \ # 端口映射
-v /usr/local/mysql/master/conf/my.cnf:/etc/mysql/my.cnf \  #挂载配置文件
-v /usr/local/mysql/master/data:/var/lib/mysql \  # 持久化数据
-v /usr/local/mysql/master/mysql-files:/var/lib/mysql-files \ # 持久化数据
-e MYSQL_ROOT_PASSWORD=123456 \   # 设置用户名和密码
-d mysql # 后台运行容器

3.进入主机容器并运行mysql客户端

docker exec -it mysql_master bash
mysql -uroot -p123456

4.开放端口

防火墙在创建centOS的时候就已经关闭，这一步可以直接跳过

quit # 退出mysql 也可使用 quit
exit # 退出 mysql容器 

firewall-cmd --zone=public --add-port=3306/tcp --permanent # 开放端口
firewall-cmd --reload # 将重新加载防火墙
firewall-cmd --zone=public --list-ports # 查看开放端口

5.测试连接

Mysql从机部署

1.检查主机binlog是否开启

docker exec -it mysql_master bash 
mysql -uroot -p123456
show variables like 'log_%'; # 查看log_bin 是否为NO

2.查看主机的日志文件

show master status; 
file 是binlog文件 Position 是日志文件的起始位置

3. 创建与slave通信的用户

create user 'slave'@'%' identified by '123456'; # 设置用户
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON . TO 'slave'@'%'; #授权
flush privileges; # 应用授权

4.查询主机容器的的ip地址

docker inspect mysql_master |grep IPA

5.启动Mysql从机

mkdir -p /usr/local/mysql/salve/conf   /usr/local/mysql/salve/data
cp my.cnf ../../salve/conf
vim  /usr/local/mysql/salve/conf/my.cnf
修改 文件中最后几行
server_id=2
logbin=mysql-salve-bin
relay_log=slave-relay-bin
read_only=1

创建容器
docker run -it \
--name mysql_salve \
--privileged \
-p 3310:3306 \
-v /usr/local/mysql/salve/conf/my.cnf:/etc/mysql/my.cnf \
-v /usr/local/mysql/salve/data:/var/lib/mysql \
-v /usr/local/mysql/salve/mysql-files:/var/lib/mysql-files \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql

docker exec -it mysql_salve bash

mysql -uroot -p123456

6.通过主机的IP和用户将其和从机关联

change master to
master_host='172.17.0.3',
master_user='slave',
master_password='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=850;

7.开启从机，并检查测试

start slave; # 开启从机
show slave stus \G; # 查看从机状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.17.0.3
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 850
               Relay_Log_File: wnhz-relay-bin.000002
                Relay_Log_Pos: 328
        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: 850
              Relay_Log_Space: 538
              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: 200
                  Master_UUID: 0fcf4e13-cfc3-11ee-8349-0242ac110003
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 10
                  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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

两个Mysql从机线程都处于Yes状态，Mysql主从配置成功。
接下来可以尝试使用连接工具在主库创建表，再在从库中验证表数据是否完成同步。

三、说明

1.环境说明

本地VM虚拟机部署，防火墙已关闭。
真实环境下建议使用docker-compose或者使用K8S部署。

2.参考说明

本文为本人实操笔记，有些借鉴的地方出自其他同学博文中，如有任何介意之处，请联系我修改。期待和大家共同进步。