#####################################
常见的数据备份类型和备份工具
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)
|