MySQLのバックアップ

MySQLのバックアップは、「mysqldump」ということで、実行してみたところなぜかエラーが出力されます。

#実行コマンド
mysqldump $dbname --host=$dbhost -u $dbuser --password=$dbpasswd > $dumpfilename

#出力されたエラーメッセージ
-- MySQL dump 10.11
-- ------------------------------------------------------
-- Server version   5.6.11-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 */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)

パラメータが間違っているのかと思い、ひとしきり内容を確認してみるもののなんだか状況は変わらず。ただ、ローカル環境では動作するのにサーバー環境ではエラーになるので、どうやら環境依存のようだ。

インターネットで調べてみると、mysqld(サーバ)と、mysqldump(クライアント)のバージョンが異なることが原因らしい。
サーバが5.6にバージョンアップした際にSET OPTION構文が廃止されたのが原因で、5.6未満のクライアントから接続するとこのような事象になるようです。

そうとわかれば、バージョンアップと行きたいところですが、あいにくレンタルサーバー上で動作しているため新しいバージョンのmysqldumpをインストールしたり、mysqld側の設定変更をするわけにもいかず・・・、とはいえバックアップはとりたいし・・・。(そもそも何だこのサービスと一瞬、思いました。)

レンタルサーバーのサイトには何も書いていないので、通常だとわからないのですが、たまたま最近「SSH接続サービス」を契約したのでちょっと/usr/bin/の中をのぞいてみました。
なるほど「mysqldump-5.6」というクライアントが入っていました。
ついでにいろいろと調べたところ、パラメータも若干変わっているようでしたのでちょっと修正しました。

  • –single-transaction
    処理開始時点のスナップショットからデータを取得することができるようになり、処理途中にデータの更新が入った場合でも一貫性を保持したバックアップを取得することができるようになるようです。
  • –opt
    –quick –add-drop-table –add-locks –extended-insert –lock-tables を指定するのと同じです。ダンプしたデータをMySQL サーバに読み込むための最速ダンプを提供します。(完全におまじない状態ですが、つけた方が良さそうです。)

そんなわけで、最終的なコマンドはこうなりました。パスワードをあわせて実行するとあまりセキュアで無いとのことですが、バックアップはとれているようです。

#実行コマンド
mysqldump-5.6 --single-transaction $dbname --host=$dbhost --user $dbuser --password=$dbpasswd --opt > $dumpfilename

#エラーでは無いけど警告
Warning: Using a password on the command line interface can be insecure.

出力されたファイルを確認すると、それっぽくは出力されているようです。
念のため、phpMyAdminのアプリで出力したデータと比較してみると、mysqldumpで出力したファイルの行数が明らかに少ない。
またもやあたふたしそうになりましたが、mysqldumpで出力したファイルをよく見るとデータのInsert部分が1行で出力されています。
そんなわけで、最終的なスクリプトはこんな感じになりました。
よかったよかった。

#!/bin/bash

dumpdir='ダンプのディレクトリ'
dumpdate=`date +%Y%m%d`
dumpfilename=ダンプのファイル名

dbname='データベース名'
dbhost='データベースのホスト名'
dbuser='データベースのユーザ名'
dbpasswd='データベースユーザのパスワード'

compress_ext='.tar.gz'

# データベースのバックアップ処理
mysqldump-5.6 --single-transaction $dbname --host=$dbhost --user $dbuser --password=$dbpasswd --opt > $dumpdir$dumpfilename

# バックアップファイルの圧縮処理
pushd .
cd $dumpdir
tar zcvf $dumpfilename$compress_ext $dumpfilename
rm $dumpdir$dumpfilename
popd