首页 > 代码库 > 备份与恢复

备份与恢复

一、逻辑备份和恢复

      MySQL里面,逻辑备份的最大优点是对于各种存储引擎,都可以用同样的方法来备份;而物理备份则不同,

不同的存储引擎有着不同的备份方法。因此,对于不同存储引擎混合的数据库,用逻辑备份会更简单一些。

1. 逻辑备份:

      MySQL中的逻辑备份是将数据库中的数据备份为一个文本文件,在MySQL 中,使用mysqldump工具来完成逻辑备份。

使用方法:

1)备份指定的数据库,或者此数据库中某些表:

mysqldump [options] db_name [tables]

2)备份指定的一个或多个数据库:

mysqldump [options] --database DB1 [DB2 DB3...]

3)备份所有数据库:

mysqldump [options] --all-database

(如果没有指定数据库中的任何表,默认导出所有数据库中所有表。)

      为了保证数据备份的一致性,MyISAM存储引擎在备份的时候需要加上-l数,表示将所有表加上读锁,在备份

期间,所有表将只能读而不能进行数据更新。但是对于事务存储引擎(InnoDBBDB)来说,可以采用更好的选项

--single-transaction,此选项将使得InnoDB存储引擎得到一个快照(Snapshot),使备份的数据能够保证一致性。

使用示例:

mysqldump -u root -p test -l user_myi > user_myi.sql

2. 完全恢复:

      mysqldump的恢复很简单,将备份作为输入执行即可,具体语法如下:

mysql -uroot -p dbname < bakfile

    注意,将备份恢复后数据并不完整,还需要将备份后执行的日志进行重做,语法如下:

mysqlbinlog binlog-file | mysql -u root -p

完全恢复示例:

1)备份:

mysqldump -uroot -p -l -F test > test.sql

-F表示生成一个新的日志文件)

2)恢复备份:

flush logs;   

mysql -uroot -p test < test.sql

注意:

      这里创建新binlog日志文件再进行备份恢复,这样做是为了防止备份恢复的操作也被记录到完全恢复需要使用的

binlog日志文件中。

3)使用mysqlbinlog恢复自mysqldump备份以来的BINLOG

mysqlbinlog localhost-bin.000015 | mysql -u root p test

(这里的localhost-bin.000015就是从备份到第二步执行fush logs期间的binlog日志文件)

3. 不完全恢复:

      由于误操作,比如误删除了一张表,这时使用完全恢复是没有用的,因为日志里面还存在误操作的语句,我们需要

的是恢复到误操作之前的状态,然后跳过误操作语句,再恢复后面执行的语句,完成我们的恢复。这种恢复叫不完全恢复,

MySQL中,不完全恢复分为基于时间点的恢复和基于位置的恢复。

      基于时间点的恢复主要使用mysqlbinlog中的两个选项--stop-date--start-date来跳过误操作的时间点。而基于位置

的恢复则是先在binlog日志文件中找到误操作语句前后的位置号,然后使用mysqlbinlog的选项--stop-position--start-position

跳过误操作的位置。这两种方法是类似的,但基于位置的恢复更精确,因为同一个时间点可能有很多条SQL语句同时执行。

 

二、物理备份和恢复

      物理备份又分为冷备份和热备份两种,和逻辑备份相比,它的最大优点是备份和恢复的速度更快,因为物理备份的原理

都是基于文件的cp

1. 冷备份:

      冷备份其实就是停掉数据库服务,cp数据文件的方法。这种方法对MyISAM InnoDB存储引擎都适合,但是一般很少使用,

因为很多应用是不允许长时间停机的。

      进行备份操作时,停掉MySQL服务,在操作系统级别备份MySQL的数据文件和日志文件到备份目录。进行恢复操作时,

首先停掉MySQL服务,在操作系统级别恢复MySQL 的数据文件;然后重启MySQL服务,使用mysqlbinlog工具恢复自备份以来的

所有BINLOG

2. 热备份:

      MySQL中,对于不同的存储引擎热备份方法也有所不同。

1MyISAM存储引擎:

      MyISAM存储引擎的热备份有很多方法,本质其实就是将要备份的表加读锁,然后再cp数据文件到备份目录。

方法一:使用mysqlhotcopy工具。

mysqlhotcopy db_name [/path/to/new_directory]

方法2:手工锁表copy

首先数据库中所有表加读锁:

flush tables for read ;

然后手工cp数据文件到备份目录即可。

2InnoDB存储引擎:

      对于InnoDB存储引擎数据库,可以使用Innobase公司的一个热备份工具ibbackup进行物理热备份。对于InnoDB

MyISAM混合的数据库,可以使用Innobase公司提供的开源Perl脚本innobackup进行备份。

 

三、表的导入导出

1. 导出:

    在某些情况下,为了一些特定的目的,经常需要将表里的数据导出为某些符号分割的纯数据文本,而不是SQL语句。

方法一:使用SELECT ...INTO OUTFILE ...命令来导出数据,具体语法如下。

SELECT * FROM tablename INTO OUTFILE ‘target_file‘ [option];

其中option 参数可以是以下选项:

FIELDS TERMINATED BY ‘string‘ (字段分隔符,默认为制表符’\t’);

FIELDS [OPTIONALLY] ENCLOSED BY ‘char‘(字段引用符,如果加OPTIONALLY 选项则只用在charvarchar text

等字符型字段上。默认不使用引用符);

FIELDS ESCAPED BY ‘char‘ (转义字符,默认为’\’);

LINES STARTING BY ‘string‘ (每行前都加此字符串,默认‘‘);

LINES TERMINATED BY ‘string‘(行结束符,默认为’\n’);

其中char表示此符号只能是单个字符,string表示可以是字符串。

使用示例:

select * from emp into outfile ‘/tmp/emp.txt‘ fields terminated by "," optionally enclosed by ‘"‘ ;

注意:

SELECTINTO OUTFILE...产生的输出文件如果在目标目录下有重名文件,将不会创建成功,源文件不能被自动覆盖。

      另外,若语句执行过程中遇到The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

错误提示,那是因为mysql配置文件中配置的“secure-file-priv”配置项限制了SELECT INTO OUTFILE报表到指定的目录,你可以

选择使用配置中限定的这个目录来存放导出的文件,或者不使用这个配置项。

方法二:用mysqldump导出数据为文本。

mysqldump -u username -T target_dir dbname tablename [option]

其中option参数可以是以下选项:

--fields-terminated-by=name(字段分隔符);

--fields-enclosed-by=name(字段引用符);

--fields-optionally-enclosed-by=name(字段引用符,只用在charvarchar text 等字符型字段上);

--fields-escaped-by=name(转义字符);

--lines-terminated-by=name(记录结束符)

使用示例:

mysqldump -uroot -T /tmp test emp --fields-terminated-by ‘,‘

--fields-optionally-enclosed-by ‘"‘

      语句执行成功后,除了生成数据文件emp.txt之外,还生成一个emp.sql文件,里面记录了emp表的创建脚本,可以发现,

除多了一个表的创建脚本文件外,mysqldumpSELECINTO OUTFILE…的选项和语法非常类似。其实,mysqldump实际调用的就

是后者提供的接口,并在其上面添加了一些新的功能而已。

2. 导入:

      这里只讨论用SELECTINTO OUTFILE或者mysqldump导出的纯数据文本的导入方法。和导出类似,导入也有两种不同的方法,

分别是LOAD DATA INFILE…和mysqlimport,它们的本质是一样的,区别只是在于一个在MySQL 内部执行,另一个在MySQL 外部执行。

方法一:使用LOAD DATA INFILE…”命令。

LOAD DATA [LOCAL] INFILE filenameINTO TABLE tablename [option]

      这里的option除了有和select ...into outfile一样的选项以外,还有一些不同的选项:

IGNORE number LINES(忽略输入文件中的前n 行数据);

(col_name_or_user_var,...) (按照列出的字段顺序和字段数量加载数据);

SET col_name = expr,... 将列做一定的数值转换后再加载。

使用示例:

load data infile ‘/tmp/emp.txt‘ into table emp fields terminated by ‘,‘ enclosed by

‘"‘ ignore 2 lines (id,content,name);

方法二:用mysqlimport来实现,具体命令如下。

mysqlimport -u root -p*** [--LOCAL] dbname order_tab.txt [option]

      这里的option除了有和mysqldump一样的选项以外,还有一些不同的选项:

-- ignore-lines=number(或略前几行)。

使用示例:

mysqlimport -uroot test /tmp/emp.txt --fields-terminated-by=‘,‘

--fields-enclosed-by=‘"‘

 

注意:

      使用select...into outfile方法导出的文件中记录的顺序和数据表中记录的顺序是一样的,而使用mysqldump导出的文件中

记录的顺序则是和数据表中记录的顺序相反。使用load data infile方法和mysqlimport导入的数据表中的记录顺序和文件中记录的

顺序都是相反的。所以,如果要求数据表导入导出前后表中记录顺序不变的话,应该使用mysqldump来导出。

 

 

 

 

备份与恢复