mysqldump備份失敗案例

一、問題描述

在一次備份過程中使用mysqldump以單表時間資料類型進行過濾備份，到恢複資料時發現mysqldump備份出來的sql檔案中并沒有資料。

二、問題複現與排查

2.1 問題複現

為了更好的問題排查，在表中建立了timestamp和datetime兩列進行對比驗證，表結構和資料如下：

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table                                           
+-------+-------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time2` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+----+---------------------+---------------------+
| id | time1               | time2               |
+----+---------------------+---------------------+
|  1 | 2019-07-08 13:10:13 | 2019-07-08 13:10:13 |
|  2 | 2019-07-08 13:10:14 | 2019-07-08 13:10:14 |
|  3 | 2019-07-08 13:10:15 | 2019-07-08 13:10:15 |
|  4 | 2019-07-08 13:10:15 | 2019-07-08 13:10:15 |
|  5 | 2019-07-08 13:10:15 | 2019-07-08 13:10:15 |
|  6 | 2019-07-08 13:10:16 | 2019-07-08 13:10:16 |
|  7 | 2019-07-08 13:10:16 | 2019-07-08 13:10:16 |
|  8 | 2019-07-08 13:10:18 | 2019-07-08 13:10:18 |
|  9 | 2019-07-08 13:12:48 | 2019-07-08 13:12:48 |
| 10 | 2019-07-08 13:12:49 | 2019-07-08 13:12:49 |
| 11 | 2019-07-08 13:12:49 | 2019-07-08 13:12:49 |
| 12 | 2019-07-08 13:12:54 | 2019-07-08 13:12:54 |
| 13 | 2019-07-08 13:13:13 | 2019-07-08 13:13:13 |
| 14 | 2019-07-08 13:13:16 | 2019-07-08 13:13:16 |
| 15 | 2019-07-08 13:13:17 | 2019-07-08 13:13:17 |
| 16 | 2019-07-08 13:13:19 | 2019-07-08 13:13:19 |
| 17 | 2019-07-08 13:13:21 | 2019-07-08 13:13:21 |
| 18 | 2019-07-08 13:13:22 | 2019-07-08 13:13:22 |
| 19 | 2019-07-08 13:13:23 | 2019-07-08 13:13:23 |
| 20 | 2019-07-08 13:13:25 | 2019-07-08 13:13:25 |
+----+---------------------+---------------------+
20 rows in set (0.00 sec)

使用mysqldump對timestamp和datetime兩種資料類型進行過濾備份，備份語句和備份結果如下：

使用timestamp列作為過濾條件的備份語句：

mysqldump -uroot -p -S /usr/local/mysql/data/sock/mysql.sock --default-character-set=utf8 -q --master-data=2 --single-transaction --triggers --routines --events --databases boke --tables t1 --where "time1>'2019-07-08 13:12:48'">t1_time1.sql

使用datetime列作為過濾條件的備份語句：

mysqldump -uroot -p -S /usr/local/mysql/data/sock/mysql.sock --default-character-set=utf8 -q --master-data=2 --single-transaction --triggers --routines --events --databases boke --tables t1 --where "time2>'2019-07-08 13:12:48'">t1_time2.sql

使用timestamp列作為過濾條件的備份結果：

···
/*!40103 SET TIME_ZONE='+00:00' */;
···
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time2` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
-- WHERE:  time1>'2019-07-08 13:12:48'
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

使用datetime列作為過濾條件的備份結果：

···
/*!40103 SET TIME_ZONE='+00:00' */;
···
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time2` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t1`
--
-- WHERE:  time2>'2019-07-08 13:12:48'

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (10,'2019-07-08 05:12:49','2019-07-08 13:12:49'),(11,'2019-07-08 05:12:49','2019-07-08 13:12:49'),(12,'2019-07-08 05:12:54','2019-07-08 13:12:54'),(13,'2019-07-08 05:13:13','2019-07-08 13:13:13'),(14,'2019-07-08 05:13:16','2019-07-08 13:13:16'),(15,'2019-07-08 05:13:17','2019-07-08 13:13:17'),(16,'2019-07-08 05:13:19','2019-07-08 13:13:19'),(17,'2019-07-08 05:13:21','2019-07-08 13:13:21'),(18,'2019-07-08 05:13:22','2019-07-08 13:13:22'),(19,'2019-07-08 05:13:23','2019-07-08 13:13:23'),(20,'2019-07-08 05:13:25','2019-07-08 13:13:25');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

進行資料恢複：

mysql -S /usr/local/mysql/data/sock/mysql.sock boke<t1_time2.sql 
 mysql -S /usr/local/mysql/data/sock/mysql.sock -e "select * from boke.t1;"
+----+---------------------+---------------------+
| id | time1               | time2               |
+----+---------------------+---------------------+
| 10 | 2019-07-08 13:12:49 | 2019-07-08 13:12:49 |
| 11 | 2019-07-08 13:12:49 | 2019-07-08 13:12:49 |
| 12 | 2019-07-08 13:12:54 | 2019-07-08 13:12:54 |
| 13 | 2019-07-08 13:13:13 | 2019-07-08 13:13:13 |
| 14 | 2019-07-08 13:13:16 | 2019-07-08 13:13:16 |
| 15 | 2019-07-08 13:13:17 | 2019-07-08 13:13:17 |
| 16 | 2019-07-08 13:13:19 | 2019-07-08 13:13:19 |
| 17 | 2019-07-08 13:13:21 | 2019-07-08 13:13:21 |
| 18 | 2019-07-08 13:13:22 | 2019-07-08 13:13:22 |
| 19 | 2019-07-08 13:13:23 | 2019-07-08 13:13:23 |
| 20 | 2019-07-08 13:13:25 | 2019-07-08 13:13:25 |
+----+---------------------+---------------------+

2.2 問題分析

由上文的備份結果可知，在相同條件下，使用timestamp列作為過濾條件進行備份時，備份檔案中并沒有備份資料；在使用datetime列作為過濾條件進行備份的情況下，可以發現雖然是有資料的，但是'time1'和'time2'兩列的時間不一緻。

看到上面這個現象，這個時候我們應該就明白了，問題出在這兩個時間資料類型上面。對于timestamp這種資料類型，會把用戶端插入的時間從目前時區轉化為UTC（世界标準時間）進行存儲。查詢時，将其又轉化為用戶端目前時區進行傳回。而對于datetime這種資料類型，不會做任何改變，就是原樣輸入和輸出。也就是說timestamp資料類型的記錄導出會以utc時間格式導出，導入庫中自動由UTC格式轉為系統預設時區，是以看到導出檔案timestamp内容和實際存儲的不相符。同時也可以明白為什麼使用time1列作為過濾條件為什麼備份檔案中沒有資料。因為timestamp資料類型在資料庫中是以utc時間格式存儲，也就是将時間回退八個小時，但是過濾條件依舊是原來的時間。這就會導緻備份時過濾出的結果并不是原本需要的，導緻備份“失敗”。

三、解決辦法

在使用mysqldump備份時，加入參數--skip-tz-utc用于禁止timestamp時區轉換。

mysqldump -S /usr/local/mysql/data/sock/mysql.sock --default-character-set=utf8 -q --master-data=2 --single-transaction --triggers --routines --events --skip-tz-utc --databases boke --tables t1 --where "time1>'2019-07-08 13:12:48'">t1_time1_2.sql

DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time2` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t1`
--
-- WHERE:  time1>'2019-07-08 13:12:48'

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (10,'2019-07-08 13:12:49','2019-07-08 13:12:49'),(11,'2019-07-08 13:12:49','2019-07-08 13:12:49'),(12,'2019-07-08 13:12:54','2019-07-08 13:12:54'),(13,'2019-07-08 13:13:13','2019-07-08 13:13:13'),(14,'2019-07-08 13:13:16','2019-07-08 13:13:16'),(15,'2019-07-08 13:13:17','2019-07-08 13:13:17'),(16,'2019-07-08 13:13:19','2019-07-08 13:13:19'),(17,'2019-07-08 13:13:21','2019-07-08 13:13:21'),(18,'2019-07-08 13:13:22','2019-07-08 13:13:22'),(19,'2019-07-08 13:13:23','2019-07-08 13:13:23'),(20,'2019-07-08 13:13:25','2019-07-08 13:13:25');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

mysql -S /usr/local/mysql/data/sock/mysql.sock -e "drop table boke.t1;"
 mysql -S /usr/local/mysql/data/sock/mysql.sock -e "select * from  boke.t1;"
 ERROR 1146 (42S02) at line 1: Table 'boke.t1' doesn't exist
 mysql -S /usr/local/mysql/data/sock/mysql.sock boke< t1_time1_2.sql 
 mysql -S /usr/local/mysql/data/sock/mysql.sock -e "select * from  boke.t1;"
+----+---------------------+---------------------+
| id | time1               | time2               |
+----+---------------------+---------------------+
| 10 | 2019-07-08 13:12:49 | 2019-07-08 13:12:49 |
| 11 | 2019-07-08 13:12:49 | 2019-07-08 13:12:49 |
| 12 | 2019-07-08 13:12:54 | 2019-07-08 13:12:54 |
| 13 | 2019-07-08 13:13:13 | 2019-07-08 13:13:13 |
| 14 | 2019-07-08 13:13:16 | 2019-07-08 13:13:16 |
| 15 | 2019-07-08 13:13:17 | 2019-07-08 13:13:17 |
| 16 | 2019-07-08 13:13:19 | 2019-07-08 13:13:19 |
| 17 | 2019-07-08 13:13:21 | 2019-07-08 13:13:21 |
| 18 | 2019-07-08 13:13:22 | 2019-07-08 13:13:22 |
| 19 | 2019-07-08 13:13:23 | 2019-07-08 13:13:23 |
| 20 | 2019-07-08 13:13:25 | 2019-07-08 13:13:25 |
+----+---------------------+---------------------+

備份恢複成功！這個時候一定會有人問恢複時不會多加八個小時麼？因為使用--skip-tz-utc選項後，導出檔案中開頭不會設定的/!40103 SET TIME_ZONE='+00:00' /。

四、總結

在使用mysqldump備份且過濾條件為timestamp資料類型時，需要指定--skip-tz-utc選項，否則可能會導緻備份資料異常。

備份後一定要驗證備份檔案的有效性！

mysqldump備份失敗案例

一、問題描述

二、問題複現與排查

2.1 問題複現

2.2 問題分析

三、解決辦法

四、總結

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述