本文主要讲述基于mysqldump、逻辑卷的快照和Xtrabackup做数据库的备份与复制及注意事项
做备份的目录目的为做灾难恢复、做审计、测试，要对备份的数据做恢复性测试
备份类型
根据备份时，数据库服务器是否在线，可以分为
冷备：cold backup
温备：warm backup 全局施加共享锁，只能读，不能写
热备：hot backup
根据备份的数据集，可以分为：
完全备份 full backup
部分备份 partial backup
根据备份时的接口（直接备份数据文件还是通过mysql服务器导出数据）
物理备份：直接复制（归档）数据文件的备份方式
逻辑备份：把数据从库中提取出来保存为文本文件
根据备份时是备份整个数据还是仅备份变化的数据，可以分为
完全备份 full backup
增量备份 incremental backup
差异备份 differential backup 从上一次完全备份开始备份
备份策略：
选择备份方式
选择备份时间
考虑恢复成本
考虑恢复时长
考虑备份成本：施加锁的时间，备份时长，备份负载
备份对象：
数据
配置文件
代码：存储过程、存储函数、触发器
OS相关的配置文件，如crontab配置计划及相关脚本
跟复制相关的配置信息：二进制日志文件
常用的备份工具简介
mysqldump，单线程，是一个逻辑备份工具，可以对InnoDB热备、MyISAM温备、Aria温备
缺点：备份和恢复过程较慢，很难实现差异或增量备份
mysqldumper：多线程的mysqldump
缺点：很难实现差异或增量备份
基于逻辑卷快照：接近于热备的工具，因为要先请求全局锁，而后创建快照，并在创建完成后释放全局锁，可以使用cp、tar等工具进行物理备份，备份和恢复岁都很快
缺点：很难实现增量备份，并且请求全局锁需要等待一段时间，在繁忙的服务器上尤其如此
select语句：
备份：select clause into outfile '/path/to/somefile'
恢复：load data infile '/path/to/somefile' into table 'tb_name'
Innobase:商业备份工具，
innobackup
Xtarbackup: 由Percona提供的开源备份工具。
mysqlhostcopy：几乎冷备
一、mysqldump
mysqldump仅适用于数据集较小场景
用法：mysqldump [options] [db_name [tbl_name ...]]
主要选项解释：
--all-databases, -A：备份所有数据库
--databases, -B：要备份的数据库，可以同时备份多个，使用空格分隔
--flush-logs, -F：备份前、请求到锁之后滚动日志，要记录下复制时的二进制日志
--flush-privileges：通知数据库重读授权表
--host=host_name, -h host_name：要备份的数据库的主机名，可以基于网络备份
--lock-all-tables, -x：请求锁定所有表之后再备份，对MyISAM，InnoDB,Aria做温备
--single-transaction：能够对InnoDB存储引擎实现热备
-u usename 备份的用户名
-p password 登陆数据库的密码
--events:备份事件调度器代码
--routines：备份存储过程和存储函数
--triggers：备份触发器
--master-date={0|1|2}，0表示不记录，1表示距离为change master 语句，2表示记录为注释的change master语句
备份:
|
1
2
3
4
5
6
7
8
9
10
11
|
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5
rows
in
set
(
0.08
sec)
|
此处我们备份hellodb和test数据库，将备份的文件保存在/tmp下以backup.sql命名
|
1
2
3
4
|
[root@bogon ~]# mysqldump -uroot -hlocalhost -p --databases hellodb test --lock-all-tables --flush-logs --master-data=
2
> /tmp/backup.sql
Enter password:
[root@bogon ~]# ll /tmp/backup.sql
-rw-r--r--
1
root root
8621
Apr
11
22
:
49
/tmp/backup.sql
|
恢复：此处我们模拟将hellodb和test数据库都删除掉
|
1
2
3
4
5
6
7
8
9
10
11
12
|
MariaDB [(none)]> drop database hellodb;
Query OK,
8
rows affected (
0.08
sec)
MariaDB [(none)]> drop database test;
Query OK,
0
rows affected (
0.00
sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
|
导入数据库
|
1
2
|
[root@bogon ~]# mysql -uroot -hlocalhost -p < /tmp/backup.sql
Enter password:
|
查看数据库
|
1
2
3
4
5
6
7
8
9
10
|
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
|
我们看到hellodb和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
|
#!/bin/bash
username=root
hostname=localhost
password=wangfeng7399
mysql -u$username -h$hostname -p$password -e
"show databases"
read -p
"PLZ input a dbname:"
dbname
read -p
"PLZ input backupdir:"
backupdir
read -p
"PLZ input backupname:"
backupname
if
[[
"$dbname"
==
"quit"
||
"$backupdir"
==
"quit"
||
"$backupname"
==
"quit"
]];then
exit
4
else
echo
"----------backup start----------"
! [ -d $backupdir ] && mkdir -p $backupdir
if
[[
"$dbname"
==
"all"
]];then
/usr/local/mysql/bin/mysqldump -u$username -h$hostname -p$password --all-databases --lock-all-tables --flush-logs --master-data=
2
>> $backupdir/$backupname
else
/usr/local/mysql/bin/mysqldump -u$username -h$hostname -p$password --databases $dbname --lock-all-tables --flush-logs --master-data=
2
>> $backupdir/$backupname
fi
if
[ $? -eq
0
];then
echo
"---------backup OK-------"
echo
"---------backup stop-------"
else
echo
"-------back lose--------"
exit
fi
fi
|
1.恢复：关闭二进制日志，关闭其他用户的写操作，并在使用souce恢复二进制文件，恢复应该使用完全备份+各二进制文件至此刻的事件
2.备份：mysqldump+二进制日志文件
3.对于MySQL的配置文件，已经与mysql相关的OS配置文件在每次修改后都永固直接进行备份
二、基于lvm逻辑卷的快照备份
要求：
1.事务日志跟数据文件必须在同一个卷上
2.创建快照卷以前，要先请求mysql的全局锁，在快照创建完成之后释放锁
3.在请求全局锁完成之后，做一次日志滚动，做好二进制日志文件及位置标记（需要手动进行）
步骤：
1.请求全局锁，并滚动日志
|
1
2
3
4
|
MariaDB [(none)]> flush tables
with
read lock;
Query OK,
0
rows affected (
0.00
sec)
MariaDB [(none)]> flush logs;
Query OK,
0
rows affected (
0.06
sec)
|
2.做好二进制日志文件及位置标记（在另外一个终端进行）
|
1
2
3
4
|
[root@bogon ~]# mysql -e
"show master status"
> logs.txt
[root@bogon ~]# cat logs.txt
File Position Binlog_Do_DB Binlog_Ignore_DB
master-bin.
000003
367
|
3.创建快照卷（在另外一个终端进行）
|
1
2
|
[root@bogon ~]# lvcreate -L 1G -s -n mydata-snap -p r /dev/myvg/mydata
Logical volume
"mydata-snap"
created
|
4.释放全局锁
|
1
2
|
MariaDB [(none)]> unlock tables;
Query OK,
0
rows affected (
0.00
sec)
|
5.挂载快照并备份
|
1
2
|
[root@bogon ~]# mount /dev/myvg/mydata-snap /mnt
[root@bogon ~]# tar -Jcf /tmp/mysql.tar.xz /mnt/*
|
6.备份完成后，删除快照卷
|
1
2
|
[root@bogon ~]# umount /mnt
[root@bogon ~]# lvremove /dev/myvg/mydata-snap
|
恢复：
1.二进制日志要保存好，提取备份之后的所有时间至某sql脚本中
2.还原数据，将备份的数据复制到MariaDB的数据目录下，修改权限及属主和属组等，并启动mysql
3.做即时点还原，本处不在给出还原步骤
三、基于Xtrabackup备份与恢复
1.简介
Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：
(1)备份过程快速、可靠；
(2)备份过程不会打断正在执行的事务；
(3)能够基于压缩等功能节约磁盘空间和流量；
(4)自动实现备份检验；
(5)还原速度快；
2.安装
|
1
2
|
[root@bogon ~]# wget http:
//www.percona.com/redir/downloads/XtraBackup/LATEST/RPM/rhel6/x86_64/percona-xtrabackup-2.1.8-733.rhel6.x86_64.rpm
[root@bogon ~]# yum install percona-xtrabackup-
2.1
.
8
-
733
.rhel6.x86_64.rpm -y
|
3.备份的实现
完全备份
①、创建一个拥有最小权限的用户
|
1
2
3
4
5
6
|
MariaDB [(none)]> create user
'bkpuser'
@
'localhost'
identified by
'wangfeng7399'
;
Query OK,
0
rows affected (
0.17
sec)
MariaDB [(none)]> grant reload,lock tables,replication client on *.* to
'bkpuser'
@
'localhost'
;
Query OK,
0
rows affected (
0.00
sec)
MariaDB [(none)]> flush privileges;
Query OK,
0
rows affected (
0.09
sec)
|
备份数据
|
1
2
3
4
5
6
|
[root@bogon ~]# innobackupex --user=bkpuser --password=wangfeng7399 /tmp/
140413
17
:
21
:
26
innobackupex: Connection to database server closed
140413
17
:
21
:
26
innobackupex: completed OK!
查看文件
[root@bogon ~]# ll /tmp/
drwxr-xr-x
6
root root
4096
Apr
13
17
:
21
2014
-
04
-13_17-
21
-
22
|
使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有的表机构定义的相关文件（.frm）、已经MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中
在备份的同时，innobackupex还会在北方目录中创建如下文件：
1.xtrabackup_checkpoints 备份类型（如完全或增量备份）、备份状态（如是否已经为prepared状态）和LSN（日志序列号）范围信息
每个InnoDB页（通常为16k大小）都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面的LSN能够标明此页面最近是如何发生改变的
2.xtrabackup_binlog_info MariaDB服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置
3.xtrabackup_binlog_pos_innodb 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
4.xtrabackup_binary 备份中用到的xtrabackup的可执行文件
5.backup-my.cnf 备份命令用到的配置选项信息
在使用innobackupex进行备份时，还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录，如此以来，innobackupex命令会创建一个BACK-DIR目录来存储备份数据
②、准备（prepare）一个完全备份
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交当尚未同步至数据文件中的事务，因此，此时数据文件仍处于不一致状态，“准备”的主要作用正是通过回滚为提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致状态
|
1
2
3
4
5
6
7
|
[root@bogon ~]# innobackupex --apply-log /tmp/
2014
-
04
-13_17-
21
-
22
/
如果执行正确，其最后输出的几行信息通常如下
xtrabackup: starting shutdown
with
innodb_fast_shutdown =
1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number
1619478
140413
17
:
45
:
09
innobackupex: completed OK!
|
在实现“准备”的过程中，innobackupex通常还可以使用--user-memory选项来指定其可以使用的内存大小，默认通常为100M，如果有足够的内存可以，可以多划分一下内存给preoare的过程，以提高其完成速度
③、从一个完全备份中恢复数据
注意：恢复不用启动mysql
innobackupex命令的--copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程，innobackup通过backup-my.cnf来获取DATADIR目录的相关信息
当前的数据库
|
1
2
3
4
5
6
7
8
9
10
|
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
可以看到已经没有之前的hellodb数据库了
|
1）、如果只是删除某个数据库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
[root@bogon
2014
-
04
-13_17-
21
-
22
]# cp -rf hellodb /mydata/data/.
[root@bogon data]# chown mysql:mysql /mydata/data/hellodb -R
ps：复制回去以后，属主和属组都会是root，应该修改成mysql
登陆数据库查看
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
可以看到我们的hellodb数据库了
|
2）、如果将整个数据库数据目录所在的文件删除了
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
[root@bogon data]# rm -rf /mydata/data/*
[root@bogon data]# innobackupex --copy-back /tmp/
2014
-
04
-13_17-
21
-
22
/
请确保如上信息的最行一行出现“innobackupex: completed OK!”。
[root@bogon data]# chown -R mysql:mysql /mydata/data/
一定要将数据库的属主和属组修改
启动数据库，查看数据
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
|
增量备份
每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长，这正是InnoDB表可以进行增量备份的基础，即innobackup通过备份上次完全备份之后发生改变的页面来实现
①、我们在原来数据库上创建一个表，进行一次增量备份
|
1
2
3
4
5
|
MariaDB [hellodb]> create table t1(name char(
20
));
备份
[root@bogon tmp]# innobackupex --incremental /tmp --incremental-basedir=/tmp/
2014
-
04
-13_19-
49
-
27
/
其中，--incremental-basedir是指完全备份所在的目录，此命令执行结束后，innobackupex命令会在/backup目录中创建一个新的以时间命令的目录以存放所有的增量备份数据。另外，在执行过一次增量备份之后再一次进行增量备份时，其--incremental-basedir应该只上一次的增量备份所在的目录
需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。
|
②、我们在新创建的表中，插入一些数据，在做一次增量备份
|
1
2
3
4
5
|
MariaDB [hellodb]> insert into t1 values (
'zhangsan'
),(
'lisi'
),(
'lili'
);
[root@bogon tmp]# innobackupex --incremental /tmp --incremental-basedir=/tmp/
2014
-
04
-13_19-
54
-
29
/
140413
20
:
00
:
31
innobackupex: Connection to database server closed
140413
20
:
00
:
31
innobackupex: completed OK!
ps： 这一次的--incremental-basedir为上次增量备份的目录
|
③、准备增量备份
准备增量备份与准备完全备份有着一些不同，尤其要注意的是
（1）需要在每个备份（包括完全备份和各个增量备份）上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上
（2）基于所有的备份将为提交的事务进行“回滚”
|
1
2
3
|
[root@bogon tmp]# innobackupex --apply-log --redo-only /tmp/
2014
-
04
-13_19-
49
-
27
/ --incremental-dir=/tmp/
2014
-
04
-13_19-
54
-
29
/
[root@bogon tmp]# innobackupex --apply-log --redo-only /tmp/
2014
-
04
-13_19-
49
-
27
/ --incremental-dir=/tmp/
2014
-
04
-13_20-
00
-
26
/
其中/tmp/
2014
-
04
-13_19-
49
-
27
/为完全备份所在的目录，而第一次 --incremental-dir=/tmp/
2014
-
04
-13_19-
54
-
29
/为第一次增量备份的目录，第二次 --incremental-dir=/tmp/
2014
-
04
-13_20-
00
-
26
/为第二次增量备份的目录，其他依次类推，即如果有多次增量备份，每一次都要执行一次上述的操作
|
④、恢复，本处我们将所有的文件都删除
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
[root@bogon data]# rm -rf /mydata/data/*
[root@bogon tmp]# innobackupex --copy-back /tmp/
2014
-
04
-13_19-
49
-
27
/
看最后一行
innobackupex: completed OK!
修改权限
[root@bogon data]# chown mysql.mysql * -R
MariaDB [hellodb]> select * from t1;
+----------+
| name |
+----------+
| zhangsan |
| lisi |
| lili |
+----------+
可以看到我们的数据已经存在了，恢复完成
|
4、Xtrabackup的“流”及“备份压缩”功能
Xtrabackup对备份的数据文件支持“流”功能，即可以将备份数据通过STDOUT传输给tar程序进行归档，而不是默认的直接保存至备份目录中。要使用功能，需要使用--stream选项即可
|
1
2
3
|
[root@bogon ~]# innobackupex --stream=tar /tmp | gzip > /tmp/`date +%F_%H-%M-%S`.tar.gz
甚至也可以使用类似如下命令将数据备份至其它服务器：
[root@bogon ~]# innobackupex --stream=tar /tmp | ssh root@
192.168
.
1.112
"gzip - > /root/`date +%F_%H-%M-%S`.tar.gz"
|
此外，在执行本地备份时，还可以使用--paraller选项对多个文件进行并行复制。此选项用于指定在复制是启动的线程数目。当然，在世界进行备份时要利用此功能的便利性，也需要启用innodb_file_per_table选项或共享的空间通过innodb_data_file_path选项存储在多个ibdata文件中。对某一数据库的多个文件的复制无法利用到此功能。其简单实用方法如下
|
1
|
innobackupex --parallel /path/to/backup
|
同时，innobackupex备份的数据文件也可以存储至远程主机，这可以使用--remote-host选项来实现：
# innobackupex --remote-host=root@192.168.1.112 /path/IN/REMOTE/HOST/to/backup
5、导入或导出单张表
默认情况下，InnoDB表不能通过直接复制表文件的方式在MariaDB服务器之间进行移植，即便使用了innodb_file_per_table选项。而是用Xtrabackup工具可以实现此功能，不过需要“导出”表的MariaDB启用innodb_file_per_table选项（严格来说，是要“导出”的表再起创建之前，MariaDB服务器就启用了innodb_file_per_table选项），而且“导入”表的服务器同时启用了innodb_file_per_table和innodb_expand_import选项
①“导出表”
导出表示在备份的prepare阶段进行的，因此，一旦完全备份完成，就可以在prepare过程中通过--export选项将某表导出了
|
1
2
3
4
5
6
7
8
|
[root@bogon ~]# innobackupex --apply-log --export /tmp/
2014
-
04
-13_19-
49
-
27
/
[root@bogon hellodb]# ll
total
964
-rw-r--r--
1
root root
473
Apr
13
20
:
42
classes.cfg
-rw-r--r--
1
root root
16384
Apr
13
20
:
42
classes.exp
-rw-r-----
1
root root
1277
Apr
13
20
:
08
classes.frm
-rw-r-----
1
root root
98304
Apr
13
19
:
49
classes.ibd
此命令会在每个innodb表的表空间创建一个以.exp结尾的文件，这些以.exp结尾的文件则可以用于导入至其他服务器
|
②、“导入表”
要在MariaDB服务器上导入来之与其他服务器的innodb表，需要先在当前服务器上个创建一个跟原表表结构一致的表，而后才能实现将表的导入
|
1
2
3
4
5
|
MariaDB [mydb]> create table t1(name char(
20
));
Query OK,
0
rows affected (
0.13
sec)
将此表的表空间删除
MariaDB [mydb]> alter table t1 discard tablespace;
Query OK,
0
rows affected (
0.05
sec)
|
将来自于“导出”表的服务器的t1表的t1.ibd和t1.exp文件复制到当前服务器的数据目录，然后使用如下命令将其“导入”
|
1
2
3
4
5
6
7
8
9
10
11
|
[root@bogon hellodb]# cp t1.ibd t1.exp /mydata/data/mydb/
[root@bogon hellodb]# chown mysql:mysql /mydata/data/mydb/*
MariaDB [mydb]> alter table t1
import
tablespace;
MariaDB [mydb]> select * from t1;
+----------+
| name |
+----------+
| zhangsan |
| lisi |
| lili |
+----------+
|
可以看到我们的数据能够查询出来
6.使用Xtrabackup对数据库进行部分备份
Xtrabackup也可以实现部分备份，即指备份某个或某些指定的数据库中的某些表，但是要使用此功能，必须启动innodb_file_per_table选项，即每张表保存为一个独立的文件。同时，其也不支持--stream选项，即不支持将数据通过管道传输给其他程序进行处理
此外，还原部分备份跟还原全部数据的备份有所不同，即不能通过使用--copy-back选项直接复制回数据目录，而是要通过导入表的方法来实现还原。当然，有些情况下，部分备份也可以直接--copy-back进行还原，当这种方式还原而来的数据多数会产生数据不一致的问题，因此，无论如果不推荐使用这种方式。
①、创建部分备份
创建部分备份的方式有三种：正则表达式（--include）、枚举表文件（--tables-file）和列出要备份的数据库（--databases）
a、使用--include
使用--include时，要求为其指定要被哦分的表的完整名称，即形如databasename.tablename，如
|
1
|
[root@bogon ~]# innobackupex --
include
=
'^hellodb[.]tb1'
/tmp/
|
b、使用tables-file
此选项的参数需要是一个文件名，此文件中每行包含一个要备份的表的完整名称
|
1
2
|
[root@bogon ~]# echo -e
'hellodb.t1\nhellodb.t2'
> tables.txt
[root@bogon ~]# innobackupex --tables-file=/root/tables.txt /tmp
|
c.使用--databases
此选项接受的参数为数据库名称，如果要指定多个数据库，彼此间需要以空格隔开；同时，在指定某数据库时，也可以只指定其中的某张表。此外，此选项也可以接受一个文件为参数，文件中每一行作为一个要备份的对象。
|
1
|
[root@bogon ~]# innobackupex --databases=
"hellodb mydb"
/tmp
|
②、整理部分备份
prepare部分备份的过程类似于导出表的过程，要使用--export选项进行
|
1
|
# innobackupex --apply-log --export /pat/to/partial/backup
|
此命令执行过程中，innobackupex会调用xtrabackup命令从数据字典中移除缺失的表，因此，会显示出许多关于“表不存在”类的警告信息。同时。也会显示出为备份文件中存在的表创建.exp文件的相关信息
③、还原部分备份
还原部分备份的过程跟导入表的过程相同，当然，也可以通过直接复制prepared状态的备份直接至数据目录实现还不过此时要求数据目录处于一致状态
四、注意事项：
1.备份
1.将数据和备份放在不同的磁盘设备上；最好做异机或者异地存储较为理想
2.备份的数据应该周期性地进程还原测试
3.每次灾难恢复后都应该立即做一次完全备份
4.针对不同规模和级别的数据量，要定制好备份策略
5.二进制日志应该跟数据文件在不同的磁盘上，并周期性的备份好二进制日志文件
2.从备份中恢复应该遵循的步骤
1.停止mysql服务器
2.记录服务器的配置和文件授权
3.将数据从备份移到mysql数据目录：其执行方式依赖于工具
4.改变配置和文件权限
5.以限制访问模块从其服务器：MariaDB的--skip-networking选项可以跳过网络
6.载入逻辑备份（如果有），而后检查和重放二进制日志
7.检查已经还原的数据
8.重新以完全访问模式重启服务器：注释前面在配置文件中添加的选项，并重启
本文转自wangfeng7399 51CTO博客，原文链接：http://blog.51cto.com/wangfeng7399/1394996，如需转载请自行联系原作者
