mysql数据库备份
时间:2013-10-08 08:10:53 作者:beebol 标签: 分类: Mysql
备份主要有两种方法
（1）将数据生成为可以完全重现当时数据库中数据的INSERT语句；
（2）通过软件，将数据库表的数据以特定的分隔符进行分隔后记录在文本文件中。
一、生成INSERT语句备份：
问题：mysqldump不停机备份时，可能有数据变更的请求产生，这样可能造成mysqldump备份出来的数据不一致，在对一致性和完整性要求比较严格的系统中，该备份可能是无效的备份。
解决方法：
（1）可以把整个备份过程控制在一个事务中；
mysqldump通过--single-transaction选项支持此功能而不影响数据库的服务。
（2）通过锁表：--lock-tables每次锁定一个数据库的表；
另，--lock-all-tables一次锁定所有的表，适用于dump的表分别处于各个不同的数据库中的情况。
二、生成特定格式的纯文本备份数据文件：
二、生成特定格式的纯文本备份数据文件：
优点：使用存储空间小，数据格式清晰，编辑方便；
缺点：每个备份文件只能备份一个表，没有数据库结构的重建命令（不过可以通过其他方式生成）； 生成特定格式的纯文本备份数据文件：
生成特定格式的纯文本备份数据文件：
（1）select ... from... into outfile [fields terminated by]
[lines terminated by]
（2）通过mysqldump，即 –T选项：结构与数据分别导出备份；
例如：mysqldump -u root -p -T /root/Desktop/test;
[root@localhost Desktop]# mysqldump test -T test/注意：这里的test/是目录，不是文件。 如果出现如下报错，一般是当前目录的权限不够，数据为数据库运行用户才行。
[root@localhost Desktop]# mysqldump test -T test/ mysqldump: Got error: 1: Can't create/write to file '/root/Desktop/test/pet.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'chown -R mysql.mysql test/之后，然后再进行备份，看到如下内容：
[root@localhost test]# ls pet.sql pet.txt [root@localhost test]# more pet.txt xixi liuzhen dog m 2013-10-04 \N Fluffy Harold cat F 1993-02-04 \N Claws Gwen cat M 1994-03-17 \N Buffy Harold dog F 1989-05-13 \N Chirpy Gwen bird F 1998-09-11 \N Fang Benny dog M 1990-08-27 \N Bowser Diane dog M 1990-08-31 1995-07-29 Whistler Gwen bird \N 1997-12-09 \N Slim Benny snake M 1996-04-29 \N Puffball Diane hamster F 1999-03-30 \N另：上面\N在数据库里为NULL mysql还有一种备份方法是用mysqlhotcopy mysqlhotcopy –u root –p 密码 数据库名 备份目录
[root@localhost Desktop]# mysqlhotcopy test data/ Locked 2 tables in 0 seconds. Flushed tables (`test`.`food`, `test`.`pet`) in 0 seconds. Copying 4 files... Copying indices for 0 files... Unlocked tables. mysqlhotcopy copied 2 tables (4 files) in 0 seconds (0 seconds overall). [root@localhost Desktop]# ls data/ test [root@localhost Desktop]# cd data/test/ [root@localhost test]# ls food.frm food.MYD food.MYI pet.frm就是把数据库目录文件全部拷贝出来
[root@localhost test]# ls food.frm food.MYD food.MYI pet.frm [root@localhost test]# pwd /var/mysql/data/test使用mysqlhotcopy需要提前安装perl-DBD-Mysql,安装好后才能使用，否则无法使用，会报如下错误
[root@localhost Desktop]# mysqlhotcopy test data/ install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.8/i386-linux-thread-multi /usr/lib/perl5/5.8.8 .) at (eval 7) line 3. Perhaps the DBD::mysql perl module hasn't been fully installed, or perhaps the capitalisation of 'mysql' isn't right. Available drivers: DBM, ExampleP, File, Proxy, Sponge. at /usr/local/mysql/bin/mysqlhotcopy line 197那么，MysqlDump与MysqlHotCopy的区别是什么？ mysqlhotcopy 只能用于备份 MyISAM，并且只能运行在 类Unix 和 NetWare 系统上。 目前 MySQL 支持的免费备份工具有：mysqldump、mysqlhotcopy，还可以用 SQL 语法进行备份：BACKUP TABLE 或者 SELECT INTO OUTFILE，又或者备份二进制日志（binlog），还可以是直接拷贝数据文件和相关的配置文件。MyISAM 表是保存成文件的形式，因此相对比较容易备份，上面提到的几种方法都可以使用。Innodb 所有的表都保存在同一个数据文件 ibdata1 中（也可能是多个文件，或者是独立的表空间文件），相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。 mysqldump的调用方式： shell> mysqldump [options] db_name [tables] shell> mysqldump [options] --database DB1 [DB2 DB3...] shell> mysqldump [options] --all-database 注意：如果没有指定任何表或使用了--database或--all-database选项，则转储整个数据库。 Mysqldump的重要选项：
| Options(参数) | 说明 |
| --add-drop-database | 在每个CREATE DATABASE语句前添加DROP DATABASE语句 |
| --add-drop-tables | 在每个CREATE TABLE语句前添加DROP TABLE语句 |
| --all-database | 转储所有数据库中的所有表，与--database相同 |
| --allow-keywords | 允许创建关键字列名 |
| --comments[={0|1}] | 如果设置为 0，禁止转储文件中的其它信息。 默认值为1，即包括额外信息。 |
| --default-character-set=charset | 使用charsetas默认字符集。 |
| --database，-B | 转储几个数据库 |
| --quick | 该选项用于转储大的表。它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。 |
| --add-locks | 在插入数据开始的时候加上LOCK TABLES |
| -R, --routines | 导出函数和存储过程 |
| --triggers | 导出触发器 |
| -n, --no-create-db | 不提供建库语句 |
| -t, --no-create-info | 不写重新创建每个转储表的CREATE TABLE语句 |
| -d, --no-data | 不提供导出数据库数据记录 |
| 分别导出数据库结构和数据，生成.sql和.txt文件 | |
| --tables | 用表格式显示输出 |