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

安装 xtrabackup

Centos7 安装

1
2
3

$ yum install -y perl-DBD-MySQL perl-Digest-MD5  perl-Digest libev-devel
$ yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
$ yum -y install percona-xtrabackup-24

创建备份用户

  • 创建 xtrabackup 生成全备用户
1

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

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

备份数据

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

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

  • 生成全备数据

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

$ xtrabackup --defaults-file=/usr/local/platform/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  --user-memory=1G --target-dir=backupfile/
  • 删除旧数据、移入新数据
1
2
3
4
5
6
7
8
9

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

[mysqld]
# 更改 server_id ，保证 server_id 唯一
server_id = 10001
# 配置普通用户只读
read_only=1
# 配置超级用户只读
super_read_only=1
  • 开启从库 MySQL

mysql_upgrade

1

mysql_upgrade  -uroot -hlocalhost -pRootPasswd  --socket=/usr/local/platform/mysql/mysql_data/data_3306/mysql.sock
  • 然后重启 MySQL

开启同步

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
20

$  cat /usr/local/platform/backupfile/xtrabackup_info
uuid = c290215b-c147-11eb-9e7f-0800270141f0
name =
tool_name = xtrabackup
tool_command = --defaults-file=/usr/local/platform/mysql/mysql_data/my_3306.cnf --backup --user=xtrabackup_user --password=... --target-dir=./backupfile
tool_version = 2.4.22
ibbackup_version = 2.4.22
server_version = 5.7.33-log
start_time = 2021-05-30 09:05:45
end_time = 2021-05-30 09:05:51
lock_time = 1
binlog_pos = filename 'mysql-bin.000007', position '577', GTID of the last change '838eb7eb-bc9a-11eb-baaf-0800270141f0:1-255044'
innodb_from_lsn = 0
innodb_to_lsn = 115112370
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
  • 从库设置从库从日志点同步
1
2
3
4
5
6

mysql> change master to MASTER_HOST='192.168.1.130',  -- 设置主库 IP
    -> MASTER_PORT=3306,                              -- 设置主库 端口
    -> MASTER_USER='backup_user',                     -- 主库配置的同步用户
    -> MASTER_PASSWORD='slaveBkPass',                 -- 主库配置的同步用户的密码
    -> MASTER_LOG_FILE='mysql-bin.000007',            -- xtrabackup_info文件中 binlog_pos 的 的filename 
    -> MASTER_LOG_POS=577;                            -- xtrabackup_info文件中 binlog_pos 的 position
  • 开启从库同步
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='838eb7eb-bc9a-11eb-baaf-0800270141f0:1-255044';

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

mysql> change master to MASTER_HOST='192.168.1.130',  -- 设置主库 IP
    -> MASTER_PORT=3306,                              -- 设置主库 端口
    -> MASTER_USER='backup_user',                     -- 主库配置的同步用户
    -> MASTER_PASSWORD='slaveBkPass',                 -- 主库配置的同步用户的密码
    -> master_auto_position=1 ;                       -- 开启自动 GTID 同步
  • 开启同步
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
66
67

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.130
                  Master_User: backup_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 1454
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 1197
        Relay_Master_Log_File: mysql-bin.000007
             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: 1454
              Relay_Log_Space: 1398
              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: 56109
                  Master_UUID: 838eb7eb-bc9a-11eb-baaf-0800270141f0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave 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: 838eb7eb-bc9a-11eb-baaf-0800270141f0:255045-255047
            Executed_Gtid_Set: 838eb7eb-bc9a-11eb-baaf-0800270141f0:1-255043:255045-255047
           -- GTID 同步模式
           Retrieved_Gtid_Set: 838eb7eb-bc9a-11eb-baaf-0800270141f0:255045-255047
            Executed_Gtid_Set: 838eb7eb-bc9a-11eb-baaf-0800270141f0:1-255047
e7ea1b31-bc9a-11eb-bf35-08002708f4a2:1
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

其他命令

  • 停止从库同步
1

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

RESET SLAVE ALL;

补充说明

Seconds_Behind_Master

  • 表示slave比master落后多少，如果这个值为0的表示主从已经处于一致了，实际上是SQL thread与IO thread之间的延迟，我们都知道在MySQL的复制环境中，slave先从master上将binlog拉取到本地（通过IO thread），然后通过SQL thread将binlog重放，而Seconds_Behind_Master表示本地relaylog中未被执行完的那部分的差