热备份:读、写不受影响;
温备份:仅可以执行读操作;
冷备份:离线备份;读、写操作均中止;
物理备份:复制数据文件;
逻辑备份:将数据导出至文本文件中;
完全备份:备份全部数据;
增量备份:仅备份上次完全备份或增量备份以后变化的数据;
差异备份:仅备份上次完全备份以来变化的数据;
在备份速度上两种备份要取决于不同的存储引擎
物理备份的还原速度非常快。但是物理备份的最小力度只能做到表
逻辑备份保存的结构通常都是纯ASCII的,所以我们可以使用文本处理工具来处理
逻辑备份有非常强的兼容性,而物理备份则对版本要求非常高
逻辑备份也对保持数据的安全性有保证
逻辑备份要对RDBMS产生额外的压力,而裸备份无压力
逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩
逻辑备份可能会丢失浮点数的精度信息
mysqldump 逻辑备份工具,支持所有引擎,MyISAM引擎是温备,InnoDB引擎是热备,备份速度中速,还原速度非常非常慢,但是在实现还原的时候,具有很大的操作余地。具有很好的弹性。
mysqlhotcopy 物理备份工具,但只支持MyISAM引擎,基本上属于冷备的范畴,物理备份,速度比较快。
cp 冷备份,支持所有引擎,复制命令,只能实现冷备,物理备份。使用归档工具,cp命令,对其进行备份的,备份速度快,还原速度几乎最快,但是灵活度很低,可以跨系统,但是跨平台能力很差。
lvm 几乎是热备份,支持所有引擎,基于快照(LVM,ZFS)的物理备份,速度非常快,几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用,所以备份速度比较快,恢复速度比较快,没有什么弹性空间,而且LVM的限制:不能对多个逻辑卷同一时间进行备份,所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。
ibbackup 商业工具 MyISAM是温备份,InnoDB是热备份 ,备份和还原速度都很快,这个软件它的每服务器授权版本是5000美元。
xtrabackup 开源工具 MyISAM是温备份,InnoDB是热备份 ,是ibbackup商业工具的替代工具。
数据文件要在逻辑卷上;
此逻辑卷所在卷组必须有足够空间使用快照卷;
数据文件和事务日志要在同一个逻辑卷上;
1
2
|
mysql> FLUSH TABLES WITH READ LOCK;
mysql> FLUSH LOGS;
|
1
|
mysql -uroot -p -e
'SHOW MASTER STATUS\G'
>
/path/to/master
.info
|
1
|
lvcreate -L
# -s -p r -n LV_NAME /path/to/source_lv
|
1
|
mysql> UNLOCK TABLES;
|
1
2
|
mount
cp
|
xtrabackup 只能备份InnoDB和XtraDB两种数据表,而不能备份MyISAM数据表。
innobackupex 是参考了InnoDB Hotbackup的innoback脚本修改而来的.innobackupex是一个perl脚本封装,封装了xtrabackup。主要是为了方便的 同时备份InnoDB和MyISAM引擎的表,但在处理myisam时需要加一个读锁。并且加入了一些使用的选项。如slave-info可以记录备份恢复后作为slave需要的一些信息,根据这些信息,可以很方便的利用备份来重做slave。
备份过程快速、可靠;
备份过程不会打断正在执行的事务;
能够基于压缩等功能节约磁盘空间和流量;
自动实现备份检验;
还原速度快;
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
|
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)
|
1
2
3
4
|
[root@mysql ~]
# mkdir /mybinlog
[root@mysql ~]
# chown mysql:mysql /mybinlog
[root@mysql /]
# ll | grep mybinlog
drwxr-xr-x 2 mysql mysql 4096 7月 22 14:39 mybinlog
|
1
2
3
|
[root@mysql ~]
# vim /etc/my.cnf
log-bin=
/mybinlog/mysql-bin
#二进制日志目录及文件名前缀
innodb_file_per_table = 1
#启用InnoDB表每表一文件,默认所有库使用一个表空间
|
1
|
[root@mysql ~]
# service mysqld restart
|
1
2
|
[root@mysql ~]
# ls /mybinlog/
mysql-bin.000001 mysql-bin.index
|
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)
|
1
2
3
4
5
6
7
8
|
mysql>use
test
;
#使用 test数据库
mysql>create table t1 (
#创建一个简单的t1表,里面只有一个字段 id
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;
#重复多次便能插入1亿条数据
mysql>
select
count(*) from t1;
#查看插入数据的总数
|
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
2
3
|
[root@mysql data]
# mysql
mysql> FLUSH TABLES WITH READ LOCK;
#刷新表到时磁盘中并读锁
Query OK, 0 rows affected (0.00 sec)
|
1
2
|
[root@mysql data]
# mkdir /root/alldb.`date +%F-%H-%M-%S`/ #创建备份目录
[root@mysql data]
# cp -rp /mydata/data/* /root/alldb.2013-07-22-13-46-22/ #复制所以的数据库文件
|
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 ~]
# ll alldb.2013-07-22-13-46-22/ #查看备份好的数据库
总用量 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
|
1
2
3
4
|
[root@mysql data]
# rm -rf *
[root@mysql data]
# ll
总用量 0
[root@mysql data]
#
|
1
2
|
[root@mysql mydata]
# service mysqld stop
ERROR! MySQL server PID
file
could not be found!
|
1
2
3
|
[root@mysql mydata]
# ps aux | grep mysqld
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
|
1
|
[root@mysql ~]
# killall mysqld
|
1
|
[root@mysql ~]
# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/mydata/data/ --user=mysql
|
1
2
|
[root@mysql
test
]
# alias cp=cp #修改cp别名,不然复制时老是提醒是否覆盖
[root@mysql
test
]
# cp -pr /root/alldb.2013-07-22-13-46-22/* /mydata/data/ #复制完全备份的文件到数据目录中
|
1
2
|
[root@mysql
test
]
# service mysqld start
Starting MySQL SUCCESS!
|
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
]
# mysql
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)
|
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
#在备份文件中记录当前二进制日志的位置,并且为注释的,1是不注释掉在主从复制中才有意义
--flush-logs
#日志滚动一次
|
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)
|
1
|
[root@mysql mybackup]
# mysqldump --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/mybackup/2013-07-22-16-20.full.sql
|
1
2
3
|
[root@mysql mybackup]
# ll -h
总用量 739M
-rw-r--r-- 1 root root 739M 7月 22 16:31 2013-07-22-16-20.full.sql
|
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)
|
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);
|
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)
|
1
|
[root@mysql mybackup]
# cp /mybinlog/mysql-bin.000023 /root/mybackup/2013-07-22-16-20.binlog.full.000001
|
1
2
3
4
|
[root@mysql mybackup]
# ll
总用量 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
|
1
2
3
4
5
6
7
8
9
|
[root@mysql mybackup]
# cd /mydata/data/
[root@mysql data]
# ls
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]
# rm -rf * #删除所有数据
[root@mysql data]
# ll
总用量 0
|
1
2
3
4
5
|
[root@mysql data]
# ps -aux | grep mysqld
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
|
1
|
[root@mysql data]
# killall mysqld
|
1
|
[root@mysql data]
# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/mydata/data/ --user=mysql
|
1
|
[root@mysql data]
# rm -rf /mybinlog/*
|
1
|
[root@mysql ~]
# service mysqld start
|
1
2
|
mysql>
set
global sql_log_bin=0;
mysq>
source
/root/mybackup/2013-07-22-16-20
.full.sql
|
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)
#可以看到用mysqldump备份数据,还原myisam引擎时大概需要30s时间(共1亿多条数据,速度不是挺快的)
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)
#还原INNODB引擎,大概50s左右(共2千多万条数据)
|
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 ~]
# mysqlbinlog /root/mybackup/2013-07-22-16-20.binlog.full.000001 | mysql test
[root@mysql ~]
# mysql test
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>
|
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)
|
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)
|
1
|
[root@mysql ~]
# lvcreate -L 2G -n mysql-snap -s -p r /dev/myvg/mydata
|
1
2
|
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
|
1
2
3
4
5
|
[root@mysql ~]
# mount /dev/myvg/mysql-snap /mnt
[root@mysql ~]
# mkdir /root/mybackup/lvm
[root@mysql ~]
# cp -pR /mnt/* /root/mybackup/lvm/
[root@mysql ~]
|