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 目录结构#

1
tree -L 1
2
.
3
├── conf
4
|    └── master.cnf
5
├── data
6
├── docker-compose.yml
7
└── logs

2.1.2 Master 配置#

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

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

12
#binlog
13
log_bin=mysql-binlog
14
log_slave_updates=on
15
binlog_format=row
16
skip_slave_start=1
17
log_replica_updates=ON
18
skip_replica_start=ON
19

20
#mem 0.75*
21
innodb_buffer_pool_size = 12884901888
22
innodb_flush_log_at_trx_commit = 0
23

24
# cpu num
25
innodb_parallel_read_threads = 8
26
replica_parallel_type = LOGICAL_CLOCK
27
replica_parallel_workers = 8
28
slave_parallel_type = LOGICAL_CLOCK
29
slave_parallel_workers = 8

2.1.3 Docker-compose 配置#

1
cat docker-compose.yml
2
services:
3
  mysql-master:
4
    image: swr.cn-east-3.myhuaweicloud.com/plugins/mysql:8.0
5
    container_name: mysql-master
6
    network_mode: host
7
    command:
8
      --character-set-server=utf8mb4
9
      --collation-server=utf8mb4_general_ci
10
      --explicit_defaults_for_timestamp=true
11
      --lower_case_table_names=1
12
    expose:
13
      - "3306"
14
    volumes:
15
      - ./data:/var/lib/mysql
16
      - ./conf:/etc/mysql/conf.d
17
      - ./logs:/var/log/mysql
18
    environment:
19
      TZ: Asia/Shanghai
20
      MYSQL_ROOT_PASSWORD: hXG7CscMWKzrhpTx
21
      MYSQL_DATABASE: nacos_config
22
      MYSQL_USER: gouyouhui
23
      MYSQL_PASSWORD: BNk6J3pHCtEdEsXf
24
      LANG: C.UTF-8
25
    healthcheck:
26
      test: [ "CMD", "mysqladmin" ,"ping", "-h", "localhost" ]
27
      interval: 5s
28
      timeout: 10s
29
      retries: 10
30
  mysqld_exporter:
31
    image: registry.bluethink.cn:5001/mysqld-exporter:v0.17.2
32
    container_name: master-mysqld_exporter
33
    hostname: app-mysqld_exporter
34
    network_mode: host
35
    restart: unless-stopped
36
    volumes:
37
      - /usr/share/zoneinfo/PRC:/etc/localtime
38
    environment:
39
      MYSQLD_EXPORTER_PASSWORD: hXG7CscMWKzrhpTx
40
    entrypoint:
41
      - /bin/mysqld_exporter
42
      - --mysqld.address=192.168.1.135:3306
43
      - --mysqld.username=mysqld_exporter
44
      - --collect.info_schema.innodb_metrics
45
      - --collect.info_schema.tables
46
      - --collect.info_schema.processlist
47
      - --collect.info_schema.tables.databases=*

2.1.4 启动并查看#

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

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

2.2 Slave#

2.3 目录结构#

1
tree -L 1
2
.
3
├── conf
4
|    └── slave.cnf
5
├── data
6
├── docker-compose.yml
7
└── logs

2.3.1 Slave 配置#

1
cat conf/slave.cnf
2
[mysqld]
3
max_connections=2000
4
skip-name-resolve
5
innodb_numa_interleave=OFF
6
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
7

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

13
#binlog
14
log_bin =mysql-binlog
15
log_slave_updates=on
16
binlog_format=row
17

18
#relay log
19
skip_slave_start=1
20
read_only=on                   #设置只读
21

22
#mem 0.75*
23
innodb_buffer_pool_size = 12884901888
24
innodb_flush_log_at_trx_commit = 0
25

26
# cpu num
27
innodb_parallel_read_threads = 8
28
replica_parallel_type = LOGICAL_CLOCK
29
replica_parallel_workers = 8
30
slave_parallel_type = LOGICAL_CLOCK
31
slave_parallel_workers = 8

2.3.2 Docker-compose 配置#

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

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

2.3.3 启动#

1
docker exec -it mysql-slave bash
2
root@mysql-slave:/# mysql -uroot -p
3
mysql> show variables like '%server_id%';
4
+----------------+-------+
5
| Variable_name  | Value |
6
+----------------+-------+
7
| server_id      | 2     |
8
| server_id_bits | 32    |
9
+----------------+-------+
10
2 rows in set (0.00 sec)
11

12
mysql> SHOW MASTER STATUS\G
13
*************************** 1. row ***************************
14
             File: mysql-binlog.000001
15
         Position: 157
16
     Binlog_Do_DB:
17
 Binlog_Ignore_DB:
18
Executed_Gtid_Set:
19
1 row in set (0.00 sec)

3 配置主从同步#

3.1 主库添加账号#

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

3.2 从库配置主从同步#

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

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

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

3.2.1 查看状态#

1
SHOW SLAVE STATUS\G
2
*************************** 1. row ***************************
3
               Slave_IO_State: Connecting to source
4
                  Master_Host: 192.168.1.135
5
                  Master_User: bluethink-mysql
6
                  Master_Port: 3306
7
                Connect_Retry: 60
8
              Master_Log_File:
9
          Read_Master_Log_Pos: 4
10
               Relay_Log_File: redis-relay-bin.000001
11
                Relay_Log_Pos: 4
12
        Relay_Master_Log_File:
13
             Slave_IO_Running: Yes
14
            Slave_SQL_Running: Yes

4 备份#

4.1 安装 `xtrabackup`#

[!attention] 注意版本这里备份的版本是 MySQL 8.0

1
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
2
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

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

4.3 备份#

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

4.3.1 全量备份#

这里选择备份 Master

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

4.3.2 增量备份#

1
xtrabackup --backup \
2
--host=192.168.1.135 \
3
--user=gouyouhui_backup \
4
--password=8WeRYA2BQkBPFzNw \
5
--datadir=/data/server/mysql/data \
6
--target-dir=/data/mysql_backup \
7
--log_bin=/data/server/mysql/data/ \
8
--log-bin-index=/data/server/mysql/data/mysql-binlog.index
9
--incremental: /data/mysql_backup/incremental
10
--incremental-basedir: /data/mysql_backup`date +%F`

1 环境规划#

2 搭建 Mysql#

2.1 Master#

2.1.1 目录结构#

2.1.2 Master 配置#

2.1.3 Docker-compose 配置#

2.1.4 启动并查看#

2.2 Slave#

2.3 目录结构#

2.3.1 Slave 配置#

2.3.2 Docker-compose 配置#

2.3.3 启动#

3 配置主从同步#

3.1 主库添加账号#

3.2 从库配置主从同步#

3.2.1 查看状态#

4 备份#

4.1 安装 `xtrabackup`#

4.2 准备备份账号#

4.3 备份#

4.3.1 全量备份#

4.3.2 增量备份#

文章分享

评论区

音乐

文章目录

1 环境规划#

2 搭建 Mysql#

2.1 Master#

2.1.1 目录结构#

2.1.2 Master 配置#

2.1.3 Docker-compose 配置#

2.1.4 启动并查看#

2.2 Slave#

2.3 目录结构#

2.3.1 Slave 配置#

2.3.2 Docker-compose 配置#

2.3.3 启动#

3 配置主从同步#

3.1 主库添加账号#

3.2 从库配置主从同步#

3.2.1 查看状态#

4 备份#

4.1 安装 xtrabackup#

4.2 准备备份账号#

4.3 备份#

4.3.1 全量备份#

4.3.2 增量备份#

文章分享

评论区

音乐

文章目录

4.1 安装 `xtrabackup`#