mysqldumpを使ったユーザ情報を含むバックアップの取り方

1. MySQLでのダンプ

MySQLサーバを移行したりするときに、エクスポート、インポートの作業が必要になりますね。
phpMyAdminやGUIのツールを使ってエクスポート、バックアップをする事もあるんだけれど、諸般の理由によってCUIで操作する事が必要、という場合もありますね。

諸般の理由
phpMyAdminはwebサーバとPHPで動いているため、それらの制約を受けます。具体的にはmax_excecution_timeやpost_max_sizeといった時間やサイズの制限によって、小規模では無いMySQLサーバではバックアップをしようとしてもタイムアウトになってしまいます。

僕はGUIのツールとして、Oracle社の作っているMySQL WordBenchというのを使っていますが、このツールのエクスポート機能は実はCUIのコマンドを利用していたりします。
つまり最も初歩的ながら、実は最も基本になっているCUIのMySQLダンプツールを使います。

ちなみにSQLサーバの内容を書き出す操作について、「吐き出させる」という意味のダンプを用います。
バックアップ用途であればバックアップ、他のサーバ、他のSQLサービスへ入力するためであればエクスポートと呼ばれますが、ここではダンプとします。

  1. CUIツールでの利用
    MySQLではCUI,コマンドラインで以下のようにしてダンプを行います。

    shell> mysqldump --all-databases
    -- MySQL dump 10.13  Distrib 5.6.21, for osx10.10 (x86_64)
    --
    -- Host: 127.0.0.1    Database:
    -- ------------------------------------------------------
    -- Server version       5.1.72
    
    /*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
    /*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
    /*!40101 SET @[email protected]@COLLATION_CONNE
    ... 後略
    

    このときmysqldumpコマンドに付け加えることのできるオプションは、CUIでのmysqlコマンドに似ています。

例えばホストを指定するのであれば次のようにします。

shell> mysqldump -h mysql.example.com --all-databases

IPアドレス指定であれば下記のようになります。

shell> mysqldump -h 192.0.2.1 --all-databases

何も指定していなければ設定に応じて/tmp/mysql.sock等をつかってソケット通信しますが、ソケット通信を許可していない場合にはエラーとなります。
ソケット通信

shell> mysqldump --all-databases
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connec

この場合には恐らく、IPアドレスのみを許容していると思いますのでそちらで接続しましょう。

shell> mysqldump -h 127.0.0.1 --all-databases

権限などのユーザ認証が必要な場合もあります。

shell> mysqldump --all-databases
mysqldump: Got error: 1045: Access denied for user 'yousan'@'localhost' (using password: NO) when trying to connect

その際にもmysqlコマンドと同じように指定することができます。

shell> mysqldump --all-databases -u user -p
Enter password:

推奨されませんが、mysqlコマンドと同様にパスワードをワンライナーで入れ込む事ができます。

shell> mysqldump --all-databases -u user -ppassword

上記のパスワードをコマンドに入れ込むやり方は、ダンプしている最中psコマンドなどでパスワードが見えてしまいます。またhistoryを設定している場合にはコマンド履歴に残ります。
セキュリティリスクを考慮して使ってください。

権限に対して、一般ユーザを指定した覚えが無いし、うまくいかないと言う場合には、スーパーユーザを利用するとうまくいったりします。

shell> sudo mysqldump

ダンプには最低一つ以上のデータベース(スキーマ)を指定します。

shell> mysqldump hogedatabase

すべてのデータベースを示す –all-databases、もしくは一つ以上のデータベースを指定しないとヘルプが出てきます。

shell> mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

出力結果はデフォルトでは標準出力に出てきます。標準出力に出されても使いにくいのでファイルに保管しておきましょう。

shell> mysqldump --all-databases > ~/tmp/dump.sql

出力されるsqlファイルはテキストファイルなので、圧縮効率がいいです。

shell> mysqldump --all-databases > ~/tmp/dump.sql
shell> gzip ~/tmp/dump.sql
shell> ls -lath
total 266800
-rw-r--r--   1 yousan  staff    23M Oct 24 15:07 dump.sql.gz
-rw-r--r--   1 yousan  staff   107M Oct 24 14:42 dump.sql

慣れるとワンライナー(一行)で圧縮できます。

sell> mysqldump --all-databases | gzip -c > dump.sql.gz

さて以上が基本的な使い方となりました。
次の章では実際に利用する際に僕が利用している使い方を紹介します。

  1. 実際のダンプ方法
    shell> mysqldump --all-databases > /tmp/dump.sql
    shell> mysqldump --allow-keywords mysql > /tmp/mysql.sql
    

    上の行でデータベース自体のダンプを行っています。

でもmysqlスキーマはパスワードを含んでいるのでこれだけだとうまく復元できない。だから二行目でパスワードとかそれっぽいやつらを個別にきれいに抜き出してやる。リストアするときはダメだった一行目のダンプの上にちゃんとできた二行目のダンプで上書きしてやる。

#リストア
mysql < /tmp/dump.sql
mysql < /tmp/mysql.sql

コメントを残す