MySQL 集群部署

环境

集群模式：双主集群

服务器：192.168.93.100、192.168.93.101

版本：mysql-5.7.44

配置

192.168.93.101

my.cnf

[mysqld]
skip-host-cache
skip-name-resolve
# 修改数据磁盘 data 目录
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
symbolic-links=0
pid-file=/var/run/mysqld/mysqld.pid
server-id=2
character-set-server=UTF8MB4
lower_case_table_names=1
default-time_zone='+8:00'
# 指定不需要同步的数据库名称
binlog-ignore-db=mysql
# 开启二进制日志功能
log_bin=/var/lib/mysql/itpuxdb-binlog
# 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M
# 设置使用的二进制日志格式（mixed，statement，row）
binlog_format=mixed
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
# 二进制日志过期清理时间，默认值 0，表示不自动清理
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免 slave 端复制中断
# 如：1062 错误是指一些主键重复，1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062
# 设置步进值
auto-increment-offset=1
auto-increment-increment=2
[client]
socket=/var/run/mysqld/mysqld.sock

192.168.93.100

my.cnf

[mysqld]
skip-host-cache
skip-name-resolve
# 修改数据磁盘 data 目录
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
symbolic-links=0
pid-file=/var/run/mysqld/mysqld.pid
server-id=1
character-set-server=UTF8MB4
lower_case_table_names=1
default-time_zone='+8:00'
# 指定不需要同步的数据库名称
binlog-ignore-db=mysql
# 开启二进制日志功能
log_bin=/var/lib/mysql/itpuxdb-binlog
# 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M
# 设置使用的二进制日志格式（mixed，statement，row）
binlog_format=row
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
# 二进制日志过期清理时间，默认值 0，表示不自动清理
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免 slave 端复制中断
# 如：1062 错误是指一些主键重复，1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062
# 设置步进值
auto-increment-offset=2
auto-increment-increment=2
[client]
socket=/var/run/mysqld/mysqld.sock

启动 MySQL 容器

192.168.93.100、192.168.93.101

docker run --name mysql1 \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /data/mysql/data:/var/lib/mysql \
-v /data/mysql/log:/var/log/mysql \
-v /data/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-p 13306:3306 -d mysql:5.7.44

配置主从关系

192.168.93.100

create user 'slave'@'%' identified by 'root';
grant replication slave,replication client on *.* to 'slave'@'%';

# 查看主节点 1 的日志文件名称和偏移量用于后面从节点配置使用
show master status;

mysql> create user 'slave'@'%' identified by 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to 'slave'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| itpuxdb-binlog.000004 |      627 |              | mysql            |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

192.168.93.101

change master to master_host='192.168.93.100',master_user='slave',master_password='root',master_port=13306,master_log_file='itpuxdb-binlog.000004',master_log_pos=627,master_connect_retry=30,master_retry_count=3;

# 开启主从复制
start slave;

# 查看 slave 状态
show slave status\G;

192.168.93.101

create user 'slave'@'%' identified by 'root';
grant replication slave,replication client on *.* to 'slave'@'%';

# 查看主节点 1 的日志文件名称和偏移量用于后面从节点配置使用
show master status;

mysql> create user 'slave'@'%' identified by 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to 'slave'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| itpuxdb-binlog.000004 |      627 |              | mysql            |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

192.168.93.100

change master to master_host='192.168.93.101',master_user='slave',master_password='root',master_port=13306,master_log_file='itpuxdb-binlog.000004',master_log_pos=627,master_connect_retry=30,master_retry_count=3;

# 开启主从复制
start slave;

# 查看 slave 状态
show slave status\G;

注意：从节点的 master_log_file 和 master_log_pos 要与主节点一致