注意:备份加入–databases 备份文件中会有创建数据库的语句,否则没有

建议:导出库加入、导出表不加此参数

一、mysqldump命令备份Mysql数据库的参数说明

在用mysqldump备份使用那些参数选项是最完美的组合呢?

–skip-opt—-跳过 –add-drop-table, –add-locks,–create-options, –quick, –extended-insert等

–create-option —-添加create相关的选项

–single-transaction —-一致性备份,保证导出数据一致性

-q —-采用快速的dump方式(提高导出性能)

-e —-采用多重insert语句形式(提高还原性能),当一个表的数据量很大情况下不知道会不会导致死锁?

–no-autocommit —-采用批量提交方式(提高还原性能)

-R —-导出存储过程,函数,和触发器

–master-data —-如果有写log-bin且版本为5.0以上的版本,打开-lock-all-tables

则再加上 –master-data=1 (输出中会带change master 便于从库搭建)

则再加上 –master-data=2 (输出中会带注释change master便于从库搭建)

–events —-如果是5.1以上的版本使用,包含事件

–compress —-客户端与服务器之间启用压缩

CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000028′, MASTER_LOG_POS=154;

mysqldump产生两种类型的输出,具体取决于是否指定了该–tab 选项:

没有–tab, mysqldump将SQL语句写入标准输出。该输出包含 CREATE用于创建转储对象(数据库,表,存储的例程等)的INSERT语句,

以及 用于将数据加载到表中的语句。可以将输出保存在文件中,并稍后使用mysql重新创建转储的对象来重新加载 。选项可用于修改SQL语句的格式,并控制转储哪些对象。

使用–tab, mysqldump为每个转储的表产生两个输出文件。服务器以制表符分隔的文本形式写入一个文件,每表行一行。该文件tbl_name.txt 在输出目录中命名 。

服务器还将CREATE TABLE表的语句发送 到mysqldump,将其写为tbl_name.sql 在输出目录中命名的文件 。

不带–databases,转储输出不包含CREATE DATABASE or USE 语句。这有几个含义:

重新加载转储文件时,必须指定默认数据库名称,以便服务器知道要重新加载的数据库。

对于重新加载,可以指定一个与原始名称不同的数据库名称,这使您可以将数据重新加载到另一个数据库中。

如果要重装的数据库不存在,则必须首先创建它。

因为输出将不包含任何 CREATE DATABASE语句,所以该–add-drop-database 选项无效。如果使用它,则不会产生任何 DROP DATABASE语句。

二、mysqldump (sql 格式输出) ,默认标准输出到屏幕

单数据库导出:

mysqldump -uroot -p’gzjpm0330′ -S /tmp/mysql.sock kyriba > /home/mysql/backup/kyriba_fullback.sql

mysqldump -uroot -p’gzjpm0330′ -S /tmp/mysql.sock –databases kyriba > /home/mysql/backup/kyriba_fullback01.sql

单表导出:

mysqldump -uroot -p’gzjpm0330′ -S /tmp/mysql.sock kyriba k_bank > /home/mysql/backup/kyriba_k_bank.sql

多数据库导出:

mysqldump -uroot -p’gzjpm0330′ -S /tmp/mysql.sock –databases kyriba mysql >/home/mysql/backup/kyriba_mysql_fullback.sql

全部数据库导出:

mysqldump -uroot -p’gzjpm0330′ -S /tmp/mysql.sock –all-databases >/home/mysql/backup/database_fullback.sql

多库导出可以使用参数:-B 代替–databases

全库导出可以使用参数:-A 代替–all-databases

三、mysqldump输出定界格式文件,并且输出为实际数据的.txt文件和对象结构的.sql 文件(注意不用于导出库)

–fields-terminated-by:指定列值的分隔符,默认值Tab符

–fields-enclosed-by:指定列值的包括符,默认值没有(对于字符中包含列分隔符的直接包括起来)例如:12#kk#2#4,2与4间包括了#,使用包括符后,”2#4“

–fields-optionally-enclosed-by: 指定非数字列的包括符,默认值没有(有就使用,没有就不使用)

–fields-escped-by:指定转义符,默认值转义符\

–lines-terminated-by:指定行结束符,默认值就是换行符

通用性强的CSV 格式导出:

mysqldump -uroot -p’gzjpm0330′ -S /tmp/mysql.sock –tab=/home/mysql/backup/k_bank kyriba k_bank –fields-terminated-by=’,’ –fields-enclosed-by='”‘

–tab :备份输出到此位置

导出以逗号分隔,列值使用双引号引住

导出后有.txt(数据)和.sql(结构) 文件。每个表都有这两个文件

导出不包含数据:

shell> mysqldump –no-data test > dump-defs.sql

导出不包含结构:

shell> mysqldump –no-create-info test > dump-data.sql

四、使用mysqldump测试升级不兼容性

在考虑进行MySQL升级时,谨慎地将较新的版本与当前的生产版本分开安装。然后,您可以从生产服务器中转储数据库和数据库对象定义,

并将它们加载到新服务器中,以验证是否已正确处理它们。(这对于测试降级也很有用)

在生产服务器上:

shell> mysqldump –all-databases –no-data –routines –events > dump-defs.sql

在升级的服务器上:

shell> mysql < dump-defs.sql

因为转储文件不包含表数据,所以可以对其进行快速处理。这使您能够发现潜在的不兼容性,而无需等待冗长的数据加载操作。在处理转储文件时查找警告或错误。

验证定义是否正确处理后,转储数据并尝试将其加载到升级的服务器中。

在生产服务器上:

shell> mysqldump –all-databases –no-create-info > dump-data.sql

在升级的服务器上:

shell> mysql < dump-data.sql

现在检查表内容并运行一些测试查询

五、恢复

要重新加载由mysqldump编写的 包含SQL语句的转储文件,请将其用作mysql客户端的输入 。

如果转储文件是由mysqldump使用 –all-databases或 –databases选项创建的 ,

则它包含CREATE DATABASE和 USE语句,并且无需指定默认数据库以将数据加载到其中:

1、定界符格式恢复

先恢复表结构:

mysql -uroot -p’gzjpm030′ -S /tmp/mysql.sock kyriba </home/mysql/backup/k_bank/k_bank.sql

恢复数据:

mysql>use kyriba;

mysql>LOAD DATA INFILE ‘/home/mysql/backup/k_bank/k_bank.txt’ into table k_bank;

这种方式用的很少,对于语法要求很高。

2、SQL 格式恢复(默认的方式)

2.1、使用重定义符<

恢复kyriba数据库(注意导出方式)

1、先创建数据库

mysql -uroot -p’gzjpm030′ -S /tmp/mysql.sock kyriba < /home/mysql/backup/kyriba_fullback.sql

2、直接恢复

mysql -uroot -p’gzjpm030′ -S /tmp/mysql.sock < /home/mysql/backup/kyriba_fullback01.sql

恢复k_bank 表

mysql -usystem -p -S /usr/local/mysql/data/mysql.sock kyriba < kyriba_k_bank.sql

2.2、使用source(先创建kyriba数据库)

mysql>use kyriba;

mysql>source /home/mysql/backup/kyriba_fullback.sql;

1、mysqldump 从全备份中恢复单库、单表

恢复单库:(前提条件库是要存在的,恢复的是库里的对象)

mysql -uroot -p’gzjpm030′ -S /tmp/mysql.sock kyriba –one-database < database_fullback.sql

恢复单表:

先获取表结构:3种都可以,建议使用第3个,前面两个都需要获取备份中下一个表的名称(顺序与show tables 查看的一样)

awk ‘/^– Table structure for table `t2`/,/^– Table structure for table `t3`/{print}’ gl_test_full05.sql > t2.sql

sed -n -e ‘/– Table structure for table `t2`/,/– Table structure for table `t3`/p’ gl_test_full05.sql > t2_sed.sql

sed -e’/./{H;$!d;}’ -e ‘x;/CREATE TABLE `t4`/!d;q’ gl_test_full05.sql > t4.sql

获取表的数据:

grep -i ‘INSERT INTO `t4`’ gl_test_full05.sql >> t4.sql

恢复:

mysql -usystem -p -S /usr/local/mysql/data/mysql.sock gl_test < t4.sql