サイトをできるだけ止めずにMySQLデータベースの再構築を行う方法
my.cnfでinnodb_file_per_tableを指定せずibdata1ファイルが膨れ上がった時など、後からレコードを削除してもファイルを縮小することができない為、ディスク使用量を減らすためにはデータベースの再構築を行う必要があります。
再構築はmysqldumpでバックアップしておいて、mysql_install_db で初期化した新しいデータベースに流し込むだけです。
全体のサイズが比較的小さい場合は短時間で済むのでメンテナンス時間も短くて済みますが、サイズが大きくなると再構築に時間がかかるようになり夜間メンテの時間だけでは終わらなくなってきます。
サイトを止めている時間を最小にして、サイトにできるだけ負荷を掛けずに再構築する方法を記載しておきます。
とは言えダンプするときと切り替える時で最低2回止める必要がありますので、夜中などアクセスの少ないタイミングで作業しましょう。
今回の対象は下記の場合を対象としています。
- サーバー1台で運用しレプリケーションを取っていない
- 再構築できる程度の空き容量はある
- LVMなどで、スナップショットが取れる環境がない
想定としては大体会員数が数万人規模の比較的小さなサイトが対象です。
手順
手順を簡単に説明すると下記の流れになります。
- バイナリログを残す
- mysqldumpでフルダンプする
- 新データベースの作成
- 同一サーバー内に別プロセスでmysqldを新しく立ち上げ、ダンプしたデータを新データベースに流しこむ。
- スレーブ設定
- 新データベースを旧データベースのスレーブに設定して同期をとる。
- 新データベースへの切り替え
- 同期が完了した時点でタイミングを見てサイトを停止し、新データベースに切り替える。
簡単に言うとスレーブサーバーを立てて、同期がとれた時点で一度停止して切り替えるということです。
Step.1 バイナリログを残す
まず、レプリケーションのためにバイナリーログを残します。
/etc/my.cnfを編集し設定を行います。
[mysqld] server-id=1 log-bin=/var/lib/mysql/binary.log expire_logs_days=2
mysqldを再起動します。
service restart mysql
Step.2 mysqldumpでフルダンプする。
次にフルダンプを行います。
データベースがロックされるので、ここだけは確実に停止します。
mysqldump --all-databases --flush-logs --master-data --add-drop-table --extended-insert --add-locks -uUSERNAME -pPASSWORD -h127.0.0.1 -x > mysqldump.sql
ダンプが終わったあとは切り替えるまで運用可能です。
Step.3 新データベースの作成
次に今稼動しているデータベースとは別に新データベース用にデータ ディレクトリを初期化します。
今回の例では稼働中のデータが/var/lib/mysqlにあるものとして、同一パーティションの/var/lib/mysql_newに作成します。
mysql_install_db --datadir=/var/lib/mysql_new --user=mysql
既存のmy.cnfをコピーし新データベース用の設定ファイルを作成します。
cp -p /etc/my.cnf /etc/my_new.cnf
/etc/my_new.cnfの中身を編集します。
以下に編集箇所を抜粋します。
[client] port=3307 socket=/var/lib/mysql_new/mysql.sock [mysqld] port=3307 datadir=/var/lib/mysql_new socket=/var/lib/mysql_new/mysql.sock server-id=2 pid-file=/var/run/mysqld/mysqld_new.pid [mysqld_safe] log-error=/var/log/mysqld_new.log pid-file=/var/run/mysqld/mysqld_new.pid
ポートはデフォルトでは3306ですが、重複しないように3307を使用しています。
datadirは新データベース用の/var/lib/mysql_newに、server-idは別のidに変えておきます。
socket、pid-fileもそれぞれ変更します。
別プロセスでmysqlを立ち上げます。
ioniceとniceを使用してプライオリティを下げておきます。
nohupとniceはmysqld_safeで行います。
ionice -c3 mysqld_safe --defaults-file=/etc/my_new.cnf --nice=19 >/dev/null 2>&1 &
ダンプしたSQLを流しこみます。
この時もioniceとniceでプライオリティを下げておきます。
cd /var/tmp nohup ionice -c3 nice -n 19 mysql -uroot -h 127.0.0.1 -P 3307 < mysqldump.sql >/dev/null 2>&1 &
SQLの実行が完了するまで暫く待ちます。
Step.4 スレーブ設定
ダンプしたSQLを流し込んだあと、旧データベースから新データベースへのスレーブ設定を行い同期をとります。
まずは、ポジションを調べておきます。
コマンド
head -n 100 mysqldump.sql |grep 'CHANGE MASTER TO'
レスポンス
CHANGE MASTER TO MASTER_LOG_FILE='binary.000175', MASTER_LOG_POS=106;
ゆっくり同期するためのバッチスクリプト slow_repl.shを作成します。
#!/bin/bash
MASTER_HOST=127.0.0.1
MASTER_PORT=3306
MASTER_USER=XXXXXXXX
MASTER_PASSWORD=XXXXXXXX
MASTER_LOG_FILE=binary.000175
MASTER_LOG_POS=106
SLAVE_HOST=127.0.0.1
SLAVE_PORT=3307
SLAVE_USER=XXXXXXXX
SLAVE_PASSWORD=XXXXXXXX
OPTIONS="-u ${SLAVE_USER} -p${SLAVE_PASSWORD} -h ${SLAVE_HOST} -P ${SLAVE_PORT}"
mysql $OPTIONS -e "
CHANGE MASTER TO
MASTER_HOST='${MASTER_HOST}',
MASTER_PORT=${MASTER_PORT},
MASTER_USER='${MASTER_USER}',
MASTER_PASSWORD='${MASTER_PASSWORD}',
MASTER_LOG_FILE='${MASTER_LOG_FILE}',
MASTER_LOG_POS=${MASTER_LOG_POS}"
echo "`date` START SLAVE"; mysql $OPTIONS -e "START SLAVE";
sleep 1;
BEHIND=`mysql $OPTIONS -e "show slave status\G" |grep Seconds_Behind_Master|cut -f 2 -d ':'`
while [ ${BEHIND} -gt 0 ]
do
echo "`date` STOP SLAVE"; mysql $OPTIONS -e "STOP SLAVE IO_THREAD";
sleep 5;
echo "`date` START SLAVE"; mysql $OPTIONS -e "START SLAVE IO_THREAD";
sleep 1;
BEHIND=`mysql $OPTIONS -e "show slave status\G" |grep Seconds_Behind_Master|cut -f 2 -d ':'`
echo "`date` Seconds_Behind_Master:${BEHIND}";
doneslow_repl.shを実行し同期します。
chmod +x slow_repl.sh ./slow_repl.sh
同期が追いつくまで暫く待ちます。
Step.5 新データベースへの切り替え
mysqldを停止し、新しいデータ ディレクトリに切り替え、mysqldを開始します。
以下のスクリプトに適当な名前(change_new.shなど)を付けて保存します。
#!/bin/bash service stop mysql sleep 3 mysqladmin --defaults-file=/etc/my_new.cnf shutdown mv /var/lib/mysql /var/lib/mysql_old mv /var/lib/mysql_new /var/lib/mysql service start mysql
スクリプトを実行します。
chmod +x change_new.sh ./change_new.sh
以上、そんな感じで。
P.S.
Percona Xtra Backupとか使うと、止めずに作業も可能かもしれません。
試したことがある方がおられれば是非ご紹介いただければと思います。
