部署 MySQL/Mariadb 主从复制

MariaDB Foundation - MariaDB.org

本文主要介绍 MySQL/Mariadb 主从复制部署过程,主从复制原理请看这里

一、实验环境

  • 作业系统:CentOS 7.7
  • mariadb version:10.5.0
服务器角色IP地址主机名应用程式
Master172.50.1.107nacos-mha-s1mariadb
Slave1172.50.1.108nacos-mha-s2mariadb
Slave2172.50.1.109nacos-mha-s3mariadb

二、部署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
herui

一枚干着DevOps工作的职业命理师

发表评论

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据