サイトをできるだけ止めずにMySQLデータベースの再構築を行う方法

my.cnfでinnodb_file_per_tableを指定せずibdata1ファイルが膨れ上がった時など、後からレコードを削除してもファイルを縮小することができない為、ディスク使用量を減らすためにはデータベースの再構築を行う必要があります。

再構築はmysqldumpでバックアップしておいて、mysql_install_db で初期化した新しいデータベースに流し込むだけです。

全体のサイズが比較的小さい場合は短時間で済むのでメンテナンス時間も短くて済みますが、サイズが大きくなると再構築に時間がかかるようになり夜間メンテの時間だけでは終わらなくなってきます。

サイトを止めている時間を最小にして、サイトにできるだけ負荷を掛けずに再構築する方法を記載しておきます。

とは言えダンプするときと切り替える時で最低2回止める必要がありますので、夜中などアクセスの少ないタイミングで作業しましょう。

今回の対象は下記の場合を対象としています。

• サーバー1台で運用しレプリケーションを取っていない
• 再構築できる程度の空き容量はある
• LVMなどで、スナップショットが取れる環境がない

想定としては大体会員数が数万人規模の比較的小さなサイトが対象です。

手順

手順を簡単に説明すると下記の流れになります。

1. バイナリログを残す
2. mysqldumpでフルダンプする
3. 新データベースの作成
   • 同一サーバー内に別プロセスでmysqldを新しく立ち上げ、ダンプしたデータを新データベースに流しこむ。
4. スレーブ設定
   • 新データベースを旧データベースのスレーブに設定して同期をとる。
5. 新データベースへの切り替え
   • 同期が完了した時点でタイミングを見てサイトを停止し、新データベースに切り替える。

簡単に言うとスレーブサーバーを立てて、同期がとれた時点で一度停止して切り替えるということです。

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}";
done

slow_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とか使うと、止めずに作業も可能かもしれません。
試したことがある方がおられれば是非ご紹介いただければと思います。

参考

MySQLでslave追加時にmasterが全力でbinlogを送って困る時 · As a Futurist...

関連記事

お名前.com レンタルサーバーVPSを借りてみた - 理想未来はどうなった？
ionice -- I/Oスケジューリングクラスおよび優先度を設定／変更する - 理想未来はどうなった？
Linux メモリ解放のおまじない - 理想未来はどうなった？
64bit版のライブラリのインストール先について - 理想未来はどうなった？
メールを高速に配信する方法 - 理想未来はどうなった？