煮酒听雨

mysql备份数据库

编辑时间：2017-02-26 编辑：忘尘阅读：755

1.导出所有数据库

mysqldump -uroot -proot --all-databases >/tmp/all.sql

2.导出db1、db2两个数据库的所有数据

mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql

3.导出db1中的a1、a2表

mysqldump -uroot -proot --databases db1 --tables a1 a2  >/tmp/db1.sql

注意导出指定表只能针对一个数据库进行导出，且导出的内容中和导出数据库也不一样，导出指定表的导出文本中没有创建数据库的判断语句，只有删除表-创建表-导入数据

4.条件导出，导出db1表a1中id=1的数据

如果多个表的条件相同可以一次性导出多个表

字段是整形

mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1'  >/tmp/a1.sql

字段是字符串,并且导出的sql中不包含drop table,create table

mysqldump -uroot -proot --no-create-info --databases db1 --tables a1 --where="id='a'"  >/tmp/a1.sql

5.生成新的binlog文件,-F

有时候会希望导出数据之后生成一个新的binlog文件,只需要加上-F参数即可

mysqldump -uroot -proot --databases db1 -F >/tmp/db1.sql

6.只导出表结构不导出数据，--no-data

mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql

7.跨服务器导出导入数据

mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2

将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错  加上-C参数可以启用压缩传递。

mysqldump --host=192.168.80.137 -uroot -proot -C --databases test |mysql --host=192.168.80.133 -uroot -proot test

8.保证导出的一致性状态--single-transaction

该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎（它不显示加锁通过判断版本来对比数据），仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。

9.--lock-tables, -l

开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。

请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。

10.导出存储过程和自定义函数--routines, -R

mysqldump  -uroot -p --host=localhost --all-databases --routines

11.压缩备份

mysqldump -uroot -proot --databases abc 2>/dev/null |gzip >/abc.sql.gz

还原

gunzip -c abc.sql.gz |mysql -uroot -proot abc

####常用案例：

1.导出数据库

mysqldump -hHost -uUser -pPassword --single-transaction --default-character-set=utf8 dbName(数据库名) > 导出路径/dbname.sql

2.导出数据库不包括A表

mysqldump -hHost -uroot -plnmp_root --single-transaction --ignore-table=dbname.A  --default-character-set=utf8 dbName(数据库名) > 导出路径/dbname.sql

3.导出数据库表A 表B

mysqldump -hHost -uroot -p123456 --databases db1 --tables A B > 导出路径/table.sql;

来说两句吧