官網給出的說法是這樣: --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 /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-- -- 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 /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER S
USE `test`;
-- -- Table structure for table `account_for_withdraw` --
DROP TABLE IF EXISTS `account_for_withdraw`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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后的腳本的確更緊湊了...少了很多注釋... 最開始我以為是好事,畢竟這些注釋對一相同環境來說,沒什么大的影響..可后來在線上一次操作,導致了大問題產生... 這里,只記錄我這次操作失誤有關的參數...其他的參數,另請參考官網. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; .......(中間建表啊,插入sql啊忽略) /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; 我的失誤就是由這三行造成的....在加了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 /*!32312 IF NOT EXISTS*/ `test1` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test1`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; 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行參數: /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; .......(中間建表啊,插入sql啊忽略) /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
第一行: /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; 是表示將現在mysql的時區重新命名,相當于備份當前使用的時區(就這么理解吧,小學語文是體育老師教的).重命名的目的是,在后面還需要用到,后面再講; 第二行: /*!40103 SET TIME_ZONE='+00:00' */; 設置當前會話的時區為0時區,不多做解釋,這就是為什么在dump出來的時候,timestamp時間會少了8個小時的原因. 前面兩行都是在dump文件最開始的時候就定義的...這一抬頭就能看到的... 第三行,/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; 是在文件的尾端,基本上就是在最后一張表的unlock table table_name;后面. 這一行參數的目的就是將當前會話修改成原來的時區....
