使用mysqldump导出数据库

怼烎@ 2022-08-13 08:28 217阅读 0赞

mysqldump是mysql用于转存储数据库的客户端程序。它主要产生一系列的SQL语句，可以封装到文件，该文件包含有所有重建您的数据库所需要的SQL命令如CREATE DATABASE，CREATE TABLE，INSERT等等。可以用来实现轻量级的快速迁移或恢复数据库。是mysql数据库实现逻辑备份的一种方式。本文描述了mysqldump的一些重要参数以及给出了相关示例供大家参考。

1、获取mysqldump的帮助信息  
  \[root@SZDB ~\]\# mysqldump --help|more  
  mysqldump  Ver 10.13 Distrib 5.6.12, for Linux (x86\_64)  
    
  \#以下为MySQL dump调用的几种常用方式  
  Dumping structure and contents of MySQL databases and tables.  
  Usage: mysqldump \[OPTIONS\] database \[tables\]  
  OR     mysqldump \[OPTIONS\] --databases \[OPTIONS\] DB1 \[DB2 DB3...\]  
  OR     mysqldump \[OPTIONS\] --all-databases \[OPTIONS\]  
    
  \#如果没有指定任何表或使用了---database或--all--database选项，则转储整个数据库  
                   
  --opt           Same as --add-drop-table, --add-locks, --create-options,  
                   --quick, --extended-insert, --lock-tables, --set-charset,  
                   and --disable-keys. Enabled by default, disable with  
                   --skip-opt.  
                  
  -q, --quick           Don't buffer query, dump directly to stdout.    
                            (Defaults to on; use --skip-quick to disable.)  
  \#以上2个参数未使用的情况下，在转储结果之前会把全部内容载入到内存中，对于较大的数据库转储将严重影响性能。  
  \#缺省情况下这2个参数为开启状态。有些类似于Oracle的绕过PGA而直接写direct write。  
                
  --skip-opt        Disable --opt. Disables --add-drop-table, --add-locks,  
                      --create-options, --quick, --extended-insert,  
                      --lock-tables, --set-charset, and --disable-keys.               
  \#skip-opt与前2个参数相反，在转储之前先load到内存中。  
    
  --compatible=name   Change the dump to be compatible with a given mode. By  
                        default tables are dumped in a format optimized for  
                        MySQL. Legal modes are: ansi, mysql323, mysql40,  
                        postgresql, oracle, mssql, db2, maxdb, no\_key\_options,  
                        no\_table\_options, no\_field\_options. One can use several  
                        modes separated by commas. Note: Requires MySQL server  
                        version 4.1.0 or higher. This option is ignored with  
                        earlier server versions.  
    
  \#产生与其它数据库系统或旧版本MySQL服务器相兼容的输出。用于跨数据库，跨版本之间的迁移。  
  \#其值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no\_key\_options、no\_tables\_options或者no\_field\_options。  
  \#如果要使用多个值，用逗号将它们隔开。该选项不能保证同其它数据库服务器之间的完全兼容。如Oracle的数据类型等。  
                         
  --compact           Give less verbose output (useful for debugging). Disables  
                        structure comments and header/footer constructs.  Enables  
                        options --skip-add-drop-table --skip-add-locks  
                        --skip-comments --skip-disable-keys --skip-set-charset.  
  \#该选项使得输出的文件更小，启用后等用于使用一些skip项等。  
    
  -B, --databases     Dump several databases. Note the difference in usage; in  
                        this case no tables are given. All name arguments are  
                        regarded as database names. 'USE db\_name;' will be  
                        included in the output.  
  \#该选项一次导出多个数据库所有名字参量看作数据库名，更重要的是会生成CREATE DATABASE IF NOT EXISTS dbname   
    
  --default-character-set=name  
                        Set the default character set.  
  \#设置导出脚本的字符集，未指定的情况下为UTF8。  
    
  --flush-privileges  Emit a FLUSH PRIVILEGES statement after dumping the mysql  
                        database.  This option should be used any time the dump  
                        contains the mysql database and any other database that  
                        depends on the data in the mysql database for proper  
                        restore.  
  \#在dump mysql数据库以及依赖于mysql数据库恢复时建议使用该选项生成FLUSH PRIVILEGES语句  
    
  -F, --flush-logs    Flush logs file in server before starting dump. Note that  
                        if you dump many databases at once (using the option  
                        --databases= or --all-databases), the logs will be  
                        flushed for each database dumped. The exception is when  
                        using --lock-all-tables or --master-data: in this case  
                        the logs will be flushed only once, corresponding to the  
                        moment all tables are locked. So if you want your dump  
                        and the log flush to happen at the same exact moment you  
                        should use --lock-all-tables or --master-data with  
                        --flush-logs.  
  \#在启动dump前会flush日志，此方式可以用于实现增量备份  
                                                                                          
  -d, --no-data       No row information.  
  \#不输出数据行，仅导出结构  
                       
  -f, --force         Continue even if we get an SQL error.                                             
  \#在碰到错误时，依旧强制dump  
    
  --master-data\[=\#\]   This causes the binary log position and filename to be  
                        appended to the output. If equal to 1, will print it as a  
                        CHANGE MASTER command; if equal to 2, that command will  
                        be prefixed with a comment symbol. This option will turn  
                        --lock-all-tables on, unless --single-transaction is  
                        specified too (in which case a global read lock is only  
                        taken a short time at the beginning of the dump; don't  
                        forget to read about --single-transaction below). In all  
                        cases, any action on logs will happen at the exact moment  
                        of the dump. Option automatically turns --lock-tables  
                        off.  
  \#添加二进制日志位置到输出。1表示输出change master命令，2则注释输出change master命令。  
                         
  -R, --routines      Dump stored routines (functions and procedures).  
  \#导出函数和过程以及触发器，缺省情况下，这些不会被导出  
    
  -t, --no-create-info  
                        Don't write table creation info.  
  \#不生成建表语句  
                         
  --single-transaction  
                        Creates a consistent snapshot by dumping all tables in a  
                        single transaction. Works ONLY for tables stored in  
                        storage engines which support multiversioning (currently  
                        only InnoDB does); the dump is NOT guaranteed to be  
                        consistent for other storage engines. While a  
                        --single-transaction dump is in process, to ensure a  
                        valid dump file (correct table contents and binary log  
                        position), no other connection should use the following  
                        statements: ALTER TABLE, DROP TABLE, RENAME TABLE,  
                        TRUNCATE TABLE, as consistent snapshot is not isolated  
                        from them. Option automatically turns off --lock-tables.  
  \#创建一致性快照，仅仅针对innodb引擎  
  \#不能存在其他操作：ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE，关闭--lock-tables。  
                         
  -w, --where=name    Dump only selected records. Quotes are mandatory.  
  \#使用where子句只导出符合条件的记录  
  \# Author : Leshami  
  \# Blog   : [http://blog.csdn.net/leshami][http_blog.csdn.net_leshami]

2、dump的相关示例        
  备份单个数据库  
  shell> mysqldump -uroot -pxxx sakila >sakila.sql  
    
  带压缩方式备份数据库  
  shell> mysqldump -uroot -pxxx sakila |gzip >sakila.sql.gz  
    
  备份数据库上的特定表  
  shell> mysqldump -uroot -pxxx sakila actor >sakila\_actor.sql  
    
  备份表上特定的记录  
  shell> mysqldump -uroot -pxxx sakila actor -w "first\_name='NICK'" >sakila\_actor\_row.sql  
    
  备份数据库的结构  
  shell> mysqldump -uroot -pxxx sakila --no-data >sakila\_structure.sql  
    
  同时备份多个数据库  
  shell> mysqldump -uroot -pxxx --database sakila tempdb test >multidb.sql  
    
  备份服务器上的所有数据库  
  shell> mysqldump -uroot -pxxx --all-databases --opt --compact --flush-privileges >alldb.sql  
    
  只导出数据库中的存储过程，函数，触发器  
  shell> mysqldump -uroot -pxxx sakila --no-create-db --no-data --no-tablespaces --no-create-info --routines >sakila.sql  
    
  全量备份与增量备份  
  \#下面首先对数据库做全量备份并在备份前flush日志  
  shell> mysqldump -uroot -pxxx --single-transaction --flush-logs --master-data=2 sakila >sakila\_full.sql  
  \#假定备份时flush的日志为mysql-bin.000004，则使用如下方式恢复  
  shell> mysql -uroot -pxxx < sakila\_full.sql  
  shell> mysqlbinlog mysql-bin.000004 | mysql -uroot -pPwd

3、其它  
a、对比--database与直接数据库备份，如下，也就是说使用--database会生成建库语句  
  shell> mysqldump -uroot -pxxx --database tempdb >tempdb1.log  
  shell> mysqldump -uroot -pxxx --opt tempdb >tempdb2.log  
  shell> grep DATABASE tempdb1.log  
   CREATE DATABASE /\*!32312 IF NOT EXISTS\*/ \`tempdb\` /\*!40100 DEFAULT CHARACTER SET utf8 \*/;  
  shell> grep DATABASE tempdb2.log

b、性能相关  
  使用--quick或者--opt有助于加快dump的过程，减少dump所需的时间。  
  mysqldump不适用于大型数据库备份与恢复，速度慢，不支持并行，其次SQL重放将耗用大量的I/O。对于这种情形，建议使用物理备份方式。  
  如果mysql数据库中使用的存储引擎主要为innodb或myisam，或者2者的混合，可以考虑使用mysql企业版更高效的mysqlbackup工具。  
  如果mysql数据库中主要的表为myisam，更好的性能建议使用mysqlhotcopy方式。  
  详细参考：[http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html][http_dev.mysql.com_doc_refman_5.6_en_mysqldump.html]

[![鹏城DBA总群][DBA]][DBA_DBA]

[http_blog.csdn.net_leshami]: http://blog.csdn.net/leshami
[http_dev.mysql.com_doc_refman_5.6_en_mysqldump.html]: http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html
[DBA]: http://pub.idqqimg.com/wpa/images/group.png
[DBA_DBA]: /images/20220810/375393b6c2424df8a38ea500e991de9e.png