知らないかもしれないmysqldumpパラメータ

前回の記事で、mysqldump バックアップファイルに記録されるタイムスタンプデータは UTC タイムゾーンに基づいていることを説明しました。単一のデータベースまたはテーブルを選択して復元する場合は、タイムゾーンの違いに注意してください。その後、再度ドキュメントを確認したところ、tz-utc と skip-tz-utc パラメータがこれに関係していることがわかりました。この記事では、これらのパラメータの役割について見ていきましょう。

1. tz-utc および skip-tz-utc パラメータの概要

これら 2 つのパラメータは、mysqldump バックアッププロセスで使用でき、互いに反対の意味を持ちます。名前が示すように、1 つのパラメーターはタイムスタンプを UTC タイムゾーンに変更し、もう 1 つはタイムゾーンの変更をスキップします。

mysql サーバーで mysqldump --help コマンドを実行すると、次の段落が表示されます。

[root@host ~]# mysqldump --help
mysqldump Ver 10.13 Distrib 5.7.23、Linux (x86_64) 用
Copyright (c) 2000, 2018, Oracle およびその関連会社。無断複写・転載を禁じます。
...多くのコンテンツを省略 --tz-utc ダンプの先頭に SET TIME_ZONE='+00:00' を設定して、
           サーバーが異なる時刻のデータを持っている場合のTIMESTAMPデータ
           ゾーンまたはデータがサーバー間で移動されている
           異なるタイムゾーン。
           (デフォルトではオンになっています。無効にするには --skip-tz-utc を使用してください。)

--tz-utc パラメータは mysqldump のデフォルトパラメータで、mysqldump エクスポートファイルの先頭にタイムゾーンを設定するための SET TIME_ZONE='+00:00' ステートメントを追加します。このタイムゾーンはグリニッジ標準時で、0 タイムゾーンです。このように、タイムスタンプフィールドがエクスポートされると、サーバーによって設定された現在のタイムゾーンで表示されるタイムスタンプの時刻値が、グリニッジ標準時で表示される時刻に変換されます。たとえば、データベースは北京タイムゾーン 8 を使用しており、mysqldump によってエクスポートされたファイルに表示されるタイムスタンプ値は、データベースクエリによって表示される時間と比較して 8 時間前になります。

--tz-utc がわかれば、--skip-tz-utc の意味は、mysqldump がデータをエクスポートするときにグリニッジ標準時を使用せず、現在の MySQL サーバーのタイムゾーンを使用してエクスポートするということです。このように、エクスポートされたデータに表示されるタイムスタンプの時間値は、テーブルでクエリされた時間値と同じになります。

2. 実験パラメータの特定の効果

このパラメータの効果をよりよく理解するために、具体的なテストを行ってみましょう。mysqldump を where 条件とともに使用してデータをバックアップできることはわかっています。タイムスタンプフィールドに基づいてデータをバックアップすると、パラメータに何らかの影響があるでしょうか?一緒に検証してみましょう:

まず、環境設定とテストデータを見てみましょう。

mysql> バージョンを選択します();
+------------+
| バージョン() |
+------------+
| 5.7.23-ログ |
+------------+
セット内の 1 行 (0.00 秒)
# タイムゾーンは北京タイムゾーン 8 です。mysql> show variables like 'time_zone'; 
+---------------+--------+
| 変数名 | 値 |
+---------------+--------+
| タイムゾーン | +08:00 |
+---------------+--------+
セット内の 1 行 (0.00 秒)

# テスト テーブルには、datetime フィールドと timestamp フィールドに 10 個のデータがあります。2 つの時刻は同じに表示されます。mysql> show create table test_tb\G
************************** 1. 行 ****************************
    テーブル: test_tb
テーブルの作成: CREATE TABLE `test_tb` (
 `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自動増分主キー',
 `stu_id` int(11) NOT NULL COMMENT '学生ID',
 `stu_name` varchar(20) デフォルト NULL コメント '学生名',
 `dt_time` 日時 NOT NULL、
 `create_time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時刻',
 主キー (`increment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='テストテーブル'
セット内の 1 行 (0.00 秒)

mysql> test_tb から * を選択します。
+--------------+--------+----------+----------------------+----------------------+
| increment_id | stu_id | stu_name | dt_time | create_time |
+--------------+--------+----------+----------------------+----------------------+
| 1 | 1001 | fgds | 2020-07-10 09:43:28 | 2020-07-10 09:43:28 |
| 2 | 1002 | fgsw | 2020-10-10 09:43:28 | 2020-10-10 09:43:28 |
| 3 | 1003 | vffg | 2020-10-10 02:00:00 | 2020-10-10 02:00:00 |
| 4 | 1004 | wdsd | 2020-10-31 23:43:28 | 2020-10-31 23:43:28 |
| 5 | 1005 | grdb | 2020-11-01 00:00:00 | 2020-11-01 00:00:00 |
| 6 | 1006 | sdfv | 2020-11-01 02:00:00 | 2020-11-01 02:00:00 |
| 7 | 1007 | fgfg | 2020-11-06 02:00:00 | 2020-11-06 02:00:00 |
| 8 | 1008 | tyth | 2020-11-10 09:43:28 | 2020-11-10 09:43:28 |
| 9 | 1009 | ewer | 2020-11-10 09:43:28 | 2020-11-10 09:43:28 |
| 10 | 1010 | エラー | 2020-11-11 15:17:03 | 2020-11-11 15:17:03 |
+--------------+--------+----------+----------------------+----------------------+

デフォルトでは、mysqldump は tz-utc をオンにします。まずはデフォルトでのバックアップ結果を見てみましょう。

# 結果をより明確にするために、skip-extended-insert を使用してデータを行ごとに表示します # 完全なデータベース バックアップ [root@host ~]# mysqldump -uroot -pxxxx --skip-extended-insert --databases testdb > utc_testdb.sql
mysqldump: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
[root@host ~]# utc_testdb.sql の詳細 
-- MySQL ダンプ 10.13 Distrib 5.7.23、Linux (x86_64) 用
--
-- ホスト: localhost データベース: testdb
-- ------------------------------------------------------
--サーバーバージョン 5.7.23-ログ

... /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */ を省略;
/*!40103 TIME_ZONE='+00:00' を設定します */;
# まず古いタイムゾーンを保存し、次にこのセッションのタイムゾーンを 0 タイムゾーンに変更します...省略--
-- テーブル `test_tb` のデータをダンプしています
--

LOCK TABLES `test_tb` WRITE;
/*!40000 ALTER TABLE `test_tb` でキーを無効にする */;
`test_tb` に値 (1,1001、'fgds'、'2020-07-10 09:43:28'、'2020-07-10 01:43:28') を挿入します。
`test_tb` に値 (2,1002、'fgsw'、'2020-10-10 09:43:28'、'2020-10-10 01:43:28') を挿入します。
`test_tb` に値 (3,1003、'vffg'、'2020-10-10 02:00:00'、'2020-10-09 18:00:00') を挿入します。
`test_tb` に値 (4,1004、'wdsd'、'2020-10-31 23:43:28'、'2020-10-31 15:43:28') を挿入します。
`test_tb` に値 (5,1005、'grdb'、'2020-11-01 00:00:00'、'2020-10-31 16:00:00') を挿入します。
`test_tb` に値 (6,1006、'sdfv'、'2020-11-01 02:00:00'、'2020-10-31 18:00:00') を挿入します。
`test_tb` に値 (7,1007、'fgfg'、'2020-11-06 02:00:00'、'2020-11-05 18:00:00') を挿入します。
`test_tb` に値 (8,1008、'tyth'、'2020-11-10 09:43:28'、'2020-11-10 01:43:28') を挿入します。
`test_tb` に値 (9,1009、'ewer'、'2020-11-10 09:43:28'、'2020-11-10 01:43:28') を挿入します。
`test_tb` に値 (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 07:17:03') を挿入します。
# タイムスタンプの時刻値は 8 時間減算されますが、日付時刻の時刻値は変更されないままであることがわかります UNLOCK TABLES;
/*!40103 TIME_ZONE=@OLD_TIME_ZONE を設定します */;
# タイムゾーンを元のタイムゾーンに変更します /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
-- ダンプは 2020-11-11 15:34:21 に完了しました

# where 条件を使用して、単一のテーブル内の部分的なデータをバックアップし、11 月以降のデータをバックアップします。# データベースでクエリを実行します。mysql> select * from test_tb where create_time >= '2020-11-01 00:00:00';
+--------------+--------+----------+----------------------+----------------------+
| increment_id | stu_id | stu_name | dt_time | create_time |
+--------------+--------+----------+----------------------+----------------------+
| 5 | 1005 | grdb | 2020-11-01 00:00:00 | 2020-11-01 00:00:00 |
| 6 | 1006 | sdfv | 2020-11-01 02:00:00 | 2020-11-01 02:00:00 |
| 7 | 1007 | fgfg | 2020-11-06 02:00:00 | 2020-11-06 02:00:00 |
| 8 | 1008 | tyth | 2020-11-10 09:43:28 | 2020-11-10 09:43:28 |
| 9 | 1009 | ewer | 2020-11-10 09:43:28 | 2020-11-10 09:43:28 |
| 10 | 1010 | エラー | 2020-11-11 15:17:03 | 2020-11-11 15:17:03 |
+--------------+--------+----------+----------------------+----------------------+
セット内の 6 行 (0.00 秒)
# mysqldump エクスポート [root@host ~]# mysqldump -uroot -pxxxx --skip-extended-insert testdb test_tb --where "create_time >= '2020-11-01 00:00:00' " > utc_testdb2.sql
mysqldump: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
[root@host ~]# utc_testdb2.sql の詳細 
-- MySQL ダンプ 10.13 Distrib 5.7.23、Linux (x86_64) 用
--
-- ホスト: localhost データベース: testdb
-- ------------------------------------------------------
--サーバーバージョン 5.7.23-ログ
...
/*!40103 @OLD_TIME_ZONE=@@TIME_ZONE を設定します */;
/*!40103 TIME_ZONE='+00:00' を設定します */;
...省略 - 
-- テーブル `test_tb` のデータをダンプしています
--
-- 場所: create_time >= '2020-11-01 00:00:00' 

LOCK TABLES `test_tb` WRITE;
/*!40000 ALTER TABLE `test_tb` でキーを無効にする */;
`test_tb` に値 (7,1007、'fgfg'、'2020-11-06 02:00:00'、'2020-11-05 18:00:00') を挿入します。
`test_tb` に値 (8,1008、'tyth'、'2020-11-10 09:43:28'、'2020-11-10 01:43:28') を挿入します。
`test_tb` に値 (9,1009、'ewer'、'2020-11-10 09:43:28'、'2020-11-10 01:43:28') を挿入します。
`test_tb` に値 (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 07:17:03') を挿入します。
# エクスポートできる UNLOCK TABLES は 4 つだけ見つかりました。
/*!40103 TIME_ZONE=@OLD_TIME_ZONE を設定します */;

-- ダンプは 2020-11-11 15:58:56 に完了しました

上記のエクスポートされた結果をよく見てみることをお勧めします。正直なところ、これまで詳細なテストを行ったことがなく、今の結果を見て少し驚いています。デフォルトでは、データは問題ありません。タイムスタンプ値は 0 タイムゾーンに変換されますが、データベースをインポートすると、データベースのタイムゾーンで表示されます。しかし、where 条件を使用してデータの一部をエクスポートすると、データベースクエリから取得された結果が dump でエクスポートされた結果と異なっていました。このとき、mysqldump は 0 タイムゾーンに変換後の時間値が where 条件に合致するデータのみをエクスポートし、直接クエリで取得された結果とは異なっていました。これは、これまで気づかなかったことです。

--skip-tz-utc パラメータを見て、期待どおりかどうか確認してみましょう。

# skip-tz-utc フルバックアップを使用 [root@host ~]# mysqldump -uroot -pxxxx --skip-extended-insert --skip-tz-utc --databases testdb > skiputc_testdb.sql
mysqldump: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
[root@host ~]# skiputc_testdb.sql をさらに実行 
-- MySQL ダンプ 10.13 Distrib 5.7.23、Linux (x86_64) 用
--
-- ホスト: localhost データベース: testdb
-- ------------------------------------------------------
--サーバーバージョン 5.7.23-ログ
..省略された見えないタイムゾーン変更ステートメント--
-- テーブル `test_tb` のデータをダンプしています
--

LOCK TABLES `test_tb` WRITE;
/*!40000 ALTER TABLE `test_tb` でキーを無効にする */;
`test_tb` に値 (1,1001,'fgds','2020-07-10 09:43:28','2020-07-10 09:43:28') を挿入します。
`test_tb` に値 (2,1002、'fgsw'、'2020-10-10 09:43:28'、'2020-10-10 09:43:28') を挿入します。
`test_tb` に値 (3,1003,'vffg','2020-10-10 02:00:00','2020-10-10 02:00:00') を挿入します。
`test_tb` に値 (4,1004、'wdsd'、'2020-10-31 23:43:28'、'2020-10-31 23:43:28') を挿入します。
`test_tb` に値 (5,1005、'grdb'、'2020-11-01 00:00:00'、'2020-11-01 00:00:00') を挿入します。
`test_tb` に値 (6,1006、'sdfv'、'2020-11-01 02:00:00'、'2020-11-01 02:00:00') を挿入します。
`test_tb` に値 (7,1007,'fgfg','2020-11-06 02:00:00','2020-11-06 02:00:00') を挿入します。
`test_tb` に値 (8,1008、'tyth'、'2020-11-10 09:43:28'、'2020-11-10 09:43:28') を挿入します。
`test_tb` に値 (9,1009、'ewer'、'2020-11-10 09:43:28'、'2020-11-10 09:43:28') を挿入します。
`test_tb` に値 (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 15:17:03') を挿入します。
# タイムスタンプ値は変換されずに日付時刻値と同じように表示されます。UNLOCK TABLES;
-- ダンプは 2020-11-11 16:23:32 に完了しました

# skip-tz-utc を使用して一部のデータをバックアップします [root@host ~]# mysqldump -uroot -pxxxx --skip-extended-insert --skip-tz-utc testdb test_tb --where "create_time >= '2020-11-01 00:00:00' " > skiputc_testdb2.sql
mysqldump: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
[root@host ~]# skiputc_testdb2.sql をさらに実行 
-- MySQL ダンプ 10.13 Distrib 5.7.23、Linux (x86_64) 用
--
-- ホスト: localhost データベース: testdb
-- ------------------------------------------------------
--サーバーバージョン 5.7.23-ログ
.. 省略 - 
-- テーブル `test_tb` のデータをダンプしています
--
-- 場所: create_time >= '2020-11-01 00:00:00' 

LOCK TABLES `test_tb` WRITE;
/*!40000 ALTER TABLE `test_tb` でキーを無効にする */;
`test_tb` に値 (5,1005、'grdb'、'2020-11-01 00:00:00'、'2020-11-01 00:00:00') を挿入します。
`test_tb` に値 (6,1006、'sdfv'、'2020-11-01 02:00:00'、'2020-11-01 02:00:00') を挿入します。
`test_tb` に値 (7,1007,'fgfg','2020-11-06 02:00:00','2020-11-06 02:00:00') を挿入します。
`test_tb` に値 (8,1008、'tyth'、'2020-11-10 09:43:28'、'2020-11-10 09:43:28') を挿入します。
`test_tb` に値 (9,1009、'ewer'、'2020-11-10 09:43:28'、'2020-11-10 09:43:28') を挿入します。
`test_tb` に値 (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 15:17:03') を挿入します。
# 6 つのデータは、データベース UNLOCK TABLES 内のクエリと一致しています。
-- ダンプは 2020-11-11 16:28:39 に完了しました

上記の結果から、--skip-tz-utc パラメータを使用した後、タイムスタンプフィールドの値は変換されず、エクスポートされたデータも期待どおりであることがわかります。

3. ちょっとした提案

では、このパラメータの重要性は何でしょうか?データベースサーバーが異なるタイムゾーンにある場合。 1 つのサーバーが北京 (東部第 8 地区) にあり、もう 1 つのサーバーが東京 (東部第 9 地区) にあるとします。この場合、北京のサーバーのデータを東京のサーバーにインポートする必要があります。デフォルトの --skip-tz-utc パラメータを使用せずにダンプファイルをインポートすると、照会されたタイムスタンプの時間データは、East Zone 8 サーバーの以前の時間値よりも 1 時間長くなります。ただし、East Zone 8 サーバーの 13:00 と East Zone 9 サーバーの 14:00 は同じ時間を表すため、East Zone 9 サーバーに表示される追加の 1 時間は正しいです。 --skip-tz-utc パラメータを追加すると、ダンプファイルが East 9 サーバーにインポートされた後、表示される時間値は East 8 サーバーによって以前に表示された時間値と同じですが、表す時間は異なります。

このパラメータの使用方法については、まず、--skip-tz-utc パラメータを追加するかどうかは、タイムスタンプフィールドのインポートとエクスポートにのみ影響し、datetime フィールドには影響しないことを理解する必要があります。

ここで著者は、まずタイムスタンプフィールドの使用を標準化することを推奨しています。たとえば、タイムスタンプフィールドは作成時間と更新時間の要件にのみ使用され、データ行の作成時間と更新時間のみを表すため、ビジネスとの関連性は弱くなります。他の時間フィールドでは、datetime を使用するようにしてください。このように、mysqldump が異なるパラメータを使用しても、実際の影響は大きくありません。

サーバーが異なるタイムゾーンにある場合は、インポートおよびエクスポートされたデータが正確になるように、デフォルトに従うことをお勧めします。サーバーがすべて同じタイムゾーンにある場合、--skip-tz-utc パラメータを使用してもほとんど違いはありません。デフォルトでは、mysqldump はタイムスタンプ値を 0 タイムゾーンに変換して保存することを知っておく必要があります。部分的なデータをバックアップし、タイムスタンプフィールドでフィルタリングする場合は、--skip-tz-utc パラメータを追加することをお勧めします。もう 1 つ注意点があります。完全バックアップから単一のデータベースまたは単一のテーブルのバックアップを選択する場合は、タイムスタンプフィールドのデータにも注意する必要があります。

上記は、皆さんが知らないかもしれないmysqldumpパラメータの詳細です。mysqldumpパラメータの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。

以下もご興味があるかもしれません: