LoginSignup
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@wsatoin株式会社アドバンスト・ネット

【MySQL】【MariaDB】 "mysqldump" "diff" コマンドを使い データベーススキーマの差分チェックを行った話

Last updated at Posted at 2025-02-12

経緯

業務の中で、運用8年目に突入するコンテンツがあり、各環境へのスキーマ更新を行った後の反映漏れの確認に mysqldiff を使いデータベーススキーマの差分チェックを行っていた。

ただ8年も運用を重ねていると、差分比較のテーブル数がマスターテーブルだけで200を超え、mysqldiff の実行結果が返ってくるまでに数分かかり反映作業のネックになっていた。

やりたい事

  • 短時間で差分結果を知りたい
  • できればツールなどは使わずに手軽に行いたい

結果

mysqldump --no-data で、各環境のスキーマ定義だけを出力し、diff コマンドで差分を取ることでひとまず解決

最終的に以下のようなコマンドで確認を行う

diff -F "^CREATE" -c -I "Dump completed on" \
  <(mysqldump --no-data -h HOST_NAME  -P PORT -u USER_NAME -p DB_NAME | sed 's/\(AUTO_INCREMENT=\)[0-9][0-9]*/\1/') \
  <(mysqldump --no-data -h HOST_NAME  -P PORT -u USER_NAME -p DB_NAME | sed 's/\(AUTO_INCREMENT=\)[0-9][0-9]*/\1/')

具体例として

以下のような状況を想定し、2つのデータベースを用意してみる

  • diff_db_pro 　⇒　本番環境のDB
  • diff_db_stg 　⇒　ステージング環境のDB
create database diff_db_pro;
create database diff_db_stg;

それぞれ3つのテーブル「shop」「item」「user」を作成

shopテーブル
  • STG環境のみカラムが追加されており、PRO環境ではカラムの追加が漏れている状況
  • shop_id　には AUTO_INCREMENT が設定されており、AUTO_INCREMENT の値がそれぞれのDBで異なる状況となる
MariaDB [diff_db_pro]> show create table shop;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| shop  | CREATE TABLE `shop` (
  `shop_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `in_date` datetime NOT NULL,
  `up_date` datetime NOT NULL,
  PRIMARY KEY (`shop_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [diff_db_stg]> show create table shop;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| shop  | CREATE TABLE `shop` (
  `shop_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `add_column` text NOT NULL,
  `in_date` datetime NOT NULL,
  `up_date` datetime NOT NULL,
  PRIMARY KEY (`shop_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
itemテーブル
  • itemテーブルついては、差分が無い状況
MariaDB [diff_db_stg]> show create table item;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| item  | CREATE TABLE `item` (
  `item_id` smallint(5) unsigned NOT NULL,
  `name` text NOT NULL,
  `in_date` datetime NOT NULL,
  `up_date` datetime NOT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
userテーブル
  • 本番環境ににみ（diff_db_pro）、PKに PARTITION の設定がされている状況
MariaDB [diff_db_pro]> show create table user;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `user_id` int(10) unsigned NOT NULL,
  `name` text NOT NULL,
  `in_date` datetime NOT NULL,
  `up_date` datetime NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
 PARTITION BY LINEAR HASH (`user_id`)
PARTITIONS 10 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

この状態で、差分をdiff コマンドの context形式で出力してみる
※diff コマンドについてはコチラを参考

$ diff  -c  \
>   <(mysqldump --no-data -u■■■■■■■ -p■■■■■■ diff_db_pro) \
>   <(mysqldump --no-data -u■■■■■■■ -p■■■■■■ diff_db_stg)
*** /dev/fd/63  2025-02-10 20:49:08.945826829 +0900
--- /dev/fd/62  2025-02-10 20:49:08.945826829 +0900
***************
*** 1,6 ****
  -- MySQL dump 10.17  Distrib 10.3.13-MariaDB, for Linux (x86_64)
  --
! -- Host: localhost    Database: diff_db_pro
  -- ------------------------------------------------------
  -- Server version     10.3.13-MariaDB-log

--- 1,6 ----
  -- MySQL dump 10.17  Distrib 10.3.13-MariaDB, for Linux (x86_64)
  --
! -- Host: localhost    Database: diff_db_stg
  -- ------------------------------------------------------
  -- Server version     10.3.13-MariaDB-log

***************
*** 41,50 ****
  CREATE TABLE `shop` (
    `shop_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    `name` text NOT NULL,
    `in_date` datetime NOT NULL,
    `up_date` datetime NOT NULL,
    PRIMARY KEY (`shop_id`)
! ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
  /*!40101 SET character_set_client = @saved_cs_client */;

  --
--- 41,51 ----
  CREATE TABLE `shop` (
    `shop_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    `name` text NOT NULL,
+   `add_column` text NOT NULL,
    `in_date` datetime NOT NULL,
    `up_date` datetime NOT NULL,
    PRIMARY KEY (`shop_id`)
! ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
  /*!40101 SET character_set_client = @saved_cs_client */;

  --
***************
*** 60,68 ****
    `in_date` datetime NOT NULL,
    `up_date` datetime NOT NULL,
    PRIMARY KEY (`user_id`)
! ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
!  PARTITION BY LINEAR HASH (`user_id`)
! PARTITIONS 10;
  /*!40101 SET character_set_client = @saved_cs_client */;
  /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

--- 61,67 ----
    `in_date` datetime NOT NULL,
    `up_date` datetime NOT NULL,
    PRIMARY KEY (`user_id`)
! ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  /*!40101 SET character_set_client = @saved_cs_client */;
  /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

差分として、
「データベース名」「AUTO_INCREMENT値」「追加したカラム（add_column）」「PARTITIONの設定」が出力される

このままだと、差分としてPARTITIONがある userテーブル名が出力されておらず対象テーブルが分からない為、diff コマンドのオプションに　-F "^CREATE"　をつけてみる
※差分があった場合、前の行にさかのぼってマッチする行を表示してくれる。

-F 正規表現 --show-function-line=正規表現 context形式とunified形式で、各差分のブロックに対し、その前方がパターンにマッチした最後の行の一部を表示 
$ diff -F "^CREATE" -c \
>   <(mysqldump --no-data -u■■■■■■■ -p■■■■■■ diff_db_pro) \
>   <(mysqldump --no-data -u■■■■■■■ -p■■■■■■ diff_db_stg)
*** /dev/fd/63  2025-02-10 21:31:26.109432389 +0900
--- /dev/fd/62  2025-02-10 21:31:26.109432389 +0900
***************
*** 1,6 ****
  -- MySQL dump 10.17  Distrib 10.3.13-MariaDB, for Linux (x86_64)
  --
! -- Host: localhost    Database: diff_db_pro
  -- ------------------------------------------------------
  -- Server version     10.3.13-MariaDB-log

--- 1,6 ----
  -- MySQL dump 10.17  Distrib 10.3.13-MariaDB, for Linux (x86_64)
  --
! -- Host: localhost    Database: diff_db_stg
  -- ------------------------------------------------------
  -- Server version     10.3.13-MariaDB-log

*************** CREATE TABLE `item` (
*** 41,50 ****
  CREATE TABLE `shop` (
    `shop_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    `name` text NOT NULL,
    `in_date` datetime NOT NULL,
    `up_date` datetime NOT NULL,
    PRIMARY KEY (`shop_id`)
! ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
  /*!40101 SET character_set_client = @saved_cs_client */;

  --
--- 41,51 ----
  CREATE TABLE `shop` (
    `shop_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    `name` text NOT NULL,
+   `add_column` text NOT NULL,
    `in_date` datetime NOT NULL,
    `up_date` datetime NOT NULL,
    PRIMARY KEY (`shop_id`)
! ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
  /*!40101 SET character_set_client = @saved_cs_client */;

  --
*************** CREATE TABLE `user` (
*** 60,68 ****
    `in_date` datetime NOT NULL,
    `up_date` datetime NOT NULL,
    PRIMARY KEY (`user_id`)
! ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
!  PARTITION BY LINEAR HASH (`user_id`)
! PARTITIONS 10;
  /*!40101 SET character_set_client = @saved_cs_client */;
  /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

--- 61,67 ----
    `in_date` datetime NOT NULL,
    `up_date` datetime NOT NULL,
    PRIMARY KEY (`user_id`)
! ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  /*!40101 SET character_set_client = @saved_cs_client */;
  /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

これで、PARTITIONがある userテーブル名もわかるようになったが、AUTO_INCREMENT の値が環境によって異なる事が多く、差分として出力されないようになればより見やすくなる

sedコマンドを使い AUTO_INCREMENT=値 の値を出力しないことで、差分対象から除くようにしてみる

diff -F "^CREATE" -c \
>   <(mysqldump --no-data -u■■■■■■■ -p■■■■■■ diff_db_pro | sed 's/\(AUTO_INCREMENT=\)[0-9][0-9]*/\1/') \
>   <(mysqldump --no-data -u■■■■■■■ -p■■■■■■ diff_db_stg | sed 's/\(AUTO_INCREMENT=\)[0-9][0-9]*/\1/')
*** /dev/fd/63  2025-02-12 16:12:59.067101589 +0900
--- /dev/fd/62  2025-02-12 16:12:59.067101589 +0900
***************
*** 1,6 ****
  -- MySQL dump 10.17  Distrib 10.3.13-MariaDB, for Linux (x86_64)
  --
! -- Host: localhost    Database: diff_db_pro
  -- ------------------------------------------------------
  -- Server version     10.3.13-MariaDB-log

--- 1,6 ----
  -- MySQL dump 10.17  Distrib 10.3.13-MariaDB, for Linux (x86_64)
  --
! -- Host: localhost    Database: diff_db_stg
  -- ------------------------------------------------------
  -- Server version     10.3.13-MariaDB-log

*************** CREATE TABLE `item` (
*** 41,46 ****
--- 41,47 ----
  CREATE TABLE `shop` (
    `shop_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    `name` text NOT NULL,
+   `add_column` text NOT NULL,
    `in_date` datetime NOT NULL,
    `up_date` datetime NOT NULL,
    PRIMARY KEY (`shop_id`)
*************** CREATE TABLE `user` (
*** 60,68 ****
    `in_date` datetime NOT NULL,
    `up_date` datetime NOT NULL,
    PRIMARY KEY (`user_id`)
! ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
!  PARTITION BY LINEAR HASH (`user_id`)
! PARTITIONS 10;
  /*!40101 SET character_set_client = @saved_cs_client */;
  /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

--- 61,67 ----
    `in_date` datetime NOT NULL,
    `up_date` datetime NOT NULL,
    PRIMARY KEY (`user_id`)
! ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  /*!40101 SET character_set_client = @saved_cs_client */;
  /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

これで AUTO_INCREMENT=値 の行も、値が異なるだけでは差分として出力されないようになった

もし、ダンプ末尾の　-- Dump completed on XXXX-XX-XX
という表示も差分として表示されて外したい場合には、-I "Dump completed on" \ をつける事で解決できる

テーブルスキーマの差分が無くなった場合

最終的に、diff_db_pro のほうにも、
shopテーブルに add_column、 'user'テーブルに PARTITION が追加された状態で差分チェックを行うと

diff -F "^CREATE" -c -I "Dump completed on" \
>   <(mysqldump --no-data -u■■■■■■■ -p■■■■■■ diff_db_pro | sed 's/\(AUTO_INCREMENT=\)[0-9][0-9]*/\1/') \
>   <(mysqldump --no-data -u■■■■■■■ -p■■■■■■ diff_db_stg | sed 's/\(AUTO_INCREMENT=\)[0-9][0-9]*/\1/')
*** /dev/fd/63  2025-02-12 16:28:06.593081984 +0900
--- /dev/fd/62  2025-02-12 16:28:06.594082073 +0900
***************
*** 1,6 ****
  -- MySQL dump 10.17  Distrib 10.3.13-MariaDB, for Linux (x86_64)
  --
! -- Host: localhost    Database: diff_db_pro
  -- ------------------------------------------------------
  -- Server version     10.3.13-MariaDB-log

--- 1,6 ----
  -- MySQL dump 10.17  Distrib 10.3.13-MariaDB, for Linux (x86_64)
  --
! -- Host: localhost    Database: diff_db_stg
  -- ------------------------------------------------------
  -- Server version     10.3.13-MariaDB-log

差分として、データベース名だけが出力されるようになり、差分がない事がわかるようになる

まとめ

今回の、 mysqldump --no-data の結果を diffコマンドで差分を取ることによって、
今まで mysqldiff で数分時間を取られていた作業が、1～2秒で終える結果となり、本番環境へのリリース作業において、大幅な時間短縮につながる結果となった。

参考記事

2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?