返回顶部
首页 > 资讯 > 数据库 >mysqldump的一个大失误.关于--compact参数使用不当的教训...记录...备用...
  • 758
分享到

mysqldump的一个大失误.关于--compact参数使用不当的教训...记录...备用...

2024-04-02 19:04:59 758人浏览 独家记忆
摘要

Mysqldump 的一个参数 --compact 官网给出的说法是这样: --compact Produce more compact output. This option enables the --

Mysqldump 的一个参数 --compact
官网给出的说法是这样:
--compact
Produce more compact output. This option enables the --skip-add-drop-table, --skip-addlocks, --skip-comments, --skip-disable-keys, and --skip-set-charset options.
大概意思就是让导出的脚本里面取消一些注释和不必要的sql.
下面这这几行就是加了--compact参数后的抬头几行....
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000503', MASTER_LOG_POS=107;                   
                                                                                              
CREATE DATABASE `test` ;
                                                                                              
USE `test`;                                                                           
;                                
;                                                  
CREATE TABLE `account_for_withdraw` (                                                         
  `account_id` varchar(30) NOT NULL COMMENT '资金流水号',                                     
  `withdraw_id` varchar(30) DEFAULT NULL COMMENT '提现流水号',                                
  `biz_no` varchar(20) NOT NULL COMMENT '交易流水号',                                         
  `withdraw_time` int(11) DEFAULT NULL COMMENT '延迟提现时间(每天定时任务递减)',            

下面在来比较下不加--compact导出后的脚本抬头几行:
-- MySQL dump 10.13  Distrib 5.5.33, for linux (x86_64)                            
--                                                                                 
-- Host: localhost    Database: test
-- ------------------------------------------------------                          
-- Server version       5.5.33-log                                                 
                                                                                    
;                  
;                
;                  
;                                                        
;                                        
;                                                
;               
;
;        
;                           
                                                                                    
--                                                                                 
-- Position to start replication or point-in-time recovery from                    
--                                                                                 
                                                                                    
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000508', MASTER_LOG_POS=107;        
                                                                                    
--                                                                                 
-- Current Database: `test`                                                
--                                                                                 
                                                                                    
CREATE DATABASE `test` ;                     
;                                       
CREATE TABLE `account_for_withdraw` (                                              
   `account_id` varchar(30) NOT NULL COMMENT '资金流水号',                          
   `withdraw_id` varchar(30) DEFAULT NULL COMMENT '提现流水号',                     
   `biz_no` varchar(20) NOT NULL COMMENT '交易流水号',                              
   `withdraw_time` int(11) DEFAULT NULL COMMENT '延迟提现时间(每天定时任务递减)', 


两相比较,就可以看出,加了compact后的脚本的确更紧凑了...少了很多注释...
最开始我以为是好事,毕竟这些注释对一相同环境来说,没什么大的影响..可后来在线上一次操作,导致了大问题产生...
这里,只记录我这次操作失误有关的参数...其他的参数,另请参考官网.
;                                        
;
  .......(中间建表啊,插入sql啊忽略)
;       
我的失误就是由这三行造成的....在加了compact参数后,是没有这三行参数的...
失误是什么勒? 时间...时间相差8小时....都知道了吧....刚好8个时区...我们是北京时间...
先不说原因和问题,,先做一个测试:
                                                                                     
mysql> desc test;                                                                    
+-------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type        | Null | Key | Default           | Extra                       |
+-------+-------------+------+-----+-------------------+-----------------------------+
| id    | int(11)     | YES  |     | NULL              |                             |
| txt   | varchar(20) | YES  |     | NULL              |                             |
| time  | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| time2 | datetime    | YES  |     | NULL              |                             |
+-------+-------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)         

mysql> select * from test;
+------+------+---------------------+---------------------+
| id   | txt  | time                | time2               |
+------+------+---------------------+---------------------+
|    1 | test | 2015-07-09 10:14:47 | 2015-07-09 10:14:47 |
|    2 | test | 2015-07-09 10:14:50 | 2015-07-09 10:14:50 |
|    3 | test | 2015-07-09 10:14:53 | 2015-07-09 10:14:53 |
|    4 | test | 2015-07-09 10:14:56 | 2015-07-09 10:14:56 |
|    5 | test | 2015-07-09 10:14:59 | 2015-07-09 10:14:59 |
+------+------+---------------------+---------------------+
5 rows in set (0.00 sec)

[root@localhost ~]# mysqldump --compact --database test1 >test1.sql
Warning: Using unique option prefix database instead of databases is deprecated  and will be removed in a future release. Please use the full name  instead.
[root@localhost ~]# more test1.sql

CREATE DATABASE `test1` ;

USE `test1`;
;
;
CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `txt` varchar(20) DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time2` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
;
INSERT INTO `test` VALUES (1,'test','2015-07-09 02:14:47','2015-07-09 10:14:47'),(2,'test','2015-07-09 02:14:50','2015-07-09 10:14:50'),(3,'test','2015-07-09 02:14:53',
'2015-07-09 10:14:53'),(4,'test','2015-07-09 02:14:56','2015-07-09  10:14:56'),(5,'test','2015-07-09 02:14:59','2015-07-09  10:14:59');                                                   

可以看到,mysqldump出来的数据,在脚本里面只要是timestamp类型的时间,都是采用的0时区.
这就是因为在dump之初,mysql就已经做了设置,现在来讲讲,刚开始提到的3行参数:
;                                        
;
  .......(中间建表啊,插入sql啊忽略)
;    

第一行: ;  是表示将现在mysql的时区重新命名,相当于备份当前使用的时区(就这么理解吧,小学语文是体育老师教的).重命名的目的是,在后面还需要用到,后面再讲;
第二行: ; 设置当前会话的时区为0时区,不多做解释,这就是为什么在dump出来的时候,timestamp时间会少了8个小时的原因.
前面两行都是在dump文件最开始的时候就定义的...这一抬头就能看到的...
第三行,; 是在文件的尾端,基本上就是在最后一张表的unlock table table_name;后面.
这一行参数的目的就是将当前会话修改成原来的时区....

这么解释下来..就清楚明了了...
我在线上做的时候,加了compact,导致timestamp类型的时间被改成了0时区了..导致数据混乱...
血的教训啊....哎....

有位大牛跟我说了句:官方给的默认信息,是有一定道理才给的,在自己不是非常清楚的情况下,不要随便修改官方给的默认信息....
很有道理啊....继续干巴爹!
您可能感兴趣的文档:

--结束END--

本文标题: mysqldump的一个大失误.关于--compact参数使用不当的教训...记录...备用...

本文链接: https://lsjlt.com/news/46123.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
软考高级职称资格查询
编程网,编程工程师的家园,是目前国内优秀的开源技术社区之一,形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容,为IT开发者提供了一个发现、使用、并交流开源技术的平台。
  • 官方手机版

  • 微信公众号

  • 商务合作