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

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

mysqldump备份工具

使用LVM逻辑卷快照备份

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

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

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

根据业务是否可在线

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

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

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

根据物理和逻辑

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

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

根据备份范围

完全备份：备份全部数据

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

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

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

191014493.png

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

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

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

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

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

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


193510676.png


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)