mysqldump导出注意timestamp类型

最新推荐文章于 2021-05-17 22:54:09 发布
最新推荐文章于 2021-05-17 22:54:09 发布
阅读量2.1k 收藏 1
点赞数 1
分类专栏： MySQL 文章标签： MySQL mysqldump
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ashic/article/details/78445518
版权

时区

mysql> show global variables like '%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

[root@localhost recover]# date -R  
Sat, 04 Nov 2017 18:32:16 +0800

mysql> show create table fan3\G
*************************** 1. row ***************************
      Table: fan3
Create Table: CREATE TABLE `fan3` (
  `idx` bigint(20) NOT NULL AUTO_INCREMENT,
  `phone` varchar(50) NOT NULL COMMENT '手机号码/或者唯一标识',
  `system` varchar(50) DEFAULT NULL,
  `activeid` int(11) DEFAULT NULL COMMENT '所属活动',
  `prize` varchar(50) DEFAULT NULL COMMENT '获得的奖品',
  `yycode` varchar(100) DEFAULT '' COMMENT '预约送的礼包',
  `realname` varchar(50) DEFAULT NULL COMMENT '用户真实姓名',
  `address` varchar(500) DEFAULT NULL COMMENT '玩家地址',
  `idcard` varchar(30) DEFAULT NULL COMMENT '用户身份证号',
  `email` varchar(110) DEFAULT '' COMMENT '邮箱地址',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '预约时间',
  `awardtime` datetime DEFAULT NULL COMMENT '中奖时间',
  `awardcount` int(11) DEFAULT '0' COMMENT '抽奖次数',
  `is_share` tinyint(4) DEFAULT '0',
  `qq` varchar(100) DEFAULT '' COMMENT 'QQ',
  `channel` varchar(100) DEFAULT '' COMMENT '来源渠道',
  PRIMARY KEY (`idx`),
  KEY `idx_activeid` (`activeid`)
) ENGINE=InnoDB AUTO_INCREMENT=634203 DEFAULT CHARSET=utf8 COMMENT='手机预约表'
1 row in set (0.00 sec)
注意date为timestamp类型

开发误删除了一部分数据,于是我拿逻辑备份来恢复整个表到恢复环境,然后取出所需数据恢复会生产库

mysql> select count(*) from fan3 where date>='2017-10-24' and date <='2017-10-26' and activeid=37;
+----------+
| count(*) |
+----------+
|      153 |
+----------+
1 row in set (0.06 sec)
上面是开发误删除数据时指定的where条件

演示坑
下面我直接CTAS,然后myqldump导出

mysql> create table fan_select as select * from fan3 where date>='2017-10-24' and date <='2017-10-26' and activeid=37;
Query OK, 153 rows affected (0.09 sec)
Records: 153  Duplicates: 0  Warnings: 0

mysql> select min(date) from fan_select;
+---------------------+
| min(date)          |
+---------------------+
| 2017-10-24 00:03:35 |
+---------------------+
1 row in set (0.01 sec)
表中date最小时间

导出

mysqldump -pfanboshi -c --skip-add-drop-table --skip-extended-insert --single-transaction test fan_select > fan_select.sql

观察general log,发现mysqldump设置时区为'+00:00' ,而系统时区为'+08:00'
2017-11-04T10:10:47.978670Z        77 Query    /*!40100 SET @@SQL_MODE='' */
2017-11-04T10:10:47.978909Z        77 Query    /*!40103 SET TIME_ZONE='+00:00' */


[root@localhost recover]# cat fan_select.sql | grep INSERT | wc -l
153
导出行数
[root@localhost recover]# cat fan_select.sql | grep INSERT | grep 2017-10-23 | wc -l
21
注意这里我们刚才查date最小时间是2017-10-24 00:03:35, 而备份中却有23号的数据
[root@localhost recover]# cat fan_select.sql | grep INSERT | grep 2017-10-23 | awk -F')' '{print $2}' | awk -F',' '{print $11}' | sort
'2017-10-23 16:03:35'
'2017-10-23 16:14:17'
'2017-10-23 16:29:23'
'2017-10-23 16:38:22'
'2017-10-23 17:06:42'
'2017-10-23 17:10:28'
'2017-10-23 17:31:56'
'2017-10-23 17:47:26'
'2017-10-23 18:21:33'
'2017-10-23 18:44:25'
'2017-10-23 19:40:15'
'2017-10-23 20:38:30'
'2017-10-23 20:47:39'
'2017-10-23 20:57:22'
'2017-10-23 21:23:07'
'2017-10-23 22:11:36'
'2017-10-23 22:12:34'
'2017-10-23 22:13:36'
'2017-10-23 22:29:14'
'2017-10-23 23:41:34'
'2017-10-23 23:42:16'
查询发现最小的时间为'2017-10-23 16:03:35' 与 '2017-10-24 00:03:35' 差8小时

恢复

sed -i 's/fan_select/fan_load/g' fan_select.sql 

mysql> source fan_select.sql 
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
...

2017-11-04T10:13:22.989942Z        76 Query    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */
2017-11-04T10:13:22.990097Z        76 Query    /*!40103 SET TIME_ZONE='+00:00' */
观察general log发现又一次设置了时区

mysql> select count(*) from fan_load;
+----------+
| count(*) |
+----------+
|      153 |
+----------+
1 row in set (0.01 sec)

mysql> select min(date) from fan_load;
+---------------------+
| min(date)          |
+---------------------+
| 2017-10-24 00:03:35 |
+---------------------+
1 row in set (0.00 sec)
恢复出来的数据和导出前一样

结论
mysqldump导出是默认会设置时区SET TIME_ZONE='+00:00' ,会导致导出的文件中timestamp列的数据与我们预期的有些 ‘不符’ ,但是实际导入后数据是一样的,无须理会

如果我们不用CTAS 直接使用–where导出

[root@localhost recover]# mysqldump -pfanboshi -c --skip-add-drop-table --skip-extended-insert --single-transaction --skip-tz-utc test fan3 --where="date>='2017-10-24' and date <='2017-10-26' and activeid=37" > fan32.sql
使用--skip-tz-utc 禁止mysqldump设置时区

[root@localhost recover]# mysqldump -pfanboshi -c --skip-add-drop-table --skip-extended-insert --single-transaction test fan3 --where="date>='2017-10-24' and date <='2017-10-26' and activeid=37" > fan3.sql
2017-11-04T10:23:20.962390Z        80 Query    /*!40100 SET @@SQL_MODE='' */
2017-11-04T10:23:20.962488Z        80 Query    /*!40103 SET TIME_ZONE='+00:00' */
这次没使用--skip-tz-utc , 可以观察general log 设置了时区

[root@localhost recover]# cat fan3.sql | grep INSERT | grep 2017-10-23 
[root@localhost recover]# cat fan32.sql | grep INSERT | grep 2017-10-23 
两个备份中都没有2017-10-23 的数据
[root@localhost recover]# cat fan3.sql | grep INSERT | wc -l
152
[root@localhost recover]# cat fan32.sql | grep INSERT | wc -l
153
行数居然不一样

恢复

[root@localhost recover]# sed -i 's/fan3/fan3_load/g' fan3.sql 
[root@localhost recover]# sed -i 's/fan3/fan32_load/g' fan32.sql 

mysql> select count(*) from fan3_load;
+----------+
| count(*) |
+----------+
|      152 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from fan32_load;
+----------+
| count(*) |
+----------+
|      153 |
+----------+
1 row in set (0.00 sec)

mysql> select min(date) from fan32_load;
+---------------------+
| min(date)          |
+---------------------+
| 2017-10-24 00:03:35 |
+---------------------+
1 row in set (0.00 sec)

mysql> select min(date) from fan3_load;
+---------------------+
| min(date)          |
+---------------------+
| 2017-10-24 08:30:10 |
+---------------------+
1 row in set (0.00 sec)
可以看到恢复出来的数据也不对

这是为什么呢? 当不使用–skip-tz-utc 同时使用–where 包含timestamp列是,由于mysqldump设置了时区,导致原本预期的where条件也发生了变化. 所以导出的数据就已经不是我们需要的了
解决方法是
1.在–where参数中将时间自己进行转换，比如原date>='2017-10-24' and date <='2017-10-26'，应写为date>='2017-10-23 16:00:00' and date <='2017-10-25 16:00:00'，提前8小时即可.

[root@localhost recover]# mysqldump -pfanboshi -c --skip-add-drop-table --skip-extended-insert --single-transaction test fan3 --where="date>='2017-10-23 16:00:00' and date <='2017-10-25 16:00:00' and activeid=37" > fan33.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost recover]# 
[root@localhost recover]# cat fan33.sql | grep INSERT | grep 2017-10-23 | awk -F')' '{print $2}' | awk -F',' '{print $11}' | sort
'2017-10-23 16:03:35'
'2017-10-23 16:14:17'
'2017-10-23 16:29:23'
'2017-10-23 16:38:22'
'2017-10-23 17:06:42'
'2017-10-23 17:10:28'
'2017-10-23 17:31:56'
'2017-10-23 17:47:26'
'2017-10-23 18:21:33'
'2017-10-23 18:44:25'
'2017-10-23 19:40:15'
'2017-10-23 20:38:30'
'2017-10-23 20:47:39'
'2017-10-23 20:57:22'
'2017-10-23 21:23:07'
'2017-10-23 22:11:36'
'2017-10-23 22:12:34'
'2017-10-23 22:13:36'
'2017-10-23 22:29:14'
'2017-10-23 23:41:34'
'2017-10-23 23:42:16'

[root@localhost recover]# sed -i 's/fan3/fan33_load/g' fan33.sql 

mysql> source fan33.sql
..

mysql> select count(*) from fan33_load;
+----------+
| count(*) |
+----------+
|      153 |
+----------+
1 row in set (0.00 sec)

mysql> select min(date) from fan33_load;
+---------------------+
| min(date)          |
+---------------------+
| 2017-10-24 00:03:35 |
+---------------------+
1 row in set (0.00 sec)

2.使用--skip-tz-utc

参考
http://blog.csdn.net/wbb_1216/article/details/56008947

Fan_-_
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL数据库使用mysqldump导出数据详解
09-10
mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。接下来通过本文给大家介绍MySQL数据库使用mysqldump导出数据详解，需要的朋友一起学习吧
使用mysqldump 导出 含有timestamp类型的表，应注意--skip-tz-utc
流子的专栏
01-07 2842
Mysql，原和目标机器均为东八区时区 准备将符合下面条件的数据从一个服务器导到另一个服务器中： create_time 其中 create_time 为timestamp ，使用下面的语句导出 mysqldump -uroot -h127.0.0.1 -P3307 -p dbname tablename --where="create_time tabname.sql
mysqldump的tz-utc选项引发的MySQL时区问题
测试0901-1
12-17 1178
背景 在使用mysqldump备份过滤timestamp字段数据时，查看就按导出的文本文件，发现数据与在server端查看的数据不一致 环境 操作系统 centos7.4 MySQL5.7.24 [root@node1 ~]# date Mon Dec 17 11:21:14 CST 2018 [root@node1 ~]# date -R Mon, ...
mysql 使用timestamp浅析
37471180的博客
05-17 267
Note If set to SYSTEM, every MySQL function call that requires a time zone calculation makes a system library call to determine the current system time zone. This call may be protected by a global mutex, resulting in contention.
Mysql中TimeStamp、DateTime、Date关于时区(TimeZone)的问题
得未曾有 心净踊跃
03-21 2万+
存储时间的几种方式 一般情况下：数据库可以这样存储时间： bigint(存毫秒数Long) 存储时间戳，然后new Date(long date)转化为时间 DateTime TimeStamp时间戳 阅读官方文档 1、DATE, DATETIME, TIMESTAMP三者的区别： 首先说一下三者的不同之处： The DATE type is used for values with ...
MySQL关于timestamp和mysqldump的一个“bug”
追风刀·丁奇
12-14 120
复现 来源于一个同事在做数据转储碰到的的问题，简化如下： 1、建表 drop table if exists tb; CREATE TABLE tb (   c timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=gbk; insert into tb values(now(...
使用MySQL MySqldump命令导出数据时的注意事项
09-10
主要介绍了使用MySQL MySqldump命令导出数据时的注意事项,很实用的经验总结,需要的朋友可以参考下
详谈mysqldump数据导出的问题
09-09
下面小编就为大家带来一篇详谈mysqldump数据导出的问题。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧
详解 linux mysqldump 导出数据库、数据、表结构
01-20
详解 linux mysqldump 导出数据库、数据、表结构 导出完整的数据库备份： mysqldump -h127.0.0.1 -P3306 -uroot -ppassword --add-locks -q dbname > dbname.sql 说明：–add-locks:导出过程中锁定表，完成后回解锁...
mysql的时间戳_MySQL时间戳（Timestamp）函数
weixin_42297904的博客
01-18 1万+
1. MySQL 获得当前时间戳函数：current_timestamp, current_timestamp()mysql> select current_timestamp, current_timestamp();+———————+———————+| current_timestamp | current_timestamp() |+———————+———————+| 2008-08-...
mysql之TIMESTAMP（时间戳）用法详解
12-15
一、TIMESTAMP的变体 TIMESTAMP时间戳在创建的时候可以有多重不同的特性，如： 1.在创建新记录和修改现有记录的时候都对这个数据列刷新： TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 2.在创建新记录的时候把这个字段设置为当前时间，但以后修改时，不再刷新它： TIMESTAMP DEFAULT CURRENT_TIMESTAMP 3.在创建新记录的时候把这个字段设置为0，以后修改时刷新它： TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 4.在创建新记录的时候把
MySQL timestamp的类型与时区实例详解
09-09
主要介绍了 MySQL timestamp的类型与时区实例详解的相关资料,需要的朋友可以参考下
MySQL常用时间函数
Hale的博客
03-01 472
本文转自：http://www.2cto.com/database/201307/230943.html MySQL常用时间函数 1.1 获得当前日期+时间（date + time）函数：now()   1.2 获得当前日期+时间（date + time）函数：sysdate() sysdate() 日期时间函数跟 now() 类似，不同之处在
mysqldump导出 timestamp类型数据 时区偏差8小时
weixin_33856370的博客
11-14 427
timestamp数据类型导出时会有时区问题，导出机器默认为东八区时区，导出时的TIME_ZONE='+00:00' 这样导出的数据和表里看到的差了8小时。如果想在数据导出时不默认进行时区的转换，可以在导出语句中增加参数--skip-tz-utc，这样导出的timestamp数据和在表里看到的时候是一样的栗子：mysqldump -uroot -p dbname ...
Mysql中timestamp用法详解
热门推荐
hyfsx的博客
02-25 3万+
Mysql中timestamp用法详解前言：时间戳（timestamp） 前言：时间戳（timestamp） 前言：时间戳（timestamp），一个能表示一份数据在某个特定时间之前已经存在的、 完整的、 可验证的数据,通常是一个字符序列，唯一地标识某一刻的时间。使用数字签名技术产生的数据， 签名的对象包括了原始文件信息、 签名参数、 签名时间等信息。广泛的运用在知识产权保护、 合同签字、 金融帐务、 电子报价投标、 股票交易等方面。 一、MySQL中表示当前时间的方法： CURRENT_TIMESTAM
mysqldump导出数据时忽略时区
xiongyk的博客
06-28 1998
mysqldump忽略时区
node-v16.12.0-darwin-x64.tar.xz
最新发布
04-23
Node.js，简称Node，是一个开源且跨平台的JavaScript运行时环境，它允许在浏览器外运行JavaScript代码。Node.js于2009年由Ryan Dahl创立，旨在创建高性能的Web服务器和网络应用程序。它基于Google Chrome的V8 JavaScript引擎，可以在Windows、Linux、Unix、Mac OS X等操作系统上运行。 Node.js的特点之一是事件驱动和非阻塞I/O模型，这使得它非常适合处理大量并发连接，从而在构建实时应用程序如在线游戏、聊天应用以及实时通讯服务时表现卓越。此外，Node.js使用了模块化的架构，通过npm（Node package manager，Node包管理器）,社区成员可以共享和复用代码，极大地促进了Node.js生态系统的发展和扩张。 Node.js不仅用于服务器端开发。随着技术的发展，它也被用于构建工具链、开发桌面应用程序、物联网设备等。Node.js能够处理文件系统、操作数据库、处理网络请求等，因此，开发者可以用JavaScript编写全栈应用程序，这一点大大提高了开发效率和便捷性。 在实践中，许多大型企业和组织已经采用Node.js作为其Web应用程序的开发平台，如Netflix、PayPal和Walmart等。它们利用Node.js提高了应用性能，简化了开发流程，并且能更快地响应市场需求。
mysqldump导出表数据
04-04
mysqldump是MySQL数据库提供的一个命令行工具，用于导出数据库或表的数据。它可以将数据库或表的结构和数据以SQL语句的形式保存到一个文件中，方便备份和恢复数据。 使用mysqldump导出表数据的基本语法如下： ``` ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值