2018-03-04

Mysql - 如何修改 partition 並還原資料

在測試環境中我們想要置換 partition 的分區方式，而我們不想要遺失測試資料，所以需要備份 partition table 的資料。因為要修改的資料欄位很多，我不想要直接使用 alter table 的方式來修改 partition 分區，取而代之的是，我想要用 Migration 的方式來管理，所以會採用先備份 -> 修改 -> 接著再還原的方式。

深入了解 mysqldump

一般我們在備份還原的時候會使用 Mysqldump，並使用還原指令來還原。但是因為這次我需要回復的資料已經經過 Partition 分區了。用一般的回復方式很可能會失敗。秉持的實驗精神，我就來看看 mysqldump 到底是怎麼運作的。輸入指令備份資料庫：

1
mysqldump -u username -p your_database > dump.sql

產生出來的檔案我們打開來看:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
# dump.sql
-- MySQL dump 10.13  Distrib 5.7.20, for osx10.11 (x86_64)
--
-- Host: localhost    Database: log_sharding
-- ------------------------------------------------------
-- Server version	5.7.20

/*!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 */;

--
-- Table structure for table `alert_logs`
--

DROP TABLE IF EXISTS `alert_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `alert_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alert_id` int(11) DEFAULT NULL,
  `alert_code` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `alert_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `alert_time` datetime DEFAULT NULL,
  `alert_status` tinyint(2) DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT '2055-06-01 00:00:00',
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`created_at`),
) ENGINE=InnoDB AUTO_INCREMENT=4899 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY RANGE  COLUMNS(alert_time)
(PARTITION p001 VALUES LESS THAN ('2018-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p002 VALUES LESS THAN ('2018-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION p003 VALUES LESS THAN ('2018-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION p004 VALUES LESS THAN ('2018-04-01 00:00:00') ENGINE = InnoDB,
 .
 .
 .
 .
 PARTITION p451 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Dumping data for table `alert_logs`
--

LOCK TABLES `alert_logs` WRITE;
/*!40000 ALTER TABLE `alert_logs` DISABLE KEYS */;
# This is fake data
INSERT INTO `alert_logs` VALUES (16,4),(49,12),(9,14),(21,17),(11,57),(71,63),(270,91),(86,180),(125,193),(99,201),(189,385),(271,390),(225,415),(305,484),(251,543),(309,545),(257,579),(258,581),(260,585),(261,588),(279,667);
/*!40000 ALTER TABLE `alert_logs` ENABLE KEYS */;
UNLOCK TABLES;

這不就是 SQL 的指令嘛？ 邏輯如下:

  1. 如果 alert_logs table 已經存在，就刪除掉並使用 Dump File 內的備份指令重新建立 alert_logs table
  2. lock 住整個 table 並加入資料。

從這些步驟我們可以觀察到即使 db 內已經存在 alert_logs table 了，使用 dump.sql 還原時仍然會先刪除 alert_logs table，再使用 dump.sql 中的 schema 來重建 table。

知道這些情報之後 partition 的 backup 與 restore 有了頭緒。

Partition backup and restore

首先備份原先的 partition

1
mysqldump -h 'your-my-sql-ip' -u your_user_name -p your_database_name table_name_1 table_name_2 --no-create-info  --ssl-ca=your_ssl.pem > dump.sql

關鍵在於: 需要加上 --no-create-info 產生的 dump.sql 不會把本來的 table 刪除，直接 insert 資料。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# dump.sql
-- MySQL dump 10.13  Distrib 5.7.20, for osx10.11 (x86_64)
--
-- Host: localhost    Database: pro_devel
-- ------------------------------------------------------
-- Server version	5.7.20

/*!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 */;

--alert_logs
-- Dumping data for table `alert_logs`
--

LOCK TABLES `alert_logs` WRITE;
/*!40000 ALTER TABLE `alert_logs` DISABLE KEYS */;
INSERT INTO `alert_logs` VALUES (16,4),(49,12),(9,14),(21,17),(11,57),(71,63),(270,91),(86,180),(125,193),(99,201),(189,385),(271,390),(225,415),(305,484),(251,543),(309,545),(257,579),(258,581),(260,585),(261,588),(279,667);
/*!40000 ALTER TABLE `alert_logs` ENABLE KEYS */;
UNLOCK TABLES;

備份完資料後我們將原有的 database 刪除

1
DROP TABLE alert_logs;

用新的 create partition 方法建立 table，將本來用 alert_time 做分區，改成用 created_at 做分區。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE `alert_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alert_id` int(11) DEFAULT NULL,
  `alert_code` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `alert_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `alert_time` datetime DEFAULT NULL,
  `alert_status` tinyint(2) DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT '2055-06-01 00:00:00',
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`created_at`),
) ENGINE=InnoDB AUTO_INCREMENT=4899 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY RANGE  COLUMNS(created_at)
(PARTITION p001 VALUES LESS THAN ('2018-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p002 VALUES LESS THAN ('2018-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION p003 VALUES LESS THAN ('2018-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION p004 VALUES LESS THAN ('2018-04-01 00:00:00') ENGINE = InnoDB,
 .
 .
 .
 .
 PARTITION p451 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;
/*!40101 SET character_set_client = @saved_cs_client */;

最後將資料還原

1
mysql -u username -p database_name < dump.sql

檢視一下目前的 table

1
SHOW CREATE TABLE alert_logs;

確認結果是否正確
檢視一下目前的 table

1
SHOW CREATE TABLE alert_logs;

輸出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE `alert_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alert_id` int(11) DEFAULT NULL,
  `alert_code` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `alert_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `alert_time` datetime DEFAULT NULL,
  `alert_status` tinyint(2) DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`created_at`),
) ENGINE=InnoDB AUTO_INCREMENT=4899 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY RANGE  COLUMNS(created_at)
(PARTITION p001 VALUES LESS THAN ('2018-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p002 VALUES LESS THAN ('2018-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION p003 VALUES LESS THAN ('2018-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION p004 VALUES LESS THAN ('2018-04-01 00:00:00') ENGINE = InnoDB,
 .
 .
 .
 .
 PARTITION p451 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;
/*!40101 SET character_set_client = @saved_cs_client */;

確認建立 table 的資料無誤後，
進入 Rails console 試試看 AlertLog 是否可以正常存取，
這樣就完成了整個 partition 轉換並還原的流程。

地雷 - created_at

需要特別注意的是 - partition 中 為 key 的值一定必須是 NOT NULL，且不要設 DEFAULT VALUE，否則 Rails 不會幫你自動填入 created_at。

Rails Migration

如果你使用的是 Rails Migration，步驟是:

  1. backup entire database
  2. dump the partition database
  3. rake db:rollback
  4. modify migration of partition
  5. rake db:migrate
  6. restore partition database

參考資料

DBAStuff: Mysql Dump and partitions

#

歡迎訂閱，獲取最新資訊、教學文章，一起變強。

評論

最新文章

彙整

標籤

AWS2
ActiveRecord2
Agile4
Ajax3
Algorithm11
Atom5
Bootstrap3
CI/CD1
CORS1
CSS6
Coockie1
Course1
Crawler2
Cronjob1
Database2
Design Pattern2
DevOps1
Devops6
Docker1
DroneCI1
EDX Linux Foundation12
EFK6
ELK1
Elasticsearch1
Flask1
Fluentd6
Git1
Github2
Golang2
HTML14
Hacker1
Harbor1
Kanban2
Kubernetes2
Learning2
Linux13
Metaprogramming2
Meteor2
Minikube2
Mysql1
OSX4
Open Source1
PSD to HTML2
Python1
Rails17
Redmine1
Ruby16
Ruby Bit7
S32
SCSS3
Security1
Session1
Sinatra1
Sublime2
Text Editor7
Time2
Typescript1
UML1
Unit Test1
Vim1
Web Foundation1
gem1
jQuery7
javascript24
×