諸般の事情でMySQLのバイナリログをちゃんととっておかないと・・・という場面に遭遇してしまった。本来開発がメインだからセキュリティとか設定とかそういうのはやらないことにしているのだけれど、事情があってやむを得ず・・・。
MySQLはデフォルトではバイナリログを保存する設定にはなっていない。取得するための設定は/etc/my.cnfの[mysqld]セクションで以下の記述をして、MySQLを再起動。
log-bin=/home/binarylog/mysql-bin
ログを見るにはmysqlbinlogコマンドを使えばいい。
SQLだけ吐き出すにはこんな感じ（らしい）。
mysqlbinlog -D -uroot -pxxxxxxx -database=”db_name” /backup/mysql-bin.000001 > /backup/bin.sql
まぁ、使わずにすむものなら使いたくない。
共用サーバも自動で毎日バックアップを取ることにした。
今回クライアントに提供しているサーバ（VPS）のバックアップを外部サーバに自動バックアップをとるように設定したのだが、その理由は2つ。
過去に何度となくトラブルに遭遇し、その都度痛い目を見ているので、今回はすごく真面目に取り組んでいる（気付くのが遅い・・・）。そしてこれを機に運用中の共用サーバもバックアップをとることにしたというわけ（サーバの停止はそのまま収入の減少にもつながるので）。
まずサーバでバックアップ用のコマンドが使えるかどうかチェック。PHPが使えるのでsystem関数を使ってパスが通っているかどうかをチェックする。
system(‘which mysqldump’);
system(‘which tar’);
system(‘which bash’);
system(‘which gzip’);
system(‘which ssh’);
system(‘which ftp’);
system(‘which expect’);
var_dump(__FILE__);
コマンドがあればパスを吐き出してくれる。うん、うん、だいたい使えそう。
MySQLはmysqldumpで吐き出す。この時、最初は-xオプションでロックをかけようとしていたのだけれど、それだとdumpできなかった。まぁ、共用サーバだからしょうがない。-xオプション無しでdump。そしてそれをパイプに渡してgzip。これでOKだ。現在使っている共用サーバは複数のMySQLデータベースを使用できるのだが、それらのユーザ情報は共通にしてあるので、–all-databasesオプションを使えば一発で全てのDBをdumpできる。
次にファイル。HTMLとかPHPとか画像とか。これはtarで固めてgzip。ただしバックアップファイルの保存先を上手く指定してやらないといけない。あとメールデータとかゴミとか不要なのはデータ領域の無駄なのでバックアップしない。そのためシェルスクリプトを組んで、バックアップをとるデータのリストを作成し、それを引数としてtarに渡すことにした。これでファイルのバックアップもOK。
上記の二つはcronで1日1回処理。これを外部サーバに転送する。上記二つの処理は一つのシェルスクリプト内で記述しているのでそこで完結させたいのだが、外部サーバのログイン情報を共有サーバに保存しておいておくのはとてもイヤ。だからやむなく外部サーバから共有サーバをつつくことになる。今回はFTPにした（しかも非暗号化・・・）。FTPSでコマンドラインでのアクセス方法がわからなかったので仕方なかった。
もうひとつの方法として、共有サーバで圧縮したファイルの保存先を共有SSL領域にしておいて、そこに対して外部サーバからwgetというのも考えた。これでベーシック認証かけてIPアドレスではじいておけば、こちらのほうがベターかも知れない。それはそれで今後の課題とする。
何はともあれ、root権有りサーバも、root権無しサーバも、自動バックアップが出来るようになった。あとは・・・もうちょっと手を加えれば完了。
MySQLのバックアップも自動化してしまいたい。ということで、まずbackupユーザに権限を与え、パスワードなしでログイン出来るようにする。ちょっと危険なような気もするが・・・。MySQLのバックアップには、全ての特権を与える必要はない。今回は以下のように特権を与えた。
grant FILE, SELECT, LOCK TABLES, SHOW VIEW, RELOAD on *.* to backup@localhost;
localhostからのアクセスだけ。ということでご容赦願おう。でもって、実際のバックアップコマンドはこちら。
mysqldump -u backup -x –all-databases > all.dump.sql
-xオプションは全データベースをロックという意味。
実際には、パイプ処理でtarに渡し、さらにgzipして・・・最後はリモートサーバへsshで転送。
うっかりしていた。すごくうっかりしていた。
MySQLのtext型って65535文字しか入らないことを失念していた。PostgreSQLのtext型は制限無し可変長文字列だったので、それと勘違い。そして設計も甘かったかもしれない。入力される文字数を考慮していなかった（上限のことも失念していたし、上限を越えることはもっと想定していなかった）。データが途中で切れていた。
とりあえずmediumtext型に変換・・・。
MySQLからデータをダンプする便利なコマンドmysqldump。テーブルがMyISAMなら問題ないけれど、innoDBなどトランザクション処理が絡む場合、単にmysqldump処理するとエラーメッセージが表示されてdumpできない。
Got error: 1044: Access denied for user ‘user’@’localhost’ to database ‘mydb’ when using LOCK TABLES
テーブルのロックがどうこうというエラーだ。中途半端にデータがinsert等された状態でdumpされたデータだと、復元した際にデータに矛盾が生じるかもしれないから事前にロックしようとしたけどできなかったよ、ということだろう。ネットを探しているとオプションをつけてしのぐ方法が書かれている。
mysqldump –skip-lock-tables mydb > mydb.sql
上記の「–skip-lock-tables」というオプションを紹介しているブログがやたらと出てきた。しかしこれ、そもそも根本的じゃないでしょうという感じ。ロックしないんだからデータに矛盾が生じそう。つまり使えるかどうかもわからないバックアップをしていることになってしまう（個人ユースならそれでもいいんだろうけど）。
mysqldump –single-transaction mydb > mydb.sql
マニュアルにはこういう記述があった。「–single-transaction」というオプションだ。このオプションだとテーブルをロックせず、トランザクションの範囲でバックアップしてくれるとのことだった。マニュアルにも「–lock-tablesより全然良い」と書いてある。これを使うことにしよう。