MySQLでテーブルを指定してDUMP


テーブルのDUMPをすべく、mysqldumpコマンドを実行したところ下記のエラーが発生。

mysqldump -u DB_USER -p DB_NAME DB_TABLE_01 DB_TABLE_02 > mysql_dump.sql

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

どうやら、CPI の共用レンタルサーバー「シェアードプラン」では、ホストを指定して実行しないとダメらしい。

ということで、ローカルホストの指定を追加して実行したら無事成功。

mysqldump -h 127.0.0.1 -u DB_USER -p DB_NAME DB_TABLE_01 DB_TABLE_02 > mysql_dump.sql

 


MySQLのWarning(Code 1592)Unsafe statement written to the binary log…BINLOG_FORMAT = STATEMENTの回避方法


MySQLにて、カテゴリなどの階層構造のデータの持ち方を、書籍「SQL アンチパターン」を参考に「経路列挙型」で保持してみたところ、UPDATE にて下記の Warning が発生。

Note (Code 1592): Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.

ブログ用にちょっと書き換えましたが、UPDATE 文の内容はだいたい下記のような内容。

UPDATE category SET 
  category_c_path = (
    SELECT temp_cd.category_c_path FROM (
      SELECT category_c_path FROM category WHERE category_i_id = 1
    ) as temp_cd
  ) || LAST_INSERT_ID() || "/"
WHERE category_i_id = LAST_INSERT_ID();

自分自身のテーブルからデータを取得して UPDATE をかけてるので、マスタとスレーブでデータが異なってしまう場合があるよ、ということらしい。

調べてみたところ、BINLOG_FORMAT を MIXED にすれば、危険な関数を使用した場合には ROW(実際に変更された行データを記録)モードになり、それ以外は STATEMENT(実際に実行されたSQLを記録)モードになるとのこと。

危険な関数は使用してないけど、今回のケースでも適用できるのかな?と疑問に思いつつ試してみる。

ということでまずは、SET SESSION で現在の接続だけを MIXED に変更してテスト。

SET SESSION binlog_format = 'MIXED';

# 設定が MIXED に変わっていることを確認
SHOW SESSION VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+

# 一応、グローバルは STATEMENT のままなのを確認
SHOW GLOBAL VARIABLES LIKE 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

問題の SQL を実行してみて Warning が発生しないことを確認。
問題がなければ、グローバルの設定も変更。

SET GLOBAL binlog_format = 'MIXED';

あとは、再起動後も設定が保持されるように、my.cnf を編集。

vim /etc/my.cnf

[mysqld]
・・・
# レプリケーションフォーマット
binlog_format = MIXED

これにて設定完了。
my.cnf への記述が間違ってないか心配なら、再起動して確認をしておいた方が無難かな。

service mysqld restart
SHOW GLOBAL VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+

「SQLアンチパターン」はテーブル設計の仕方など、かなり参考になる良書なのですが、今回のような環境によって Warning が発生するケースがある場合は、注釈あたりで MIXED にするといいよって書いてあると、より親切だな~と思いました。

参考サイト
MySQL :: MySQL 5.1 リファレンスマニュアル :: 5.1.2.1 レプリケーション フォーマットのセッティング

MySQL – BINLOG_FORMAT 関連のエラーログ! – mk-mode BLOG


crontabでmysqldumpをデータベース毎に実行し毎日自動でバックアップ


データベース毎にMySQLのバックアップを自動で取るために設定した手順を書き記しておきます。

mkdir -pv /home/backup/mysql
chown -R user:user /home/backup

バックアップを保管するためのフォルダを作ります。

mkdir /home/user/cron

ユーザーフォルダに cron 設置用のディレクトリを作ります。

vim /home/user/cron/backup_mysql.sh

mysqldump を行うシェルスクリプトを作成し、下記の内容を書き込みます。

#!/bin/sh

# バックアップファイルを保存するディレクトリ
path='/home/backup/mysql'

# データベース名
db_name=("db_name_01" "db_name_02" "db_name_03")

date=`date +%y%m%d`

date_old=`date --date "7 days ago" +%y%m%d`

for i in ${db_name[@]}
do
  # mysqldump実行
  mysqldump --user=XXX --password=YYY --skip-lock-tables --no-create-db --single-transaction --databases ${i} > ${path}/${i}_${date}.sql

  # パーミッション変更
  chmod 700 ${path}/${i}_${date}.sql

  # 古いバックアップファイルを削除
  rm -f ${path}/${i}_${date_old}.sql
done

mysqldumpのオプションについては下記を参照。
mysqldump — データベースバックアッププログラム

chmod 700 /home/user/cron/backup_mysql.sh

DBのパスワードを記載しているため、パーミッションを絞る。

# 動作確認
/home/user/cron/backup_mysql.sh

# バックアップファイルが作成されているか確認
ls -l /home/backup/mysql
db_name_01_140725.sql
db_name_02_140725.sql
db_name_03_140725.sql

成功していれば、このようなファイルができる。

# crontabファイル
vi /home/user/crontab

「crontab -e」で直接編集するのは危険なため、crontabファイルを作成してそこに下記の内容を書き込む。

# MySQLダンプ
2 3 * * * /home/user/cron/backup_mysql.sh

毎日03:02に実行する。

crontab /home/user/crontab

crontabファイルを反映させる。

crontab -l

反映されていることを確認する。

less /var/log/cron

crontabの実行ログ確認。
※環境により、実行ログの保存ディレクトリは異なります。

mysql -u user -p db_name < /home/backup/mysql/file_name.sql

リストアが必要になった場合はこちら。

参考にしたサイト
mysqldumpとcronでMySQLを自動バックアップする