一、mysql数据库备份的前因后果
1、备份的概念：
将数据收集并保存至另外的多个副本，其目的是将数据还原恢复至备份数据时那个状态。
2、备份数据的原因
1）做灾难恢复使用，要将数据副本做到异地多份备份；
2）数据库数据改动时使用备份；
3）需要对当前服务器做相关测试时使用备份，备份与测试都是最好在服务器访问量最少时进行。
3、备份的事先注意事项
1）可以容忍丢失多长时间的数据；
2）恢复数据能在多长时间内完成；
3）是否需要持续提供服务；
4）需要恢复哪些内容，整个服务器的数据库，单个数据库，一个或多个表。
二、数据的备份类型
1、根据是否需要数据库离线可分为
1）冷备：cold backup
备份需要关闭mysql服务或读写请求均不允许；
2）温备：warm backup
备份的同时，mysql服务在线，只允许读不允许写，在线交易要终止：
3）热备：hot backup
备份的同时，mysql服务在线，支持读写请求，业务不受影响，但服务器的性能会有所下降。
2、根据要备份的数据范围可分为
1）完全备份：full backup
备份当前状态的整个数据库的数据；
2）增量备份：increment backup
备份基于上次的完全备份或增量备份以来所改变的数据；
3）差异备份：different backup
备份基于上次的完全备份改变了的数据。
3、根据备份数据是否为文件可分为
1）物理备份：直接备份数据库文件
2）逻辑备份：备份表中的数据和库代码
三、备份的对象及备份工具
1、备份对象
1）数据
2）配置文件
3）代码、存储过程、存储函数、触发器等
4）OS相关的配置文件
5）复制相关的配置
6）二进制日志
2、引擎所支持的备份
MyISAM引擎只支持温备，而InnoDB还支持热备。
3、备份工具
1）mysqldump，mysql客户端的经典备份工具
为逻辑备份工具，备份和恢复比较慢；
2）mylvmdumper，mysqldump升级版
多线程的逻辑备份工具，备份和恢复速度稍快于mysqldump；
3）lvm-snapshot,基于快照卷的备份工具
接近于热备的物理备份工具，备份和恢复的速度较快；
4）select ，mysql内置的备份工具
逻辑备份工具，速度快于mysqldump；
select into outfile；
load data infile；
5）xtrabackup，由percana提供的免费开源备份工具
为物理备份工具，速度快。
6）mysql hotcopy：几乎冷备的工具，一般不采用，速度慢。
四、数据从备份到恢复的完整流程
1）停止mysql服务；
2）记录服务和配置文件权限；
3）复制备份文件与数据目录；
4）按需调整配置；
5）按需改变文件权限；
6）尝试启动服务；
7）装载逻辑备份；
8）检查和重放二进制日志；
9）确定数据还原正常完成；
10）以完全权限重启服务器。
五、使用mysqldump进行数据备份及恢复
1：为测试的数据库及二进制创建备份目录；
|
1
2
3
4
|
[root@node1 ~]# mkdir -pv {/mydata/data,/backup,/
var
/binlog}
mkdir: created directory `/mydata/data'
mkdir: created directory `/backup'
mkdir: created directory `/
var
/binlog'
|
2：启动mysqld服务，创建测试数据库，并创建测试数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[root@node1 ~]
# service mysqld restart
Shutting down MySQL.... [ OK ]
Starting MySQL.. [ OK ]
[root@node1 ~]
#mysql
mysql> create database students;
Query OK, 1 row affected (0.00 sec)
mysql> use students
Database changed
mysql> create table TLtb (Id tinyint unsigned not null primary key auto_increment,Name char(20) not null unique key,Age tinyint unsigned,Gender char(1) default
'M'
,Courses char(30) not null);
Query OK, 0 rows affected (0.28 sec)
mysql> insert into TLtb (Name,Age,Gender,Courses) values (
'Xu zu'
,20,
'M'
,
'Xiao Wuxianggong'
),(
'Qiao Feng'
,28,
'M'
,
'Xianglong Shibazhang'
),(
'Duan Fu'
,23,
'M'
,
'Liumai Shenjian'
);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
select
* from TLtb;
+----+-----------+------+--------+----------------------+
| Id | Name | Age | Gender | Courses |
+----+-----------+------+--------+----------------------+
| 1 | Xu zu | 20 | M | Xiao Wuxianggong |
| 2 | Qiao Feng | 28 | M | Xianglong Shibazhang |
| 3 | Duan Fu | 23 | M | Liumai Shenjian |
+----+-----------+------+--------+----------------------+
3 rows
in
set
(0.01 sec)
|
3：使用mysqldump对测试数据库进行备份，并且滚动二进制日志，记录日志位置；
|
1
2
3
|
[root@node1 ~]# mysqldump --lock-all-tables --flush-logs --master-data=
2
--databases students > /backup/students_`date +%F`.sq
[root@node1 ~]# cp /
var
/binlog/mysql-bin.
mysql-bin.
000001
mysql-bin.
000002
mysql-bin.
000003
mysql-bin.
000004
mysql-bin.
000005
mysql-bin.
000006
mysql-bin.index
|
4：备份二进制日志文件；
|
1
2
3
4
|
[root@node1 ~]# cp /
var
/binlog/mysql-bin.
00000
* /backup/
[root@node1 ~]# ls /backup/
mysql-bin.
000001
mysql-bin.
000003
mysql-bin.
000005
students_2013-
09
-
30
.sql
mysql-bin.
000002
mysql-bin.
000004
mysql-bin.
000006
students_.sql
|
5：新增数据库数据进行增量备份，查看当前日志位置；
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> create table CDtb (Id tinyint unsigned not
null
primary key auto_increment,Name char(
20
) not
null
unique key,Age tinyint unsigned,Gender char(
1
)
default
'M'
,Courses char(
30
) not
null
);
Query OK,
0
rows affected (
0.14
sec)
mysql> insert into CDtb (Name,Age,Gender,Courses) values (
'Yideng Dashi'
,
80
,
'M'
,
'Yiyangzhi'
),(
'Hong Qigong'
,
66
,
'M'
,
'Dagou Bangfa'
),(
'Huang Yaoshi'
,
60
,
'M'
,
'Tanzhi Shengong'
);
Query OK,
3
rows affected (
0.05
sec)
Records:
3
Duplicates:
0
Warnings:
0
mysql> select * from CDtb;
+----+--------------+------+--------+-----------------+
| Id | Name | Age | Gender | Courses |
+----+--------------+------+--------+-----------------+
|
1
| Yideng Dashi |
80
| M | Yiyangzhi |
|
2
| Hong Qigong |
66
| M | Dagou Bangfa |
|
3
| Huang Yaoshi |
60
| M | Tanzhi Shengong |
+----+--------------+------+--------+-----------------+
3
rows
in
set
(
0.01
sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.
000006
|
716
| | |
+------------------+----------+--------------+------------------+
1
row
in
set
(
0.00
sec)
|
|
1
2
3
|
[root@node1 ~]# less /backup/students_2013-
09
-
30
.sql
-- CHANGE MASTER TO MASTER_LOG_FILE=
'mysql-bin.000006'
, MASTER_LOG_POS=
107
;
[root@node1 ~]# mysqlbinlog --start-position=
107
/
var
/binlog/mysql-bin.
000006
> /backup/students_incremental.sql
|
6：模拟数据库数据损坏；
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> insert into CDtb (Name,Age,Gender,Courses) values (
'Ou Yangfeng'
,
75
,
'M'
,
'Hamagong'
);
Query OK,
1
row affected (
0.05
sec)
mysql> drop database students;
Query OK,
2
rows affected,
2
warnings (
0.13
sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.
000006
|
1082
| | |
+------------------+----------+--------------+------------------+
1
row
in
set
(
0.00
sec)
|
7：恢复备份数据：完全备份+增量备份+二进制日志文件；
bac
|
1
2
3
4
5
6
7
|
[root@node1 ~]# mysqlbinlog /
var
/binlog/mysql-bin.
000006
......
# at
993
#
130930
22
:
51
:
40
server id
1
end_log_pos
1082
Query thread_id=
2
exec_time=
0
error_code=
1146
SET TIMESTAMP=
1380552700
/*!*/
;
drop database students
[root@node1 ~]# mysqlbinlog --start-position=
716
--stop-position=
993
/
var
/binlog/mysql-bin.
000006
> /backup/students_993.sql
|
|
1
2
3
4
5
6
7
8
9
10
11
12
|
[root@node1 ~]# mysqlbinlog /
var
/binlog/mysql-bin.
000006
......
# at
993
#
130930
22
:
51
:
40
server id
1
end_log_pos
1082
Query thread_id=
2
exec_time=
0
error_code=
1146
SET TIMESTAMP=
1380552700
/*!*/
;
drop database students
[root@node1 ~]# mysqlbinlog --start-position=
716
--stop-position=
993
/
var
/binlog/mysql-bin.
000006
> /backup/students_993.sql
mysql>
set
global sql_log_bin=
0
;
Query OK,
0
rows affected (
0.00
sec)
mysql> source /backup/students_2013-
09
-
30
.sql
mysql> source /backup/students_incremental.sql
mysql> source /backup/students_993.sql
|
8：检测备份的数据是否已经正常恢复。
|
1
2
|
mysql>
set
global sql_log_bin=
1
;
Query OK,
0
rows affected (
0.00
sec)
|
|
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
|
mysql>
use
students;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_students |
+--------------------+
| CDtb |
| TLtb |
+--------------------+
2
rows
in
set
(
0.00
sec)
mysql> select * from TLtb;
+----+-----------+------+--------+----------------------+
| Id | Name | Age | Gender | Courses |
+----+-----------+------+--------+----------------------+
|
1
| Xu zu |
20
| M | Xiao Wuxianggong |
|
2
| Qiao Feng |
28
| M | Xianglong Shibazhang |
|
3
| Duan Fu |
23
| M | Liumai Shenjian |
+----+-----------+------+--------+----------------------+
3
rows
in
set
(
0.00
sec)
mysql> select * from CDtb;
+----+--------------+------+--------+-----------------+
| Id | Name | Age | Gender | Courses |
+----+--------------+------+--------+-----------------+
|
1
| Yideng Dashi |
80
| M | Yiyangzhi |
|
2
| Hong Qigong |
66
| M | Dagou Bangfa |
|
3
| Huang Yaoshi |
60
| M | Tanzhi Shengong |
|
4
| Ou Yangfeng |
75
| M | Hamagong |
+----+--------------+------+--------+-----------------+
4
rows
in
set
(
0.00
sec)
|
六、使用lvm-snapshot进行数据备份及恢复
1、创建LVM逻辑卷并开机自动挂载，并创建mysql数据与二进制日志存放目录；
y
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[root@localhost ~]# pvcreate /dev/sdb{
1
,
2
}
Physical volume
"/dev/sdb1"
successfully created
Physical volume
"/dev/sdb2"
successfully created
[root@localhost ~]# vgcreate vg1 /dev/sdb{
1
,
2
}
Volume group
"vg1"
successfully created
[root@localhost ~]# lvcreate -L +10G -n lv1 vg1
Logical volume
"lv1"
created
[root@localhost ~]# mke2fs -t ext4 /dev/vg1/lv1
[root@localhost ~]# vim /etc/fstab
/dev/vg1/lv1 /Mydata ext4 defaults
0
0
[root@localhost ~]# mount -a
[root@localhost ~]# mkdir /Mydata/{data,,binlog} /backup1
[root@localhost ~]# chown -R mysql:mysql /Mydata/*
|
2、新建mysql数据库并新增数据；
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> create database schooldb;
Query OK,
1
row affected (
0.01
sec)
mysql>
use
schooldb
Database changed
mysql> create table studentstb (Id tinyint unsigned not
null
primary key auto_increment,Name char(
20
) not
null
unique key,Age tinyint unsigned,Gender char(
1
)
default
'M'
,Courses char(
30
) not
null
);
Query OK,
0
rows affected (
0.38
sec)
mysql> insert into studentstb (Name,Age,Gender,Courses) values (
'Zhang San'
,
19
,
'M'
,
'Shujujiegou'
),(
'Li Ling'
,
18
,
'F'
,
'Daxueyingyu'
),(
'Wang Wu'
,
20
,
'M'
,
'Dianluyuanli'
);
Query OK,
3
rows affected (
0.09
sec)
Records:
3
Duplicates:
0
Warnings:
0
mysql> select * from studentstb;
+----+-----------+------+--------+--------------+
| Id | Name | Age | Gender | Courses |
+----+-----------+------+--------+--------------+
|
1
| Zhang San |
19
| M | Shujujiegou |
|
2
| Li Ling |
18
| F | Daxueyingyu |
|
3
| Wang Wu |
20
| M | Dianluyuanli |
+----+-----------+------+--------+--------------+
3
rows
in
set
(
0.03
sec)
|
3、登录mysql，对所有表加锁，并滚动日志，查看当前日志所在位置；
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> flush tables
with
read lock;
Query OK,
0
rows affected (
0.00
sec)
mysql> flush logs;
Query OK,
0
rows affected (
0.04
sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.
000015
|
107
| | |
+------------------+----------+--------------+------------------+
1
row
in
set
(
0.01
sec)
|
4、另外启动一个终端，为逻辑卷创建快照卷，做完快照释放施加的锁请求；
|
1
2
|
[root@localhost ~]# lvcreate -L 1G -s -p r -n lv1-snap /dev/vg1/lv1
Logical volume
"lv1-snap"
created
|
|
1
2
|
mysql> unlock tables;
Query OK,
0
rows affected (
0.00
sec)
|
5、挂载快照卷，并备份数据，备份完成卸载快照卷并删除；
|
1
2
3
4
5
|
[root@localhost ~]# cp -rp /mnt/* /backup1
[root@localhost ~]# umount /mnt/
[root@localhost ~]# lvremove /dev/vg1/lv1-snap
Do you really want to remove active logical volume lv1-snap? [y/n]: y
Logical volume
"lv1-snap"
successfully removed
|
6、停止mysql服务，模拟数据库数据损坏，进行数据恢复；
|
1
2
3
4
5
6
|
[root@localhost ~]# service mysqld stop
Shutting down MySQL... [ OK ]
[root@localhost ~]# rm -rf /Mydata/*
[root@localhost ~]# cp -rp /backup1/* /Mydata/
[root@localhost ~]# service mysqld start
Starting MySQL.. [ OK ]
|
7、检测数据恢复是否正常恢复完成。
|
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
|
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| schooldb |
| test |
| xiaozheng |
+--------------------+
7
rows
in
set
(
0.01
sec)
mysql>
use
schooldb
Database changed
mysql> show tables;
+--------------------+
| Tables_in_schooldb |
+--------------------+
| studentstb |
+--------------------+
1
row
in
set
(
0.00
sec)
mysql> select * from studentstb;
+----+-----------+------+--------+--------------+
| Id | Name | Age | Gender | Courses |
+----+-----------+------+--------+--------------+
|
1
| Zhang San |
19
| M | Shujujiegou |
|
2
| Li Ling |
18
| F | Daxueyingyu |
|
3
| Wang Wu |
20
| M | Dianluyuanli |
+----+-----------+------+--------+--------------+
3
rows
in
set
(
0.00
sec)
|
七、使用xtrabackup进行数据备份恢复
1、下载并安装xtrabackup；
|
1
2
3
4
|
[root@localhost ~]# ls
anaconda-ks.cfg install.log percona-xtrabackup-
2.1
.
4
-
656
.rhel6.x86_64.rpm
hellodb.sql install.log.syslog
[root@localhost ~]# yum -y install percona-xtrabackup-
2.1
.
4
-
656
.rhel6.x86_64.rpm
|
2、创建有备份权限的数据库用户；
|
1
2
3
4
5
6
7
8
|
mysql> create user
'xtrabackup'
@
'localhost'
identified by
'mypass'
;
Query OK,
0
rows affected (
0.09
sec)
mysql> revoke all privileges,grant option from
'xtrabackup'
@
'localhost'
;
Query OK,
0
rows affected (
0.00
sec)
mysql> grant reload,lock tables,replication client,event on *.* to
'xtrabackup'
@
'localhost'
;
Query OK,
0
rows affected (
0.01
sec)
mysql> flush privileges;
Query OK,
0
rows affected (
0.00
sec)
|
3、对数据库进行完全备份；
|
1
2
3
4
5
6
7
8
|
[root@localhost ~]# innobackupex --user=xtrabackup --password=mypass /backup2
InnoDB Backup Utility v1.
5.1
-xtrabackup; Copyright
2003
,
2009
Innobase Oy
and Percona Ireland Ltd
2009
-
2012
. All Rights Reserved.
........
innobackupex: Backup created
in
directory
'/backup2/2013-09-08_21-06-19'
innobackupex: MySQL binlog position: filename
'mysql-bin.000017'
, position
598
130908
21
:
06
:
24
innobackupex: Connection to database server closed
130908
21
:
06
:
24
innobackupex: completed OK!
|
4、关闭mysql服务并模拟数据损坏,并准备一个完全备份（prepare）；
|
1
2
3
4
5
6
7
8
9
10
11
|
[root@localhost ~]# service mysqld stop
Shutting down MySQL... [ OK ]
[root@localhost ~]# rm -rf /mydata/data/*
[root@localhost backup2]# innobackupex --apply-log /backup2/
2013
-
09
-08_21-
06
-
19
/
InnoDB Backup Utility v1.
5.1
-xtrabackup; Copyright
2003
,
2009
Innobase Oy
and Percona Ireland Ltd
2009
-
2012
. All Rights Reserved.
........
xtrabackup: starting shutdown
with
innodb_fast_shutdown =
1
130908
21
:
13
:
44
InnoDB: Starting shutdown...
130908
21
:
13
:
48
InnoDB: Shutdown completed; log sequence number
1626636
130908
21
:
13
:
48
innobackupex: completed OK!
|
5、从一个完全备份中恢复数据,并检测数据库数据是否正常恢复完成。
|
1
2
3
4
5
6
7
8
|
[root@localhost ~]# innobackupex --copy-back /backup2/
2013
-
09
-08_21-
06
-
19
/
InnoDB Backup Utility v1.
5.1
-xtrabackup; Copyright
2003
,
2009
Innobase Oy
and Percona Ireland Ltd
2009
-
2012
. All Rights Reserved.
............
innobackupex: Copying
'/backup2/2013-09-08_21-06-19/ib_logfile1'
to
'/mydata/data'
innobackupex: Finished copying back files.
130908
21
:
21
:
23
innobackupex: completed OK!
[root@localhost ~]#service mysqld start
|
|
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
|
[root@localhost ~]# innobackupex --copy-back /backup2/
2013
-
09
-08_21-
06
-
19
/
InnoDB Backup Utility v1.
5.1
-xtrabackup; Copyright
2003
,
2009
Innobase Oy
and Percona Ireland Ltd
2009
-
2012
. All Rights Reserved.
............
innobackupex: Copying
'/backup2/2013-09-08_21-06-19/ib_logfile1'
to
'/mydata/data'
innobackupex: Finished copying back files.
130908
21
:
21
:
23
innobackupex: completed OK!
[root@localhost ~]# service mysqld start
Starting MySQL.. [ OK ]
mysql>
use
schooldb
Database changed
mysql> show tables;
+--------------------+
| Tables_in_schooldb |
+--------------------+
| studentstb |
+--------------------+
1
row
in
set
(
0.00
sec)
mysql> select * from studentstb;
+----+-----------+------+--------+--------------+
| Id | Name | Age | Gender | Courses |
+----+-----------+------+--------+--------------+
|
1
| Zhang San |
19
| M | Shujujiegou |
|
2
| Li Ling |
18
| F | Daxueyingyu |
|
3
| Wang Wu |
20
| M | Dianluyuanli |
+----+-----------+------+--------+--------------+
3
rows
in
set
(
0.00
sec)
|
--------------------------------------------------------------------
6、在测试数据库中新增数据，实现数据的增量备份；
7、进行第一次增量备份；
8、继续于测试数据库中新增数据，进行第二次增量备份；
9、关闭mysql服务并模拟数据库数据损坏，进行两次增量备份的恢复；
10、检测增量备份的数据恢复是否正常完成。
120
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
