• 使用 Percona 的 XtraBackup 对数据库进行物理备份
  • 在从库进行数据恢复
  • 根据备份数据获取 binlog 具体位置，在从库配置拉取日志开始位置，进行主从同步

安装 xtrabackup

安装

1
2

wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.32-26/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.32-26.1.el7.x86_64.rpm
yum -y install percona-xtrabackup-80-8.0.32-26.1.el8.x86_64.rpm

创建备份用户

  • 创建 xtrabackup 生成全备用户
1
2

create user 'xtrabackup_user'@'localhost' identified by 'backupUserPasswd';
GRANT SELECT, RELOAD, PROCESS, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT,REPLICATION SLAVE,BACKUP_ADMIN ,EVENT ON *.* TO 'xtrabackup_user'@'localhost' ;
  • 创建从库同步用户
1
2

create user 'backup_user'@'ip地址' identified by 'slaveBkPass';
GRANT SELECT, RELOAD, PROCESS, SHOW DATABASES, LOCK TABLES, SUPER, REPLICATION CLIENT,REPLICATION SLAVE, EVENT ON *.* TO 'backup_user'@'ip地址' ;

备份数据

  • 利用 xtrabackup 不停机进行物理备份

  • 如果已有全备、增倍数据，可直接利用全备、增备操作，无需备份

  • 生成全备数据

    • –defaults-file：指定配置文件路径
    • –target-dir：指定全备目录(相当于完全复制mysql数据文件，需保证磁盘空间充足)
1

$ xtrabackup --defaults-file=/data/mysql/mysql_data/my_3306.cnf --backup --user='xtrabackup_user' --password='backupUserPasswd'  --target-dir=./backupfile

基于全备在从库恢复数据

  • 将数据发送至从库(需保证从库服务器磁盘空间充足)
  • 如果基于全备、增备恢复参照：https://blog.xiangy.cloud/post/mysql5.7-flashback/#%E5%9F%BA%E4%BA%8E%E5%85%A8%E5%A4%87%E6%81%A2%E5%A4%8D
1

$ scp -r backupfile root@192.168.1.131:/usr/local/platform
  • 整理数据文件
1

$ xtrabackup --prepare --target-dir=backupfile/
  • 删除旧数据、移入新数据
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# 删除安装数据库初始化时生成的旧数据
$ rm -rf mysql_data/data/*
# 删除 之前(安装mysql时)的 redo log 日志
$ rm -rf mysql_log/innodb_log/*
# 删除 之前(安装mysql时)的 bin log 日志
$ rm -rf mysql_log/bin_log/mysql-bin.*
# 拷贝数据文件及 redo log
$ xtrabackup --defaults-file=/data/mysql/mysql_data/my_3306.cnf --copy-back  --target-dir=./backupfile
# 更改权限
$ chown  mysql.mysql -R mysql_data/data
$ chown  mysql.mysql -R mysql_log/innodb_log
$ chown  mysql.mysql -R mysql_log/bin_log
  • 更改从库配置文件
1
2
3
4
5

[mysqld]
# 配置普通用户只读
read_only=1
# 配置超级用户只读
super_read_only=1
  • 开启从库 MySQL
1

./start_3306.sh

开启同步

PLAN1(old)

  • 基于 binlog 日志及 position id，配置同步进度
  • 获取 binlog position 位置
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

$  cat ~/backupfile/xtrabackup_info
uuid = 03bfbfa7-ddec-11ed-b957-000c29c15bdf
name =
tool_name = xtrabackup
tool_command = --defaults-file=/data/mysql/mysql_data/my_3306.cnf --backup --user=xtrabackup_user --password=... --target-dir=./backupfile
tool_version = 8.0.32-26
ibbackup_version = 8.0.32-26
server_version = 8.0.32
start_time = 2023-04-18 09:22:02
end_time = 2023-04-18 09:22:06
lock_time = 1
binlog_pos = filename 'mysql-bin.000003', position '197', GTID of the last change 'f579e88f-dddc-11ed-9daf-000c29c15bdf:1-61'
innodb_from_lsn = 0
innodb_to_lsn = 46819811
partial = N
incremental = N
format = file
compressed = N
encrypted = N
  • 从库设置从库从日志点同步
1
2
3
4
5
6
7

mysql> change master to MASTER_HOST='172.16.2.181',  -- 设置主库 IP
    -> MASTER_PORT=3306,                              -- 设置主库 端口
    -> MASTER_USER='backup_user',                     -- 主库配置的同步用户
    -> MASTER_PASSWORD='slaveBkPass',                 -- 主库配置的同步用户的密码
    -> MASTER_LOG_FILE='mysql-bin.000003',            -- xtrabackup_info文件中 binlog_pos 的 的filename 
    -> MASTER_LOG_POS=197,                            -- xtrabackup_info文件中 binlog_pos 的 position
    -> get_master_public_key=1;                       -- 自动获取 pem，加密为 caching_sha2_password 时可保证正常连接。
  • 开启从库同步
1

mysql> start slave;

PLAN2(new)

  • 基于 GTID 复制

说明

  • MySQL5.6 之前的版本只支持传统复制
  • MySQL5.6 之后版本支持基于 GTID 复制，利用 GTID 自动定位的特性，不需要二进制日志的位置复制

配置

  • 清空从库 GTID 信息。
1

reset master;
  • 从库指定备份的主库的 GTID
    • xtrabackup_info 文件中 GTID of the last change 的值
1
2
3
4

SET GLOBAL GTID_PURGED='f579e88f-dddc-11ed-9daf-000c29c15bdf:1-61';

-- PS：极端情况下，master 可能会有多个 GTID，从库需 都配置上，否则主从切换时会出现问题。如
SET GLOBAL GTID_PURGED='838eb7eb-bc9a-11eb-baaf-0800270141f0:1-255044,bca9ac2e-aa0a-11e9-ac0c-005056b71124:1-6304';
  • 从库配置主库信息
1
2
3
4
5
6

mysql> change master to MASTER_HOST='172.16.2.181',  -- 设置主库 IP
    -> MASTER_PORT=3306,                              -- 设置主库 端口
    -> MASTER_USER='backup_user',                     -- 主库配置的同步用户
    -> MASTER_PASSWORD='slaveBkPass',                 -- 主库配置的同步用户的密码
    -> master_auto_position=1 ,                       -- 开启自动 GTID 同步
    -> get_master_public_key=1;                       -- 自动获取 pem，加密为 caching_sha2_password 时可保证正常连接。
  • 开启同步
1

start slave;

查看同步状态

  • Slave_IO_Running、Slave_SQL_Running 均为 Yes，表示成功连接并开始同步
  • Seconds_Behind_Master 为 0 时，可认为同步完成
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.16.2.181
                  Master_User: backup_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 379
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 595
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 379
              Relay_Log_Space: 1055
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1681817659
                  Master_UUID: f579e88f-dddc-11ed-9daf-000c29c15bdf
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: f579e88f-dddc-11ed-9daf-000c29c15bdf:62-64
            Executed_Gtid_Set: f579e88f-dddc-11ed-9daf-000c29c15bdf:1-64
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 1
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

其他命令

  • 停止从库同步
1

stop slave;
  • 清除从库同步配置
1

RESET SLAVE ALL;