MySQLバックアップとリストアの概要
MySQLデータベースのバックアップとリストアは、可用性と信頼性を確保するための重要な運用タスクです。mysqldumpとPercona XtraBackupは、それぞれ論理バックアップと物理バックアップを実現する代表的なツールです。
ロック動作の比較
両ツールともバックアップ中にロックを発生させますが、適切なパラメータ設定によりロック時間を最小化できます。
mysqldumpのロック挙動
--optオプションは指定データベースの全テーブルをロック
--master-data=1または2はデフォルトで全データベースのテーブルをロック
--single-transactionオプションでトランザクションを利用しロック時間を短縮
general_logを分析すると、single-transaction使用時はSTART TRANSACTION WITH CONSISTENT SNAPSHOTで一貫性を確保し、非使用時はテーブルロックで一貫性を確保する違いが確認できます。
XtraBackupのロック挙動
バックアップ中に短時間のグローバルリードロック(FTWL)を発生
InnoDBストレージエンジンに最適化
MyISAMテーブルが多い環境ではロック時間が長くなる可能性
XtraBackupの内部動作
MySQL 5.6ベースのXtraBackupは、/tmp/xtrabackup_suspendedファイルを使用して内部通信を行います。バックアップ中に/tmpディレクトリの権限変更があると、通信不能となりプロセスがハングする可能性があります。
mysqldumpを用いたレプリケーション構築
バックアップ手順
# 業務データベースのバックアップ
mysqldump -uroot -p example_db --master-data=2 --single-transaction --triggers --routines --events > /backup/dump.sql
# ユーザ権限のバックアップ
mysqldump -u root -p mysql user > /backup/mysql_user.sql
リストアとレプリケーション設定
# 権限テーブルのリストア
mysql> source /backup/mysql_user.sql
# 業務データのリストア
mysql> use example_db;
mysql> source /backup/dump.sql
# バイナリログ位置情報の確認
cat /backup/dump.sql | grep "CHANGE MASTER TO"
バイナリログベースのレプリケーション設定
RESET SLAVE ALL;
RESET MASTER;
CHANGE MASTER TO
MASTER_HOST='192.168.1.100',
MASTER_PORT=3306,
MASTER_USER='repl_user',
MASTER_PASSWORD='repl_password',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=1204;
START SLAVE;
SHOW SLAVE STATUS \G
GTIDベースのレプリケーション設定
RESET SLAVE ALL;
RESET MASTER;
SET @ORIG_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN = 0;
SET @@GLOBAL.GTID_PURGED='identifier-set';
SET @@SESSION.SQL_LOG_BIN = @ORIG_LOG_BIN;
CHANGE MASTER TO
MASTER_HOST='192.168.1.100',
MASTER_USER='repl_user',
MASTER_PASSWORD='repl_password',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
START SLAVE;
SHOW SLAVE STATUS \G;
XtraBackupを用いたレプリケーション構築
リモートバックアップの実施
# SSH鍵認証の設定
ssh-keygen -t rsa
ssh-copy-id mysql@backup-host
# リモートストリームバックアップ
innobackupex --defaults-file=/etc/my.cnf --parallel=4 --user=backup_user \
--password='secure_pass' --socket=/tmp/mysql.sock --stream=xbstream \
/mysql/backup | ssh mysql@backup-host "xbstream -x -C /mysql/restore"
リストアプロセス
# ログの適用
innobackupex --apply-log /mysql/restore
# データの復元
innobackupex --move-back /mysql/restore
# 権限設定
chown -R mysql:mysql /var/lib/mysql
# データベース起動
mysqld_safe --defaults-file=/etc/my.cnf &
レプリケーション設定
# バックアップ情報の確認
cat /mysql/restore/xtrabackup_info
# GTID情報の設定
SET @@GLOBAL.GTID_PURGED='backup-gtid-set';
# レプリケーション設定
CHANGE MASTER TO
MASTER_HOST='primary-host',
MASTER_USER='repl_user',
MASTER_PASSWORD='repl_pass',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
START SLAVE;
双方向レプリケーションの構成
# プライマリでのレプリケーション設定
CHANGE MASTER TO
MASTER_HOST='replica-host',
MASTER_USER='repl_user',
MASTER_PASSWORD='repl_pass',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
START SLAVE;