基于Ubuntu系统的docker环境对MySQL8.0.36主从部署

1. 环境准备 1、1 前言 本文基于Ubuntu系统的docker环境对MySQL8.0.36进行2台物理服务器的主从部署。 1.2 服务器硬件环境准备

准备2台物理服务器192.168.8.6 和 192.168.8.9。 2台物理服务器在同一个局域网环境下，可以相互网络通信。 服务器操作系统版本：Ubuntu 24.04.1 LTS 服务器配置：20C 32G + 2.5T 查看CPU个数和每个CPU核数 lscpu | grep “Socket(s)” lscpu | grep “Core(s) per socket” 挂载数据盘到/data目录下并设置开机自动挂载

vim /etc/fstab /dev/sda1 /data ext4 defaults 0 2

如下图所示：

1.3 服务器软件环境准备 1.3.1 安装好docker环境

1）docker版本支持：27.5.1 2）Docker镜像源配置：/etc/docker/daemon.json

{ "registry-mirrors": [ " docker.registry.cyou", " docker-cf.registry.cyou"," dockercf.jsdelivr.fyi"," docker.jsdelivr.fyi"," dockertest.jsdelivr.fyi"," mirror.aliyuncs "," dockerproxy "," mirror.baidubce "," docker.m.daocloud.io"," docker.nju.edu "," docker.mirrors.sjtug.sjtu.edu "," docker.mirrors.ustc.edu "," mirror.iscas.ac "," docker.rainbond.cc" ], "data-root": "/data/docker" } 1.3.2 拉取MySQL8.0.36的docker镜像

执行命令

docker pull mysql:8.0.36

2. Docker部署MySQL8主节点

进入到/data/mysql-mainer/mainer目录下，创建build.sh文件，touch build.sh 并给该文件添加本用户的可执行权限：chmod +x build.sh 脚本文件build.sh内容如下：

docker run -d --name mysql-mainer --restart=always --privileged=true -p 3308:3306 -e MYSQL_ROOT_PASSWORD=****** -v /data/mysql-mainer/mainer/data:/var/lib/mysql -v /data/mysql-mainer/mainer/conf:/etc/mysql/conf.d -v /data/mysql-mainer/mainer/log:/var/log/mysql -v /etc/localtime:/etc/localtime:ro -d mysql:8.0.36

容器创建成功如下图所示： mysql启动成功日志如下：

3、MySQL8主节点配置 3.1 配置允许mysql远程登录连接

使用如下命令进入到容器内：

docker exec -it mysql-mainer bash

进入容器后使用如下命令登录容器内的MySQL的终端：

mysql -uroot -p

登录到MySQL后如下图所示开始开启root远程允许任一IP登录连接： 具体mysql执行的SQL脚本如下： – 授予 root 用户从任何主机远程连接的权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

– 刷新权限

FLUSH PRIVILEGES; 3.2 查看主库的binlog的POS位置

– 查看主库状态

SHOW MASTER STATUS

如下图所示：

3.3 设置主库的master f

进入到宿主机挂载的目录下/data/mysql-mainer/mainer/conf，新增master f文件，touch master f 如下图所示：

然后vim 编辑master f插入并保存如下配置：

# For advice on how to change settings please see # http://dev.mysql /doc/refman/8.0/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # dev.mysql /doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin # default-authentication-plugin=mysql_native_password skip-host-cache skip-name-resolve datadir=/var/lib/mysql socket=/var/run/mysqld/mysqld.sock secure-file-priv=/var/lib/mysql-files user=mysql pid-file=/var/run/mysqld/mysqld.pid # add self define config default_time_zone = "+8:00" character-set-server = utf8mb4 collation-server = utf8mb4_general_ci init_connect='SET NAMES utf8mb4' transaction_isolation = REPEATABLE-READ explicit_defaults_for_timestamp = true max_connections = 2000 max_connect_errors = 600 back_log = 110 table_open_cache = 600 table_definition_cache = 700 table_open_cache_instances = 64 max_allowed_packet = 512M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 20 interactive_timeout