www.baike369.com
百科369 > MySQL教程 > MySQL使用mysqldump命令备份数据库

MySQL使用mysqldump命令备份数据库


MySQL使用mysqldump命令备份数据库

mysqldump命令可以将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。

mysqldump命令的工作原理很简单。它先查出需要备份的表的结构,再在文本文件中生成一个CREATE语句。然后,将表中的所有记录转换成一条INSERT语句。这些CREATE语句和INSERT语句都是还原时使用的。

还原数据时就可以使用其中的CREATE语句来创建表,使用其中的INSERT语句来还原数据了。


MySQL备份一个数据库

使用mysqldump命令备份一个数据库的基本语法格式如下:

mysqldump -u user -h host -ppassword dbname[tbname [tbname ...]]>filename.sql

其中:

  • user参数表示用户名称。
  • host参数表示登录用户的主机名称。
  • password参数为登录密码。
  • dbname参数表示需要备份的数据库的名称。
  • tbname参数为dbname数据库中需要备份的数据表,可以指定多个需要备份的表。没有该参数时将备份整个数据库。
  • 右箭头符号“>”告诉mysqldump将备份数据表的定义和数据写入备份文件。
  • filename.sql参数表示备份文件的名称,文件名前面可以加上一个绝对路径。通常将数据库备份成一个扩展名为sql的文件。

提示:

mysqldump命令备份的文件并非一定要求后缀名为.sql,也可以备份成其它格式的文件,例如,扩展名为.txt的文件。但是,通常情况下是备份成后缀名为.sql的文件。因为,后缀名为.sql的文件给人第一感觉就是与数据库有关的文件。

实例:

使用mysqldump命令备份数据库中的所有表。SQL代码如下:

D:\>mysqldump -u root -p db_xuesheng>F:\backup\db_xuesheng_20141028.sql

db_xuesheng是要备份的数据库的名称。

打开操作系统命令行输入窗口,输入备份命令。如下图所示:

使用mysqldump命令备份数据库中的所有表的操作效果

输入密码之后,MySQL便对数据库进行了备份。在F:\backup文件夹下面可以看到刚才备份过的文件。如下图所示:

在F:\backup文件夹中可以看到备份的MySQL文件

使用“记事本”程序打开备份的文件db_xuesheng_20141028.sql,可以看到其部分文件内容。如下图所示:

使用“记事本”程序查看MySQL备份文件

可以看到,备份文件包含了一些信息,文件开头首先表明了备份文件使用的mysqldump工具的版本号;然后是备份账户的名称和主机信息,以及备份的数据库的名称;最后是MySQL服务器的版本号,在这里为5.6.19。

备份文件中的“--”字符开头的行为注释语句;以“/*!”开头、“*/”结尾的语句为可执行的MySQL注释,这些语句可以被MySQL执行,但在其它数据库管理系统将被作为注释忽略,这可以提高数据库的可移植性。

备份文件开始的一些语句以数字开头,这些数字代表了MySQL版本号,该数字告诉我们,这些语句只有在指定的MySQL版本或者比该版本高的情况下才能执行。例如40101,表明这些语句只有在MySQL版本号为4.01.01或者更高的版本下才可以被执行。

后面的DROP语句、CREATE语句和INSERT语句都是还原时使用的。文件的最后记录了备份的时间。

注意:db_xuesheng_20141028.sql文件中没有创建数据库的语句,因此,db_xuesheng_20141028.sql文件中的所有表和记录必须还原到一个已经存在的数据库中。可以先创建一个空数据库,然后再还原数据库文件。还原数据时,CREATE TABLE语句会在数据库中创建表,然后执行INSERT语句向表中插入记录。


MySQL备份数据库中的某个表

mysqldump还可以备份数据库中的某个表。基本的语法格式如下:

mysqldump -u user -h host -p dbname [tbname [tbname ...]]>filename.sql
  • tbname表示数据库中的表名,多个表名之间用空格隔开。

备份表和备份数据库中所有表的语句中不同的地方在于,要在数据库名称dbname之后指定需要备份的表名称。

实例:

备份db_xuesheng数据库中的tb_chengji表。输入的SQL语句如下:

mysqldump -u root -p db_xuesheng tb_chengji>F:\backup\tb_chengji.sql

执行结果如下:

备份db_xuesheng数据库中的tb_chengji表的操作效果

该语句创建了名称为tb_chengji.sql的备份文件。


MySQL备份多个数据库

mysqldump命令备份多个数据库的语法格式如下:

mysqldump -u user -h host -p --databases [dbname [dbname]]>filename.sql
  • 使用--databases参数之后,必须指定至少一个数据库的名称,多个数据库名称之间用空格隔开。

实例:

使用mysqldump命令备份db_xuesheng和example数据库。SQL代码如下:

mysqldump -u root -p --databases db_xuesheng example>F:\backup\xuesheng_example.sql

执行结果如下:

MySQL备份多个数据库的操作效果

该语句创建了名称为xuesheng_example.sql的备份文件,文件中包含了创建两个数据库db_xuesheng和example所必须的所有语句。


MySQL备份所有数据库

mysqldump命令备份所有数据库的语法格式如下:

mysqldump -u user -h host -p --all-databases>filename.sql

使用“--all-databases”选项可以备份所有的数据库。

实例:

使用mysqldump命令备份服务器中所有的数据库。SQL代码如下:

mysqldump -u root -p --all-databases>F:\backup\alldb.sql

执行结果如下:

MySQL备份所有数据库的操作效果

该语句创建了名称为alldb.sql的备份文件,文件中包含了对系统中所有数据库的备份信息。


mysqldump的其它选项

mysqldump还有一些其它选项可以用来制定备份过程。例如--opt选项,该选项将打开--quick、--add-locks、--extended-insert等多个选项。使用--opt选项可以提供最快速的数据库转储。

mysqldump的其它常用选项如下:

  • --add-drop-database:在每个CREATE DATABASE语句前添加DROP DATABASE语句。
  • --add-drop-tables:在每个CREATE TABLE语句前添加DROP TABLE语句。
  • --add-locking:用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。
  • --all--database, -A:转储所有数据库中的所有表。与使用--database选项相同,在命令行中命名所有数据库。
  • --comments[=0|1]:如果设置为0,禁止转储文件中的其它信息,例如程序版本、服务器版本和主机。--skip-comments与--comments=0的结果相同。默认值为1,即包括额外信息。
  • --compact:产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking选项。
  • --compatible=name:产生与其它数据库系统或旧的MySQL服务器更兼容的输出。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options或者no_field_options。
  • --complete-insert, -c:使用包括列名的完整的INSERT语句。
  • ---debug[=debug_options], -#[debug_options]:写调试日志。
  • --delete, -D:导入文本文件前清空表。
  • --default-character-set=charset:使用charsetas默认字符集。如果没有指定,mysqldump使用utf8。
  • --delete-master-logs:在主复制服务器上,完成转储操作后删除二进制日志。该选项自动启用-master-data。
  • --extended-insert, -e:使用包括几个VALUES列表的多行INSERT语法。这样使转储文件更小,重载文件时可以加速插入。
  • --flush-logs, -F:开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。
  • --force, -f:在表转储过程中,即使出现SQL错误也继续。
  • --lock-all-tables, -x:对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。该选项自动关闭--single-transaction和--lock-tables。
  • --lock-tables,-1:开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表、对于事务表(例如InnoDB和BDB),--single-transaction是一个更好的选项,因为它根本不需要锁定表。
  • --no-create-db, -n:该选项禁用CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name语句,如果给出--database或--all--database选项,则包含到输出中。
  • --no-create-info, -t:只导出数据,而不添加CREATE TABLE语句。
  • --no-data, -d:不写表的任何行信息,只转储表的结构。
  • --opt:该选项是速记,等同于指定--add-drop-tables--add-locking,--create-option,--disable-keys--extended-insert,--lock-tables-quick和--set-charset。它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件。该选项默认开启,但可以用--skip-opt禁用。要想禁用使用-opt启用的选项,可以使用--skip形式,例如--skip-add-drop-tables或--skip-quick。
  • --password[=password], -p[password]:当连接服务器时使用的密码。如果使用短选项形式(-p),选项和密码之间不能有空格。如果在命令行中--password或-p选项后面没有密码值,则提示输入一个密码。
  • --port=port_num, -P port_num:用于连接的TCP/IP端口号。
  • --protocol={TCP|SOCKET|PIPE|MEMORY}:使用的连接协议。
  • --replace,-r --replace和--ignore选项控制替换或复制唯一键值已有记录的输入记录的处理。如果指定--replace,新行替换有相同的唯一键值的已有行;如果指定--ignore,复制已有的唯一键值的输入行被跳过。如果不指定这两个选项,当发现一个复制键值时会出现一个错误,并且忽视文本文件的剩余部分。
  • --silent, -s:沉默模式,只有出现错误时才输出。
  • --socket=path, -S path:当连接localhost时使用的套接字文件(为默认主机)。
  • --user=user_name, -u user-name:当连接服务器时MySQL使用的用户名。
  • --verbose, -v:冗长模式。打印出程序操作的详细信息。
  • --version, -V:显示版本信息并退出。
  • --xml, -X:产生XML输出。

注:以--xml, -X为例,可以使用--xml参数或者使用-X参数,二者只能使用其一。

mysqldump提供许多选项,包括用于调试和压缩的,在这里只是列举最有用的。运行帮助命令mysqldump --help,可以获得特定版本的完整选项列表。

提示:

如果运行mysqldump没有--quick或--opt选项,mysqldump在转储结果前将整个结果集装入内存。如果转储大数据库可能会出现问题。该选项默认启用,但可以用--skip-opt禁用。如果使用最新版本的mysqldump程序备份数据,并用于还原到比较旧版本的MySQL服务器中,则不要使用--opt或-e选项。

Copyright© 2011-2016 www.baike369.com All Rights Reserved