MySQL 8.0 主从

1 环境规划

ip		端口	名称
10.1.1.1	8.0	3306	Master
10.1.1.2	8.0	3306	Slave

2 搭建 Mysql

2.1 Master

2.1.1 目录结构

tree -L 1
.
├── conf
|    └── master.cnf
├── data
├── docker-compose.yml
└── logs

2.1.2 Master 配置

cat conf/master.cnf
[mysqld]
skip-name-resolve #禁用DNS查找,不使用域名开启这个
max_connections=2000 #连接数
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

#gtid:
server_id=1                   #服务器id
gtid_mode=on                  #开启gtid模式
enforce_gtid_consistency=on   #强制gtid一致性，开启后对于特定create table不被支持

#binlog
log_bin=mysql-binlog
log_slave_updates=on
binlog_format=row
skip_slave_start=1
log_replica_updates=ON
skip_replica_start=ON

#mem 0.75*
innodb_buffer_pool_size = 12884901888
innodb_flush_log_at_trx_commit = 0

# cpu num
innodb_parallel_read_threads = 8
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8

2.1.3 Docker-compose 配置

cat docker-compose.yml
services:
  mysql-master:
    image: swr.cn-east-3.myhuaweicloud.com/plugins/mysql:8.0
    container_name: mysql-master
    network_mode: host
    command:
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
    expose:
      - "3306"
    volumes:
      - ./data:/var/lib/mysql
      - ./conf:/etc/mysql/conf.d
      - ./logs:/var/log/mysql
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: hXG7CscMWKzrhpTx
      MYSQL_DATABASE: nacos_config
      MYSQL_USER: gouyouhui
      MYSQL_PASSWORD: BNk6J3pHCtEdEsXf
      LANG: C.UTF-8
    healthcheck:
      test: [ "CMD", "mysqladmin" ,"ping", "-h", "localhost" ]
      interval: 5s
      timeout: 10s
      retries: 10
  mysqld_exporter:
    image: registry.bluethink.cn:5001/mysqld-exporter:v0.17.2
    container_name: master-mysqld_exporter
    hostname: app-mysqld_exporter
    network_mode: host
    restart: unless-stopped
    volumes:
      - /usr/share/zoneinfo/PRC:/etc/localtime
    environment:
      MYSQLD_EXPORTER_PASSWORD: hXG7CscMWKzrhpTx
    entrypoint:
      - /bin/mysqld_exporter
      - --mysqld.address=192.168.1.135:3306
      - --mysqld.username=mysqld_exporter
      - --collect.info_schema.innodb_metrics
      - --collect.info_schema.tables
      - --collect.info_schema.processlist
      - --collect.info_schema.tables.databases=*

2.1.4 启动并查看

docker-compose up -d
docker exec -it mysql-master bash
root@mysql-master:/# mysql -uroot -p
mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 1     |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.01 sec)

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-binlog.000013
         Position: 783964
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 773cdc71-2fa1-11f0-a510-3a61321a98f9:1-3137
1 row in set (0.00 sec)

2.2 Slave

2.3 目录结构

tree -L 1
.
├── conf
|    └── slave.cnf
├── data
├── docker-compose.yml
└── logs

2.3.1 Slave 配置

cat conf/slave.cnf
[mysqld]
max_connections=2000
skip-name-resolve
innodb_numa_interleave=OFF
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

#GTID:
server_id=2                  #服务器id
gtid_mode=on                 #开启gtid模式
enforce_gtid_consistency=on  #强制gtid一致性，开启后对于特定create table不被支持

#binlog
log_bin =mysql-binlog
log_slave_updates=on
binlog_format=row

#relay log
skip_slave_start=1
read_only=on                   #设置只读

#mem 0.75*
innodb_buffer_pool_size = 12884901888
innodb_flush_log_at_trx_commit = 0

# cpu num
innodb_parallel_read_threads = 8
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8

2.3.2 Docker-compose 配置

跟 Master 配置基本一样, 改下名字

cat docker-compose.yml
services:
  mysql-master:
    image: swr.cn-east-3.myhuaweicloud.com/plugins/mysql:8.0
    container_name: mysql-slave
......

2.3.3 启动

docker exec -it mysql-slave bash
root@mysql-slave:/# mysql -uroot -p
mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 2     |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.00 sec)

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-binlog.000001
         Position: 157
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

3 配置主从同步

3.1 主库添加账号

# 监控账号
docker exec -it mysql-master bash
CREATE USER 'mysqld_exporter'@'192.168.1.%' IDENTIFIED BY 'hXG7CscMWKzrhpTx' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'192.168.1.135';
# 主从同步账号
CREATE USER 'bluethink-mysql'@'192.168.1.55' IDENTIFIED WITH mysql_native_password BY 'BNk6JpHCtEdEsXf'; 
GRANT REPLICATION SLAVE ON *.* TO 'bluethink-mysql'@'192.168.1.55';
SELECT User, Host FROM mysql.user;
+------------------+---------------+
| User             | Host          |
+------------------+---------------+
| mysqld_exporter  | 192.168.1.135 |
| bluethink-mysql  | 192.168.1.55  |

3.2 从库配置主从同步

docker exec -it mysql-slave bash
CHANGE MASTER TO master_host='192.168.1.135', master_port=3306, master_user='bluethink-mysql', master_password='BNk6JpHCtEdEsXf', master_auto_position=1;
Query OK, 0 rows affected, 8 warnings (0.09 sec)

mysql> RESET SLAVE;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.03 sec)

3.2.1 查看状态

SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to source
                  Master_Host: 192.168.1.135
                  Master_User: bluethink-mysql
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: redis-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

4 备份

4.1 安装 xtrabackup

注意版本

这里备份的版本是 MySQL 8.0

wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.32-26/binary/redhat/9/x86_64/percona-xtrabackup-80-8.0.32-26.1.el9.x86_64.rpm
yum install percona-xtrabackup-80-8.0.32-26.1.el9.x86_64.rpm

4.2 准备备份账号

• RELOAD 开始复制文件之前, 使用备份锁时需要此权限。
• REPLICATION CLIENT 为了获得二进制日志位置。
• CREATE TABLESPACE 以导入表（请参见还原单个表）。
• PROCESS 查看服务器上运行的所有线程 SHOW ENGINE INNODB STATUS
• SUPER 为了在复制环境中启动/停止副本线程，使用 XtraDB 更改页跟踪进行增量备份和改进的 FLUSH 表以及读锁定处理。
• CREATE 创建 PERCONA_SCHEMA.xtrabackup_history 数据库和表。
• ALTER 升级 PERCONA_SCHEMA.xtrabackup_history 数据库和表。
• INSERT 历史记录添加到 PERCONA_SCHEMA.xtrabackup_history 表中。
• SELECT 查找 innobackupex –incremental-history-name 或 innobackupex –incremental-history-uuid

CREATE USER 'gouyouhui_backup'@'192.168.1.%' IDENTIFIED BY '8WeRYA2BQkBPFzNw';
GRANT BACKUP_ADMIN, RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'gouyouhui_backup'@'192.168.1.%';
GRANT SELECT ON performance_schema.* TO 'gouyouhui_backup'@'192.168.1.%';
FLUSH PRIVILEGES;

4.3 备份

• 备份 master 的话可能对 master 性能产生影响，但恢复的时候不会有任何问题
• 备份 slave 节点的话不会对 master 性能产生影响，但是恢复的话如果可以与 master 通信会自动加入到主从，数据可能会有问题，不过可以在备份的时候先暂停下主从同步

4.3.1 全量备份

这里选择备份 Master

xtrabackup --backup \
--host=192.168.1.135 \
--user=gouyouhui_backup \
--password=8WeRYA2BQkBPFzNw \
--datadir=/data/server/mysql/data \
--target-dir=/data/mysql_backup`date +%F` \
--log_bin=/data/server/mysql/data/ \
--log-bin-index=/data/server/mysql/data/mysql-binlog.index

4.3.2 增量备份

xtrabackup --backup \
--host=192.168.1.135 \
--user=gouyouhui_backup \
--password=8WeRYA2BQkBPFzNw \
--datadir=/data/server/mysql/data \
--target-dir=/data/mysql_backup \
--log_bin=/data/server/mysql/data/ \
--log-bin-index=/data/server/mysql/data/mysql-binlog.index
--incremental: /data/mysql_backup/incremental
--incremental-basedir: /data/mysql_backup`date +%F`