Shulou(Shulou.com)06/01 Report--

A parameter of mysqldump-- compact

The statement given on the official website is as follows:

-- 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. It probably means to cancel some comments and unnecessary sql in the exported script.

The following lines are the header lines with the addition of the-compact parameter.

-- 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_ draw` (

`account_ id` varchar (30) NOT NULL COMMENT 'Capital flow number'

`withdraw_ id`varchar (30) DEFAULT NULL COMMENT 'withdrawal serial number'

`biz_ No`varchar (20) NOT NULL COMMENT 'transaction serial number'

`withdraw_ time`int (11) DEFAULT NULL COMMENT 'delayed withdrawal time (daily scheduled tasks decrease)'

The following is a comparison of the script header lines without adding-- compact export:

-- 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_ draw`

--

DROP TABLE IF EXISTS `account_for_ draw`

/ *! 40101 SET @ saved_cs_client = @ @ character_set_client * /

/ *! 40101 SET character_set_client = utf8 * /

CREATE TABLE `account_for_ draw` (

`account_ id` varchar (30) NOT NULL COMMENT 'Capital flow number'

`withdraw_ id`varchar (30) DEFAULT NULL COMMENT 'withdrawal serial number'

`biz_ No`varchar (20) NOT NULL COMMENT 'transaction serial number'

`withdraw_ time`int (11) DEFAULT NULL COMMENT 'delayed withdrawal time (daily scheduled tasks decrease)'

Comparing the two, we can see that the script after adding compact is indeed more compact. There are a lot of notes missing.

At first I thought it was a good thing. After all, these notes don't have much impact on the same environment. But then an online operation led to a big problem.

Here, only the parameters related to my operation error are recorded. For other parameters, please refer to the official website.

/ * 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /

/ * 40103 SET TIME_ZONE='+00:00' * /

. (create tables in the middle, insert sql and ignore)

/ * 40103 SET TIME_ZONE=@OLD_TIME_ZONE * /

My mistake is caused by these three lines. After adding the compact parameter, there are no these three lines of parameters.

What was the mistake? Time. The time difference is 8 hours. You know all about it. It's exactly eight time zones. We are Beijing time.

Leaving aside the causes and problems, let's do a test:

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), (2) INSERT INTO `test`, 2015-07-09 10 (14)), (2) (3) (3) (3) (3)

'2015-07-09 10 1415 53'), (4)) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (4) (5) (5) (5) (5) (5) (5)

As you can see, the data from mysqldump is in the 0 time zone as long as it is of timestamp type in the script.

This is because mysql was set up at the beginning of dump, and now let's talk about the three lines of parameters mentioned at the beginning:

/ * 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /

/ * 40103 SET TIME_ZONE='+00:00' * /

. (create tables in the middle, insert sql and ignore)

/ * 40103 SET TIME_ZONE=@OLD_TIME_ZONE * /

The first line: / *! 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /; means to rename the current mysql time zone, which is equivalent to backing up the current time zone (to understand it, primary school Chinese is taught by PE teachers). The purpose of renaming is to use it later, and we'll talk about it later.

The second line: / *! 40103 SET TIME_ZONE='+00:00' * /; set the current session's time zone to 0 without much explanation, which is why the timestamp time is 8 hours less when the dump comes out.

The first two lines are defined at the beginning of the dump file. You can see it as soon as you look up.

The third line, / *! 40103 SET TIME_ZONE=@OLD_TIME_ZONE * /; is at the end of the file, basically after the unlock table table_name; of the last table.

The purpose of this line of parameters is to modify the current session to the original time zone.

Explain it this way. It will be clear.

When I did it online, I added compact, which caused the timestamp type time to be changed to 0 time zone. Lead to data confusion.

The lesson of blood. Hey.

A Daniel said to me: the default information given by the government is only given with some reason. if you are not very clear, do not modify the default information given by the government casually.

It makes a lot of sense. Keep doing it, Baddy!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.