はじめに
本稿ではmysqldumpを使ったMySQLのDBバックアップの取得と、開発環境(PC)上のMySQLでは”mysqldump: Error: Binlogging on server not active”というエラーに遭遇したのでその二つの記録を残しておきます。
mysqldumpでDBバックアップ
とにもかくにも、実行例です。
mysqldump --quote-names --skip-lock-tables --single-transaction --master-data=2 -u ${DB_USER} -p${DB_PASSWORD} -h ${DB_SERVER} ${DB_NAME} > ${BKUP_DIR}/mysql_${DATE}.dump
それぞれのオプションの意味を以下に記載しますが、まず真っ先に知っておくこととして、--optオプションは指定もしていないのに勝手に有効になっています。なので、その動作が気に入らない場合は、それを否定するオプションを指定する必要があります。
| オプション | 説明 |
|---|---|
| –opt | --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charsetと同じです。 |
| –add-drop-table | DROP DATABASE ステートメントを CREATE DATABASE ステートメントの前に追加します。バックアップ中の動作では無く、リストア時の動作を決めています。 |
| –add-locks | 各テーブルへのINSERT文の前後にLOCK_TABLES文とUNLOCK TABLES文が含まれます。バックアップ中の動作では無く、リストア時の動作を決めています。テーブルをロックすることと引き換えに、ロード(Insert)の速度は向上します。 |
| –create-options | CREATE TABLE文にMySQL特有のオプションを含めます。元に戻すことを目的にバックアップを取得する場合は通常付けると思います。 |
| –disable-keys | 各テーブルについて、全てのレコードのロード(Insert)が完了するまでインデックスを作らないようにします。ロード(Insert)の速度向上が期待できますが、MyISAMテーブルの(UNIQUEではない)通常のインデックスにしか効果がありません。つまりInnoDBなら役には立っていませんが、邪魔にもなっていないという感じです。 |
| –extended_insert | INSERT文をコンパクトな書式でダンプします。ダンプファイルのサイズが小さくなり、ロード(Insert)の時間も短くなります。 |
| –lock-tables | ダンプの前にDBの全テーブルをロックします。ロックはDBごとに行われるのでDB間でのデータ整合性は保証されません。ですが、InnoDBの場合は--single-transactionを使うことでこのオプションで狙っている効果が得られます。 |
| –skip-lock-tables | –lock-tablesオプションを無効にします。–optが–lock-tablesを有効にするので、それを打ち消す為です。 |
| –quick | ダンプ時にテーブルの全レコードをメモリに一旦バッファする代わりに、サーバーから 1 行ずつ行を取得することを強制します。データ量の大きなテーブルがある場合に有用です。(あまりオプションの英単語と動作に関連性がありませんね…) |
| –set-charset | 出力に SET NAMES を追加します。 |
| –quote-names | 識別子 (データベース、テーブル、およびカラム名など) を 「`」 文字で囲みます。 |
| –single-transaction | ダンプ処理をトランザクションで囲みます。InnoDBの場合、アプリケーションをブロックすることなく、START TRANSACTION が発行された時点のデータベースの一貫した状態をダンプできます。MyISAMテーブルが含まれるDBでは意味が無いので、代わりに–lock-tablesか–lock-all-tablesを使います。 |
| –flush-logs | ダンプを始める前に MySQL サーバーログファイルをフラッシュします。 |
| –lock-all-tables | ダンプの開始から完了まで、全データベースの全テーブルをロックします。マニュアルの日本語訳ではこうは読み取れませんが”Lock all tables across all databases.”とかいてあるので、”全データベースの…”です。これを使うと自動的に--single-transactionと--lock-tablesオプションはオフになります。 |
| –master-data[=value] | 単純にバックアップをとりたい場合は2，レプリケーションを構成して、という場合は1を指定します。1を指定すると、2の場合にコメントになっているステートメントが有効になっています。知識があれば2の場合でも1相当に編集可能ということですね。 |
| –password | MySQLサーバに接続する時のパスワード |
| –host | 与えられたホスト上の MySQL サーバーからデータをダンプします。 |
mysqldump: Error: Binlogging on server not active
MacBookPro:~ $ mysqldump --quote-names --skip-lock-tables --single-transaction --master-data=2 -u ${DB_USER} -p${DB_PASSWORD} -h ${DB_SERVER} ${DB_NAME} > ${BKUP_DIR}/mysql.${DATE}.dump
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: Binlogging on server not active
MacBookPro:~ $
エラーの内容としてはバイナリログがアクティブではないのでダメだと言っているようです。Ubuntuの場合ですが、/etc/mysql/mysql.conf.d/mysqld.cnfに関連する設定があります。
#log_bin = /var/log/mysql/mysql-bin.log
これのコメントアウトを外してMySQLを再起動すれば良いのですが、、、
私の場合はここで起動に失敗するという事態が発生しました。
#server-id = 1
log_binを有効にするには、サーバーIDなるものが指定されている必要があります。複数のDBサーバー間でレプリケーションを実施する場合などは、それぞれに1，2などとIDを振って区別する必要があるのですが、単にバックアップをとるためには、log_binを指定し、そのためにserver-idの指定が必要という格好です。
これらを指定すると、無事にMySQLの再起動が可能となり、mysqldumpの実行も可能となります。
mysqldump: [Warning] Using a password on the command line interface can be insecure.
最後にもう一つ。そもそもエラー遭遇時にコマンドラインにパスワードを指定するとセキュリティが云々と指摘されていますが、これは次のファイルを準備して--defaults-extra-fileで指定すると警告が消えます。
[client] user = db_user password = "password"
ですが、ホントにコマンドラインで直接指定しているなら少しセキュリティが落ちています(コマンドの履歴に残ったりする)が、.bashrcに書くよりconfファイルに書いたらセキュリティが上がるってことはないので、分かっていて無視する分には問題は無いと思います。