MySQLでデータベースの情報をバックアップ（dump）するときに、ある特定のテーブル（レコード）だけをダンプしたいときがあります。
ここではその方法についてまとめています。
MySQLで複数のテーブルをパターンマッチングでダンプする方法
結論からいうと、以下のコマンドを実行すれば対応可能です。
$ mysql -u <ユーザー名> -h <ホスト名> -p <データベース名> -N -e 'show tables like "パターンマッチング"' | xargs mysqldump -u <ユーザー名> -h <ホスト名> -p <データベース名> --skip-column-statistics > 出力ファイル名.sqlパスワード入力を2回求められるので、それぞれMySQLのパスワードを入力してください。
すると、指定したファイル名「出力ファイル名.sql」のファイルが生成され、指定したテーブルの内容のみが入ります。
なおパターンマッチングの部分は、SQLのLike句で指定します。
| ワイルドカード | 内容 |
|---|---|
| % | 0文字以上の任意の文字列 |
| _ | 任意の1文字 |
例えば、「show tables like “pre_%”」とすれば、テーブル名の冒頭に「pre_」がつくテーブルのみがダンプの対象となります。
何をしているか？
上記で実行したコードは大きく２つの処理に分かれます。
- 指定したパターンにマッチするテーブルのみを抜き出す。
- 対象のテーブルのみをダンプする。
$ mysql -u <ユーザー名> -h <ホスト名> -p <データベース名> -N -e 'show tables like "パターンマッチング"' | xargs mysqldump -u <ユーザー名> -h <ホスト名> -p <データベース名> --skip-column-statistics > 出力ファイル名.sql指定したパターンにマッチするテーブルのみを抜き出す
コマンド
最初の処理は「指定したパターンにマッチするテーブルのみを抜き出す」です。
以下の「mysql」コマンドの部分が該当します。
$ mysql -u <ユーザー名> -h <ホスト名> -p <データベース名> -N -e 'show tables like "パターンマッチング"'オプションが複数あるので、ややこしそうに見えますが、それぞれきちんと意味を持っています。
- 「-u」：ユーザ名を指定するオプション。
- 「-h」：ホスト名を指定するオプション。
- 「-p」：パスワードを指定するオプション。
- 「-N」：表題を非表示にする。
- 「-e」：SQLクエリを投げる。
「-u」「-h」「-p」オプション
「-u」「-h」「-p」はMySQLの対話モードにログインするために必要なオプションになります。
「-e」オプションと合わせてクエリも投げる場合は、「-p」オプションと合わせてパスワードをコマンド内に記述することはできません。
別途入力が必要です。
以下のような警告が発生します。
mysql: [Warning] Using a password on the command line interface can be insecure.「-N」オプション
「-N」オプションはテーブル一覧を表示したときに、1行目に表示される表題を非表示にするオプションです。
例えば「-N」オプションがないときは以下のように表示されます。
+-------------------------------------------+
| Tables_in_DB-Name (prograshi_%) |
+-------------------------------------------+
| prograshi_actionscheduler_actions |
| prograshi_actionscheduler_claims |
| prograshi_actionscheduler_groups |
| prograshi_actionscheduler_logs |
| prograshi_bwt_failed_submissions |
| prograshi_bwt_passed_submissions |
| prograshi_cocoon_accesses |
+-------------------------------------------+この状態では「Tables_in_DB-Name (prograshi_%)」が表示されてしまいます。
これがあると、ダンプを実行したときにエラーになってしまいます。
「-N」オプションがあると、一行目の「Tables_in_DB-Name (prograshi_%)」が以下のように非表示になります。
+-----------------------------------+
| prograshi_actionscheduler_actions |
| prograshi_actionscheduler_claims |
| prograshi_actionscheduler_groups |
| prograshi_actionscheduler_logs |
| prograshi_bwt_failed_submissions |
| prograshi_bwt_passed_submissions |
| prograshi_cocoon_accesses |
+-----------------------------------+「-e」オプション
「-e」オプションを使うとSQLクエリを指定することができます。
「show tables like “パターン”」とすることで、パターンに一致するテーブルのみを抽出することができます。
▼例
以下のようにすると「prograshi_」で始まるテーブルのみを抽出する指定となります。
-e 'show tables like "prograshi_%"'対象のテーブルのみをダンプする
コマンド
欲しいテーブル名のみを抽出することができたら、次はその内容を「mysqldump」コマンドに渡して、ダンプを行います。
| xargs mysqldump -u <ユーザー名> -h <ホスト名> -p <データベース名> --skip-column-statistics > 出力ファイル名.sql| xargs
「処理1 | xargs 処理2」は、処理１の結果を処理２に引数として渡すことができる便利なコマンドです。
ここでは「mysql ~ | xargs mysqldump ~」としているので、mysqlの処理の実行結果が、mysqldumpコマンドの引数に渡されます。
mysqldump
「mysqldump」コマンドは、MySQLをダンプ（dump）するコマンドです。
私たち日本人にはダンプというのが直感的に分かりにくいですが、簡単に言い換えるとバックアップをとるということです。（mysqlbackupコマンドにしてくれればいいのに、、）
mysqlコマンドとは全く別物です。
基本形は以下のようになっています。
mysqldump -u <ユーザー名> -h <ホスト名> -p <データベース名> <テーブル名1> <テーブル名2>,,,, > 出力ファイル名.sql「-u」「-h」「-p」をつけることでMySQLにアクセスできるようにします。
テーブルの指定はオプション
その後ろに「データベース名」と「テーブル名」を引数として渡し、「> 出力ファイル名.sql」とすることで、指定した内容を指定したファイルに書き込みます。
なお、「テーブル名」は必須ではありません。「テーブル名」を記載せずに「データベース名」だけを指定すれば、指定したデータベースの中の全てのテーブルをバックアップします。
対象としたいテーブルの数が１，２個程度ならコマンドに記述するのもありですが、数が多い場合は、前述したコマンドを使って引数として渡すのが便利です。
–skip-column-statistics｜エラー対処法：Couldn’t execute ‘SELECT COLUMN_NAME
mysqldumpコマンドを実行したときに、MySQLのバージョンが8よりも古い場合、以下のようなエラーが発生します。
mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'DB-name' AND TABLE_NAME = 'prograshi_actionscheduler_actions';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)これは、MySQLのv8.0以降に倣って、mysqldumpを実行すると統計情報を管理するテーブル（information_schema.COLUMN_STATISTICS ）を参照しようとしますが、そのようなテーブルが存在しないために発生するエラーです。
MySQL5.7以前の場合特に、この統計情報は不要なので、統計情報をチェックしないようにスキップするオプションが「--skip-column-statistics」です。
このため、MySQLのバージョンが8.0以上の場合は「--skip-column-statistics」は不要です。
> 出力ファイル名.sql
「> 出力ファイル名.sql」とすることで、コマンドを実行したディレクトリに、指定したファイル名のファイルが生成されます。
これがダンプファイル（バックアップ）になります。
実例
コマンドを実行すると、パスワードの入力を2回求められます。
$ mysql -u userName -h mysq.lolipop.lan -p database -N -e 'show tables like "prograshi_%"' | xargs mysqldump -u userName -h mysq.lolipop.lan -p database --skip-column-statistics > dump_prograshi.sql
Enter password:
Enter password:
lsコマンドで確認すると、指定したファイルが生成されていることがわかります。
$ ls
dump_prograshi.sql wp-admin wp-content wp-login.php xmlrpc.php
index.php wp-blog-header.php wp-cron.php wp-mail.php
license.txt wp-comments-post.php wp-includes wp-settings.php
readme.html wp-config-sample.php wp-links-opml.php wp-signup.php
wp-activate.php wp-config.php wp-load.php wp-trackback.phpファイルの中身にバックアップの内容が記載されています。
$ less dump_prograshi.sql
[main 27941b4] [A]MySQL dump
-- MySQL dump 10.13 Distrib 8.0.16, for Linux (x86_64)
--
-- Host: mysq.lolipop.lan Database: database
-- ------------------------------------------------------
-- Server version 5.6.23-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
:dump_prograshi.sql以上でバックアップは完了です。
