MySQL主从及podman容器搭建

默认my.cnf：

# For advice on how to change settings please see
# http://dev.mysql.com/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:
# https://dev.mysql.com/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

[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

预先放置好配置文件：

[mysqld]
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

[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

主节点podman脚本：

VOL_HOME=/data/volumes;
mkdir -p $VOL_HOME/mysql-master/conf
mkdir -p $VOL_HOME/mysql-master/conf/conf.d
mkdir -p $VOL_HOME/mysql-master/data
echo '!includedir /etc/mysql/conf.d/' > $VOL_HOME/mysql-master/conf/my.cnf
echo [mysqld] > $VOL_HOME/mysql-master/conf/conf.d/master.cnf
echo server-id=1 >> $VOL_HOME/mysql-master/conf/conf.d/master.cnf
echo port=13301 >> $VOL_HOME/mysql-master/conf/conf.d/master.cnf
echo lower_case_table_names=1 >> $VOL_HOME/mysql-master/conf/conf.d/master.cnf
echo default-time_zone = '+8:00' >> $VOL_HOME/mysql-master/conf/conf.d/master.cnf
echo character_set_server=utf8mb4 >> $VOL_HOME/mysql-master/conf/conf.d/master.cnf
echo init_connect='SET NAMES utf8mb4' >> $VOL_HOME/mysql-master/conf/conf.d/master.cnf
echo binlog_format = mixed >> $VOL_HOME/mysql-master/conf/conf.d/master.cnf
echo log-bin=/var/lib/mysql/mysql-bin >> $VOL_HOME/mysql-master/conf/conf.d/master.cnf
podman run --name mysql-master \
-e TZ=Asia/Shanghai \
-e MYSQL_ROOT_PASSWORD=joycode \
-v $VOL_HOME/mysql-master/conf/my.cnf:/etc/my.cnf \
-v $VOL_HOME/mysql-master/conf/conf.d:/etc/mysql/conf.d \
-v $VOL_HOME/mysql-master/data:/var/lib/mysql \
-p 13301:13301 \
-p 23301:33060 \
-d \
mysql:8.0.32 \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_general_ci;

从节点podman脚本：

VOL_HOME=/data/volumes;
mkdir -p $VOL_HOME/mysql-slave/conf
mkdir -p $VOL_HOME/mysql-slave/conf/conf.d
mkdir -p $VOL_HOME/mysql-slave/data
echo '!includedir /etc/mysql/conf.d/' > $VOL_HOME/mysql-slave/conf/my.cnf
echo [mysqld] > $VOL_HOME/mysql-slave/conf/conf.d/slave.cnf
echo server-id=2 >> $VOL_HOME/mysql-slave/conf/conf.d/master.cnf
echo port=13302 >> $VOL_HOME/mysql-slave/conf/conf.d/slave.cnf
echo lower_case_table_names=1 >> $VOL_HOME/mysql-slave/conf/conf.d/slave.cnf
echo default-time_zone = '+8:00' >> $VOL_HOME/mysql-slave/conf/conf.d/slave.cnf
echo character_set_server=utf8mb4 >> $VOL_HOME/mysql-slave/conf/conf.d/slave.cnf
echo init_connect='SET NAMES utf8mb4' >> $VOL_HOME/mysql-slave/conf/conf.d/slave.cnf
echo relay-log=relay-bin >> $VOL_HOME/mysql-slave/conf/conf.d/slave.cnf
echo relay-log-index=relay-bin.index >> $VOL_HOME/mysql-slave/conf/conf.d/slave.cnf
podman run --name mysql-slave \
-e TZ=Asia/Shanghai \
-e MYSQL_ROOT_PASSWORD=joycode \
-v $VOL_HOME/mysql-slave/conf/my.cnf:/etc/my.cnf \
-v $VOL_HOME/mysql-slave/conf/conf.d:/etc/mysql/conf.d \
-v $VOL_HOME/mysql-slave/data:/var/lib/mysql \
-p 13302:13302 \
-p 23302:33060 \
-d \
mysql:8.0.32 \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_general_ci;

主库执行：

show master status;   

从库执行：

stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.56.100',MASTER_PORT=13301,MASTER_USER='root',MASTER_PASSWORD='joycode',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=157;
start slave;
show slave status;

参考：

• MySQL主从复制(一主两从)
• mysql 主从配置，slave master 设置
• log_bin_trust_function_creators变量解释
• MySQL集群部署（使用多个MySQL实例模拟）
• MySQL主从不同步原因有哪些，能解决吗