mysqldump在备份数据库的问题集
2013-03-11 10:01:42 
阿炯
一、mysqldump在备份数据库时锁表的问题
二、mysqldump对timestamp类型字段的转换问题
三、mysqldump时跳过某些表(skip some tables)
--------------------------------------------------------------
一、mysqldump在备份数据库时锁表的问题
在使用'mysqldump'工具在备份库表时，会对库表进行锁定，这会对业务的运行产生一定的影响；对于锁表的过程及解决方法，作者也谈谈自己的认识。
缓解锁定的方法：
1、Use --skip-lock-tables mysqldump option. Your tables won't be locked, but your backup might be not consistent (depends on your schema: if your database transactions touch several tables at once, and one table is already backed up and other is not. For example: two tables: customers and orders. If customers is backed up first, and then new customer/order pair is inserted, you might end up with order without customer in your backup).
This problem is primary reason why mysqldump locks all tables by default. If this issue does not apply to you, skipping the table locking in mysql is the most simple solution.
会影响数据的一致性(可能比丢数据还要遭糕)，故不推荐使用这个方法。
2、Use some other backup method. For example: Flush tables with read lock, create LVM snapshot, unlock tables, mount your LVM snapshot and back up your data. This gets quickly, much more complex than simple mysqldump.
If your MySQL data is not on a LVM volume, you can create a slave MySQL server and back it up, without touching the master, so locking occurs on the slave and master is always free.
可以参考“lvm备份mysql数据库参考”，这篇文章。
锁表参数介绍
--lock-tables, -l
Lock all tables before dumping them. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB and BDB, --single-transaction is a much better option, because it does not need to lock the tables at all.
注意：这个参考仅针对当前正在备份的库。
--lock-all-tables
Locks all tables across all databases. This is achieved by taking a global read lock for the duration of the whole dump. Automatically turns --single-transaction and --lock-tables off.
To dump large tables, you should combine the --single-transaction option with --quick.
For InnoDB tables, you should use the --single-transaction option. For MyISAM there is --lock-tables.
官方关于在'dump'时有关锁表的文档：
http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_lock-tables
另外介绍'mysqldump'工具三个不常使用但很有用的选项：
--single-transaction produces a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes. Those incoming changes do not become part of the dump. That ensures the same point-in-time for all tables.
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.
--routines dumps all stored procedures and stored functions
--triggers dumps all triggers for each table that has them
--------------------------------------------------------------
二、mysqldump对timestamp类型字段的转换问题
这个现象发生在对数据有条件的转存储时会出现。
表结构如下：
CREATE TABLE `nt` (
`id` int(11) NOT NULL,
`issue_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM
> select * from nt limit 9;
+----+---------------------+
| id | issue_dt |
+----+---------------------+
| 1 | 2016-09-13 17:20:20 |
| 2 | 2016-09-13 17:20:24 |
| 3 | 2016-09-12 21:00:01 |
+----+---------------------+
开始dump：
$ mysqldump -h192.168.0.9 -udba -pyoupaswd foasite nt --where='issue_dt="2016-09-13 17:20:24"' | grep -i INSERT
返回为空，也就是说没有数据被导出。如果不加这个where条件，则是有的。
关于timestamp这个字段类型。
首先，从大小上你可以看出来，它不是个字符串，实际上是一个整型。所以当我们执行 where c="2016-09-13 17:20:24"的时候，需要将其转换为整型。这就涉及到转换规则。也就是说，对于相同的时间戳，在不同的时区显示的结果是不一样的。反过来也一样，相同的字符串，在不同的时区解释下，会得到不同的时间戳。
我们来看一下整个mysqldump的结果。在文件头部，可以看到/*!40103 SET TIME_ZONE='+00:00' */; 字样，说明mysqldump在默认情况下，是按’+00:00’(零时区)。
而mysql客户端的默认值呢：
mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
这个SYSTEM表示MySQL取操作系统的默认时区，因此是东8区。如果我们设置为与mysqldump相同时区看看。
mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)
> select * from nt limit 9;
+----+---------------------+
| id | issue_dt |
+----+---------------------+
| 1 | 2016-09-13 09:20:20 |
| 2 | 2016-09-13 09:20:24 |
| 3 | 2016-09-12 13:00:01 |
+----+---------------------+
就跟我们上面看到的全表导出的结果一样了。也就是说，这个问题是因为mysqldump强行设置了时区为零时区造成的。
解决之法
可以用 --tz-utc=0 参数去掉前面的设置时区的动作，这样用的也是默认时区。
如果这个这么好，为什么mysqldump的开发者不把--tz-utc=0作为默认行为呢，也就是说这样做有什么风险？
实际上是因为要防止跨时区导数据。假设你把中国一个机器上的数据导入到美国的一个mysql服务器上，若不显式地设置一个时区，在导入时就会出错了。因为都用系统默认的时区，相同的字符串值会得到不同的时间戳。如我们前面说的， 时间戳是以整型方式存储的。如果你确定源和目标系统时区没变，这也是没有问题的。
--------------------------------------------------------------
mysqldump时跳过某些表(skip some tables)
将某个库中table1表排除
mysqldump -u freeoa -p database --ignore-table=database.table1 > database.sql
在备份出来的文件中，会连table1的表结构都不会有的，这点需要注意到。如果想在目标库的还原时有table1的表结构(自增id也会带过去)的话，需要分成如下的两步：
1、备份整个库表的表结构
mysqldump -h <host> -u <freeoa> -p <schema> --no-data > db-structure.sql
2、在排除部分表后备份该库的数据(不再包含结构)
mysqldump -h <host> -u <freeoa> -p <schema> --no-create-info --ignore-table=schema.table1 --ignore-table=schema.table2 > db-data.sql
要排除多个库或表的写法：
mysqldump -u freeoa -p --ignore-table=db1.tbl1 --ignore-table=db2.tbl1 --databases db1 db2 >db_1_2.sql
mysqldump -u freeoa -p --ignore-table={db_test.test1,db_test.test3} db_test > db_test.sql
using --ignore-table and create an array of tables, with syntaxs like database.table:
--ignore-table={db_test.table1,db_test.table3,db_test.table4}
在新的数据库中还原的步骤：
mysql -u user -p new_db < database_structure.sql
mysql -u user -p new_db < database_data.sql
二、mysqldump对timestamp类型字段的转换问题
三、mysqldump时跳过某些表(skip some tables)
--------------------------------------------------------------
一、mysqldump在备份数据库时锁表的问题
在使用'mysqldump'工具在备份库表时，会对库表进行锁定，这会对业务的运行产生一定的影响；对于锁表的过程及解决方法，作者也谈谈自己的认识。
缓解锁定的方法：
1、Use --skip-lock-tables mysqldump option. Your tables won't be locked, but your backup might be not consistent (depends on your schema: if your database transactions touch several tables at once, and one table is already backed up and other is not. For example: two tables: customers and orders. If customers is backed up first, and then new customer/order pair is inserted, you might end up with order without customer in your backup).
This problem is primary reason why mysqldump locks all tables by default. If this issue does not apply to you, skipping the table locking in mysql is the most simple solution.
会影响数据的一致性(可能比丢数据还要遭糕)，故不推荐使用这个方法。
2、Use some other backup method. For example: Flush tables with read lock, create LVM snapshot, unlock tables, mount your LVM snapshot and back up your data. This gets quickly, much more complex than simple mysqldump.
If your MySQL data is not on a LVM volume, you can create a slave MySQL server and back it up, without touching the master, so locking occurs on the slave and master is always free.
可以参考“lvm备份mysql数据库参考”，这篇文章。
锁表参数介绍
--lock-tables, -l
Lock all tables before dumping them. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB and BDB, --single-transaction is a much better option, because it does not need to lock the tables at all.
注意：这个参考仅针对当前正在备份的库。
--lock-all-tables
Locks all tables across all databases. This is achieved by taking a global read lock for the duration of the whole dump. Automatically turns --single-transaction and --lock-tables off.
To dump large tables, you should combine the --single-transaction option with --quick.
For InnoDB tables, you should use the --single-transaction option. For MyISAM there is --lock-tables.
官方关于在'dump'时有关锁表的文档：
http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_lock-tables
另外介绍'mysqldump'工具三个不常使用但很有用的选项：
--single-transaction produces a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes. Those incoming changes do not become part of the dump. That ensures the same point-in-time for all tables.
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.
--routines dumps all stored procedures and stored functions
--triggers dumps all triggers for each table that has them
--------------------------------------------------------------
二、mysqldump对timestamp类型字段的转换问题
这个现象发生在对数据有条件的转存储时会出现。
表结构如下：
CREATE TABLE `nt` (
`id` int(11) NOT NULL,
`issue_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM
> select * from nt limit 9;
+----+---------------------+
| id | issue_dt |
+----+---------------------+
| 1 | 2016-09-13 17:20:20 |
| 2 | 2016-09-13 17:20:24 |
| 3 | 2016-09-12 21:00:01 |
+----+---------------------+
开始dump：
$ mysqldump -h192.168.0.9 -udba -pyoupaswd foasite nt --where='issue_dt="2016-09-13 17:20:24"' | grep -i INSERT
返回为空，也就是说没有数据被导出。如果不加这个where条件，则是有的。
关于timestamp这个字段类型。
首先，从大小上你可以看出来，它不是个字符串，实际上是一个整型。所以当我们执行 where c="2016-09-13 17:20:24"的时候，需要将其转换为整型。这就涉及到转换规则。也就是说，对于相同的时间戳，在不同的时区显示的结果是不一样的。反过来也一样，相同的字符串，在不同的时区解释下，会得到不同的时间戳。
我们来看一下整个mysqldump的结果。在文件头部，可以看到/*!40103 SET TIME_ZONE='+00:00' */; 字样，说明mysqldump在默认情况下，是按’+00:00’(零时区)。
而mysql客户端的默认值呢：
mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
这个SYSTEM表示MySQL取操作系统的默认时区，因此是东8区。如果我们设置为与mysqldump相同时区看看。
mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)
> select * from nt limit 9;
+----+---------------------+
| id | issue_dt |
+----+---------------------+
| 1 | 2016-09-13 09:20:20 |
| 2 | 2016-09-13 09:20:24 |
| 3 | 2016-09-12 13:00:01 |
+----+---------------------+
就跟我们上面看到的全表导出的结果一样了。也就是说，这个问题是因为mysqldump强行设置了时区为零时区造成的。
解决之法
可以用 --tz-utc=0 参数去掉前面的设置时区的动作，这样用的也是默认时区。
如果这个这么好，为什么mysqldump的开发者不把--tz-utc=0作为默认行为呢，也就是说这样做有什么风险？
实际上是因为要防止跨时区导数据。假设你把中国一个机器上的数据导入到美国的一个mysql服务器上，若不显式地设置一个时区，在导入时就会出错了。因为都用系统默认的时区，相同的字符串值会得到不同的时间戳。如我们前面说的， 时间戳是以整型方式存储的。如果你确定源和目标系统时区没变，这也是没有问题的。
--------------------------------------------------------------
mysqldump时跳过某些表(skip some tables)
将某个库中table1表排除
mysqldump -u freeoa -p database --ignore-table=database.table1 > database.sql
在备份出来的文件中，会连table1的表结构都不会有的，这点需要注意到。如果想在目标库的还原时有table1的表结构(自增id也会带过去)的话，需要分成如下的两步：
1、备份整个库表的表结构
mysqldump -h <host> -u <freeoa> -p <schema> --no-data > db-structure.sql
2、在排除部分表后备份该库的数据(不再包含结构)
mysqldump -h <host> -u <freeoa> -p <schema> --no-create-info --ignore-table=schema.table1 --ignore-table=schema.table2 > db-data.sql
要排除多个库或表的写法：
mysqldump -u freeoa -p --ignore-table=db1.tbl1 --ignore-table=db2.tbl1 --databases db1 db2 >db_1_2.sql
mysqldump -u freeoa -p --ignore-table={db_test.test1,db_test.test3} db_test > db_test.sql
using --ignore-table and create an array of tables, with syntaxs like database.table:
--ignore-table={db_test.table1,db_test.table3,db_test.table4}
在新的数据库中还原的步骤：
mysql -u user -p new_db < database_structure.sql
mysql -u user -p new_db < database_data.sql