部署 MySQL/Mariadb 主从复制

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

一、实验环境

• 作业系统：CentOS 7.7
• mariadb version:10.5.0

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