【大阪 / 横浜 / 徳島】インフラ / サーバーサイドエンジニア募集中！

【導入実績 500社以上】AWS 構築・運用保守・監視サービス

【CentOS 後継】AlmaLinux OS サーバー構築・移行サービス

【WordPress 専用】クラウドサーバー『ウェブスピード』

【格安】Webサイトセキュリティ自動診断「クイックスキャナー」

【予約システム開発】EDISONE カスタマイズ開発サービス

【100URLの登録が0円】Webサイト監視サービス『Appmill』

【200ヶ国以上に対応】グローバル eSIM「ビヨンドSIM」

【中国への旅行・出張・駐在なら】中国SIMサービス「チョコSIM」

【グローバル専用サービス】北米・中国でも、ビヨンドのMSP

【YouTube】ビヨンド公式チャンネル「びよまるチャンネル」

ダンプが楽しくなる mysqldump の後継ツール mysqlpump の話

2020.08.01

サーバー構築 / 運用

こんにちは。
開発チームのフリー素材担当、まんだいです。

かなり出遅れてますが mysqldump の後継ツールとなる mysqlpump を使ってみたので、まだ使ったことがない人向けにオススメポイントをまとめてみました。
さすが後継というべきか、基本的なオプションの使い方はそのままで mysqldump から移行してきた人にも優しい設計になっていましたので、ぜひ使ってみてほしいと思いました。
MySQL に同梱されているのがポイント高いですよね。

これだけでも乗り換える価値がある、進捗表示

今回試したデータベースはせいぜい 200MB 程度のものなのであまり恩恵はなかったですが、 mysqlpump はダンプの進行具合を逐一表示してくれます。

mysqlpump -uroot -p --databases xxxxx  > xxxxx_20XX0X0X.sql
Enter password:
Dump progress: 1/6 tables, 0/352 rows
Dump progress: 73/145 tables, 551291/1274767 rows
Dump completed in 2073 milliseconds

基本のコマンドはこんな感じです。
パッと見た感じ、タイポかな？と思うくらい mysqldump ですよね。

同じデータベースを mysqldump でもダンプしてみたところ、 mysqlpump の方が少し遅かったです。
これには少し残念な気持ちを隠せなかったですが、今回の容量くらいだと0.2秒くらいだったので誤差といってもいいレベルでしょう。

出力ファイルを圧縮するオプションを持っている

ダンプファイルはバックアップするにせよ、他のサーバーに転送して複製するにせよ大抵の場合圧縮しますよね。
その点 mysqlpump は圧縮した状態で出力するオプションを持っているので、面倒な後処理も必要ありません。

mysqlpump -uroot -p --compress-output=LZ4 --databases xxxxx  > xxxxx_20XX0X0X.lz4

--compress-output オプションで圧縮フォーマットを選べます。
ただし、対応している形式が LZ4 と openssl zlib というあまり見ない形式のみ。
恥ずかしながら openssl zlib ってなんだよと思い調べてみると zip ファイルのことでしたね。

zip じゃ面白くないので LZ4 で固めてみましたが、今度はどうやって解凍するんだろうとなる訳です。

そこで見つけたのは MySQL のドキュメント。

LZ4 形式は Linux 標準で対応している圧縮形式のようで、解凍コマンドが用意されていました。

lz4_decompress xxxxx_20XX0X0X.lz4 xxxxx_20XX0X0X.sql

そのまんま。そして長い。
unzip 的な感じでお願いしたかった。
そんなぼやきはさておき、解答してできたファイルを mysql コマンドで叩き込むだけでインポートできます。

mysql -uroot -p < xxxxx_20XX0X0X.sql

ファイル圧縮といえば気になるのが圧縮率ですが、 LZ4 は圧縮率ではそこまで優位性はないようで、圧縮速度がポイントのようです。
とはいえ、圧縮率もそこまで悪くないようなので mysqlpump を使う時はしばらく LZ4 形式で固めてみようと思っています。

並列処理ができる

mysqldump と大きく違うのが並列処理、という概念。
ただ、ドキュメントを読む限り mysqlpump のダンプ処理を掴めていないと有効に使えなさそうです。

--default-parallelism オプションで立ち上げるスレッド数を指定します。デフォルトは 2 です。

mysqlpump -uroot -p \
  --default-parallelism=4 \
  --databases xxxxx \
  > xxxxx_20XX0X0X.sql

--parallel-schemas オプションでキューを作り、どのキューにどのテーブルを処理させるかを指定します。

mysqlpump -uroot -p \
  --parallel-schemas=xxxxx \
  --parallel-schemas=yyyyy \
  > xxxxxyyyyy_20XX0X0X.sql

これで xxxxx テーブルと yyyyy テーブルを別々のスレッドで処理できます。簡単ですね。
さらに --default-parallelism オプションとの合わせ技でキューを処理するスレッド数を指定できます。

mysqlpump -uroot -p \
  --default-parallelism=4 \
  --parallel-schemas=xxxxx \
  --parallel-schemas=yyyyy \
  > xxxxxyyyyy_20XX0X0X.sql

上記の例だとキューが2つ作成され、それぞれのキューを4つのスレッドで処理するという意味になります。

テーブル単位でキューを作るのは覚えておいたほうがいい点で、処理量をうまく分散させられれば実行時間の短縮になるんでしょう、きっと。
最初のうちはそんな計算をしてるうちにダンプできちゃうんじゃないかなと思ったりしますが、巨大なテーブルと細かいテーブルたちをそれぞれ別のキューで処理させる、といった感じですね。

--parallel-schemas オプションは起動するスレッド数を同時に指定することもできるので、スレッド数に偏りを持たせることも可能です。

mysqlpump -uroot -p \
  --parallel-schemas=2:xxxxx \
  --parallel-schemas=4:yyyyy \
  > xxxxxyyyyy_20XX0X0X.sql

このコマンドだと xxxxx テーブルのダンプに2スレッド、 yyyyy テーブルのダンプに4スレッド使用するという意味になります。

ダンプしようとしたら変なエラーが出た

最初に実行した時、訳のわからないエラーが出てテンパったので備忘録として載せておきます。

mysqlpump -uroot -p --databases xxxxx  > xxxxx_20XX0X0X.sql
Enter password:
mysqlpump: [WARNING] (1356) View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_file_io_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_stages' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_statement_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_statement_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.innodb_buffer_stats_by_schema' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.innodb_buffer_stats_by_table' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.innodb_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.io_by_thread_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.io_global_by_file_by_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.io_global_by_file_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.io_global_by_wait_by_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.io_global_by_wait_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.latest_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.memory_by_host_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.memory_by_thread_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.memory_by_user_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.memory_global_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.memory_global_total' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.schema_index_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.schema_table_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.schema_table_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.schema_table_statistics_with_buffer' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.schema_tables_with_full_table_scans' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.session' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.statement_analysis' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.statements_with_errors_or_warnings' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.statements_with_full_table_scans' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.statements_with_runtimes_in_95th_percentile' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.statements_with_sorting' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.statements_with_temp_tables' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.user_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_file_io_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_stages' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_statement_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_statement_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.wait_classes_global_by_avg_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.wait_classes_global_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.waits_by_host_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.waits_by_user_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.waits_global_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.x$ps_schema_table_statistics_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.x$schema_table_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.x$schema_table_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.x$schema_table_statistics_with_buffer' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [ERROR] (1356) View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.host_summary_by_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.host_summary_by_file_io_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.host_summary_by_stages' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.host_summary_by_statement_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.host_summary_by_statement_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.innodb_buffer_stats_by_schema' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.innodb_buffer_stats_by_table' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.innodb_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.io_by_thread_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.io_global_by_file_by_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.io_global_by_file_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.io_global_by_wait_by_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.io_global_by_wait_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.latest_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.memory_by_host_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.memory_by_thread_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.memory_by_user_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.memory_global_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.memory_global_total' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.schema_index_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.schema_table_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.schema_table_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.schema_table_statistics_with_buffer' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.schema_tables_with_full_table_scans' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.session' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.statement_analysis' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.statements_with_errors_or_warnings' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.statements_with_full_table_scans' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.statements_with_runtimes_in_95th_percentile' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.statements_with_sorting' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.statements_with_temp_tables' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.user_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.user_summary_by_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.user_summary_by_file_io_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.user_summary_by_stages' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.user_summary_by_statement_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.user_summary_by_statement_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.wait_classes_global_by_avg_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.wait_classes_global_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.waits_by_host_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.waits_by_user_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.waits_global_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.x$ps_schema_table_statistics_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.x$schema_table_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.x$schema_table_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [ERROR] (1356) View 'sys.x$schema_table_statistics_with_buffer' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Dump process encountered error and will not continue.
mysqlpump: [WARNING] (1356) View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_file_io_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_stages' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_statement_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_statement_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.innodb_buffer_stats_by_schema' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.innodb_buffer_stats_by_table' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.innodb_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.io_by_thread_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.io_global_by_file_by_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.io_global_by_file_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.io_global_by_wait_by_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.io_global_by_wait_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.latest_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.memory_by_host_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.memory_by_thread_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.memory_by_user_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.memory_global_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.memory_global_total' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.schema_index_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.schema_table_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.schema_table_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.schema_table_statistics_with_buffer' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.schema_tables_with_full_table_scans' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.session' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.statement_analysis' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.statements_with_errors_or_warnings' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.statements_with_full_table_scans' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.statements_with_runtimes_in_95th_percentile' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.statements_with_sorting' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.statements_with_temp_tables' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.user_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_file_io_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_stages' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_statement_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_statement_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.wait_classes_global_by_avg_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.wait_classes_global_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.waits_by_host_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.waits_by_user_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.waits_global_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.x$ps_schema_table_statistics_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.x$schema_table_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.x$schema_table_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.x$schema_table_statistics_with_buffer' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

何をやらかしたのか分からなかったのですが、どうも MySQL のテーブル互換性の問題のよう。俺は悪くない。

解決法はシンプルで、システムテーブルのアップグレードをするだけ。

こちらのブログを参考にさせてもらいました。
たった1行のブログですが、勇気をもらった気がします。
[FIXED] mysqlpump: [WARNING] (1356) – Biesma.net

言うだけあってシンプルに mysql_upgrade とだけ書いてありますが、実際のところ root ユーザーじゃないと動かないんじゃないでしょうか。
なので、以下のように実行してみました。

mysql_upgrade -uroot -p

テーブルの修復をしている時のような出力（キャプチャし忘れました）が出たあと、正常に終了。
念の為ですが、全てのデータを mysqldump でバックアップを取ってから実行します。
この時点で目的を達しているのでは……。

一体何をしているのか分からなくなってきましたが、その後無事に mysqlpump が通るようになったのでした。

まとめ

今回は mysqldump コマンドの後継ツール mysqlpump をご紹介しました。
進捗表示と圧縮オプションだけでも十分使い勝手が良いのではないかなと思います。

それに加えての並列処理ですからね。
もっと巨大なデータベースをダンプしたいなと思っているのですが、なかなか標的は見つからないですね。

最後に今回お誂え向きに発生したトラブルをご紹介しました。
キレイに解決できたと思っていますが、テーブル修復なので万が一のためにバックアップは忘れずに。

MySQL 5.7.8 以降のバージョンに同梱されているので、ぜひ使ってみてください。

最後に

私が所属するシステム開発のサービスサイト「SEKARAKU Lab（セカラクラボ）」を開設しました。
ビヨンドは、サーバーの設計・構築から運用までをワンストップでお任せいただけますので、サーバーサイド開発でお困りの方はお気軽にお問い合わせください。
SEKARAKU Lab：[https://sekarakulab.beyondjapan.com/](https://sekarakulab.beyondjapan.com/)

以上です。

この記事がお役に立てば【いいね】のご協力をお願いいたします！

読み込み中...