MySQL SSL replication on CentOS5

※this text from livedoor blog 2011/7/25

SSHトンネリングを利用してマスターとスレーブ間をSSLで接続する
バイナリログが転送時に壊れるケースを防ぐ
参考） http://nippondanji.blogspot.com/2009/03/mysql10.html

slave

sudo su mysql

公開鍵作成

ssh-keygen -t rsa
cd /var/lib/mysql/
cat .ssh/id_rsa.pub
XXXXXXXXXXXXXXXXXXXXXXXXXXX

master
slaveのid_rsa.pubをauthorized_keysに書き込み

sudo su mysql
cd /var/lib/mysql/
mkdir -p .ssh
emacs .ssh/authorized_keys
XXXXXXXXXXXXXXXXXXXXXXXXXXX
chmod -R 700 .ssh

slaveアカウントの作成

/usr/bin/mysql -uUSER -pPASS
GRANT REPLICATION SLAVE ON *.* TO slave@XX.XX.XX.XX IDENTIFIED BY 'PASS';
FLUSH PRIVILEGES;

mysqldumpでバックアップデータを作成

/usr/bin/mysqldump -uUSER -pPASS --single-transaction --master-data=2 --all-databases | gzip >  20110725.sql.gz

mysqldumpバックアップデータをslaveへ転送

scp -l 8000 20110725.sql.gz slave@XX.XX.XX.XX:/home/slave/

※上記の例ではscp -l 8000として1000KB/秒で転送している

slave
server-id設定
masterと違うidを設定する

/usr/bin/mysql -uUSER -pPASS
mysql> SET GLOBAL server_id = 101;
mysql> SHOW GLOBAL VARIABLES like '%server_id%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 101   |
+---------------+-------+
1 row in set (0.01 sec)

master(IP XXX.XXX.XXX.XXX)のポート3306をスレーブの3307にマッピング

ssh -f XXX.XXX.XXX.XXX -L 3307:localhost:3306 -N -4

master MySQLへの接続確認

/usr/bin/mysql -uUSER -pPASS -h127.0.0.1 -P3307

mysqldump データインポート

gunzip /home/slave/20110725.sql.gz
/usr/bin/mysql -uUSER -pPASS < /home/slave/20110725.sql

MASTER_LOG_FILEと、MASTER_LOG_POSをチェック

$ head -100 20110725.sql | grep CHANGE
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000024', MASTER_LOG_POS=968653647;

slaveのレプリケーション設定とslave開始

/usr/bin/mysql -uUSER -pPASS
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO
           MASTER_HOST='127.0.0.1',
           MASTER_PORT=3307,
           MASTER_USER='USER',
           MASTER_PASSWORD='PASS',
           MASTER_LOG_FILE='mysql-bin.000024',
           MASTER_LOG_POS=968653647,
           MASTER_CONNECT_RETRY=10;

mysql> START SLAVE;

master
Position確認

mysql> SHOW MASTER STATUS\G

slave
Position確認

mysql> SHOW SLAVE STATUS\G

master & slave

110725 14:05:08 [ERROR] Invalid (old?) table or database name '.emacs.d'
110725 14:05:08 [ERROR] Invalid (old?) table or database name '.ssh'

MySQLのエラーログにデータディレクトリでは無いディレクトリのエラーが出力されるので、ファイルを退避しておく

cp -r /var/lib/mysql/.ssh /XXX/XXX
cp -r /var/lib/mysql/.emacs /XXX/XXX
rm -r /var/lib/mysql/.ssh
rm -r /var/lib/mysql/.emacs