一、数据库备份的类型

备份类型分类

按照服务器的运行状态,可分为:冷备、热备、温备。

  • 冷备:数据库处于停机状态下进行备份。

  • 热备:数据库处于运行状态下备份,在热备期间,数据库的读写操作均可正常进行。

    ​ 在mysql中,MyISAM存储引擎不支持热备,InnoDB存储引擎支持热备。

  • 温备:数据库处于运行状态下备份,但是在温备期间数据库只能进行读操作,不能进行写操作


按照备份后的内容量,可分为:全备、增备、差异备份。

  • 全量备份:对所有数据进行一个完全的备份。
  • 增备备份:对上一次”备份”以后变化的数据的备份。
  • 差异备份:对上一次”全量备份”以后变化的数据的备份。

按照备份的方式,可分为:逻辑备份、物理备份。

  • 逻辑备份:将数据从数据库中导出,并将导出的数据进行存档备份。

  • 物理备份:直接备份数据库所对应的数据文件。物理备份相对逻辑备份来说,性能更强。

 

备份方式优缺点

MySQL进行全量数据备份时,可以选择物理备份(如 Xtrabackup)和逻辑备份(如 mysqldump)两种方式。这两种备份方法各有优缺点,适用于不同的场景:

物理备份(Xtrabackup)

优点:

  1. 速度快:物理备份直接复制数据文件,对于大型数据库,备份和恢复的速度通常比逻辑备份快得多。
  2. 资源占用较低:进行物理备份时,对数据库的影响较小,不需要像逻辑备份那样执行大量SQL查询。
  3. 支持热备份:使用Xtrabackup可以在数据库运行时进行备份,不需要停机。

缺点:

  1. 平台依赖性:物理备份与MySQL的存储引擎和文件系统紧密相关,因此可能不易于跨平台恢复。
  2. 灵活性较低:物理备份通常备份整个数据库实例,不如逻辑备份那样容易实现对单个数据库或表的备份和恢复。

逻辑备份(mysqldump)

优点:

  1. 兼容性好:逻辑备份生成的是SQL语句,因此备份文件在不同平台和版本的MySQL之间具有很高的兼容性。
  2. 灵活性高:可以选择备份整个数据库、单个数据库或特定表。还可以通过SQL对备份数据进行筛选。
  3. 易于理解和修改:备份文件是纯文本的SQL语句,可以直接查看和编辑。

缺点:

  1. 速度慢:对于大型数据库,mysqldump可能会非常慢,因为它需要读取每一行数据并生成SQL语句。
  2. 资源消耗大:逻辑备份在运行时会对数据库性能产生影响,尤其是锁表操作会暂停对应表的读写。

如何选择

  • 对于大型生产环境,如果要求最小化对业务的影响,且备份和恢复速度非常关键,推荐使用物理备份方法(Xtrabackup)。
  • 对于小型数据库,或者在兼容性和灵活性要求较高的场景下,逻辑备份(mysqldump)是一个简单有效的选择。
  • 灾难恢复计划:在灾难恢复计划中,通常建议结合使用这两种备份方法,以平衡它们的优缺点,确保数据的安全和恢复的灵活性。

 

 

二、数据库备份方式

在数据库备份当中,一般会采用逻辑备份、物理备份这两种方式对数据库进行备份。

2.1 逻辑备份

基于SQL语句进行备份。

常见得逻辑备份方式:

  • 以binlog方式进行备份

  • 使用mysqldump备份工具备份

  • replication(数据库主从)

  • select into outfile (使用SQL语句导出数据到文件)

2.2 物理备份

基于数据文件的备份。

常见物理备份方式:

  • 使用xtrabackup备份工具备份

 

三、MySQL主流备份工具之mysqldump

mysqldump是MySQL自带的免费备份工具,是一种逻辑备份工具。

在MySQL 5.7中新增了一个mysqldump的改进版备份工具mysqlpump,它支持基于表级别的并行备份。

3.1 备份方法

  • 备份单表或者一组指定的库表
mysqldump [options] db_name [tbl_name ...]
  • 备份一组或者一个数据库下的所有表
mysqldump [options] --databases db_name ...
  • 备份整个数据库实例
mysqldump [options] --all-databases

3.2 mysqldump常用命令行选项

  • -A,–all-databases:备份所有数据库下的所有表。
  • -B,–databases:备份指定数据库下的所有表。
  • –master-data[=value]:则在备份文件中会生成一 条CHANGE MASTER TO语句(包含binlog pos,binlog file)
    • value=2:备份文件中生成的CHANGE MASTER TO语句将被注释
    • value=1:该语句不会被注释
    • value=0:不记录CHANGE MASTER TO语句
  • -F,–flush-logs:在开始备份之前刷新MySQL二进制日志文件。
  • -E,–events:在备份数据中包含数据库的事件。
  • –triggers:在备份文件中包含每个备份表的触发器。
  • –single-transaction:在备份数据之前开启一个事务快照,然后在事务快照内进行备份。
    • 结合–master-data实现热备
  • -d,–no-data:不备份表数据,只备份表结构。
  • -t,–no-create-info:只备份数据,不备份表结构。
  • -R,–routines:备份数据中存储过程和函数。

3.3 mysqldump备份注意

  • mysqldump在备份和恢复时都需要MySQL实例启动为前提
  • 一般数据量级100G以内,大约15-30分钟可以恢复(TB、PB、EB就需要考虑别的方式)
  • mysqldump是以覆盖的形式恢复数据的

3.4 实战:模拟完全备份和恢复

在备份源数据库中插入一些数据

mysql> select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | tset1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
| 6 | test6 |
+----+-------+
6 rows in set (0.00 sec)

mysql> insert into test(name) values("tset7"),("test8"),("test9"),("test10");
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
......
| 7 | test7 |
| 8 | test8 |
| 9 | test9 |
| 10 | test10 |
+----+--------+
10 rows in set (0.00 sec)

在远程备份服务器中使用mysqldump远程备份数据:

[root@db02 ~]# mkdir /data/backup/mysql/mysqldump -p
[root@db02 ~]# cd /data/backup/mysql/mysqldump/
[root@db02 mysqldump]# mysqldump -h 172.16.1.51 -uroot -p123 --single-transaction \
--master-data=2 --triggers --events -R -A > backup_`date +%F_%H_%M_%S`.sql
[root@db02 mysqldump]# ls -lh
total 347M
-rw-r--r-- 1 root root 347M Jun 19 23:37 backup_2020-06-19_23_37_03.sql

查看备份文件中的binlog pos位置、binlog文件:

[root@db02 mysqldump]# head -100 backup_2020-06-19_23_37_03.sql | grep -i CHANGE
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=911;

把备份文件导入恢复服务器的数据库实例中:

[root@db01 mysqldump]# mysql -uroot -p123 -e "reset master;"
[root@db01 mysqldump]# mysql -uroot -p123 < backup_2020-06-19_23_37_03.sql

登录恢复服务器中的数据库,校验数据:

mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | tset1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
| 6 | test6 |
| 7 | tset7 |
| 8 | test8 |
| 9 | test9 |
| 10 | test10 |
+----+--------+
10 rows in set (0.01 sec)

 

四、MySQL主流备份工具之XtraBackup

XtraBackup是一款开源的热备份软件(物理备份),它可以非阻塞地对InnoDB和XtraDB数据库进行备份。

XtraBackup的出现,解决了mysqldump在备份数据量大的数据库时,备份和恢复的时间高开销的问题。

4.1 XtraBackup的都优点

  • 快速可靠地完成备份。
  • 在备份期间不间断地处理事务。
  • 节省磁盘空间和网络带宽。
  • 自动备份验证。
  • 更快地恢复,以保障业务有更长的在线时间。

4.2 备份方式(物理备份)

  • 对于非innodb表(比如myisam)是直接锁表,cp数据文件,属于一种温备。
  • 对于innodb的表(支持事务),不锁表,cp数据页最终以数据文件方式保存下来,并且把redo和undo一并备走,属于热备方式。
  • 备份时读取配置文件/etc/my.cnf

4.3 下载安装XtraBackup软件

XtraBackup 的 RPM 包可去 Percona 官网下载相应的版本。

  • 安装依赖
yum install -y perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
  • 安装XtraBackup
yum install -y https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm

4.4 innobackupex命令常用选项

  • –user=USER:备份服务器数据库用户名
  • –password=PASSWORD:备份服务器数据库密码
  • –host=HOST:备份服务器的IP
  • –port=PORT:备份服务器的端口号
  • –no-timestamp:不在指定备份目录下创建以时间戳命名的目录
  • –defaults-file:指定默认的my.cnf配置文件
  • –apply-log:在指定备份目录下执行应用xtrabackup_logfile文件中的事务日志操作,同时根据配置文件中的innodb_log_file_size系统参数生成新的事务日志
  • –redo-only:针对未提交的事务不执行回滚操作,结合–apply-log一起使用
  • –copy-back:会从指定目录中读取数据文件,并复制到目标数据库的数据目录中
  • –incremental:告诉xtrabackup创建一个增量备份,而不是一个完整的备份
  • –incremental-basedir=DIRECTORY:与–incremental一同使用,指定完全备份或上一次增量备份的路径
  • –incremental-dir=DIRECTORY:项用于在应用redo日志时指定一个增量备份目录

4.5 实战:模拟完全备份与恢复

使用innobackupex命令完全备份数据库文件,并将备份文件放在/data/backup/mysql/test_backup目录下。

[root@db01 ~]# mkdir -p /data/backup/mysql/test_backup
[root@db01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 \
--no-timestamp /data/backup/mysql/test_backup/
......
200621 21:18:48 completed OK! #看到类似的输出信息,则表示备份成功!

对备份目录执行–apply-log操作:

[root@db01 ~]# innobackupex --user=root --password=123 --apply-log /data/backup/mysql/test_backup/
......
200621 21:22:06 completed OK! #看到类似的输出信息,则表示--apply-log操作执行成功!

现在模拟数据库数据丢失情况。即先停止数据库,清空数据目录。

[root@db01 ~]# /etc/init.d/mysqld stop
[root@db01 ~]# rm -rf /usr/local/mysql/data/*

恢复数据,对备份目录执行–copy-back操作,把备份文件复制到之前清空的数据目录下:

[root@db01 ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup/mysql/test_backup/
......
200621 21:51:42 completed OK! #看到类似的输出信息,表示--copy-back操作执行成功!

修改数据目录的属主、属组,启动数据库:

[root@db01 ~]# chown -R mysql:mysql /usr/local/mysql/data
[root@db01 ~]# /etc/init.d/mysqld start

登录数据库,校验数据是否正确:

[root@db01 ~]# mysql -uroot -p123 -e "show databases;"

4.6 实战:模拟增量备份与恢复

增量备份特性

  • 基于上一次备份进行增量
  • 增量备份无法单独恢复,必须基于全备进行恢复
  • 所有增量必须要按顺序合并到全备当中

4.6.1 完全备份和增量备份

使用innobackupex命令给数据库进行一次完全备份,备份目录到backup-$(date +%F)

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /data/backup/mysql/backup-$(date +%F)
......
200622 18:38:04 completed OK!

完全备份成功后,进入数据库新建库、表并插入一些数据(制造增量数据):

mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

mysql> use test1;
Database changed
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tb1(id) values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from tb1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)

进行第一次增备备份。第一次增量备份的basedir是完全备份,因为在第一 次增量备份之前的最近一次备份只有完全备份。

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental-basedir=/data/backup/mysql/backup-2020-06-22/ --incremental /data/backup/mysql/incremental_one
......
200622 18:54:29 completed OK!

继续制造测试数据:

mysql> create table tb2(id int,name varchar(10));
Query OK, 0 rows affected (0.37 sec)

mysql> insert into tb2(id,name) values(1,'name1'),(2,'name2'),(3,'name3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from tb2;
+------+-------+
| id | name |
+------+-------+
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
+------+-------+
3 rows in set (0.00 sec)

进行第二次增量备份。第二次增量备份就不需要再基于完全备份了,因为最近一次备份是第一次增量备份,这里第二次增量备份的basedir是基于第一次增量备份的。

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental-basedir=/data/backup/mysql/incremental_one/ --incremental /data/backup/mysql/incremental_two
......
200622 19:06:28 completed OK!

4.6.2 增量备份恢复

在恢复数据前,先停止数据库并清空相关数据目录(建议先备份在情况)。

[root@db01 ~]# /etc/init.d/mysqld stop
[root@db01 ~]# rm -rf /usr/local/mysql/data/*

进入完全备份目录,执行–apply-log操作需要带上–redo-only选项,只应用完成redo日志,不对未提交的事务执行回滚操作。

[root@db01 ~]# cd /data/backup/mysql/backup-2020-06-22
[root@db01 backup-2020-06-22]# innobackupex --apply-log --redo-only ./
......
200622 19:54:00 completed OK!

在完全备份的基础上执行第一次增量备份–apply-log操作(带上–redo-only)

[root@db01 backup-2020-06-22]# innobackupex --apply-log --redo-only /data/backup/mysql/backup-2020-06-22/ --incremental-dir=/data/backup/mysql/incremental_one/
......
200622 19:58:29 completed OK!

在执行–apply-log操作的第一次增量备份的完全备份目录中,再对第二次增量备份执行–apply-log操作,因为这是最后一次增量备份,所以不需要加–redo-only选项。

[root@db01 backup-2020-06-22]# innobackupex --apply-log /data/backup/mysql/backup-2020-06-22/ --incremental-dir=/data/backup/mysql/incremental_two/
......
200622 20:02:57 completed OK!

最后,在对完全备份目录执行一次–apply-log操作,生成redo log文件。

[root@db01 backup-2020-06-22]# innobackupex --apply-log --redo-only /data/backup/mysql/backup-2020-06-22/
......
200622 20:38:34 completed OK!

[root@db01 backup-2020-06-22]# ll -h
total 243M
-rw-r----- 1 root root 430 Jun 22 20:33 backup-my.cnf
drwxr-x--- 2 root root 70 Jun 22 20:37 binlog
drwxr-x--- 2 root root 4.0K Jun 22 20:37 db_test1
drwxr-x--- 2 root root 20 Jun 22 20:37 db_test3
-rw-r----- 1 root root 76M Jun 22 20:38 ibdata1
-rw-r----- 1 root root 50M Jun 22 20:33 ibdata2
-rw-r----- 1 root root 48M Jun 22 20:38 ib_logfile0
-rw-r----- 1 root root 48M Jun 22 20:38 ib_logfile1
-rw-r----- 1 root root 12M Jun 22 20:37 ibtmp1
drwxr-x--- 2 root root 4.0K Jun 22 20:37 mysql
drwxr-x--- 2 root root 4.0K Jun 22 20:37 performance_schema
drwxr-x--- 2 root root 60 Jun 22 20:37 test
drwxr-x--- 2 root root 80 Jun 22 20:37 test1
drwxr-x--- 2 root root 176 Jun 22 20:37 world
-rw-r----- 1 root root 27 Jun 22 20:37 xtrabackup_binlog_info
-rw-r--r-- 1 root root 27 Jun 22 20:38 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 117 Jun 22 20:38 xtrabackup_checkpoints
-rw-r----- 1 root root 595 Jun 22 20:37 xtrabackup_info
-rw-r----- 1 root root 8.0M Jun 22 20:35 xtrabackup_logfile

将完全备份目录下的数据文件复制到相关目录下:

[root@db01 backup-2020-06-22]# innobackupex --copy-back ./
......
200622 20:40:22 completed OK!

修改数据目录属主并启动MySQL:

[root@db01 ~]# chown -R mysql.mysql /usr/local/mysql/data/
[root@db01 ~]# /etc/init.d/mysqld start

登录数据库,效验数据。

[root@db01 ~]# mysql -uroot -p123 -e "use test1;select * from tb1,tb2;"

4.7 XtraBackup 备份所需权限

使用 XtraBackup 工具对数据库进行备份时,建议创建一个最小权限集合的账号。
备份所需最小权限集合:RELOAD,LOCK TABLES,REPLICATION CLIENT,PROCESS
当使用到备份的高级功能是可能需要用到 CREATE TABLESPACE, CREATE, INSERT, SELECT, SUPER 等权限,根据需求添加:

GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT,PROCESS ON ****.**** TO bkpdbuser@'10.168.%' IDENTIFIED BY 'bkpdbpass';