HEBIN博客


一个专注于IT原创技术教程分享的博客

MySQL备份与恢复

2020年6月23日 2条评论 2.68k次阅读 5人点赞 HEBIN
文章目录
一、数据库备份的类型
二、数据库备份方式
2.1 逻辑备份
2.2 物理备份
三、MySQL主流备份工具之mysqldump
3.1 备份方法
3.2 mysqldump常用命令行选项
3.3 mysqldump备份注意
3.4 实战：模拟完全备份和恢复
四、MySQL主流备份工具之XtraBackup
4.1 XtraBackup的都优点
4.2 备份方式（物理备份）
4.3 下载安装XtraBackup软件
4.4 innobackupex命令常用选项
4.5 实战：模拟完全备份与恢复
4.6 实战：模拟增量备份与恢复

一、数据库备份的类型

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

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

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

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

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

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

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

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

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

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

二、数据库备份方式

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

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二进制日志文件。

  • --triggers：在备份文件中包含每个备份表的触发器。

  • -E,--events：在备份数据中包含数据库的事件。

  • --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_`data +%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软件

  • 安装依赖
yum install -y perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL perl-Digest-MD5 perl-Digest libev rsync
  • 安装XtraBackup
yum install -y https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.20/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.20-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 实战：模拟增量备份与恢复

增量备份特性

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

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!

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.0 国际许可协议 进行许可
点赞
最后编辑：2020年11月20日
  1. 拾贰说道：
    2020年6月27日 下午2:51
    Google Chrome Windows 10
    差评！！！ :hehe:
  2. Pingback： MySQL 数据库回档方案 - HEBIN博客

发表回复