mysql dump全部数据库_Mysqldump 备份说明及数据库备份脚本分享-运维笔记

MySQLdump是MySQL自带的导出数据工具，即mysql数据库中备份工具，用于将MySQL服务器中的数据库以标准的sql语言的方式导出，并保存到文件中。Mysqldump是一个客户端逻辑备份的工作，备份的SQL文件可以在其他MySQL服务器上进行还原。

如需备份，则至少需要对该表的select权限，需要备份视图则需要改账户具有SHOW VIEW权限，触发器需要TRIGGER。如需锁表，则不可使用--single-transaction选项。其他权限暂未列出。

如需还原，则需要对应的执行权限，如create表，则需要对该库的create权限。

Mysqldump导出可以包括ALTER DATABASE与相关更改，其中包括导出元数据、更改字符编码，在导入对应文件时，相应的权限更改也会发生影响。如果在服务器上备份的表均为MyISAM，也可以使用mysqlhotcopy来完成任务。

从性能和伸缩性考虑，mysqldump的优势包括：在输出之前可以很方便的查看或编辑文件，你还可以克隆数据库与DBA的工作，或者将生产环境下的数据进行微小测试。这不是作为快速备份或可伸缩性很强的方案。即针对不同大小的数据需要安排合理时间，在需要还原时速度并不快，因为需要重新进行I/O、创建索引等。

对于大量数据来说，物理备份可能更为合适，因为它可以快速还原。

- 如果你的表以Innodb引擎为主，可以考虑使用MySQL的mysqlbackup命令。因为他提供了最好的Innodb备份：它也可以备份其他如MyIsAM与其他存储引擎；提供了更多的备份参数进行选择。详情见手册 25.2 “MySQL Enterprise Backup Overview”

- 如果你的表以MyISAM引擎为主，可以考虑使用mysqlhotcopy来替代，它可能比mysqldump表现更为好，详情见mysqlhotcopy手册。

Mysqldump可以行级导出数据，或者也可以将整个表载入内存一次性导出，如果将所有数据导入内存可能会面临内存缓冲区不够的问题。如果要行级导出数据，使用--quick选项(或者--opt，它支持--quick)。--opt选项是默认选项，所以为了保证内存缓冲区，使用--skip-quick。

如果使用高版本的mysqldump来备份一个低版本的MySQL服务器中的数据，使用--skip-opt代替--opt或者-- extended-insert选项。

下面对它的使用进行总结性梳理：

查询该命令的帮助信息(有很多参数，具体也可以man一下)

[root@Mysql-node1 ~]# mysqldump --help

常用的几个选项

--add-drop-table：在每个创建数据库表语句前添加删除数据库表的语句；

--add-locks：备份数据库表时锁定数据库表；

--all-databases(即-A)：备份MySQL服务器上的所有数据库；

--comments：添加注释信息；

--compact：压缩模式，产生更少的输出；

--complete-insert：输出完成的插入语句；

--databases(即-B)：指定要备份的数据库；

--default-character-set：指定默认字符集；

--force(即-f)：当出现错误时仍然继续备份操作；

--host：指定要备份数据库的服务器；

--lock-tables：备份前，锁定所有数据库表；

--no-create-db：禁止生成创建数据库语句；

--no-create-info：禁止生成创建数据库库表语句；

--password：连接MySQL服务器的密码；

--port：MySQL服务器的端口号；

--user：连接MySQL服务器的用户名。

--no-data(即-d)：只导出表结构。

--quick,(即-q)：快速导出。

--xml,(即-X)：导出为xml文件。

--tables：覆盖 --databases or -B选项，后面所跟参数被视作表名。

--default-character-set，字符集设置。比如--default-character-set=gb2312、--default-character-set=utf8

参数--lock-tables 表示的是一次性锁定当前库的所有表, 而不是锁定当前导出表! 例如:

# mysqldump -u root -p --default-character-set=utf8 --opt --extended-insert=false --lock-tables MyDB > db_backup_MyDB.sql

实例说明：

1)导出、导入所有的库(--all-databases,  -A   导出全部数据库)

导出所有的库

[root@Mysql-node1 ~]# mysqldump -uroot -hlocalhost -p123456 --all-databases >/opt/all.sql

Warning: Using a password on the command line interface can be insecure.

导入所有的库

[root@Mysql-node1 ~]# mysql -uroot -hlocalhost -p123456 < /opt/all.sql

Warning: Using a password on the command line interface can be insecure.

或者

mysql> source /opt/all.sql;

2)导出、导入某个库(比如kevin库)

导出kevin库

[root@Mysql-node1 ~]# mysqldump -uroot -hlocalhost -p123456 kevin > /opt/kevin.sql

Warning: Using a password on the command line interface can be insecure.

导入kevin库(前提是kevin库要存在，如果没有，在导入前先创建kevin空库)

[root@Mysql-node1 ~]# mysql -uroot -hlocalhost -p123456 kevin < /opt/kevin.sql

Warning: Using a password on the command line interface can be insecure.

或者

mysql> use kevin；

mysql> source /opt/all.sql;

3)导出、导入某几个库(比如kevin、grace库)(--databases， -B 导出部分数据库)

导出kevin、grace库

[root@Mysql-node1 ~]# mysqldump -uroot -hlocalhost -p123456 -B kevin grace > /opt/kevin_grace.sql

Warning: Using a password on the command line interface can be insecure.

导入kevin、grace库(多个库的导入、恢复数据操作，不需要要求这些库必须存在，如没有存在，不需要提前创建这些空库)

[root@Mysql-node1 ~]# mysql -uroot -hlocalhost -p123456 < /opt/kevin_grace.sql

Warning: Using a password on the command line interface can be insecure.

或者

mysql> source /opt/kevin_grace.sql;

4)导出、导入某个库的某些表数据

导出kevin库的haha、heihei表数据(可以在导出时加--add-locks参数，表示导出时锁定数据库表；这里没有添加)

[root@Mysql-node1 ~]# mysqldump -uroot -hlocalhost -p123456 kevin haha heihei > /opt/kevin_hahei.sql

Warning: Using a password on the command line interface can be insecure.

导入kevin库的haha、heihei表数据(要保证kevin库真实存在，如果没有，需要提前创建)<