注意:备份加入–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