Mysql使用mysqldump按时间导出时的一个注意事项

在使用Mysql的mysqldump命令按时间进行数据导出时，我们可能会遇到导出数据与原库中不符或者导出数据和我们期望的数据不同的情况。

我们知道Mysql数据库导出可以使用命令mysqldump，使用方式可以通过在终端中输入mysqldump --help来查看。
简单的使用方式如下：
1. 导出整个数据库(包括数据库中的数据）
mysqldump -u username -p dbname > dbname.sql

2. 导出数据库结构（不含数据）
mysqldump -u username -p -d dbname > dbname.sql

3. 导出数据库中的某张数据表（包含数据）
mysqldump -u username -p dbname tablename > tablename.sql

4. 导出数据库中的某张数据表的表结构（不含数据）
mysqldump -u username -p -d dbname tablename > tablename.sql

我们可以用-w(或-where)指定备份条件。在使用过程中遇到一个问题，按时间导出时，导出的数据时“丢失”了一部分数据，并且时间与库中的不同。

例如我们有下面这个表：

CREATE TABLE `test_timestamp` (
`pk` int(20) unsigned NOT NULL AUTO_INCREMENT,
`record_time` timestamp NULL DEFAULT NULL COMMENT '记录日期',
PRIMARY KEY (`pk`),
KEY `time` (`record_time`)
) ENGINE=MyISAM AUTO_INCREMENT=564374307 DEFAULT CHARSET=utf8;

INSERT INTO `test_timestamp` VALUES (1,'2016-08-04 16:00:00'),(2,'2016-08-04 20:00:00'),(3,'2016-08-05 00:00:00'),(4,'2016-08-05 04:00:00');

其中record_time为timestamp类型，在数据库中查询为：

mysql> SELECT * FROM iptv.test_timestamp;
+----+-----------------------------+
| pk | record_time |
+----+-----------------------------+
| 1 | 2016-08-05 00:00:00 |
| 2 | 2016-08-05 04:00:00 |
| 3 | 2016-08-05 08:00:00 |
| 4 | 2016-08-05 12:00:00 |
+----+-----------------------------+

 

我们将时间大于等于2016-08-05 00:00:00的数据导出：

mysqldump -u root -p iptv test_timestamp -w"record_time >= '2016-08-05 00:00:00' " > /home/test.sql

 

然后将其导入到原来的库中后再查询：

mysql> SELECT * FROM iptv.test_timestamp;
+----+-----------------------------+
| pk | record_time |
+----+-----------------------------+
| 3 | 2016-08-05 08:00:00 |
| 4 | 2016-08-05 12:00:00 |
+----+-----------------------------+
2 rows in set (0.00 sec)

 

发现只有两条数据。此时，如果我们查看导出的test.sql文件，发现里面只有两条记录：
(3,'2016-08-05 00:00:00'),
(4,'2016-08-05 04:00:00')

这与以前库中的记录相差8个小时。

原因就是在导出时，如果没有加其他选项，在我们的导出文件中有这样几行：
….
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
….
也就是说，在导出时，首先会将时区time_zone设置为'+00:00'，而time_zone这个值在数据库中默认是什么呢：

mysql> show variables like '%time_zone%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+--------------------------+------------+
2 rows in set (0.00 sec)

 

是系统默认时区，查看系统默认时区：

[root@hadoop-data-1 /]# date -R
Thu, 25 Aug 2016 16:25:06 +0800

 

所有我们的在导出数据时，时间会先做转换，然后再进行查询，转换后的记录为：
(1,'2016-08-04 16:00:00'),
(2,'2016-08-04 20:00:00'),
(3,'2016-08-05 00:00:00'),
(4,'2016-08-05 04:00:00');

按照 -w"record_time >= '2016-08-05 00:00:00' "过滤后只剩下：
(3,'2016-08-05 00:00:00'),
(4,'2016-08-05 04:00:00');

所以插入后只剩下两条数据了。

我们在按时间导出数据时需要注意这个问题。解决的方式一种是在-w参数中将时间自己进行转换，另外一种方式是先将需要导出的库的时区进行设置
set time_zone='+00:00';
导出结束后再恢复：
set time_zone='SYSTEM';

同时，如果导出和导入的库所使用 的时区不同，那么导入的只是时间显示就不一样，如果把两个库时区设置为一样，数值显示就相同了。所有当同样的数据在不同数据库中显示不同时，需要先确认一下时区的问题。