大纲
一、MySQL备份类型
二、MySQL备份都备份什么?
三、MySQL备份工具
四、MySQL备份策略
五、备份准备工作
六、备份策略具体演示
注:系统版本 CentOS6.4 X86_64,MySQL版本 MySQL 5.5.32,相关软件下载 http://yunpan.cn/QnymShsCMzGg9
一、MySQL备份类型
1.热备份、温备份、冷备份 (根据服务器状态)
-
热备份:读、写不受影响;
-
温备份:仅可以执行读操作;
-
冷备份:离线备份;读、写操作均中止;
2.物理备份与逻辑备份 (从对象来分)
-
物理备份:复制数据文件;
-
逻辑备份:将数据导出至文本文件中;
3.完全备份、增量备份、差异备份 (从数据收集来分)
4.逻辑备份的优点:
-
在备份速度上两种备份要取决于不同的存储引擎
-
物理备份的还原速度非常快。但是物理备份的最小力度只能做到表
-
逻辑备份保存的结构通常都是纯ASCII的,所以我们可以使用文本处理工具来处理
-
逻辑备份有非常强的兼容性,而物理备份则对版本要求非常高
-
逻辑备份也对保持数据的安全性有保证
5.逻辑备份的缺点:
6.增量备份与差异备份区别

说明,差异备份要比增量备份占用的空间大,但恢复时比较方便!但我们一般都用增量备份!
二、MySQL备份都备份什么?
我们备份,一般备份以下几个部分:
1.数据文件
2.日志文件(比如事务日志,二进制日志)
3.存储过程,存储函数,触发器
4.配置文件(十分重要,各个配置文件都要备份)
5.用于实现数据库备份的脚本,数据库自身清理的Croutab等……
三、MySQL备份工具
如下图,

上面的所有备份工具对比,下面我们就来说一下,常用的备份工具,
1.Mysql自带的备份工具
2.文件系统备份工具
-
cp 冷备份,支持所有引擎,复制命令,只能实现冷备,物理备份。使用归档工具,cp命令,对其进行备份的,备份速度快,还原速度几乎最快,但是灵活度很低,可以跨系统,但是跨平台能力很差。
-
lvm 几乎是热备份,支持所有引擎,基于快照(LVM,ZFS)的物理备份,速度非常快,几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用,所以备份速度比较快,恢复速度比较快,没有什么弹性空间,而且LVM的限制:不能对多个逻辑卷同一时间进行备份,所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。
3.其它工具
四、MySQL备份策略
1.策略一:直接拷贝数据库文件(文件系统备份工具 cp)(适合小型数据库,是最可靠的)
当你使用直接备份方法时,必须保证表不在被使用。如果服务器在你正在拷贝一个表时改变它,拷贝就失去意义。保证你的拷贝完整性的最好方法是关闭服务器,拷贝文件,然后重启服务器。如果你不想关闭服务器,要在执行表检查的同时锁定服务器。如果服务器在运行,相同的制约也适用于拷贝文件,而且你应该使用相同的锁定协议让服务器“安静下来”。当你完成了备份时,需要重启服务器(如果关闭了它)或释放加在表上的锁定(如果你让服务器运行)。要用直接拷贝文件把一个数据库从一台机器拷贝到另一台机器上,只是将文件拷贝到另一台服务器主机的适当数据目录下即可。要确保文件是MyIASM格式或两台机器有相同的硬件结构,否则你的数据库在另一台主机上有奇怪的内容。你也应该保证在另一台机器上的服务器在你正在安装数据库表时不访问它们。
2.策略二:mysqldump备份数据库(完全备份+增加备份,速度相对较慢,适合中小型数据库)(MyISAM是温备份,InnoDB是热备份)
mysqldump 是采用SQL级别的备份机制,它将数据表导成 SQL 脚本文件,在不同的 MySQL 版本之间升级时相对比较合适,这也是最常用的备份方法。mysqldump 比直接拷贝要慢些。对于中等级别业务量的系统来说,备份策略可以这么定:第一次完全备份,每天一次增量备份,每周再做一次完全备份,如此一直重复。而对于重要的且繁忙的系统来说,则可能需要每天一次全量备份,每小时一次增量备份,甚至更频繁。为了不影响线上业务,实现在线备份,并且能增量备份,最好的办法就是采用主从复制机制(replication),在
slave 机器上做备份。
3.策略三:lvs快照从物理角度实现几乎热备的完全备份,配合二进制日志备份实现增量备份,速度快适合比较烦忙的数据库
前提:
-
数据文件要在逻辑卷上;
-
此逻辑卷所在卷组必须有足够空间使用快照卷;
-
数据文件和事务日志要在同一个逻辑卷上;
步骤:
(1).打开会话,施加读锁,锁定所有表;
1
2
|
mysql>
FLUSH TABLES WITH READ LOCK;
mysql>
FLUSH LOGS;
|
(2).通过另一个终端,保存二进制日志文件及相关位置信息;
1
|
mysql
-uroot -p -e 'SHOW
MASTER STATUS\G' > /path/to/master .info
|
(3).创建快照卷
(4).释放锁
(5).挂载快照卷,备份
(6).删除快照卷;
(7).增量备份二进制日志;
4.策略四:xtrabackup 备份数据库,实现完全热备份与增量热备份(MyISAM是温备份,InnoDB是热备份),由于有的数据在设计之初,数据目录没有存放在LVM上,所以不能用LVM作备份,则用xtrabackup代替来备份数据库
说明:Xtrabackup是一个对InnoDB做数据备份的工具,支持在线热备份(备份时不影响数据读写),是商业备份工具InnoDB Hotbackup或ibbackup的一个很好的替代品。
Xtrabackup有两个主要的工具:xtrabackup、innobackupex
-
xtrabackup 只能备份InnoDB和XtraDB两种数据表,而不能备份MyISAM数据表。
-
innobackupex 是参考了InnoDB Hotbackup的innoback脚本修改而来的.innobackupex是一个perl脚本封装,封装了xtrabackup。主要是为了方便的 同时备份InnoDB和MyISAM引擎的表,但在处理myisam时需要加一个读锁。并且加入了一些使用的选项。如slave-info可以记录备份恢复后作为slave需要的一些信息,根据这些信息,可以很方便的利用备份来重做slave。
特点:
-
备份过程快速、可靠;
-
备份过程不会打断正在执行的事务;
-
能够基于压缩等功能节约磁盘空间和流量;
-
自动实现备份检验;
-
还原速度快;
5.策略五:主从复制(replication)实现数据库实时备份(集群中常用)
6.总结
单机备份是完全备份(所有数据库文件)+增量备份(备份二进制日志)相结合!
集群中备份是完全备份(所有数据库文件)+增量备份(备份二进制日志)+主从复制(replication)相结合的方法!
五、备份准备工作
1.查看服务器状态,
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql>
\s
--------------
mysql
Ver 14.14 Distrib 5.5.32, for Linux
(x86_64) using readline 5.1
Connection id :
1
Current
database:
Current
user: root@localhost
SSL:
Not in use
Current
pager: stdout
Using
outfile: ''
Using
delimiter: ;
Server
version: 5.5.32-log Source distribution
Protocol
version: 10
Connection:
Localhost via UNIX socket
Server
characterset: utf8
Db
characterset: utf8
Client
characterset: utf8
Conn.
characterset: utf8
UNIX
socket: /tmp/mysql .sock
Uptime:
2 min 0 sec
Threads:
1 Questions: 4 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.033
|
2.查看数据目录存放位置
1
2
3
4
5
6
7
|
mysql>
show variables like '%datadir%' ;
+---------------+---------------+
|
Variable_name | Value |
+---------------+---------------+
|
datadir | /mydata/data/ |
+---------------+---------------+
1
row in set (0.01
sec)
|
3.修改二进制日志的存放位置
(1).建立一目录用于存放二进制日志
1
2
3
4
|
[root@mysql
~]
[root@mysql
~]
[root@mysql
/]
drwxr-xr-x
2 mysql mysql 4096 7月 22 14:39 mybinlog
|
(2).修改my.cnf
1
2
3
|
[root@mysql
~]
log-bin= /mybinlog/mysql-bin
innodb_file_per_table
= 1
|
(3).重新启动mysqld
4.查看新生成的binlog日志
1
2
|
[root@mysql
~]
mysql-bin.000001
mysql-bin.index
|
5.准备一个test库,里面有两张表,t1表和t2表!
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
37
38
39
40
|
mysql>
show table status from test \G
***************************
1. row ***************************
Name:
t1
Engine:
MyISAM
Version:
10
Row_format:
Fixed
Rows:
167772160
Avg_row_length:
7
Data_length:
1174405120
Max_data_length:
1970324836974591
Index_length:
1024
Data_free:
0
Auto_increment:
NULL
Create_time:
2013-07-21 19:37:44
Update_time:
2013-07-21 19:52:48
Check_time:
NULL
Collation:
utf8_general_ci
Checksum:
NULL
Create_options:
Comment:
***************************
2. row ***************************
Name:
t2
Engine:
InnoDB
Version:
10
Row_format:
Compact
Rows:
20971797
Avg_row_length:
31
Data_length:
667942912
Max_data_length:
0
Index_length:
0
Data_free:
4194304
Auto_increment:
NULL
Create_time:
2013-07-21 20:00:29
Update_time:
NULL
Check_time:
NULL
Collation:
utf8_general_ci
Checksum:
NULL
Create_options:
Comment:
2
rows in set (0.01
sec)
|
第一张t1表,使用的是MyISAM引擎,其中有1亿多行数据,第二张t2表,使用的是INNODB引擎,其中有2千多万行数据!有博友会问了,你是在做测试怎么会有这么多数据的,下面我就的大家说一下,快速插入1亿条数据的方法!具体操作如下,
t1表,
1
2
3
4
5
6
7
8
|
mysql>use test ;
mysql>create
table t1 (
id int(10)
default null
)engine=myisam
default charset=utf8;
mysql>
show create table t1;
mysql>insert
into t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
mysql>insert
into t1 select *
from t1;
mysql> select count(*)
from t1;
|
t2表,
1
2
3
4
5
6
7
|
mysql>create
table t2 (
id int(10)
default null
)engine=innodb
default charset=utf8;
mysql>
show create table t2;
mysql>insert
into t2 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
mysql>insert
into t2 select *
from t2;
mysql> select count(*)
from t2;
|
好了,下面我们就来详细说一说备份与还原!
六、备份策略具体演示
1.策略一:直接拷贝数据库文件(文件系统备份工具 cp)(适合小型数据库)
(1).标准流程:锁表->刷新表到磁盘->拷贝文件->解锁(注,若有有可能的话,可以先停止数据库,再用cp命令准备,这样备份的数据最可靠)
(2).具体步骤:
a.打开第一个终端,
1
2
3
|
[root@mysql
data]
mysql>
FLUSH TABLES WITH READ LOCK;
Query
OK, 0 rows affected (0.00 sec)
|
b.打开第二个终端
1
2
|
[root@mysql
data]
[root@mysql
data]
|
c.在第一个终端解锁
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql>
UNLOCK TABLES;
Query
OK, 0 rows affected (0.01 sec)
[root@mysql
~]
总用量
267468
-rw-rw----
1 mysql mysql 262221824 7月 21 20:17 ibdata1
-rw-rw----
1 mysql mysql 5242880 7月 22 13:40 ib_logfile0
-rw-rw----
1 mysql mysql 5242880 7月 22 13:40 ib_logfile1
drwx------
2 mysql mysql 4096 7月 20 12:33 mysql
-rw-rw----
1 mysql mysql 27698 7月 20 12:33 mysql-bin.000001
-rw-rw----
1 mysql mysql 190 7月 22 13:40 mysql-bin.index
-rw-rw----
1 mysql mysql 1925 7月 21 13:07 mysql-slow.log
-rw-r-----
1 mysql mysql 21906 7月 22 13:40 mysql. test .com.err
-rw-rw----
1 mysql mysql 5 7月 22 13:40 mysql. test .com.pid
drwx------
2 mysql mysql 4096 7月 20 12:33 performance_schema
drwx------
2 mysql mysql 4096 7月 21 20:00 test
|
(3).模拟数据库损坏
直接删除数据目录中的所有文件
1
2
3
4
|
[root@mysql
data]
[root@mysql
data]
总用量
0
[root@mysql
data]
|
(4).具体还原步骤
a.mysql这时是无法停止的
1
2
|
[root@mysql
mydata]
ERROR!
MySQL server PID file could
not be found!
|
b.查找mysql所有进程
1
2
3
|
[root@mysql
mydata]
root
2728 0.0 0.1 11300 1520 pts /1 S
15:01 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir= /mydata/data --pid- file = /mydata/data/mysql . test .com.pid
mysql
3029 0.1 9.1 773908 92312 pts /1 Sl
15:01 0:00 /usr/local/mysql/bin/mysqld --basedir= /usr/local/mysql --datadir= /mydata/data --plugin- dir = /usr/local/mysql/lib/plugin --user=mysql
--log-error= /mydata/data/mysql . test .com.err
--pid- file = /mydata/data/mysql . test .com.pid
--socket= /tmp/mysql .sock
--port=3306
|
c.杀死mysql的所有进程
d.初始化mysql
e.复制完全备份的数据文件到数据目录中
1
2
|
[root@mysql test ]
[root@mysql test ]
|
f.启动mysql数据库
1
2
|
[root@mysql test ]
Starting
MySQL SUCCESS!
|
g.测试并查看数据
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
|
[root@mysql test ]
Welcome
to the MySQL monitor. Commands end with ; or \g.
Your
MySQL connection id is
1
Server
version: 5.5.32-log Source distribution
Copyright
(c) 2000, 2013, Oracle and /or its
affiliates. All rights reserved.
Oracle
is a registered trademark of Oracle Corporation and /or its
affiliates.
Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help.
Type '\c' to clear the
current input statement.
mysql>
use test
Database
changed
mysql>
show tables;
+----------------+
|
Tables_in_test |
+----------------+
|
t1 |
|
t2 |
+----------------+
2
rows in set (0.01
sec)
mysql> select count(*)
from t1;
+-----------+
|
count(*) |
+-----------+
|
167772160 |
+-----------+
1
row in set (0.01
sec)
mysql> select count(*)
from t2;
+----------+
|
count(*) |
+----------+
|
20971520 |
+----------+
1
row in set (9.95
sec)
|
大家可以看到所有数据都恢复了,嘿嘿!
(5).总结
cp命令,对其进行的备份,速度快,还原速度几乎最快,但是灵活度很低,可以跨系统,但是跨平台能力很差,适合小型数据库备份!
2.策略二:mysqldump备份数据库(完全备份+增加备份,速度相对较慢,适合中小型数据库)(MyISAM是温备份,InnoDB是热备份)
(1).mysqldump命令详解
1
2
3
4
5
6
7
8
|
mysqldump
--all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/mybackup/2013-07-22-16-20 .full.sql
--all-tables
--lock-all-tables
--routinge
--triggers
--events
--master-data=2
--flush-logs
|
(2).具体备份过程如下
a.查看备份前的binlog日志
1
2
3
4
5
6
7
|
mysql>
show master status;
+------------------+----------+--------------+------------------+
|
File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
|
mysql-bin.000022 | 107 | | |
+------------------+----------+--------------+------------------+
1
row in set (0.01
sec)
|
b.备份所有库(完全备份)
c.查看备份是否成功
1
2
3
|
[root@mysql
mybackup]
总用量
739M
-rw-r--r--
1 root root 739M 7月 22 16:31 2013-07-22-16-20.full.sql
|
d.查看新生成的binlog日志
1
2
3
4
5
6
7
|
mysql>
show master status;
+------------------+----------+--------------+------------------+
|
File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
|
mysql-bin.000023 | 107 | | |
+------------------+----------+--------------+------------------+
1
row in set (0.01
sec)
|
e.插入几条新的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql>
use test ;
Database
changed
mysql>
show tables;
+----------------+
|
Tables_in_test |
+----------------+
|
t1 |
|
t2 |
+----------------+
2
rows in set (0.00
sec)
mysql> select count(*)
from t1;
+-----------+
|
count(*) |
+-----------+
|
167772160 |
+-----------+
1
row in set (0.01
sec)
mysql>
insert into t1 values(167772164),(167772165),(167772166);
|
f.再次查看binlog日志
1
2
3
4
5
6
7
|
mysql>
show master status;
+------------------+----------+--------------+------------------+
|
File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
|
mysql-bin.000023 | 363 | | |
+------------------+----------+--------------+------------------+
1
row in set (0.01
sec)
|
g.备份二进制日志(增量备份)
h.查看备份的二进制日志
1
2
3
4
|
[root@mysql
mybackup]
总用量
756264
-rw-r-----
1 root root 363 7月 22 16:34 2013-07-22-16-20.binlog.full.000001
-rw-r--r--
1 root root 774402118 7月 22 16:31 2013-07-22-16-20.full.sql
|
i.模拟数据库损坏
1
2
3
4
5
6
7
8
9
|
[root@mysql
mybackup]
[root@mysql
data]
ibdata1
mysql-bin.000001 mysql-bin.000005 mysql-bin.000009 mysql. test .com.err
ib_logfile0
mysql-bin.000002 mysql-bin.000006 mysql-bin.000010 mysql. test .com.pid
ib_logfile1
mysql-bin.000003 mysql-bin.000007 mysql-bin.index performance_schema
mysql
mysql-bin.000004 mysql-bin.000008 mysql-slow.log test
[root@mysql
data]
[root@mysql
data]
总用量
0
|
(3).具体还原过程如下,
a.查找mysql进程
1
2
3
4
5
|
[root@mysql
data]
Warning:
bad syntax, perhaps a bogus '-' ?
See /usr/share/doc/procps-3 .2.8 /FAQ
root
3599 0.0 0.1 11304 1340 pts /1 S
15:18 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir= /mydata/data --pid- file = /mydata/data/mysql . test .com.pid
mysql
3901 4.7 21.5 1167384 218684 pts /1 Sl
15:18 3:49 /usr/local/mysql/bin/mysqld --basedir= /usr/local/mysql --datadir= /mydata/data --plugin- dir = /usr/local/mysql/lib/plugin --user=mysql
--log-error= /mydata/data/mysql . test .com.err
--pid- file = /mydata/data/mysql . test .com.pid
--socket= /tmp/mysql .sock
--port=3306
root
4469 0.0 0.0 103244 876 pts /1 S+
16:38 0:00 grep mysqld
|
b.杀死所有进程
c.初始化mysql并启动mysql
d.因为我们不是全新初始化的,可能会有报错的二进制日志,所有我们这里全部删除
e.启动mysql数据库,启动时会重新生成新的二进制日志的
f.恢复到备份状态,备份前先关闭对恢复过程的二进制日志记录,因为记录恢复语句是毫无意义的
1
2
|
mysql> set global
sql_log_bin=0;
mysq> source /root/mybackup/2013-07-22-16-20 .full.sql
|
g.打开另一个终端查询数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
mysql> select count(*)
from t1;
+-----------+
|
count(*) |
+-----------+
|
167772163 |
+-----------+
1
row in set (1
min 29.63 sec)
mysql>
show tables;
+----------------+
|
Tables_in_test |
+----------------+
|
t1 |
|
t2 |
+----------------+
2
rows in set (0.00
sec)
mysql> select count(*)
from t2;
+----------+
|
count(*) |
+----------+
|
20971520 |
+----------+
1
row in set (46.14
sec)
|
h.查看最后十条数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> select *
from t1 order by id desc
limit 10;
+-----------+
| id |
+-----------+
|
167772163 |
|
167772162 |
|
167772161 |
|
10 |
|
10 |
|
10 |
|
10 |
|
10 |
|
10 |
|
10 |
+-----------+
10
rows in set (0.00
sec)
|
大家可以看到,我们已经恢复到,完全备份时的状态,但我们最后插入的三条数据没有恢复,下面我们恢复,数据库损坏前我们插入的三条数据!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
[root@mysql
~]
[root@mysql
~]
mysql> select *
from t1 order by id desc
limit 10;
+-----------+
| id |
+-----------+
|
167772166 |
|
167772165 |
|
167772164 |
|
167772163 |
|
167772162 |
|
167772161 |
|
10 |
|
10 |
|
10 |
|
10 |
+-----------+
10
rows in set (47.01
sec)
mysql>
|
大家可以看到,已经恢复我们最后增加的三条数据!
i.最后,打开二进制记录并查看恢复状况
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> set global
sql_log_bin=1;
mysql>
show databases;
+--------------------+
|
Database |
+--------------------+
|
information_schema |
|
mysql |
|
performance_schema |
| test |
+--------------------+
4
rows in set (0.01
sec)
mysql>
use test ;
Database
changed
mysql>
show tables;
+----------------+
|
Tables_in_test |
+----------------+
|
t1 |
|
t2 |
+----------------+
2
rows in set (0.00
sec)
|
(4).总结:
基于mysqldump通常我们就是完整备份+二进制日志来进行恢复的!
3.策略三:lvs快照从物理角度实现几乎热备的完全备份,配合二进制日志备份实现增量备份,速度快适合比较烦忙的数据库!
说明:要求你的MySQL的数据目录必须在lvm卷上!
具体步骤如下,
(1).在MySQL中为所有表加读锁,不要关闭终端,否则锁将失效,滚动日志
1
2
3
4
5
6
7
8
9
10
11
|
mysql>
flush tables with read lock;
Query
OK, 0 rows affected (0.01 sec)
mysql>
flush logs;
Query
OK, 0 rows affected (0.02 sec)
mysql>
show master status;
+------------------+----------+--------------+------------------+
|
File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
|
mysql-bin.000004 | 107 | | |
+------------------+----------+--------------+------------------+
1
row in set (0.00
sec)
|
(2).另开一终端速度建立快照,我的那个卷组是/dev/myvg/mydata
(3).速度释放读锁
1
2
|
mysql>
unlock tables;
Query
OK, 0 rows affected (0.00 sec)
|
(4).挂载快照,拷备出来,卸载快照,删除快照
1
2
3
4
5
|
[root@mysql
~]
[root@mysql
~]
[root@mysql
~]
[root@mysql
~]
[root@mysql
~]
|
(5).就这样一次完整备份就完成了,下面来测试能否正常使用
1
2
3
4
|
[root@mysql
~]
[root@mysql
~]
[root@mysql
~]
[root@mysql
~]
|
(6).如果在完整备份后MySQL出现故障,与mysqldump一样,先恢复上次的完整备份,再利用二进制日志恢复,找到完整备份时的二进制位置,把从那时到故障前的日志用mysqlbinlog导出来,然后再导入到MySQL中。这个同mysqldump中实验一致就不重复了。
(7).总结
用lvm的快照来备份速度是非常快的,而且几乎热备,恢复也很快速,操作也简单,完整恢复后再将相应二进制恢复即可。
4.策略四:xtrabackup 备份数据库,实现完全热备份与增量热备份(MyISAM是温备份,InnoDB是热备份)
(1).安装percona-xtrabackup-2.1.3-608所需的依赖包
(2).解压软件包键入命令文件目录
1
2
3
4
5
6
7
8
9
10
11
|
[root@mysql
~]
[root@mysql
src]
[root@mysql
bin]
总用量
112284
-rwxr-xr-x
2 root root 110738 5月 23 02:50 innobackupex
lrwxrwxrwx
2 root root 12 7月 23 04:48 innobackupex-1.5.1 -> innobackupex
-rwxr-xr-x
2 root root 2211237 5月 23 02:50 xbcrypt
-rwxr-xr-x
2 root root 2285672 5月 23 02:50 xbstream
-rwxr-xr-x
2 root root 13033745 5月 23 02:50 xtrabackup
-rwxr-xr-x
2 root root 16333506 5月 23 02:28 xtrabackup_55
-rwxr-xr-x
2 root root 80988093 5月 23 02:40 xtrabackup_56
|
(3).将innobackupex、xtrabackup等文件copy到mysql程序目录下/bin、目录
(4).将mysql安装目录下的文件做软链接到/usr/bin/目录下。这个比变量方便,这样就完成了部署安装
(5).测试
1
2
3
4
|
[root@mysql
bin]
innobackupex
innobackupex-1.5.1
[root@mysql
bin]
xtrabackup
xtrabackup_55 xtrabackup_56
|
(6).查看innobackupex选项
(7).设置mysql密码
(8).全库备份
报错,
1
2
|
xtrabackup:
Error: Please set parameter 'datadir'
innobackupex:
Error: ibbackup child process has died at /usr/bin/innobackupex line
389.
|
解决方法,
1
2
3
|
[root@mysql
data]
datadir
= /mydata/data
|
再次执行成功,
1
2
|
[root@mysql
xtrabackup]
130723
05:29:13 innobackupex: completed OK!
|
(9).查看备份文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
[root@mysql
xtrabackup]
总用量
4
drwxr-xr-x
5 root root 4096 7月 23 05:33 2013-07-23_05-32-51
[root@mysql
xtrabackup]
[root@mysql
2013-07-23_05-32-51]
总用量
190496
-rw-r--r--
1 root root 260 7月 23 05:32 backup-my.cnf
-rw-r-----
1 root root 195035136 7月 23 05:32 ibdata1
drwxr-xr-x
2 root root 4096 7月 23 05:33 mysql
drwxr-xr-x
2 root root 4096 7月 23 05:33 performance_schema
drwx------
2 root root 4096 7月 23 05:33 test
-rw-r--r--
1 root root 13 7月 23 05:33 xtrabackup_binary
-rw-r--r--
1 root root 23 7月 23 05:33 xtrabackup_binlog_info
-rw-r-----
1 root root 95 7月 23 05:33 xtrabackup_checkpoints
-rw-r-----
1 root root 2560 7月 23 05:33 xtrabackup_logfile
|
数据会完整备份到/root/mybackup/xtrabackup/中目录名字为当前的日期,xtrabackup会备份所有的InnoDB表,MyISAM表只是复制表结构文件、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件,同时还会备份触发器和数据库配置信息相关的文件。除了保存数据外还生成了一些xtrabackup需要的数据文件,详解如下:
-
xtrabackup_checkpoints 备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN(日志序列号)范围信息;每个InnoDB页(通常为16k大小)都会包含一个日志序列号,即LSN。LSN是整个数据库系统的系统版本号,每个页面相关的LSN能够表明此页面最近是如何发生改变的。
-
xtrabackup_binlog_info mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
-
xtrabackup_binary 备份中用到的xtrabackup的可执行文件。
-
backup-my.cnf 备份命令用到的配置选项信息。
-
xtrabackup_logfile 记录标准输出信息xtrabackup_logfile
(10).测试恢复MySQL,用xtrabackup来完整恢复
1
2
3
4
5
6
7
8
|
[root@mysql
data]
[root@mysql
data]
[root@mysql
data]
[root@mysql
data]
[root@mysql
data]
[root@mysql
data]
|
(11).在表中新增一些数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql>
insert into t1 values (123),(456),(789);
mysql>
use test ;
Database
changed
mysql> select *
from t1 order by id desc
limit 10;
+------+
| id |
+------+
|
789 |
|
456 |
|
333 |
|
222 |
|
123 |
|
111 |
|
33 |
|
22 |
|
11 |
|
10 |
+------+
10
rows in set (9.47
sec)
|
(12).增量备份
1
2
3
4
|
[root@mysql
data]
注:增量备份只能对InnoDB引擎做增量备份,对MyISAM的表是完全复制
|
(13).测试增量备份恢复
1
2
3
4
5
6
|
[root@mysql
data]
[root@mysql
data]
[root@mysql
data]
因为这个事务可能在增量备份中提交,假如的撤消了增量备份中就提交不,因为事务已经不完整
|
(14).将增量备份全部并到完整备份中去
注:这个会使增量备份中的的数据合并到完整备份中,如果还有增量备份,继续合并,恢复时恢复完整备份即可
(15).恢复数据,并起动MySQL
1
2
3
|
[root@mysql
data]
[root@mysql
data]
[root@mysql
data]
|
(16).查看数据有没丢失
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
[root@mysql
data]
mysql>
use test ;
Database
changed
mysql> select *
from t1 order by id desc
limit 10;
+------+
| id |
+------+
|
789 |
|
456 |
|
333 |
|
222 |
|
123 |
|
111 |
|
33 |
|
22 |
|
11 |
|
10 |
+------+
10
rows in set (9.47
sec)
|
所有数据全部恢复!
17.总结
如果在增量备份后数据库出现故障,我们需要通过完整备份+到现在为止的所有增量备份+最后一次增量备份到现在的二进制日志来恢复。
18.附注
单独备份:
1
|
innobackupex
--user=root --password=123456 --defaults- file = /etc/my .cnf
--database= test /root/mybackup
|
备份并打包压缩:
1
|
innobackupex
--user=root --password=123456 --defaults- file = /etc/my .cnf
--database= test --stream= tar /root/mybackup/ | gzip > /root/mybackup/testdb . tar .gz
|
带时间戳:
1
|
innobackupex
--user=root --password=123456 --defaults- file = /etc/my .cnf
--database= test --stream= tar /root/mybackup/ | gzip > /root/mybackup/ ` date +%F`_testdb. tar .gz
|
备份信息输出重定向到文件:
1
|
innobackupex
--user=root --password=123456 --defaults- file = /etc/my .cnf
--database= test --stream= tar /root/mybackup/ 2> /root/mybackup/test .log
| gzip 1> /root/mybackup/test . tar .gz
|
说明:
1
2
3
4
5
6
7
|
--stream
--database= test
2> /root/mybackup/test .log
1> /root/mybackup/test . tar .gz
解压 tar -izxvf
要加-i参数,官方解释 innobackupex: You must use -i (--ignore-zeros) option for extraction
of the tar stream.
在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。
此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
|
5.策略五:主从复制(replication)实现数据库实时备份(集群中常用)
参考: