分类： LINUX
2009-04-08 17:27:12
Mysql--备份与恢复
一：逻辑备份和恢复
1. 逻辑备份对于各种存储引擎，都可以用同样的方法来备份；而物理备份在不同的引擎有着不同的备份方法。
2. 备份
备份所有数据库
C:\>mysqldump -uroot -p123456 --all-database > c:\all.sql
备份数据库crashcourse
C:\>mysqldump -uroot -p123456 crashcourse > c:\crashcourse.sql
备份数据库crashcourse下的表customers和orderitems
C:\>mysqldump -uroot -p123456 crashcourse customers orderitems > c:\cust_order.sql
备份数据库crashcourse下的表customers为逗号分割的文本
C:\>mysqldump -uroot -p123456 -T c:\ crashcourse customers --fields-terminated-by ,
为了保证数据备份的一致性，Myisam存储引擎在备份的时候需要加上-l的参数，表示将所有表加上读锁，在备份期间，所有表将只能读而不能进行数据更新。但是对于事务存储引擎Innodb和BDB来说，可以采用更好的选项—single-transaction,此选项将使得InnoDB存储引擎得到一个快照，使得备份的数据能够保持一致性。
3. 完全恢复例子
上午11点备份数据库crashcourse
C:\>mysqldump -uroot -p123456 -lF crashcourse > c:\crashcourse.sql
此时crashcourse中orderitems表的数据如下：
mysql> select * from orderitems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
| 20005 | 1 | ANV01 | 10 | 5.99 |
| 20005 | 2 | ANV02 | 3 | 9.99 |
| 20005 | 3 | TNT2 | 5 | 10.00 |
| 20005 | 4 | FB | 1 | 10.00 |
| 20006 | 1 | JP2000 | 1 | 55.00 |
| 20007 | 1 | TNT2 | 100 | 10.00 |
| 20008 | 1 | FC | 50 | 2.50 |
| 20009 | 1 | FB | 1 | 10.00 |
| 20009 | 2 | OL1 | 1 | 8.99 |
| 20009 | 3 | SLING | 1 | 4.49 |
| 20009 | 4 | ANV03 | 1 | 14.99 |
+-----------+------------+---------+----------+------------+
11 rows in set (0.00 sec)
3.2 11点半备份完毕，然后，插入新的数据
mysql> insert into orderitems values(20008,2,'FB',10,2.0);
Query OK, 1 row affected (0.13 sec)
3.3 12点数据库突然故障，数据无法访问，需要恢复备份：
C:\>mysql -uroot -p123456 crashcourse < c:\crashcourse.sql
mysql> select * from orderitems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
| 20005 | 1 | ANV01 | 10 | 5.99 |
| 20005 | 2 | ANV02 | 3 | 9.99 |
| 20005 | 3 | TNT2 | 5 | 10.00 |
| 20005 | 4 | FB | 1 | 10.00 |
| 20006 | 1 | JP2000 | 1 | 55.00 |
| 20007 | 1 | TNT2 | 100 | 10.00 |
| 20008 | 1 | FC | 50 | 2.50 |
| 20009 | 1 | FB | 1 | 10.00 |
| 20009 | 2 | OL1 | 1 | 8.99 |
| 20009 | 3 | SLING | 1 | 4.49 |
| 20009 | 4 | ANV03 | 1 | 14.99 |
+-----------+------------+---------+----------+------------+
11 rows in set (0.00 sec)
这和备份时的数据是一样的。
3.4 使用mysqlbinlog恢复来自完全备份以来的BINLOG.
mysql> select * from orderitems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
| 20005 | 1 | ANV01 | 10 | 5.99 |
| 20005 | 2 | ANV02 | 3 | 9.99 |
| 20005 | 3 | TNT2 | 5 | 10.00 |
| 20005 | 4 | FB | 1 | 10.00 |
| 20006 | 1 | JP2000 | 1 | 55.00 |
| 20007 | 1 | TNT2 | 100 | 10.00 |
| 20008 | 1 | FC | 50 | 2.50 |
| 20008 | 2 | FB | 10 | 2.00 |
| 20009 | 1 | FB | 1 | 10.00 |
| 20009 | 2 | OL1 | 1 | 8.99 |
| 20009 | 3 | SLING | 1 | 4.49 |
| 20009 | 4 | ANV03 | 1 | 14.99 |
+-----------+------------+---------+----------+------------+
12 rows in set (0.00 sec)
至此，数据库完全恢复
二：物理备份与恢复
1. 物理备份分为冷备份和热备份。物理备份的原理都是基于文件的cp。
2. 冷备份对Myisam和InnoDB的存储引擎都适合。
进行备份的操作如下：停掉Mysql服务，在操作系统级别备份Mysql的数据文件和日志文件到备份目录。
进行恢复的操作如下：首先停掉Mysql服务，在操作系统级别恢复Mysql的数据文件，然后重启Mysql服务，使用mysqlbinlog工具恢复自备份以来的所有BINLOG。
3. 热备份：对于不同的存储引擎热备份方法也有所不同
3.1Myisam存储引擎
Myisam存储引擎的热备份方法很多，本质就是将备份的表加读锁，然后在cp数据文件到备份目录。常用的有以下两种：
A． shell> mysqlhotcopy db_name [/path/to/new_directory]
B． 在mysqlhotcopy使用不正常的情况下，可以手工来做热备份，操作如下：
首先数据库中所有表加读锁：
C:\flush tables with read lock;
然后cp数据文件到备份目录即可。
3.2InnoDB存储引擎
Ibbackup是Innobase公司的一个热备份工具，专门对InnoDB存储引擎进行无力备份。
三：表的导入导出
1. 导出
1.1有时需要将表里的数据导出为某些符合分割的纯数据文本，而不是SQL语句。应用如下：
A：用来作为EXCEL显示；
B：单纯为了节省备份空间；
C：为了加速地加载数据，LOAD DATA 的加载速度比普通的SQL加载要快20倍以上。
1.2使用select … INTO OUTFILE … 命令来导出数据，例如：
将数据库crashcourse的表orderitems导出为数据文本，其中，字段分隔符为“,“，字段引用符为””“
mysql> select * from orderitems into outfile 'c:\\orderitems.txt' fields terminated by "," enclosed by' "' ;
Query OK, 12 rows affected (0.16 sec)
orderitems.txt的内容如下：
"20005 ", "1 ", "ANV01 ", "10 ", "5.99 "
"20005 ", "2 ", "ANV02 ", "3 ", "9.99 "
"20005 ", "3 ", "TNT2 ", "5 ", "10.00 "
"20005 ", "4 ", "FB ", "1 ", "10.00 "
"20006 ", "1 ", "JP2000 ", "1 ", "55.00 "
"20007 ", "1 ", "TNT2 ", "100 ", "10.00 "
"20008 ", "1 ", "FC ", "50 ", "2.50 "
"20008 ", "2 ", "FB ", "10 ", "2.00 "
"20009 ", "1 ", "FB ", "1 ", "10.00 "
"20009 ", "2 ", "OL1 ", "1 ", "8.99 "
"20009 ", "3 ", "SLING ", "1 ", "4.49 "
"20009 ", "4 ", "ANV03 ", "1 ", "14.99 "
1.3select … INTO OUTFILE … 命令的转义注意：
A：当导出命令中包含字段引用符时，数据中含有转义符本身和字段引用符的字段需要被转义；
B：当导出命令中不包含字段引用符时，数据中含有转义符本身和字段分隔符的字段需要被转义。
1.4用mysqldump导出数据为文本：
mysqldump -uroot -p123456 -T c:\ crashcourse customers --fields-terminated-by "," –fields-optionally-enclose-by by '"'
2. 导入
2.1使用LOAD DATA INFILE … INTO TABLE
mysql> load data infile 'c:\\orderitems.txt' into table orderitems fields terminated by "," enclosed by '"' ;
2.2使用mysqlimport
C:\>mysqlimport -uroot -p123456 crashcourse c:\orderitems.txt –fields-terminated by "," fields—enclosed-by '"'
