#####################################

常见的数据备份类型和备份工具

mysqldump备份工具

使用LVM逻辑卷快照备份

#####################################

常见的数据备份类型和备份工具

数据的重要性我就不多说了……，常见的数据备份类型是根据不同的备份性质和要求来划分的，他们分别是：根据业务是否可在线、根据物理和逻辑、根据备份范围，下面就来简单的扫盲一下：

根据业务是否可在线

热备份：读写操作不受影响

温备份：仅可以执行读操作

冷备份：离线备份，读写操作均不能进行

根据物理和逻辑

物理备份：直接复制数据文件，速度快(在文件系统级别完成，不需要mysql进程参与)，移植性差。

逻辑备份：将数据导出至文本文件中，速度慢(需要mysql进程参数)，移植性好，丢失浮点数精度，可以方便使用文本处理工具。

根据备份范围

完全备份：备份全部数据

增量备份：仅备份上次完全备份或增量备份以后变化的数据(比增量备份多占据空间，但是还原的时候方便)

差异备份：仅备份上次完全备份以来变化的数据

这里特意盗用一幅图来更加直观的阐述增量备份和差异备份的区别：

完全+增量(还原时间长，复杂，占用空间小)

完全+差异(还原时间短，简单，占用空间大)

他们之间没有约束，比如 完全在线物理备份……

另外备份出的数据要经常测试是否可用，和制定好紧急还原方案………

备份的对象有数据、配置文件、二进制日志、事务日志等……

各个备份工具功能和性能对比如下图所示：



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
mysql> create database soulboy;
Query OK, 1 row affected (0.00 sec)
mysql> use soulboy;
Database changed
mysql> create table tb1 (
id 
int auto_increment primary key,name char(30));
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into tb1 (name) values (
'kobe'
);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> 
select 
* from tb1;               
#新库
+----+------+
| 
id 
| name |
+----+------+
|  1 | kobe |
+----+------+
1 row 
in 
set 
(0.00 sec)
完全备份，比例中表类型是InnoDB
mysqldump -uroot -p --master-data=2 --single-transaction  --all-databases  > 
/backup/alldatabases
.sql
###########################补充说明###############################
mysqldum只能对MyISAM引擎做温备份，备份之前必须锁定表,如下：
mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables > 
/root/alldatabases
.sql
##########################mysqldump选项###########################
-u                        
#指定用户名
-p                        
#指定用户密码
-h                        
#指定主机地址
-A|--all-databases        
#备份所有数据库
--databases               
#备份指定数据库
--single-transcation      
#基于此项可以实现对InnoDB表做热备份，但不需要使用
--lock-all-tables         
#执行备份时为所有表请求加锁
-E|--events               
#备份事件调度器代码
--opt                     
#同时启动各种高级选项
-R|--routines             
#备份存储过程和存储函数
--flush-logs              
#备份之前刷新日志
--triggers                
#备份触发器
--master-data=2           
#该选项将会记录binlog的日志位置与文件名并追加到文件中，如果为1将会输出CHANGE

第一天

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> use soulboy;
Database changed
mysql> insert into tb1 (name) values (
'wade'
);
Query OK, 1 row affected (0.00 sec)
mysql> 
select 
* from tb1;
+----+------+
| 
id 
| name |
+----+------+
|  1 | kobe |
|  2 | wade |
+----+------+
2 rows 
in 
set 
(0.00 sec)
####滚动二进制日志，进入第二天
mysql> FLUSH LOGS;     
Query OK, 0 rows affected (0.01 sec)
####备份第一天二进制日志为SQL格式并重命名为firstdays.sql
mysqlbinlog 
/mydata/data/mysql-bin
.000008 > 
/backup/firstday
.sql

第二天

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#####插入新数据
mysql> insert into tb1 (name) values (
'nash'
);
Query OK, 1 row affected (0.00 sec)
mysql> 
select 
* from tb1;
+----+------+
| 
id 
| name |
+----+------+
|  1 | kobe |
|  2 | wade |
|  3 | nash |
+----+------+
3 rows 
in 
set 
(0.00 sec)
#####备份第二天二进制日志为SQL格式并重命名为secondday.sql
#####注意生产环境中务必将二进制日志存储在不同于数据目录的设备中
 
mysqlbinlog  
/mydata/data/mysql-bin
.000009 > 
/backup/secondday
.sql

模拟故障

1
2
3
4
5
6
7
8
9
10
cd 
/mydata/data
#####删除数据目录中所有文件
rm 
-rf *   
cd 
/usr/local/mysql
#####初始化数据库
scripts
/mysql_install_db 
--user=myslq --datadir=
/mydata/data/
#####杀死僵死进程
ps 
aux | 
grep 
mysql
kill  
pid         
service mysqld restart

数据还原

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#####查看当前使用的二进制日志文件以及所处位置
show master status;
#####禁止还原过程中记录二进制日志
mysql> 
set 
sql_log_bin=0;
#####还原完全备份
mysql> 
source 
/backup/alldatabases
.sql;
#####还原第一天备份
mysql> 
source 
/backup/firstday
.sql;
#####还原第二天备份，全部数据完全到此结束
mysql> 
source 
/backup/secondday
.sql;
#####开启二进制日志记录功能
mysql> 
set 
sql_log_bin=1;
#####测试数据是否还原成功
mysql> use soulboy;
mysql> 
select 
* from tb1;
+----+------+
| 
id 
| name |
+----+------+
|  1 | kobe |
|  2 | wade |
|  3 | nash |
+----+------+
3 rows 
in 
set 
(0.00 sec)


使用LVM逻辑卷快照备份

锁表并滚动日志

1
2
3
4
5
6
#####锁表
mysql> FLUSH TABLES WITH READ LOCK;
#####滚动二进制日志文件
mysql> FLUSH LOGS;
#####即时点恢复时候需要使用，记录LVM时刻二进制日志文件和所处位置
mysql -e 
'SHOW MASTER STATUS\G' 
> 
/backup/master-
`
date 
+%F`.info

数据目录和事务文件创建LVM快照

1
2
3
4
5
6
7
8
9
10
11
12
#####对mydata目录进行快照
lvcreate -L 200M -s -p r -n mydata-snap 
/dev/myvg/mydata
#####以只读方式挂载镜像文件
mount 
/dev/myvg/mydata-snap 
/mnt 
-o ro
#####将镜像文件内容复制到/save目录中
mkdir 
/save
cp 
-a 
/mnt/data/
* 
/save/
#####删除镜像文件
umount 
/mnt
lvremove --force 
/dev/myvg/mydata-snap
#####删除二进制日志文件
rm 
-rf 
/save/mysql-bin
.*

解锁并插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#####解锁
mysql> UNLOCK TABLES;
#####查看当前表内容
mysql> 
select 
* from tb1;
+----+------+
| 
id 
| name |
+----+------+
|  1 | kobe |
|  2 | wade |
|  3 | nash |
+----+------+
3 rows 
in 
set 
(0.00 sec)
#####插入新数据，模拟第一天
insert into tb1 (name) values (
'firstday'
);
#####滚动二进制日志文件，模拟第二天
flush logs;                                 
insert into tb1 (name) values (
'sencondday'
);

查看快照时间和当前二进制日志信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#####查看快照时刻的二进制日志记录信息
cat 
/backup/master-2013-09-19
.info          
*************************** 1. row ***************************
            
File: mysql-bin.000005
        
Position: 107
    
Binlog_Do_DB:
Binlog_Ignore_DB:
#####查看使用当前二进制日志和所处位置           
mysql> SHOW MASTER  STATUS;                  
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      343 |              |                  |
+------------------+----------+--------------+------------------+

基于多个二进制日志生成单个SQL格式文件

1
2
3
4
5
6
7
8
9
#####查看快照时刻所处位置的时间点
[root@localhost save]
# mysqlbinlog --start-position=107 /mydata/data/mysql-bin.000005
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#130919 11:46:29 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.28-log created 130919 11:46:29  #时间
#####基于时间做限定来合并多个日志文件为一个SQL格式的文件
mysqlbinlog --start-datetime=
'2013-09-19 11:46:29' 
/mydata/data/mysql-bin
.000005  
/mydata/data/mysql-bin
.000006 > 
/backup/incremental-
`
date 
+%F`.sql

模拟故障

1
2
3
4
#####停止服务
service mysqld stop
#####删除数目目录下所有文件
rm 
-rf 
/mydata/data/
*

还原快照时刻数据

1
2
3
4
5
6
7
8
9
10
11
12
13
#####复制快照时刻数据到数据目录
cp 
-a 
/save/
*  
/mydata/data/
#####启动服务
service mysqld start
#####查看当前表内数据
mysql> 
select 
* from tb1;
+----+------+
| 
id 
| name |
+----+------+
|  1 | kobe |
|  2 | wade |
|  3 | nash |
+----+------+

还原增量数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#####关闭二进制日志记录功能
mysql> 
set 
sql_log_bin=0;
#####还原增量备份的数据
mysql> 
source 
/backup/incremental-2013-09-19
.sql
#####开启二进制日志记录功能
mysql> 
set 
sql_log_bin=0;
#####查看表内数据，还原成功
mysql> 
select 
* from tb1;
+----+------------+
| 
id 
| name       |
+----+------------+
|  1 | kobe       |
|  2 | wade       |
|  3 | nash       |
|  4 | firstday   |
|  5 | sencondday |
+----+------------+
5 rows 
in 
set 
(0.00 sec)