MySQL备份和恢复具体实施(上)
冷备：
对于InnoDB存储引擎的冷备非常简单，只需要备份MySQL数据库的frm文件、共享表空间文件、独立表空间文件（*.ibd）、重做日志文件。--建议定期备份MySQL数据库的配置文件my.cnf
冷备的优点：
a) 备份简单，只要拷贝相关文件
b) 备份文件易于在不同操作系统，不同MySQL版本进行恢复
c) 恢复相当简单，只需要把文件恢复到指定位置即可
d) 恢复速度快，不需要执行任何SQL语句，也不需要重建索引
冷备的缺点：
a) InnoDB存储引擎冷备的文件通常比逻辑文件大很多，因为表空间中存放着数据，如Undo段，插入缓冲等信息
b) 冷备不总是可以轻易地跨平台。操作系统、MySQL的版本、文件大小写敏感和浮点数格式都会成为问题
逻辑备份：
mysqldump备份工具
mysqldump的语法如下：
shell>mysqldump [argument] > file_name
备份全部数据库，使用--all-database选项：
shell>mysqldump --all-databases > all.sql
备份指定的数据库，--database：
shell> mysqldump–database db1 db2 db3 > dump.sql
mysqldump一些重要参数选项：
--single-transaction：在备份开始前，先执行STARTTRANSACTION命令，以此来获得备份的一致性
--lock-tables(-l):在备份中，以此锁住每个架构下的所有表。一般用于MyISAM存储引擎，备份时只能对数据库进行读取操作，不过备份依然可以保证一致性。对于InnoDB不需要使用该参数，用--single-transaction即可，并且--lock-tables和--single-transaction是互斥的，不能同时使用。如果你的MySQL数据库中既有MyISAM存储引擎表，又有InnoDB存储引擎表，那么这时你的选择只有--lock-tables了。--lock-tables选项是依次对每个架构中的表上锁的，因此只能保证每个架构下表备份的一致性，不能保证所有架构下表的一致性。
--lock-all-tables（-x）：在备份过程中，对所有架构中的所有表上锁
--add-drop-database：在createdatabase之前先运行drop database。这个参数需要和--all-databases或者--database选项一起使用
备份指定的表：
# mysqldump--single-transaction --socket=/var/run/mysqld/mysql5.socket test1 test1 >test1.sql
# cattest1.sql
-- MySQL dump 10.13 Distrib 5.1.61, for redhat-linux-gnu (i386)
--
-- Host: localhost Database: test1
-- ------------------------------------------------------
-- Server version 5.5.20-ndb-7.2.5-log
/*!40101 SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION*/;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,UNIQUE_CHECKS=0 */;
/*!40014 SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0 */;
--
-- Table structure for table `test1`
--
DROP TABLE IF EXISTS `test1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test1` (
`id`int(11) NOT NULL,
`name`varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client*/;
--
-- Dumping data for table `test1`
--
LOCK TABLES `test1` WRITE;
/*!40000 ALTER TABLE `test1` DISABLE KEYS */;
INSERT INTO `test1` VALUES(1,'wu'),(2,'terry'),(3,'tang'),(4,'jack'),(4,'cat'),(3,NULL),(3,'dog');
/*!40000 ALTER TABLE `test1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT*/;
/*!40101 SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2012-11-10 1:39:53
select … into outfile
select..into语句也是一种逻辑备份的方法，或者更准确地说是导出一张表中的数据
SELECT [column 1],[column2] … INTO OUTFILE'file_name' from table where… …
mysql> select * into outfile '/root/test1.txt' from test1; //必须是mysql用户可写 ERROR 1 (HY000): Can't create/write to file'/root/test1.txt' (Errcode: 13) mysql> select * into outfile '/home/mysql/test1.txt' from test1; Query OK, 7 rows affected (0.00 sec) mysql> quit Bye
# cat/home/mysql/test1.txt
1 wu
2 terry
3 tang
4 jack
4 cat
3 \N
3 dog
# mysql -e"select * into outfile '/home/mysql/test_bak' from test1.test1";
# cat/home/mysql/test_bak
1 wu
2 terry
3 tang
4 jack
4 cat
3 \N
3 dog
逻辑备份的恢复:
mysqldump的恢复操作比较简单，因为备份的文件就是导出的SQL语句
在shell命令行导入：
# mysqldump-uroot -p --socket=/var/run/mysqld/mysql5.socket >test1.sql test1
使用source恢复：
mysql> usetest1;
mysql>source /root/test1.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
… …
注：mysqldump可以导出存储过程、触发器、事件、数据，但是却不能导出视图
Load data infile
若是通过mysqldump --tab或者select into outfile导出的数据需要恢复时，则需要通过Load data infile命令来进行导入
LOAD DATA [LOW_PRIORITY | CONCURRENT][LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name =expr,...]
mysql> select * fromtest1; Empty set (0.00 sec) mysql> load datainfile '/home/mysql/test1.txt' into table test1; Query OK, 7 rows affected (0.05 sec) Records: 7 Deleted:0 Skipped: 0 Warnings: 0 mysql> select * fromtest1; +----+-------+ | id | name | +----+-------+ | 1 | wu | | 2 | terry | | 3 | tang | …… 7 rows in set (0.00 sec) mysql>
为了更快的导入，可以在导入过程忽略对外键的检查，可以使用：
mysqlimport
mysqlimport是MySQL数据库提供的一个命令行程序，从本质上来说，是LOAD DATA INFILE的命令接口，而且大多数的选项都和LOAD DATA INFILE语法相同
shell> mysqlimport[options] db_name testfile1 [textfile2 …]
与LOAD DATA INFILE不同的是，mysqlimport命令是可以导入多张表的，并且通过--user-thread参数来并发导入不同的文件。这里的并发是指并发导入多个文件，并不是指mysqlimport可以并发地导入一个文件，这是有区别的，并且并发地对同一张表进行导入，效果一般不会比串行的方式好。
通过mysqlimport并发导入两张表：
# /usr/local/mysql/bin/mysqlimport--use-threads=2 -S /var/run/mysqld/mysql5.socket test1 /home/mysql/test1.txt/home/mysql/test3.txt
test1.test1: Records: 7 Deleted: 0 Skipped: 0 Warnings: 0
test1.test3: Records: 7 Deleted: 0 Skipped: 0 Warnings: 0
二进制日志备份和恢复
二进制日志非常关键，我们可以通过它来完成point-in-time的恢复工作
开启二进制日志:
[mysqld]
bin-log
sync_binlog=1
sync_binlog
如果为正，当每个sync_binlog’th写入该二进制日志后，MySQL服务器将它的二进制日志同步到硬盘上(fdatasync())。请注意如果在autocommit模式，每执行一个语句向二进制日志写入一次，否则每个事务写入一次。默认值是0，不与硬盘同步。值为1是最安全的选择，因为崩溃时，你最多丢掉二进制日志中的一个语句/事务；但是，这是最慢的选择(除非硬盘有电池备份缓存，从而使同步工作较快)。
二进制日志默认存在databasedir目录下,如下mysql5-bin.000001
使用mysqlbinlog查看二进制日志的内容：
# mysqlbinlogmysql5-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#121110 15:40:06 server id 1 end_log_pos 112 Start: binlog v4, server v 5.5.20-ndb-7.2.5-log created 121110 15:40:06 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
1gSeUA8BAAAAbAAAAHAAAAABAAQANS41LjIwLW5kYi03LjIuNS1sb2cAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADWBJ5QEzgNAAgAEgAEBAQEEgAAWQAEGggAAAAICAgCAAAACgoK
'/*!*/;
… …
mysqlbinlog有一些选项可以使用,简单说明常用选项:
Ø -d,--database=name :指定数据库名称,只列出指定数据库的操作.
Ø -D, --disable-log-bin :执行恢复的时候,禁止二进制日志.可以防止同一台MySQL加上-t时进入死循环
Ø -o,--offset=n :忽略掉日志前n行命令
Ø -r,--result-file=name :将输出日志到指定文件
Ø -R,--read-from-remote-server :从一个MySQL服务器上读取二进制
Ø -s,--short-form :显示简单格式,省略一些信息
Ø -S, --socket=name :socket文件连接path.
Ø -t, --to-last-log :和-R一起使用,在二进制日志结束的时候并不会停止,而是在MySQL服务器最后生成的binlog结束,如果输出和输入都在一台MySQL上可能会导致死循环.
Ø --set-charset=char-name :在输出文本格式的时候,在第一行加上set names char-name.
Ø --start-datetime=#--stop-datetime=# :指定输出起始日期的日志.
Ø --start-position=#--stop-position=# :指定起始日志的位置.
# mysqlbinlog -d test2mysql5-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#121110 15:40:06 server id 1 end_log_pos 112 Start:binlog v 4, server v 5.5.20-ndb-7.2.5-log created 121110 15:40:06 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
1gSeUA8BAAAAbAAAAHAAAAABAAQANS41LjIwLW5kYi03LjIuNS1sb2cAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADWBJ5QEzgNAAgAEgAEBAQEEgAAWQAEGggAAAAICAgCAAAACgoK
'/*!*/;
# at 112
#121110 16:07:51 server id 1 end_log_pos 197 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1352534871/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
createdatabase test2
/*!*/;
… …
# mysqlbinlog -d test1--stop-position=870 mysql5-bin.000001 | mysql -uroot -p
--start-datetime=# --stop-datetime=# :指定输出起始日期的日志.
--start-position=# --stop-position=# :指定起始日志的位置.
以上两个选项类似,一个日期,一个日志的位置
文章内容为笔者读《MySQL技术内幕Innodb引擎》笔记
