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

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

mysqldump备份工具

使用LVM逻辑卷快照备份

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

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

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

根据业务是否可在线

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

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

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

根据物理和逻辑

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

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

根据备份范围

完全备份：备份全部数据

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

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

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

Mysql之一：mysqldump和LVM逻辑卷快照

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

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

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

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

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

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


Mysql之一：mysqldump和LVM逻辑卷快照


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
#####查看快照时刻所处位置的时间点
[[email protected] save]# mysqlbinlog --start-position=107 /mydata/data/mysql-bin.000005
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected]@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)