Mysqldump(A database backup program)

1.Mysqldump的功能和起源

   The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for
    backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate
    it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format. 
   2.Mysqldump常见的用法
   1.shell> mysqldump [options] db_name [tbl_name ...]
   2.shell> mysqldump [options] --databases db_name ...
   3.shell> mysqldump [options] --all-databases
     区别：
   1.对某库下某些表进行备份 ，db_name表示schema名称，[tbl_name…]表示该schema下的table。
    shell> mysqldump xiansi test1 test2 
    对 schema为xiansi下的test1和test2表进行备份 。
   2.对某些schema进行备份。db_name..
    shell> mysqldump  --databases dingyuan xiansi
    对schema为dingyuan和xiansi进行备份
   3.对所有库进行备份
    shell> mysqldump –all-databases
   4.Mysqldump默认参数带来的影响
   Mysqldump使用 –opt作为其默认参数，--opt是如下参数的集合
         --add-drop-table,--add-locks,--create-options,--disable-keys,--extended-insert,
         --lock-tables,--quick,--set-charset
   取消默认参数 –skip-opt
        --compact是如下参数的集合
        --skip-add-drop-table,--skip-add-locks, --skip-comments, --skip-disable-keys,
        --skip-set-charset
    取消—compact参数 –skip-compact
    5.Mysqldump参数
    1.--add-drop-database
     Add a DROP DATABASE statement before each CREATE DATABASE statement
    2. --add-drop-table
    Add a DROP TABLE statement before each CREATE TABLE statement
    3.--add-locks
     Eg:
     -- Dumping data for table `test2`
     LOCK TABLES `test2` WRITE;
     INSERT INTO `test2` VALUES (2,'xiaocai');
     INSERT INTO `test2` VALUES (2,'xiaocai');
     INSERT INTO `test2` VALUES (3,'xiaocai');
     UNLOCK TABLES; unarround each table dump with LOCK TABLES and UNLOCK TABLES statements(并不是说在导出的时候对表加锁，而是对导出的语句用lock包围).
    6. --all-databases
     Dump all tables in all databases
    7. –comments
     Add comments to the dump file
    8.—compact
     Produce more compact output
     Eg：
    CREATE TABLE `test1` (
    `id` int(11) NOT NULL,
    `name` varchar(20) DEFAULT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
    /*!40101 SET character_set_client = @saved_cs_client */;
     INSERT INTO `test1` VALUES (1,'xixxi'),(2,'xiaocai'),(3,'xiaocai');
     /*!40101 SET @saved_cs_client     = @@character_set_client */;
     /*!40101 SET character_set_client = utf8 */; 
    相当于--skip-add-drop-table,--skip-add-locks, --skip-comments, --skip-disable-keys,
   --skip-set-charset，数据更紧凑。
    9. --complete-insert
    /*!40101 SET character_set_client = @saved_cs_client */;
     INSERT INTO `test1` (`id`, `name`) VALUES (1,'xixxi'),(2,'xiaocai'),(3,'xiaocai');
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
     Use complete INSERT statements that include column names
    10.--create-options
     Include all MySQL-specific table options in CREATE TABLE statements
    11.—databases
     Dump several databases
    12.--debug-info
    Print debugging information, memory and CPU statistics when the program exits
     Eg:
      -- Dump completed on 2012-03-06 17:24:26
      User time 0.00, System time 0.00
      Maximum resident set size 0, Integral resident set size 0
      Non-physical pagefaults 534, Physical pagefaults 0, Swaps 0
      Blocks in 0 out 0, Messages in 0 out 0, Signals 0
      Voluntary context switches 127, Involuntary context switches 1
    13. --default-character-set=charset_name
    Use charset_name as the default character set
    14. --delete-master-logs
    On a master replication server, delete the binary logs after performing the dump operation.
    对于Master-slave系统，在master上加上此参数，会删除binlog,可能导致主备复制无法继续。
    15. --dump-date
    Include dump date as "Dump completed on" comment if --comments is given
    16. –events
    Dump events from the dumped databases
    17. --extended-insert
    Use multiple-row INSERT syntax that include several VALUES lists
    Eg:
    Insert into table values(…),(…),(…)
    18. --fields-enclosed-by=string
    Eg:
    [oracle@dbtest13 dbtest13 /tmp]
     $ mysqldump --defaults-file=/data/mysql2/my.cnf --tables xiaocai test1 test2 --complete-insert --skip-dump-date --events  --fields-enclosed-by='"' --tab=/tmp/
     $ cat test2.txt
    "2"     "xiaocai"
    "2"     "xiaocai"
    "3"     "xiaocai“
    This option is used with the --tab option and has the same meaning as the corresponding clause    for LOAD DATA INFILE.
    19. --fields-escaped-by
     This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
    20. --flush-logs
    Flush the MySQL server log files before starting the dump
    21. --flush-privileges
    Emit a FLUSH PRIVILEGES statement after dumping the mysql database
    22.--ignore-table=db_name.tbl_name
    Do not dump the given table
    23. --lock-all-tables
    Lock all tables across all databases
    会在备份期间锁住所有备份库的所有表。对于innodb表只允许访问，不允许dml操作。
    24. --lock-tables
    For each dumped database, lock all tables to be dumped before dumping them, The tables are locked with READ LOCAL to permit concurrent inserts in the case of MyISAM tables.
    会在备份该库的时候对该库下的所有表加share锁，对于innodb表，只有在备份到该database的时候才会对该库的所有表加锁，其他库的表无影响 。可能有依赖的schema间数据不一致 。
    25. --log-error=file_name
    Append warnings and errors to the named file
    26. –master-data[=value]
    Write the binary log file name and position to the output
    Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and  position)  of the dumped server. These are the master server     coordinates from which the slave should start replicating after you load the dump file into the slave.
    If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is     1.为2的时候，会把change master to语句作为comment写到备份文件中。为1则会当作执行语句写入到备份文件,默认为1,会启用—lock-all-tables.
    27. --max_allowed_packet=value
    The maximum packet length to send to or receive from the server
    此值的设置取决于网络的好坏而定。
    28.--net_buffer_length=value
    The buffer size for TCP/IP and socket communication
    29. --no-autocommit
    Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
    对于每一表的数据转储前自动加上 set autocommit=0,转储后自动加上commit;
    30. --no-create-db
    This option suppresses the CREATE DATABASE statements
    31. --no-create-info
    Do not write CREATE TABLE statements that re-create each dumped table
    32. --no-data
    Do not dump table contents
    33.—opt
    Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.
    34. --order-by-primary
    Dump each table's rows sorted by its primary key, or by its first unique index
    35. –quick
    Retrieve rows for a table from the server a row at a time
    This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.
    每次获取一行，而不是每次获得结果集放到buffer中，然后到一定量之后刷出来 。
    36.—replace
    Write REPLACE statements rather than INSERT statements
    37. --result-file=file
    Direct output to a given file
    38. --single-transaction
    This option issues a BEGIN SQL statement before dumping data from the server
    This option sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when BEGIN was issued without blocking any applications.
    When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.
    While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A c   onsistent  read is not isolated from those statements, so use of         them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.
    --single-transaction参数在刚开始的时候，会获得数据库的一个一致状态，这个时候会获得一个全局的lock，当然这个锁持有的时间会很短，当他确定状态后，这个锁便释放了。在这短暂的时间内，这个库将只能进行read(如果是带有—all-databases备份，在这个短暂的时间内，所有的表都回  lock住)。
    39. --skip-add-locks
    Do not add locks
    Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded。
    只是备份的数据前后不加lock table …/unlock table.，仅此而已。
    40.--skip-quick
    Do not retrieve rows for a table from the server a row at a time
    会将结果集放在buffer中，到一定程度之后刷到磁盘。
    5.Mysqldump使用应该注意的几点
    1.对于innodb表数据库，如果不加任何参数，默认使用—opt，在备份的时候，备份到该schema时，会对该schema下的所有表lock，备份完之后释放。在期间其他schema可进行正常dml，此schema的dml被阻塞，select正常 。
    2.对于使用—lock-all-tables参数的备份 ，会在备份期间对所有的schema加锁，防止更新，来获得一致的状态 。
    3.对于使用—single-transaction的备份，只会在备份前对所有schema加锁，获得binlog，undo等一个一致的信息 。而后释放锁，通过mvcc实现一致读，锁时间较短，推荐使用。
    4 --quick会在备份期间绕过缓存，每次返回一条记录 ，以最快的方式返回，减少了刷缓存频率，防止一些经常被访问的数据刷除去 。
    5.—skip-quick会在数据达到一定量的时候才从buffer中刷出去，需要使用buffer，可能导致经常被访问的数据在此期间被刷到磁盘。


Mysqldump总结
  1. Mysqldump不论在什么情况下，都会锁表(--single-transaction/--lock-tables/--lock-all-tables),这些锁对于提供只读的oltp系统(使用innodb表)并没有多大影响，而对于写比较频繁的系统在可能会产生一定的影响。
  2.备份是一件重要的事情，对于mysql来说，经常会遇到拆分，搭建备库的情况，而此时如果事前有一个备份(不久之前，binlong还未被清除)，对于搭建将会是很快的 ，不需要在搭建的时候对大文件物理/逻辑的拷贝，而.ZZ费较长时间。Mysqldump简单易用，且效率还可以(没有和其他备份软   件对比过 ，随口说说…),已经足以应付数据量不是很大的备份   了，当然，这些备份都是在slave(提供只读，避免锁表)进行的。
  3.mysqldump目前只能支持单线程，对于大数据量的备份，可能不太适合，可能不能在规定的时间内完成，或许其他更专业的软件才能支持。