本文主要介绍 MySQL/Mariadb 主从复制部署过程，主从复制原理请看这里
一、实验环境
作业系统：CentOS 7.7 mariadb version:10.5.0 服务器角色 IP地址 主机名 应用程式 Master 172.50.1.107 nacos-mha-s1 mariadb Slave1 172.50.1.108 nacos-mha-s2 mariadb Slave2 172.50.1.109 nacos-mha-s3 mariadb
二、部署Maridb
三台服务器都要 部署mariadb
三、主从配置
可根据实际情况是否开启半同步配置
配置文件路径：/etc/my.cnf.d/server.cnf
[mariadb]
log-bin
server_id=1
log-basename=master
binlog-format=mixed
relay_log_purge=0 # 关闭中继日志
##### 以下配置为开启 master 半同步复制 #####
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=5000
##### 以上配置为开启 master 半同步复制 #####
[mariadb]
log-bin
server_id=2
log-basename=slave1
binlog-format=mixed
relay_log_purge=0 # 关闭中继日志
##### 以下配置为开启 slave 半同步复制 #####
rpl_semi_sync_slave_enabled=ON
##### 以上配置为开启 slave 半同步复制 #####
[mariadb]
log-bin
server_id=3
log-basename=slave2
binlog-format=mixed
relay_log_purge=0 # 关闭中继日志
##### 以下配置为开启 slave 半同步复制 #####
rpl_semi_sync_slave_enabled=ON
##### 以上配置为开启 slave 半同步复制 #####
sudo systemctl restart mariadb.service
四、获取 master 节点 binary log（二进制日志）postion
在 master 节点上，通过mysql -u root -p进入 MySQL 终端，记录 File 和 Position 字段的值 MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master-bin.000001 | 330 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
继续在 master 节点上创建主从复制专有用户并授权 CREATE USER 'replication'@'%' IDENTIFIED BY 'Admin123';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;
五、启动 Slave
分别在 Slave 节点上，执行以下语句（为 Slave 指定 Master） MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.50.1.107', MASTER_USER='replication', MASTER_PASSWORD='Admin123', MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=330, MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;
在 Slave 节点上 查看 状态 (出现以下 两个 YES MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
.........省略.........
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.........省略.........
1 row in set (0.001 sec)
六、docker-compose版部署
docker-compose.yaml配置文件
version: '3'
services:
mysql_master:
image: mysql:5.7.21
container_name: MessageCenter_MySQL_Master
ports:
- 3307:3306
environment:
TZ: Asia/Shanghai
MYSQL_ROOT_PASSWORD: Admin123
volumes:
- /data/MessageCenter_MySQL/master/mysql/mysql.conf.d:/etc/mysql/mysql.conf.d
- /data/MessageCenter_MySQL/master/mysql/lib/mysql:/var/lib/mysql
- /etc/localtime:/etc/localtime:ro
- /data/MessageCenter_MySQL/master/mysql/log:/var/log/mysql
restart: always
mysql_slave1:
image: mysql:5.7.21
container_name: MessageCenter_MySQL_Slave1
ports:
- 3308:3306
environment:
TZ: Asia/Shanghai
MYSQL_ROOT_PASSWORD: Admin123
volumes:
- /data/MessageCenter_MySQL/slave1/mysql/mysql.conf.d:/etc/mysql/mysql.conf.d
- /data/MessageCenter_MySQL/slave1/mysql/lib/mysql:/var/lib/mysql
- /etc/localtime:/etc/localtime:ro
- /data/MessageCenter_MySQL/slave1/mysql/log:/var/log/mysql
restart: always
mysql_slave2:
image: mysql:5.7.21
container_name: MessageCenter_MySQL_Slave1
ports:
- 3309:3306
environment:
TZ: Asia/Shanghai
MYSQL_ROOT_PASSWORD: Admin123
volumes:
- /data/MessageCenter_MySQL/slave2/mysql/mysql.conf.d:/etc/mysql/mysql.conf.d
- /data/MessageCenter_MySQL/slave2/mysql/lib/mysql:/var/lib/mysql
- /etc/localtime:/etc/localtime:ro
- /data/MessageCenter_MySQL/slave2/mysql/log:/var/log/mysql
restart: always
my.cnf配置文件样本(注意修改server_id和read-only的值)
[client]
default-character-set = utf8
port = 3306
[mysql]
port = 3306
default-character-set = utf8
[mysqld]
port = 3306
character-set-server = utf8
log-bin = mysql-bin
binlog_cache_size = 1M
expire_logs_days = 10
max_binlog_size = 128M
server_id=1
binlog_format=MIXED
read-only=0
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
skip-external-locking
lower_case_table_names = 1
max_connections=1000
max_user_connections=100
max_connect_errors=1000
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 8
innodb_log_file_size = 200M
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 90
innodb_support_xa = 0
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
log_bin_trust_function_creators = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 2000
innodb_file_format = Barracuda
innodb_purge_threads=1
innodb_purge_batch_size = 32
innodb_old_blocks_pct=75
innodb_change_buffering=all
innodb_stats_on_metadata=OFF
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
