MySQL备份与恢复

备份类型

完全备份：备份整个数据集

部分备份：只备份数据子集（如部分库或表）

增量备份：仅备份最近一次完全备份或增量备份以来变化的数据，备份较快，还原复杂

差异备份：仅备份最近一次完全备份以来变化的数据，备份较快，还原简单

冷备份与还原

需要停止数据库服务

备份步骤：停止数据库服务–>备份数据库文件、配置、日志

还原步骤：安装数据库服务（最好版本一致）–>复制相应文件（数据文件、配置文件、日志文件）并保留属性–>启动服务

mysql 8.0:

1
2
3
4
5
6
## 备份
[root@Rocky8-11 ~]# systemctl stop mysqld
[root@Rocky8-11 ~]# mkdir /backup
[root@Rocky8-11 ~]# rsync -av /var/lib/mysql 10.0.0.11:/backup/
[root@Rocky8-11 ~]# rsync -arv /etc/my*  10.0.0.11:/backup/
[root@Rocky8-11 ~]# rsync -arv /data/binlog  10.0.0.11:/backup/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
## 还原
[root@Rocky8-12 ~]# yum install -y mysql-server
[root@Rocky8-12 ~]# rsync -arv 10.0.0.11:/backup/mysql/ /var/lib/mysql/
[root@Rocky8-12 ~]# rsync -arv 10.0.0.11:/backup/my.cnf* /etc/
[root@Rocky8-12 ~]# rsync -arv 10.0.0.11:/backup/binlog /data/
[root@Rocky8-12 ~]# systemctl enable --now mysqld
[root@Rocky8-12 ~]# ss -ntl 
[root@Rocky8-12 ~]# mysql 
01:43:07(root@localhost) [(none)] show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.01 sec)

mariadb 10.3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
## 备份+还原（源主机：Rocky8-12  目标主机：Rocky8-13）
[root@Rocky8-13 ~]# dnf install -y  mariadb-server
[root@Rocky8-13 ~]# mkdir -p /data/binlog

[root@Rocky8-12 ~]# systemctl stop mariadb
[root@Rocky8-12 ~]# rsync -arv /etc/my.cnf*  10.0.0.13:/etc/
[root@Rocky8-12 ~]# rsync -arv /var/lib/mysql/ 10.0.0.13:/var/lib/mysql/
[root@Rocky8-12 ~]# rsync -arv /data/binlog/ 10.0.0.13:/data/binlog

[root@Rocky8-13 data]# chown -R mysql.mysql /var/lib/mysql/
[root@Rocky8-13 data]# chown -R mysql.mysql /data/binlog/
[root@Rocky8-13 data]# systemctl start mariadb
[root@Rocky8-13 data]# mysql 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.35-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

MysqlDump工具

1
2
3
4
5
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases
OR     mysqldump [OPTIONS] --system=[SYSTEMOPTIONS]]

常用参数：

-u, –user=name 登录用户名

-p, –password[=name] 登录密码

-A, –all-databases 备份所有数据库

-B, –databases 备份指定数据库

-E, –events 备份相关的所有事件调度器（计划任务）

-R, –routines 备份存储过程和自定义函数

–master-data[=#] 默认为=1，=1 适合于主从复制（数据之前加一个非注释的CHANGE MASTER TO语句） =2适合于单机备份还原（记录为被注释的CHANGE MASTER TO语句）CHANGE MASTER TO 记录的为备份时间点的二进制日志位置

-F, –flush-logs 备份前生成新的二进制文件

建议备份策略：

innoDB

1
mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob>${BACKuP}/fu11bak_${BACKUP_TIME}.sql

myisam

1
mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges  --triggers --defau1t-character-set=utf8 --hex-b1ob >${BACKUP}/fu11bak_${BACKUP_TIME}.sql

例一：完全备份和还原（冷备份）

备份

1
2
3
4
5
6
7
[root@Rocky8-11 ~]# cat /etc/my.cnf
[mysqld]
log_bin=/data/binlog/mysql-bin
[root@Rocky8-11 ~]# mkdir /backup
[root@Rocky8-11 ~]# mysqldump -u root -A -F --single-transaction --master-data=2 | gzip > /backup/all-`date +%F`.sql.gz
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
[root@Rocky8-11 ~]# scp /backup/all-2023-05-25.sql.gz 10.0.0.16:/backup

还原

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[root@Rocky8-16 ~]# yum -y install mysql-server
[root@Rocky8-16 ~]# systemctl start mysqld
[root@Rocky8-16 ~]# gzip -d /backup/all-2023-05-25.sql.gz
[root@Rocky8-16 ~]# mysql 
mysql> set sql_log_bin=off;
mysql> source /backup/all-2023-05-25.sql
mysql> set sql_log_bin=on;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.01 sec)

例二：利用二进制日志，恢复误删除的表

误删除数据

二进制日志文件独立位置

1
2
3
[root@Rocky8-11 ~]# cat /etc/my.cnf
[mysqld]
log_bin=/data/binlog/mysql-bin

完全备份

1
2
3
4
5
6
[root@Rocky8-11 ~]# mysqldump -uroot -p -A -F --single-transaction --master-data=2  > /backup/all-backup-`date +%F`.sql
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
Enter password: 
[root@Rocky8-11 ~]# ll /backup/
total 1264
-rw-r--r-- 1 root root 1292875 May 25 21:02 all-backup-2023-05-25.sql

完全备份后数据库继续更新

1
2
3
4
5
09:03:45(root@localhost) [hellodb] insert students (name,age,gender) value ('x1',20,'m');
Query OK, 1 row affected (0.01 sec)

09:03:56(root@localhost) [hellodb] insert students (name,age,gender) value ('y2',22,'m');
Query OK, 1 row affected (0.00 sec)

破坏数据库，误删除了一张表

1
2
09:04:05(root@localhost) [hellodb] drop table students;
Query OK, 0 rows affected (0.09 sec)

其它表继续更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
09:05:34(root@localhost) [hellodb] insert teachers (name,age,gender) values ('A1',30,'M');
Query OK, 1 row affected (0.01 sec)

09:08:38(root@localhost) [hellodb] insert teachers (name,age,gender) values ('b2',33,'M');
Query OK, 1 row affected (0.01 sec)

09:09:04(root@localhost) [hellodb] select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | A1            |  30 | M      |
|   6 | b2            |  33 | M      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

还原过程

从最新的完全备份中找到change master to 位置

1
2
[root@Rocky8-11 ~]# grep '\-\- CHANGE MASTER TO'  /backup/all-backup-2023-05-25.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157;

备份完全备份之后的二进制日志

1
[root@Rocky8-11 ~]# mysqlbinlog --start-position=157 /data/binlog/mysql-bin.000003 > /backup/binlog.sql

找到误操作语句，从备份中删除，若文件过大可以用sed工具：sed -i.bak ‘/^DROP TABLE/d’ /backup/binlog.sql

1
2
 [root@Rocky8-11 ~]# vim /backup/binlog.sql 
### DROP TABLE `students` /* generated by server */

测试还原

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
68
mysql> set sql_log_bin=0;
mysql> source all-backup-2023-05-25.sql
mysql> source binlog.sql
mysql> set sql_log_bin=1;

mysql> use hellodb
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | x1            |  20 | M      |    NULL |      NULL |
|    27 | y2            |  22 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)

mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | A1            |  30 | M      |
|   6 | b2            |  33 | M      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

mysql>

XtraBackup备份工具

官网：https://www.percona.com/downloads

特点：

  • 备份还原过程快速、可靠
  • 备份过程不会打断正在执行的事务
  • 能够基于压缩等功能节约磁盘空间和流量
  • 自动实现备份检验
  • 开源、免费

安装：

1
[root@Rocky8-11 ~]# yum install -y percona-xtrabackup-80-8.0.33-27.1.el8.x86_64.rpm

注意：

  • 备份文件父路径必须存在
  • 还原时必须停止数据库服务
  • 还原时目标数据目录必须为空，除非指定innobackuppex –force-non-empty-directorires，否则–copy-back不会覆盖
  • 还原后文件属性会被保留，须调整文件属主属组

案例：利用xtrabackup，实现数据库增量备份及还原

备份：

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
[root@Rocky8-11 ~]# mysql 
03:31:17(root@localhost) [(none)] show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

## 安装，创建备份目录
[root@Rocky8-11 ~]# yum install -y percona-xtrabackup-80-8.0.33-27.1.el8.x86_64.rpm
[root@Rocky8-11 ~]# mkdir /backup

## 完全备份
[root@Rocky8-11 ~]# xtrabackup  -uroot -p  --backup --target-dir=/backup/base
[root@Rocky8-11 ~]# ll /backup
total 4
drwxr-x--- 6 root root 4096 May 26 15:34 base

## 第一次增加修改数据
03:36:18(root@localhost) [hellodb] insert students (name,age,gender) value ('X1',20,'M');
Query OK, 1 row affected (0.01 sec)

03:36:33(root@localhost) [hellodb] insert students (name,age,gender) value ('Y1',23,'M');
Query OK, 1 row affected (0.00 sec)

## 第一次增量备份，备份目录/backup/inc1,相对于/backup/base做增量备份
[root@Rocky8-11 ~]# xtrabackup  -uroot -p  --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
[root@Rocky8-11 ~]# ll /backup
total 8
drwxr-x--- 6 root root 4096 May 26 15:34 base
drwxr-x--- 6 root root 4096 May 26 15:39 inc1

## 第二次增加修改数据
03:42:03(root@localhost) [hellodb] insert teachers (name,age,gender) values ('A1',30,'M');
Query OK, 1 row affected (0.02 sec)

03:42:57(root@localhost) [hellodb] insert teachers (name,age,gender) values ('B2',36,'F');
Query OK, 1 row affected (0.01 sec)

## 第二次增量备份，备份目录/backup/inc2,相对于/backup/inc1做增量备份
[root@Rocky8-11 ~]# xtrabackup  -uroot -p  --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
[root@Rocky8-11 ~]# ll /backup
total 12
drwxr-x--- 6 root root 4096 May 26 15:34 base
drwxr-x--- 6 root root 4096 May 26 15:39 inc1
drwxr-x--- 6 root root 4096 May 26 15:44 inc2

## 可以查看备份目录的文件信息：（innodb_from_lsn、innodb_to_lsn）
[root@Rocky8-11 ~]# cat /backup/base/xtrabackup_info 
uuid = ba0a28c7-fb97-11ed-985c-000c29bb093a
name = 
tool_name = xtrabackup
tool_command = -uroot -p=... --backup --target-dir=/backup/base
tool_version = 8.0.33-27
ibbackup_version = 8.0.33-27
server_version = 8.0.32
start_time = 2023-05-26 15:34:17
end_time = 2023-05-26 15:34:19
lock_time = 0
binlog_pos = filename 'binlog.000003', position '157'
innodb_from_lsn = 0
innodb_to_lsn = 19586280
partial = N
incremental = N
format = file
compressed = N
encrypted = N

[root@Rocky8-11 ~]# cat /backup/inc1/xtrabackup_info 
uuid = 615f869f-fb98-11ed-985c-000c29bb093a
name = 
tool_name = xtrabackup
tool_command = -uroot -p=... --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
tool_version = 8.0.33-27
ibbackup_version = 8.0.33-27
server_version = 8.0.32
start_time = 2023-05-26 15:38:57
end_time = 2023-05-26 15:39:00
lock_time = 0
binlog_pos = filename 'binlog.000004', position '157'
innodb_from_lsn = 19586280
innodb_to_lsn = 19587049
partial = N
incremental = Y
format = file
compressed = N
encrypted = N

[root@Rocky8-11 ~]# cat /backup/inc2/xtrabackup_info 
uuid = 298b2cfc-fb99-11ed-985c-000c29bb093a
name = 
tool_name = xtrabackup
tool_command = -uroot -p=... --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
tool_version = 8.0.33-27
ibbackup_version = 8.0.33-27
server_version = 8.0.32
start_time = 2023-05-26 15:44:34
end_time = 2023-05-26 15:44:36
lock_time = 0
binlog_pos = filename 'binlog.000005', position '157'
innodb_from_lsn = 19587049
innodb_to_lsn = 19590016
partial = N
incremental = Y
format = file
compressed = N
encrypted = N

还原：

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
[root@Rocky8-11 ~]# rsync  -avr /backup/ 10.0.0.12:/backup/

## 安装软件包，不启动mysql服务
[root@Rocky8-12 ~]# yum install -y mysql-server
[root@Rocky8-12 ~]# yum install -y percona-xtrabackup-80-8.0.33-27.1.el8.x86_64.rpm

## 预准备完全备份，--apply-log-only 阻止回滚未完成的事务
[root@Rocky8-12 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base

## 合并第一次增量备份到完全备份
[root@Rocky8-12 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1

## 合并第二次增量备份到完全备份，最后一次增量备份不需要加选项--apply-log-only
[root@Rocky8-12 ~]# xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2

## 将文件复制到数据目录
[root@Rocky8-12 ~]# rm -f /var/lib/mysql/*
[root@Rocky8-12 ~]# xtrabackup --copy-back --target-dir=/backup/base
[root@Rocky8-12 ~]# chown -R mysql.mysql /var/lib/mysql
[root@Rocky8-12 ~]# systemctl start mysqld

## 检查还原的数据 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | A1            |  30 | M      |
|   6 | B2            |  36 | F      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | X1            |  20 | M      |    NULL |      NULL |
|    27 | Y1            |  23 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.01 sec)

MySQL
#MariaDB #MySQL #Backup #XtraBackup #MysqlDump
MySQL备份与恢复
https://www.xcjyc.top/2023/05/17/MySQL备份与恢复/
作者
XCJYC
发布于
2023年5月17日
许可协议