MariaDB系列之数据库备份

Posted by Mathew on 2016-09-17

   生产环境中,尤其在当今数据大爆炸的时代,怎么强调数据的重要性都不为过。
如果一家做外卖的互谅网公司,系统中用户的帐号信息,订单信息,以及外卖产品的图片信息等等,这些数据丢失了,如果数据量过大,那么数以千万计的用户在登录系统时候都找不到自己对应的信息,这就会一下子流失很多用户量,互谅网公司如果没有用户量,基本就没什么玩儿了。由此,数据的重要性不言而喻,数据备份的重要性不言而喻。

本实验采用 CentOS 7.2 + MariaDB-5.5.50

为什么要备份

  • 硬件故障
  • 软件故障
  • 自然灾害
  • 黑客攻击
  • 误操作

上述的这几种情况都会导致我们的数据丢失,

数据备份的类型

  • 完全备份,部分备份

    • 逻辑备份:整个数据集
    • 部分备份:只备份数据子集
  • 完全备份、增量备份、差异备份

    • 增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据
    • 差异备份:仅备份最近一次完全备份以来变化的数据
  • 热备份、温备份、冷备份

    • 热备:读写操作均可执行
    • 温备:读操作可执行;但写操作不成
    • 冷备:读写操作均不可进行
      MyISAM:温备,不能热备;
      InnoDB: 热备;
  • 物理备份、逻辑备份

    • 物理备份:直接复制数据文件进行备份;
    • 逻辑备份:从数据库中“导出”数据另存而进行的备份;
      逻辑备份:与存储引擎无关;

MariaDB备份数据的方式

在MySQl中我们备份数据一般有几种方式

  • 热备份:指的是当数据库进行备份时, 数据库的读写操作均不是受影响
  • 温备份:指的是当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作
  • 冷备份:指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

MySQL中进行不同方式的备份还要考虑存储引擎是否支持

  • MyISAM热备 ×温备 √冷备 √
  • InnoDB热备 √温备 √冷备 √
  • 我们在考虑完数据在备份时、数据库的运行状态之后还需要考虑对于MySQL数据库中数据的备份方式

物理备份或者逻辑备份

物理备份一般就是通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果
逻辑备份一般就是通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)

备份需要考虑的因素

  • 持锁多久
  • 备份过程的时长
  • 备份负载
  • 恢复过程的时长

备份什么

  • 数据
  • 二进制文件、InnoDB的事务日志
  • 代码(存储过程、存储函数、触发器、时间调度器)
  • 服务器的配置文件

备份工具

这里我们列举集中常用的备份工具

1
2
3
4
mysqldump:逻辑备份工具,适用所有存储引擎,温备;完全备份、部分备份;对InnoDB存储引擎支持热备;
cp, tar等复制归档工具:物理备份工具,适用所有存储引擎;冷备;完全备份,部分备份;
lvm2的快照:几乎热备;借助于文件系统管理工具进行备份;
mysqlhotcopy:几乎冷备;仅适用于MyISAM存储引擎;

设计合适的备份策略

  • 数据集:完全+增量
  • 备份手段:物理,逻辑

常见的备份策略:

  • 直接cp,tar复制数据库文件
  • mysqldump复制BIN LOGS
  • lvm2快照复制BIN LOGS
  • xtrabackup

简单说明:

  1. 当数据量较小,可以使用第一种方式,直接复制数据库文件
  2. 如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果
  3. 如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果
  4. 如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果

实战演练

使用cp进行备份

我们这里使用CentOS 7.2系统yum安装的mariadb-5.5.50版本进行测试。 
查看数据库的信息
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
MariaDB [(none)]> SHOW DATABASES;  # 查看当前数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)

MariaDB [(none)]> USE hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> SHOW TABLES; # 查看当前数据表
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)

MariaDB [hellodb]> SELECT COUNT(*) FROM classes; # 表中有数据
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
1 row in set (0.01 sec)
向数据库施加读锁
1
2
3
4
MariaDB [hellodb]> FLUSH TABLES WITH READ LOCK ;  # 向所有表施加读锁
Query OK, 0 rows affected (0.00 sec)

[root@daniel ~]# systemctl stop mariadb.service # 一定要停掉数据库
备份数据文件
1
2
3
4
[root@daniel ~]# cp -a /var/lib/mysql/* /backup 
[root@daniel ~]# ls /backup/
aria_log.00000001 daniel.err ibdata1 ib_logfile1 performance_schema
aria_log_control hellodb ib_logfile0 mysql test
模拟数据丢失并恢复
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
[root@daniel ~]# rm -rf /var/lib/mysql/* 
[root@daniel ~]# systemctl start mariadb.service
[root@daniel ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.50-MariaDB MariaDB Server

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

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

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql | # 此时发现 hellodb数据库已经不存在
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> exit
Bye
[root@daniel ~]# systemctl stop mariadb.service # 操作数据文件之前先停掉数据库
[root@daniel ~]# rm -rf /var/lib/mysql/* # 这一步可以不做
[root@daniel ~]# cp -a /backup/* /var/lib/mysql # 将备份的数据拷贝回去
[root@daniel ~]# systemctl start mariadb.service # 重启数据库
[root@daniel ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.50-MariaDB MariaDB Server

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

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

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb | # 此时发现数据库被恢复
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> USE hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses | # 数据文件依旧存在
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.01 sec)

使用mysqldump复制BINARY LOG备份

   我们通过mysqldump进行一次完全备份, 再修改表中的数据, 然后再通过binary log进行恢复 二进制日志需要在mysql配置文件中添加 log_bin=on 开启

mysqldump命令介绍
mysqldump是一个客户端的逻辑备份工具,可以生成一个重现创建原是数据库和表的SQL语句,可以支持所有的存储引擎,对于InnoDB支持热备。  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
基本语法格式

shell> mysqldump [options] db_name [tbl_name ...] 恢复需要手动CRATE DATABASES
shell> mysqldump [options] --databases db_name ... 恢复不需要手动创建数据库
shell> mysqldump [options] --all-databases 恢复不需要手动创建数据库


其他选项:
-E, --events: 备份事件调度器
-R, --routines: 备份存储过程和存储函数
--triggers: 备份表的触发器; --skip-triggers
--master-date[=value]
1: 记录为CHANGE MASTER TO 语句、语句不被注释
2: 记录为注释的CHANGE MASTER TO语句
基于二进制还原只能全库还原

--flush-logs: 日志滚动
锁定表完成后执行日志滚动
查看数据库的信息
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
MariaDB [(none)]> SHOW DATABASES;  # 查看当前数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)

MariaDB [(none)]> USE hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> SHOW TABLES; # 查看当前数据表
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)

MariaDB [hellodb]> SELECT COUNT(*) FROM classes; # 表中有数据
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
1 row in set (0.01 sec)
使用mysqldump备份数据库
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
[root@daniel ~]# mysql -e 'SHOW MASTER STATUS'            # 查看当前二进制文件的状态, 并记录下position的数字
+-----------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------+----------+--------------+------------------+
| ON.000001 | 245 | | |
+-----------+----------+--------------+------------------+

[root@daniel ~]# mysqldump --all-databases --lock-all-tables > backup.sql # 备份所有库文件到backup.sql
[root@daniel ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.50-MariaDB MariaDB Server

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

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

MariaDB [(none)]> CREATE DATABASE TEST1; # 创建一个新库
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> SHOW MASTER STATUS; # 记下现在的position
+-----------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------+----------+--------------+------------------+
| ON.000001 | 401 | | |
+-----------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> exit
Bye
[root@daniel ~]# cp /var/lib/mysql/ON.000001 /root # 备份二进制文件
[root@daniel ~]# systemctl stop mariadb.service # 停止mysql服务
[root@daniel ~]# rm -rf /var/lib/mysql/* # 删除所有的数据文件
[root@daniel ~]# systemctl start mariadb.service # 重启mysql
[root@daniel ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.50-MariaDB MariaDB Server

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

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

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema | # 数据丢失
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> SET sql_log_bin=OFF ; # 暂时关闭二进制日志
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SOURCE backup.sql # 恢复数据

MariaDB [test]> SET sql_log_bin=ON; # 开启二进制日志
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> SHOW DATABASES; # 恢复hellodb数据库,但是缺少TEST1数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.03 sec)

[root@daniel ~]# mysqlbinlog --start-position=245 --stop-position=401 ON.000001 | mysql 通过二进制日志增量恢复
[root@daniel ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.50-MariaDB MariaDB Server

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

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

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| TEST1 | # 出现TEST1数据库
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)

## 完成

使用lvm2快照备份数据

  快照简单来说就是将所快照源分区一个时间点所有文件的元数据进行保存,如果源文件没有改变,那么访问快照卷的相应文件则直接指向源分区的源文件,如果源文件发生改变,则快照卷中与之对应的文件不会发生改变。快照卷主要用于辅助备份文件。

部署lvm环境
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
[root@daniel ~]# ls /dev/sdb    #  添加一块儿新硬盘
/dev/sdb
[root@daniel ~]# fdisk /dev/sdb
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0x44384831.

Command (m for help): n
Partition type:
p primary (0 primary, 0 extended, 4 free)
e extended
Select (default p): p
Partition number (1-4, default 1): 1
First sector (2048-41943039, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-41943039, default 41943039): +10G
Partition 1 of type Linux and of size 10 GiB is set

Command (m for help): t
Selected partition 1
Hex code (type L to list all codes): 8e
Changed type of partition 'Linux' to 'Linux LVM'

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@daniel ~]# pvcreate /dev/sdb1 # 创建物理卷
Physical volume "/dev/sdb1" successfully created
[root@daniel ~]# vgcreate myvg /dev/sdb1 # 创建卷组
Volume group "myvg" successfully created
[root@daniel ~]# lvcreate -n mydata -L 5G myvg # 创建逻辑卷
Logical volume "mydata" created.
[root@daniel ~]# mkfs.ext4 /dev/mapper/myvg-mydata # 格式化文件系统
mke2fs 1.42.9 (28-Dec-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
327680 inodes, 1310720 blocks
65536 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=1342177280
40 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736

Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

[root@daniel ~]# mkdir /lvm_data # 创建逻辑卷数据目录
[root@daniel ~]# mount /dev/mapper/myvg-mydata /lvm_data # 挂载逻辑卷到目录
[root@daniel ~]# vim /etc/my.cnf #修改mysql配置文件的datadir如下

# this is read by the standalone daemon and embedded servers
[server]


# this is only for the mysqld standalone daemon
[mysqld]
datadir=/lvm_data
socket=/lvm_data/mysql.socket

# this is only for embedded server
[embedded]


[root@daniel ~]# systemctl start mariadb.service # 启动mysql

导入数据库hellodb略过
查看数据库的信息
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
MariaDB [hellodb]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [hellodb]> USE hellodb;
Database changed
MariaDB [hellodb]> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)

MariaDB [hellodb]> SELECT COUNT(*) FROM classes;
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
1 row in set (0.01 sec)
创建快照卷并备份
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
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;   # 锁定所有表
Query OK, 0 rows affected (0.00 sec)

[root@daniel ~]# lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvg-mydata # 创建快照卷
Logical volume "mydata-snap" already exists in volume group "myvg"

MariaDB [(none)]> UNLOCK TABLES; # 解锁所有表
Query OK, 0 rows affected (0.00 sec)

[root@daniel ~]# mkdir /lvm_snap # 创建文件夹
[root@daniel ~]# mount /dev/myvg/mydata-snap /lvm_snap/ # 挂载lvm_snap
mount: /dev/mapper/myvg-mydata--snap is write-protected, mounting read-only
[root@daniel ~]# cd /lvm_snap/
[root@daniel lvm_snap]# ls
hellodb
[root@daniel lvm_snap]# tar cf /tmp/mysqlback.tar * # 打包文件到/tmp/mysqlback.tar
[root@daniel lvm_snap]# cd
[root@daniel ~]# umount /lvm_snap/ # 卸载snap
[root@daniel ~]# lvremove myvg mydata-snap # 删除snap

Do you really want to remove active logical volume mydata-snap? [y/n]: y
Logical volume "mydata-snap" successfully removed
Logical volume myvg/mydata contains a filesystem in use.
Volume group "mydata-snap" not found
Cannot process volume group mydata-snap
恢复数据
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

[root@daniel ~]# rm -rf /lvm_data/* # 删除数据
[root@daniel ~]# systemctl restart mariadb.service # 重启mysql

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema | # hellodb数据库丢失
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

[root@daniel ~]# cd /lvm_data/
[root@daniel lvm_data]# ls
aria_log.00000001 aria_log_control daniel.err daniel.pid ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
[root@daniel lvm_data]# rm -rf *
[root@daniel lvm_data]# tar xf /tmp/mysqlback.tar # 解压备份数据库到此文件夹
[root@daniel lvm_data]# ls
hellodb
[root@daniel lvm_data]# systemctl restart mariadb.service

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb | # hellodb数据库出现
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> USE hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores | # 数据完整
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)

MariaDB [hellodb]>

## 完成

使用xtrabackup备份

我们需要在配置文件中添加 innodb_file_per_table = ON 来开启

下载安装 xtrabackup
1
2
[root@daniel ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/7/x86_64/percona-xtrabackup-2.3.4-1.el7.x86_64.rpm
[root@daniel ~]# yum localinstall percona-xtrabackup-2.3.4-1.el7.x86_64.rpm
xtrabackup介绍

   xtrabackup是由percona提供的mysql数据库备份工具,据官方介绍,这也是世界上唯一一款开源的能够对innodb和xtrabackup和xtradb数据库进行热备的工具。特点:

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

   我们这里使用xtrabackup的前端配置工具innobackupex来实现对数据库的完全备份

   使用innobackupex备份时, 会调用xtrabackup备份所有的InnoDB表, 复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件, 同时还会备份触发器和数据库配置文件信息相关的文件, 这些文件会被保存至一个以时间命名的目录.

备份过程
1
2
3
4
5
6
7
8
9
[root@daniel ~]# mkdir /extrabackup 
[root@daniel ~]# innobackupex --user=root /extrabackup/
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
....

160917 06:51:37 completed OK! # 表示备份成功

[root@daniel ~]# ls /extrabackup/ # 看到备份目录
2016-09-17_06-51-35
  • 一般情况, 备份完成后, 数据不能用于恢复操作, 因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此, 此时的数据文件仍不一致, 所以我们需要”准备”一个完全备份
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[root@daniel ~]# innobackupex --apply-log /extrabackup/2016-09-17_06-51-35/
...
#一般情况下下面三行结尾代表成功*****************
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1598486
160917 06:54:05 completed OK!

[root@daniel extrabackup]# cd /extrabackup/2016-09-17_06-51-35/
[root@daniel 2016-09-17_06-51-35]# ls -lh # 查看备份文件
total 31M
-rw-r----- 1 root root 386 Sep 17 06:51 backup-my.cnf
drwx------ 2 root root 138 Sep 17 06:51 hellodb
-rw-r----- 1 root root 18M Sep 17 06:54 ibdata1
-rw-r--r-- 1 root root 5.0M Sep 17 06:54 ib_logfile0
-rw-r--r-- 1 root root 5.0M Sep 17 06:54 ib_logfile1
drwx------ 2 root root 4.0K Sep 17 06:51 mysql
drwx------ 2 root root 4.0K Sep 17 06:51 performance_schema
drwx------ 2 root root 19 Sep 17 06:51 test
-rw-r----- 1 root root 113 Sep 17 06:54 xtrabackup_checkpoints
-rw-r----- 1 root root 415 Sep 17 06:51 xtrabackup_info
-rw-r----- 1 root root 2.0M Sep 17 06:54 xtrabackup_logfile
[root@daniel 2016-09-17_06-51-35]#
恢复数据
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
[root@daniel ~]# rm -rf /lvm_data/*    #  删除数据

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

[root@daniel ~]# innobackupex --copy-back /extrabackup/2016-09-17_06-51-35/ # 恢复数据
...
出现如何即为OK
160917 07:00:22 [01] ...done
160917 07:00:22 completed OK!

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql | # 可以看到在不用重启服务的情况下数据就已然恢复
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> USE hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)

MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
+---------+----------------+----------+
8 rows in set (0.00 sec)
增量备份
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
#########创建连两个数据库以供测试#####################
MariaDB [hellodb]> CREATE DATABASE TEST1;
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> CREATE DATABASE TEST2;
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> EXIT
Bye
[root@daniel ~]# innobackupex --incremental /extrabackup/ --incremental-basedir=/extrabackup/2016-09-17_07-09-40/

[root@daniel ~]# ll /extrabackup/2016-09-17_07-17-26/
total 80
-rw-r----- 1 root root 386 Sep 17 07:17 backup-my.cnf
drwx------ 2 root root 4096 Sep 17 07:17 hellodb
-rw-r----- 1 root root 49152 Sep 17 07:17 ibdata1.delta
-rw-r----- 1 root root 44 Sep 17 07:17 ibdata1.meta
drwx------ 2 root root 4096 Sep 17 07:17 mysql
drwx------ 2 root root 4096 Sep 17 07:17 performance_schema
drwx------ 2 root root 19 Sep 17 07:17 test
drwx------ 2 root root 19 Sep 17 07:17 TEST1
drwx------ 2 root root 19 Sep 17 07:17 TEST2
-rw-r----- 1 root root 117 Sep 17 07:17 xtrabackup_checkpoints
-rw-r----- 1 root root 479 Sep 17 07:17 xtrabackup_info
-rw-r----- 1 root root 2560 Sep 17 07:17 xtrabackup_logfile
  • BASEDIR指的是完全备份所在的目录,此命令执行结束后,innobackupex命令会在/extrabackup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外,在执行过增量备份之后再一次进行增量备份时,其–incremental-basedir应该指向上一次的增量备份所在的目录。

  • 需要注意的是,增量备份仅能应用于InnoDB或XtraDB表,对于MyISAM表而言,执行增量备份时其实进行的是完全备份。

整理增量备份
1
2
[root@daniel ~]# innobackupex --apply-log --redo-only /extrabackup/2016-09-17_07-09-40/
[root@daniel ~]# innobackupex --apply-log --redo-only /extrabackup/2016-09-17_07-09-40/ --incremental-dir=/extrabackup/2016-09-17_07-17-26/
恢复数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[root@daniel ~]# rm -rf /mydata/data/* 
[root@daniel ~]# ls /extrabackup/
2016-09-17_07-09-40 2016-09-17_07-17-26
[root@daniel ~]# innobackupex --copy-back /extrabackup/2016-09-17_07-09-40/
[root@daniel ~]# chown -R mysql.mysql /mydata/data/
[root@daniel ~]# ls /mydata/data/
hellodb ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test TEST1 TEST2 xtrabackup_info


MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| TEST1 |
| TEST2 | # TEST1 和 TEST2恢复
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)

总结

1
2
3
4
5
备份方法 	备份速度	恢复速度		便捷性							功能		一般用于
cp 快 快 一般、灵活性低 很弱 少量数据备份
mysqldump 慢 慢 一般、可无视存储引擎的差异 一般 中小型数据量的备份
lvm2快照 快 快 一般、支持几乎热备、速度快 一般 中小型数据量的备份
xtrabackup 较快 较快 实现innodb热备、对存储引擎有要求 强大 较大规模的备份

CentOS 7 和 CentOS 6 在搭配Mysql或者MariaDB做备份的时候有些细节还是不一样的,需要真正做过时候才能看得出来。