ダンプが楽しくなる mysqldump の後継ツール mysqlpump の話
こんにちは。
開発チームのフリー素材担当、まんだいです。
かなり出遅れてますが mysqldump の後継ツールとなる mysqlpump を使ってみたので、まだ使ったことがない人向けにオススメポイントをまとめてみました。
さすが後継というべきか、基本的なオプションの使い方はそのままで mysqldump から移行してきた人にも優しい設計になっていましたので、ぜひ使ってみてほしいと思いました。
MySQL に同梱されているのがポイント高いですよね。
これだけでも乗り換える価値がある、進捗表示
今回試したデータベースはせいぜい 200MB 程度のものなのであまり恩恵はなかったですが、 mysqlpump はダンプの進行具合を逐一表示してくれます。
1 2 3 4 5 | 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 は圧縮した状態で出力するオプションを持っているので、面倒な後処理も必要ありません。
1 | mysqlpump -uroot -p --compress-output=LZ4 --databases xxxxx > xxxxx_20XX0X0X.lz4 |
--compress-output オプションで圧縮フォーマットを選べます。
ただし、対応している形式が LZ4 と openssl zlib というあまり見ない形式のみ。
恥ずかしながら openssl zlib ってなんだよと思い調べてみると zip ファイルのことでしたね。
zip じゃ面白くないので LZ4 で固めてみましたが、今度はどうやって解凍するんだろうとなる訳です。
そこで見つけたのは MySQL のドキュメント。
LZ4 形式は Linux 標準で対応している圧縮形式のようで、解凍コマンドが用意されていました。
1 | lz4_decompress xxxxx_20XX0X0X.lz4 xxxxx_20XX0X0X.sql |
そのまんま。そして長い。
unzip 的な感じでお願いしたかった。
そんなぼやきはさておき、解答してできたファイルを mysql コマンドで叩き込むだけでインポートできます。
1 | mysql -uroot -p < xxxxx_20XX0X0X.sql |
ファイル圧縮といえば気になるのが圧縮率ですが、 LZ4 は圧縮率ではそこまで優位性はないようで、圧縮速度がポイントのようです。
とはいえ、圧縮率もそこまで悪くないようなので mysqlpump を使う時はしばらく LZ4 形式で固めてみようと思っています。
並列処理ができる
mysqldump と大きく違うのが並列処理、という概念。
ただ、ドキュメントを読む限り mysqlpump のダンプ処理を掴めていないと有効に使えなさそうです。
--default-parallelism オプションで立ち上げるスレッド数を指定します。デフォルトは 2 です。
1 2 3 4 | mysqlpump -uroot -p \ --default-parallelism=4 \ --databases xxxxx \ > xxxxx_20XX0X0X.sql |
--parallel-schemas オプションでキューを作り、どのキューにどのテーブルを処理させるかを指定します。
1 2 3 4 | mysqlpump -uroot -p \ --parallel-schemas=xxxxx \ --parallel-schemas=yyyyy \ > xxxxxyyyyy_20XX0X0X.sql |
これで xxxxx テーブルと yyyyy テーブルを別々のスレッドで処理できます。簡単ですね。
さらに --default-parallelism オプションとの合わせ技でキューを処理するスレッド数を指定できます。
1 2 3 4 5 | mysqlpump -uroot -p \ --default-parallelism=4 \ --parallel-schemas=xxxxx \ --parallel-schemas=yyyyy \ > xxxxxyyyyy_20XX0X0X.sql |
上記の例だとキューが2つ作成され、それぞれのキューを4つのスレッドで処理するという意味になります。
テーブル単位でキューを作るのは覚えておいたほうがいい点で、処理量をうまく分散させられれば実行時間の短縮になるんでしょう、きっと。
最初のうちはそんな計算をしてるうちにダンプできちゃうんじゃないかなと思ったりしますが、巨大なテーブルと細かいテーブルたちをそれぞれ別のキューで処理させる、といった感じですね。
--parallel-schemas オプションは起動するスレッド数を同時に指定することもできるので、スレッド数に偏りを持たせることも可能です。
1 2 3 4 | mysqlpump -uroot -p \ --parallel-schemas=2:xxxxx \ --parallel-schemas=4:yyyyy \ > xxxxxyyyyy_20XX0X0X.sql |
このコマンドだと xxxxx テーブルのダンプに2スレッド、 yyyyy テーブルのダンプに4スレッド使用するという意味になります。
ダンプしようとしたら変なエラーが出た
最初に実行した時、訳のわからないエラーが出てテンパったので備忘録として載せておきます。
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 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 | 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 ユーザーじゃないと動かないんじゃないでしょうか。
なので、以下のように実行してみました。
1 | mysql_upgrade -uroot -p |
テーブルの修復をしている時のような出力(キャプチャし忘れました)が出たあと、正常に終了。
念の為ですが、全てのデータを mysqldump でバックアップを取ってから実行します。
この時点で目的を達しているのでは……。
一体何をしているのか分からなくなってきましたが、その後無事に mysqlpump が通るようになったのでした。
まとめ
今回は mysqldump コマンドの後継ツール mysqlpump をご紹介しました。
進捗表示と圧縮オプションだけでも十分使い勝手が良いのではないかなと思います。
それに加えての並列処理ですからね。
もっと巨大なデータベースをダンプしたいなと思っているのですが、なかなか標的は見つからないですね。
最後に今回お誂え向きに発生したトラブルをご紹介しました。
キレイに解決できたと思っていますが、テーブル修復なので万が一のためにバックアップは忘れずに。
MySQL 5.7.8 以降のバージョンに同梱されているので、ぜひ使ってみてください。
最後に
私が所属するシステム開発のサービスサイト「SEKARAKU Lab(セカラク ラボ)」を開設しました。
ビヨンドは、サーバーの設計・構築から運用までをワンストップでお任せいただけますので、サーバーサイド開発でお困りの方はお気軽にお問い合わせください。
SEKARAKU Lab:[https://sekarakulab.beyondjapan.com/](https://sekarakulab.beyondjapan.com/)
以上です。