MySQL 备份与恢复
逻辑备份和恢复
一、逻辑备份
逻辑备份的最大优点是对于各种存储引擎，都可以用同样的方法来备份，而物理备份则不同，不同的存储引擎有着不同的备份方法。因此，对于不同存储引擎混合的数据库，用逻辑备份会更简单一些。
三种备份方式
mysqldump [options] db_name [tables] 备份指定的数据库，或者此数据库中某些表
mysqldump [options] ---database DB1 [DB2 DB3...] 备份指定的一个或多个数据库
mysqldump [options] --all--database 备份所有数据库
注意：--fields-terminated-by ','可以指定分隔符备份为文本。
工具使用：完全恢复（mysqldump 、mysqlbinlog ）、基于时间点恢复（mysqlbinlog ）、基于位置恢复（mysqlbinlog ）
MyISAM 存储引擎在备份的时候需要加上-l 参数，表示将所有表加上读锁，在备份期间，所有表将只能读而不能进行数据更新。对于事务存储引擎（InnoDB 和 BDB）来说，可以采用更好的选项--single-transaction，此选项将使得 InnoDB 存储引擎得到一个快照（Snapshot），使得备份的数据能够保证一致性
二、恢复
mysql –uroot –p dbname < bakfile
mysqlbinlog binlog-file | mysql -u root –p*** 上一步备份恢复后数据并不完整，还需要将备份后执行的日志进行重做
例子
mysql> select count(*) from emp order by id; //输出4
[root@localhost mysql]# mysqldump -uroot –p –l –F test >test.dmp 备份数据库(-l 参数表示给所有表加读锁，-F 表示生成一个新的日志文件)
mysql> select count(*) from emp order by id; //插入两条数据，输出6。下一秒数据库宕机
[root@localhost mysql]# mysql -uroot -p test < test.dmp 恢复数据
mysql> select count(*) from emp order by id; //输出4
[root@localhost mysql]#mysqlbinlog localhost-bin.000015 | mysql -u root –p test 使用 mysqlbinlog 恢复自 mysqldump 备份以来的 BINLOG
mysql> select count(*) from emp order by id; //输出6
物理备份和恢复
物理备份又分为冷备份和热备份两种，和逻辑备份相比，它的最大优点是备份和恢复的速度更快，因为物理备份的原理都是基于文件的 cp
一、冷备份
冷备份原理：停掉数据库服务，cp 数据文件的方法。这种方法对 MyISAM 和 InnoDB 存储引擎都适合
进行备份的操作如下：停掉 MySQL 服务，在操作系统级别备份 MySQL 的数据文件和日志文件到备份目录。
进行恢复的操作如下：首先停掉 MySQL 服务，在操作系统级别恢复 MySQL 的数据文件；
然后重启 MySQL 服务，使用 mysqlbinlog 工具恢复自备份以来的所有 BINLOG
二、热备份
MySQL 中，对于不同的存储引擎热备份方法也有所不同，下面主要介绍 MyISAM 和 InnoDB两种最常用的存储引擎的热备份方法
（1）MyISAM
MyISAM 存储引擎的热备份有很多方法，本质其实就是将要备份的表加读锁，然后再 cp 数据文件到备份目录
方法1：使用 mysqlhotcopy 工具
mysqlhotcopy db_name [/path/to/new_directory]
方法 2：手工锁表 copy
flush tables for read ; 首先数据库中所有表加读锁
然后 cp 数据文件到备份目录即可。
（2）InnoDB
案例 ---ibbackup 工具
备份步骤
第一步
cat /home/pekka/my.cnf
[mysqld]
datadir = /home/heikki/data
innodb_data_home_dir = /home/heikki/data
innodb_data_file_path = ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend
innodb_log_group_home_dir = /home/heikki/data
set-variable = innodb_log_files_in_group=2
set-variable = innodb_log_file_size=20M
第二步
cat /home/pekka/backup-my.cnf
[mysqld]
datadir = /home/heikki/backup
innodb_data_home_dir = /home/heikki/backup
innodb_data_file_path = ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend
innodb_log_group_home_dir = /home/heikki/backup
set-variable = innodb_log_files_in_group=2
set-variable = innodb_log_file_size=20M
开始备份
ibbackup /home/pekka/my.cnf /home/pekka/backup-my.cnf 等待备份完成
$ ls -lh /home/heikki/backup
total 824M
-rw-r—– 1 pekka dev 22M Jan 21 17:42 ibbackup_logfile
-rw-r—– 1 pekka dev 100M Jan 21 17:36 ibdata1
-rw-r—– 1 pekka dev 200M Jan 21 17:38 ibdata2
-rw-r—– 1 pekka dev 500M Jan 21 17:42 ibdata3
恢复步骤
shell>.ibbackup --apply-log /home/pekka/backup-my.cnf 进行日志重做
shell>../bin/mysqld_saft --defaults-file=/home/pekka/backup-my.cnf & 恢复后重启数据库服务
mysqlbinlog binlog-file | mysql -u root –p*** 服务重启后，利用 BINLOG 日志将备份点与故障点之间的剩余数据进行恢复
（3）表的导入导出
导出
方法1
SELECT * FROM tablename INTO OUTFILE 'target_file' [option];
option选项
FIELDS TERMINATED BY 'string' （字段分隔符，默认为制表符’\t’）；
FIELDS [OPTIONALLY] ENCLOSED BY 'char'（字段引用符，如果加 OPTIONALLY 选项则只用在 char、
varchar 和 text 等字符型字段上。默认不使用引用符）；
FIELDS ESCAPED BY 'char' （转义字符，默认为’\’）；
LINES STARTING BY 'string' （每行前都加此字符串，默认''）；
LINES TERMINATED BY 'string'（行结束符，默认为’\n’）；
方法 2
mysqldump –u username –T target_dir dbname tablename [option]
option 参数可以是以下选项：
--fields-terminated-by=name（字段分隔符）；
--fields-enclosed-by=name（字段引用符）；
--fields-optionally-enclosed-by=name（字段引用符，只用在 char、varchar 和 text 等字符
型字段上）；
--fields-escaped-by=name（转义字符）
--lines-terminated-by=name（记录结束符）
导入
方法 1
mysql > LOAD DATA [LOCAL] INFILE ‘filename’ INTO TABLE tablename [option]
方法2
shell>mysqlimport –u root –p*** [--LOCAL] dbname order_tab.txt [option]
