
本文主要介绍 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
- master节点
[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 半同步复制 #####
- slave1 节点
[mariadb] log-bin server_id=2 log-basename=slave1 binlog-format=mixed relay_log_purge=0 # 关闭中继日志 ##### 以下配置为开启 slave 半同步复制 ##### rpl_semi_sync_slave_enabled=ON ##### 以上配置为开启 slave 半同步复制 #####
- slave2 节点
[mariadb] log-bin server_id=3 log-basename=slave2 binlog-format=mixed relay_log_purge=0 # 关闭中继日志 ##### 以下配置为开启 slave 半同步复制 ##### rpl_semi_sync_slave_enabled=ON ##### 以上配置为开启 slave 半同步复制 #####
- 三台服务器都要重新启动 mariadb 服务
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;
- 在 Slave 节点上 启动 Slave
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