目次
とある日
Twitterで「mysqlpump」が話題になってました。
?「DBのdumpとrestoreで40分かかってて大変なんです」
— kk2170@きむらだよ (@kk2170) 2023年3月24日
自分「dumpとrestoreの方法は?」
?「mysqldumpで…」
自分「とりあえずdをpにしてもらっても良いですか？」
?「10分になりました！」
みたいな事があってニヤリとした。
10分で終わる様になったので、30分かけてなぜ早くなるのかを説明した。
「mysqlpump」の名前は聞いたことありますが使ったことはありませんでした。
またその中で、MySQL Shellのダンプロードユーティリティを使うというリプがありました。
便乗すると、今ならMySQL Shellのダンプロードユーティリティがベターです。https://t.co/1Wj7joi7rz
— tkyk04 (@taka_yuki_04) 2023年3月24日
軽い、早い、安い（無料）論理バックアップが取れます。
（mysqldumpやmysqlpumpはもう頑張らないと思うので…。) https://t.co/IMrK16k48S
初めて聞いたので、「mysqlpump」と一緒に使ってみようということで使ってみた記録です。
MySQL のバックアップ方法
リプにありますが、MySQLでバックアップする方法は以下のパターンがあります。
- mysqldump
- mysqlpump
- MySQL Shellのダンプロードユーティリティ
- XtraBackup
- Clone Plugin
今回は1,2,3の3つを使って比べてみます。
各バックアップ方法のおさらい
軽く各方法について触れておきます。
リプにも各方法のメリデメとかはあります、とても参考になるのでぜひご覧ください。
mysqldumpを使うメリットは一つのファイルでSQLの形で全部残したいときですね。(大きなテーブルには向いてないけど小さいテーブルとかはDDL＋DML、中身もバッチリ見れて楽)
— tkyk04 (@taka_yuki_04) 2023年3月24日
mysqlpumpはdumpと比べて並列処理できるのでバックアップ時は高速化するんですが、リストアは実はそんなに早くないという…。 pic.twitter.com/MKHBOReieV
mysqldump — データベースバックアッププログラム
「mysqldumpコマンド」を使った方法です。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム
一般的によく使われるバックアップかなと思います。
個人的に使ってみたメリット
- 手軽に利用できる
- ドキュメントが豊富
- 他の方法に比べてオプションが豊富
- 一つのファイルに纏められる
mysqlpump — データベースバックアッププログラム
よくTypoと間違われるやつです。
自分もはじめは「え？Typoかな？」と思いました。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.5.6 mysqlpump — データベースバックアッププログラム
個人的に使ってみたメリット
- mysqldumpと同じオプションで使える
- ダンプ速度はmysqldumpより早い
- ダンプの進捗状況が見れる
「mysqlpump」については以下記事がとても参考になります。
第153回 mysqlpumpを使ってバックアップを取ってみる | gihyo.jp
mysqldumpより、はやいらしいですが、今回の検証ではリストアにかなり時間がかかりました。
あと、ダンプファイルにデータベース名が明示的に書かれているので別のデータベース先にリストアしたい場合は使えないかもです。
(オプションで変更できそうには見えなかったです、何かご存じの方がいれば教えてください)
以下は、mysqlpumpでダンプしたファイルの一部です。
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `world_x` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */; CREATE TABLE `world_x`.`city` ( `ID` int NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Info` json DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=16776941 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
MySQL Shellのダンプロードユーティリティ
MySQL Shell を使った方法です。
裏ではLOAD DATA LOCAL INFILE ステートメントが利用されるようです。
MySQL :: MySQL Shell 8.0 :: 8.6 ダンプロードユーティリティ
MySQL :: MySQL Shell 8.0 :: 8.5 インスタンスダンプユーティリティ、スキーマダンプユーティリティおよびテーブルダンプユーティリティ
個人的に使ってみたメリット
- ダンプ・リストアが早い
- dry run できる
- ダンプの進捗状況が見れる
- ファイル容量を圧縮できる
使った感じかなり癖がありますが、速度自体はかなり早かったです。
ダンプするのは、 util.dumpInstance()を使いました、ロードするのはutil.loadDump()を使いました。
それぞれの詳細については以下記事がとても参考になりました。
MySQL Shell で バックアップとリストアをパラレルで実行する – スマートスタイル技術ブログ
MySQL Shell Dump UtilityとDump Loading Utilityの使い方 - Qiita
バックアップやってみた
前提条件
MySQLのサンプルデータベースを使ってます。
MySQL :: Other MySQL Documentation
データ量はある程度見栄えがあるように2GBぐらいに増やしてます。
(root@localhost) [world_x] 8.0.32 > SELECT table_name, engine, table_rows AS tbl_rows, avg_row_length AS rlen, floor((data_length+index_length)/1024/1024) AS allmb, floor((data_length)/1024/1024) AS dmb, floor((index_length)/1024/1024) AS imb FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC; +-----------------+--------+----------+------+-------+------+------+ | TABLE_NAME | ENGINE | tbl_rows | rlen | allmb | dmb | imb | +-----------------+--------+----------+------+-------+------+------+ | city | InnoDB | 16587999 | 120 | 1909 | 1909 | 0 | | countrylanguage | InnoDB | 984 | 99 | 0 | 0 | 0 | | countryinfo | InnoDB | 239 | 616 | 0 | 0 | 0 | | country | InnoDB | 239 | 205 | 0 | 0 | 0 | +-----------------+--------+----------+------+-------+------+------+ 4 rows in set (0.11 sec)
docker compose で以下環境のもとやってます。
db7: image: mysql:8.0.32 container_name: mysql_ver_8.0.32 environment: MYSQL_ROOT_PASSWORD: root MYSQL_DATABASE: test MYSQL_USER: docker MYSQL_PASSWORD: docker TZ: "Asia/Tokyo" command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci volumes: - ../../MySQL/settings/my.cnf:/etc/mysql/conf.d/my.cnf - ../../MySQL/settings/mylogin.sh:/root/mylogin.sh
my.cnf
# MySQLサーバーへの設定 [mysqld] # 文字コード/照合順序の設定 character-set-server = utf8mb4 collation-server = utf8mb4_bin # タイムゾーンの設定 default-time-zone = SYSTEM log_timestamps = SYSTEM # デフォルト認証プラグインの設定 default-authentication-plugin = mysql_native_password # エラーログの設定 # スロークエリログの設定 slow_query_log = 1 # long_query_time = 5.0 log_queries_not_using_indexes = 0 # 実行ログの設定 general_log = 1 local_infile = 1 # mysqlオプションの設定 [mysql] # 文字コードの設定 default-character-set = utf8mb4 prompt="(\\U) [\\d] \\v \\T> " #prompt="(\\u@\\h) [\\d]> " #prompt="\\r:\\m:\\s> " # mysqlクライアントツールの設定 [client] # 文字コードの設定
mysqldump
ダンプ
bash-4.4# time mysqldump -u root world_x > world_x_mysqldump.sql real 0m23.256s user 0m11.475s sys 0m2.065s bash-4.4# ll bash: ll: command not found bash-4.4# ls -l total 1093816 -rw-r--r-- 1 root root 1120062119 Mar 26 17:25 world_x_mysqldump.sql bash-4.4#
ロード
bash-4.4# time mysql -u root world_x_dump < world_x_mysqldump.sql real 4m27.495s user 0m6.608s sys 0m0.579s
mysqlpump
ダンプ
bash-4.4# time mysqlpump -uroot world_x > world_x_mysqlpump.sql Dump progress: 0/3 tables, 250/16401493 rows Dump progress: 3/4 tables, 838734/16402477 rows Dump progress: 3/4 tables, 1710484/16402477 rows Dump progress: 3/4 tables, 2517984/16402477 rows Dump progress: 3/4 tables, 3388734/16402477 rows Dump progress: 3/4 tables, 4272984/16402477 rows Dump progress: 3/4 tables, 5238984/16402477 rows Dump progress: 3/4 tables, 6129234/16402477 rows Dump progress: 3/4 tables, 6960984/16402477 rows Dump progress: 3/4 tables, 7815484/16402477 rows Dump progress: 3/4 tables, 8637984/16402477 rows Dump progress: 3/4 tables, 9572234/16402477 rows Dump progress: 3/4 tables, 10502484/16402477 rows Dump progress: 3/4 tables, 11338484/16402477 rows Dump progress: 3/4 tables, 12300734/16402477 rows Dump progress: 3/4 tables, 13147234/16402477 rows Dump progress: 3/4 tables, 14051484/16402477 rows Dump progress: 3/4 tables, 14864234/16402477 rows Dump progress: 3/4 tables, 15814734/16402477 rows Dump progress: 3/4 tables, 16693734/16402477 rows Dump completed in 19908 real 0m19.934s user 0m14.082s sys 0m1.465s bash-4.4# ls -l total 2190020 drwxr-xr-x 2 root root 4096 Mar 26 17:26 mysqlsh -rw-r--r-- 1 root root 1120062119 Mar 26 17:25 world_x_mysqldump.sql -rw-r--r-- 1 root root 1122503879 Mar 26 17:29 world_x_mysqlpump.sql bash-4.4#
ロード
bash-4.4# time mysql -u root < world_x_mysqlpump.sql real 15m29.967s user 0m9.765s sys 0m3.871s
MySQL Shell
ダンプロードユーティリティでは
LOAD DATA LOCAL INFILEステートメントが使用されるため、インポート中は、ターゲット MySQL インスタンスのlocal_infileシステム変数のグローバル設定をONにする必要があります。 デフォルトでは、このシステム変数は標準の MySQL DB システム構成でONに設定されています。
my.cnfで「1」を指定してます。
ダンプ
めちゃくちゃ色々表示されてて便利だなと思いました。
MySQL localhost JS > util.dumpSchemas(["world_x"], "/home") Acquiring global read lock Global read lock acquired Initializing - done 1 schemas will be dumped and within them 4 tables, 0 views. Gathering information - done All transactions have been started Locking instance for backup Global read lock has been released Writing global DDL files Running data dump using 4 threads. NOTE: Progress information uses estimated values and may not be accurate. Writing schema metadata - done Writing DDL - done Writing table metadata - done Starting data dump 101% (5.54K rows / ~5.43K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed Dump duration: 00:00:00s Total duration: 00:00:00s Schemas dumped: 1 Tables dumped: 4 Uncompressed data size: 319.60 KB Compressed data size: 97.10 KB Compression ratio: 3.3 Rows written: 5541 Bytes written: 97.10 KB Average uncompressed throughput: 319.60 KB/s Average compressed throughput: 97.10 KB/s
ロード
MySQL localhost JS > util.loadDump("/home/mysqlsh", {schema:"world_x_sh"}) Loading DDL and Data from '/home/mysqlsh' using 4 threads. Opening dump... Target is MySQL 8.0.32. Dump was produced from MySQL 8.0.32 Scanning metadata - done Checking for pre-existing objects... Executing common preamble SQL Executing DDL - done Executing view DDL - done Starting data load 3 thds loading / 100% (919.52 MB / 919.52 MB), 1.70 MB/s, 4 / 4 tables done Recreating indexes - done Executing common postamble SQL 35 chunks (16.71M rows, 919.52 MB) for 4 tables in 1 schemas were loaded in 4 min 57 sec (avg throughput 3.12 MB/s) 0 warnings were reported during the load.
dry run できるのいいなと思いました。
MySQL localhost JS > util.dumpSchemas(["world_x"], "/home/test", {dryRun: true}) dryRun enabled, no locks will be acquired and no files will be created. Acquiring global read lock Global read lock acquired Initializing - done 1 schemas will be dumped and within them 4 tables, 0 views. Gathering information - done All transactions have been started Locking instance for backup Global read lock has been released Writing global DDL files Writing DDL - done Starting data dump 0% (0 rows / ~15.69M rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
テーブル毎にファイルが作られるようです。
bash-4.4# ls -l /home/mysqlsh/ total 43352 -rw-r----- 1 root root 1860 Mar 26 17:26 @.done.json -rw-r----- 1 root root 706 Mar 26 17:25 @.json -rw-r----- 1 root root 240 Mar 26 17:25 @.post.sql -rw-r----- 1 root root 240 Mar 26 17:25 @.sql -rw-r----- 1 root root 10281 Mar 26 18:55 load-progress.4319c033-5d90-11ed-b5da-0242ac140004.json -rw-r----- 1 root root 488 Mar 26 17:25 world_x.json -rw-r----- 1 root root 569 Mar 26 17:25 world_x.sql -rw-r----- 1 root root 673 Mar 26 17:25 world_x@city.json -rw-r----- 1 root root 828 Mar 26 17:25 world_x@city.sql -rw-r----- 1 root root 1537747 Mar 26 17:25 world_x@city@0.tsv.zst -rw-r----- 1 root root 224 Mar 26 17:25 world_x@city@0.tsv.zst.idx -rw-r----- 1 root root 1466007 Mar 26 17:25 world_x@city@1.tsv.zst -rw-r----- 1 root root 224 Mar 26 17:25 world_x@city@1.tsv.zst.idx -rw-r----- 1 root root 1412253 Mar 26 17:25 world_x@city@10.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:25 world_x@city@10.tsv.zst.idx -rw-r----- 1 root root 1416338 Mar 26 17:25 world_x@city@11.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:25 world_x@city@11.tsv.zst.idx -rw-r----- 1 root root 1420107 Mar 26 17:26 world_x@city@12.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@12.tsv.zst.idx -rw-r----- 1 root root 1413564 Mar 26 17:26 world_x@city@13.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@13.tsv.zst.idx -rw-r----- 1 root root 1419191 Mar 26 17:26 world_x@city@14.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@14.tsv.zst.idx -rw-r----- 1 root root 1371651 Mar 26 17:26 world_x@city@15.tsv.zst -rw-r----- 1 root root 224 Mar 26 17:26 world_x@city@15.tsv.zst.idx -rw-r----- 1 root root 1418129 Mar 26 17:26 world_x@city@16.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@16.tsv.zst.idx -rw-r----- 1 root root 1418740 Mar 26 17:26 world_x@city@17.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@17.tsv.zst.idx -rw-r----- 1 root root 1473313 Mar 26 17:26 world_x@city@18.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@18.tsv.zst.idx -rw-r----- 1 root root 1415471 Mar 26 17:26 world_x@city@19.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@19.tsv.zst.idx -rw-r----- 1 root root 1414672 Mar 26 17:25 world_x@city@2.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:25 world_x@city@2.tsv.zst.idx -rw-r----- 1 root root 1423075 Mar 26 17:26 world_x@city@20.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@20.tsv.zst.idx -rw-r----- 1 root root 1416003 Mar 26 17:26 world_x@city@21.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@21.tsv.zst.idx -rw-r----- 1 root root 1422766 Mar 26 17:26 world_x@city@22.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@22.tsv.zst.idx -rw-r----- 1 root root 1417149 Mar 26 17:26 world_x@city@23.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@23.tsv.zst.idx -rw-r----- 1 root root 1416544 Mar 26 17:26 world_x@city@24.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@24.tsv.zst.idx -rw-r----- 1 root root 1416591 Mar 26 17:26 world_x@city@25.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@25.tsv.zst.idx -rw-r----- 1 root root 1413573 Mar 26 17:26 world_x@city@26.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@26.tsv.zst.idx -rw-r----- 1 root root 1422132 Mar 26 17:26 world_x@city@27.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@27.tsv.zst.idx -rw-r----- 1 root root 1414476 Mar 26 17:26 world_x@city@28.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@28.tsv.zst.idx -rw-r----- 1 root root 1418757 Mar 26 17:26 world_x@city@29.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@29.tsv.zst.idx -rw-r----- 1 root root 1409669 Mar 26 17:25 world_x@city@3.tsv.zst -rw-r----- 1 root root 224 Mar 26 17:25 world_x@city@3.tsv.zst.idx -rw-r----- 1 root root 1413113 Mar 26 17:26 world_x@city@30.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:26 world_x@city@30.tsv.zst.idx -rw-r----- 1 root root 1414840 Mar 26 17:25 world_x@city@4.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:25 world_x@city@4.tsv.zst.idx -rw-r----- 1 root root 1417435 Mar 26 17:25 world_x@city@5.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:25 world_x@city@5.tsv.zst.idx -rw-r----- 1 root root 1415651 Mar 26 17:25 world_x@city@6.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:25 world_x@city@6.tsv.zst.idx -rw-r----- 1 root root 1398405 Mar 26 17:25 world_x@city@7.tsv.zst -rw-r----- 1 root root 224 Mar 26 17:25 world_x@city@7.tsv.zst.idx -rw-r----- 1 root root 1411859 Mar 26 17:25 world_x@city@8.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:25 world_x@city@8.tsv.zst.idx -rw-r----- 1 root root 1423065 Mar 26 17:25 world_x@city@9.tsv.zst -rw-r----- 1 root root 232 Mar 26 17:25 world_x@city@9.tsv.zst.idx -rw-r----- 1 root root 494 Mar 26 17:26 world_x@city@@31.tsv.zst -rw-r----- 1 root root 8 Mar 26 17:26 world_x@city@@31.tsv.zst.idx -rw-r----- 1 root root 653 Mar 26 17:25 world_x@country.json -rw-r----- 1 root root 770 Mar 26 17:25 world_x@country.sql -rw-r----- 1 root root 3669 Mar 26 17:26 world_x@country@@0.tsv.zst -rw-r----- 1 root root 8 Mar 26 17:26 world_x@country@@0.tsv.zst.idx -rw-r----- 1 root root 591 Mar 26 17:25 world_x@countryinfo.json -rw-r----- 1 root root 854 Mar 26 17:25 world_x@countryinfo.sql -rw-r----- 1 root root 12926 Mar 26 17:26 world_x@countryinfo@@0.tsv.zst -rw-r----- 1 root root 8 Mar 26 17:26 world_x@countryinfo@@0.tsv.zst.idx -rw-r----- 1 root root 707 Mar 26 17:25 world_x@countrylanguage.json -rw-r----- 1 root root 991 Mar 26 17:25 world_x@countrylanguage.sql -rw-r----- 1 root root 8697 Mar 26 17:26 world_x@countrylanguage@@0.tsv.zst -rw-r----- 1 root root 8 Mar 26 17:26 world_x@countrylanguage@@0.tsv.zst.idx
ついでにファイル容量調べた
検証しててふと気づいた差分についてです。
ダンプしたファイル容量がかなり違ってました。
「mysqldump」と「mysqlpump」はほぼ同じですが、「ダンプロードユーティリティ」はかなり少ないですね。
bash-4.4# du -ah -d 1 /home/ 1.1G /home/world_x_mysqlpump.sql 1.1G /home/world_x_mysqldump.sql 43M /home/mysqlsh 2.2G /home/
まとめ
それぞれの結果を貼り付けているので表記に揺れがあります。
また、1秒未満は切り捨ててます。
| 方法 | ダンプ時間 | ロード時間 | 合計時間 |
|---|---|---|---|
| mysqldump | 0m23.256s | 4m27.495s | 4m50s |
| mysqlpump | 0m19.934s | 15m29.967s | 15m48s |
| ダンプロードユーティリティ | 00:00:07s | 4 min 57 sec | 5m4s |
mysqlpumpのリストアが思いの外早くなかったですね。
ダンプロードユーティリティはダンプはめちゃくちゃ早いなってなりました。
ロードがあまり早くなかったので時と場合によって適している時があるのかなと思いました。
〆
使った感じダンプロードユーティリティが一番近代的だなって感じでした。
ただ、制約もあるみたいなので注意が必要です。
参考資料
MySQL :: MySQL Shell 8.0 :: 11.6 Dump Loading Utility
MySQL Shellのユーティリティをコマンドラインで実行する - MicroAd Developers Blog
