MySQLでレプリケーションのメモ

まずマスター側でバイナリログの出力と識別IDの設定

MySQLのmy.cnfを設定

    • 識別ＩＤはほかとかぶらないようにする
[mysqld]
log-bin      ←バイナリログの設定
server-id=1  ←識別ＩＤ

MySQLを再起動

スレイブ側の識別IDを設定

[mysqld]
server-id=2  ←識別ＩＤ

マスター側と同じようにmy.cnfを変更したらMySQLを再起動

マスター側にレプリケーションの権限ユーザ作成

スレイブ側からマスター側に接続する時のユーザ

mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user@99.999.99.999 IDENTIFIED BY 'repl_passwd';
                                                       ↑スレイブ側のIPを入れる
Query OK, 0 rows affected (0.01 sec)

マスター側のDBのバックアップ＆スレイブ側にDBを展開

マスター側のMySQLのＴＢＬへの書き込みをロック

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

マスター側のバイナリログの状態をメモ（File,Position）

mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000018 |    17263 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

マスター側ＤＢをバックアップしてロック解除

    • 今回はオフラインバックアップ（バックアップの補足は最下部にメモ）
cd /var/lib/mysql/              ←MySQLのデータディレクトリ
tar cvf ~/xxxxx.tar xxxxx       ←xxxxxＤＢをアーカイブ
mysql> UNLOCK TABLES;           ←MySQLのＴＢＬへの書き込みロックを解除

スレイブ側でアーカイブされたファイルを展開

         ↓マスター側のIPを入力
scp 99.999.99.999:/root/xxxxx.tar ./xxxxx.tar   ←スレイブ側でscpコマンド
cd /var/lib/mysql/                              ←MySQLのデータディレクトリ
※↓もし対象のＤＢが存在するなら消しておいたほうがいいかも
rm -r ./xxxxx
↓スレイブ側のMySQLのデータディレクトリに展開
tar xvf ./xxxxx.tar

レプリケーションの開始

マスターに接続するためのパラメータをスレイブ側に設定する

mysql> CHANGE MASTER TO
        MASTER_HOST='99.999.99.999',          ←マスターのホスト名orIPアドレス
        MASTER_USER='repl_user',              ←マスターで作成したレプリケーション権限ユーザ
        MASTER_PASSWORD='repl_passwd',        ←マスターで作成したレプリケーション権限ユーザのパス
        MASTER_LOG_FILE='mysqld-bin.000018',  ←マスターのバイナリログのFile
        MASTER_LOG_POS=17263;                 ←マスターのバイナリログのPosition

レプリケーションスタート

mysql> START SLAVE;

完了

補足

※注意：下記のような事があるとスレイブからマスタに接続できなかったりするので注意（接続できていないとエラーログに出力される）

    • マスターとスレイブのMySQLで文字コード等の設定が違う
    • 別のDBを参照しマスターを更新などした場合に参照した別DBがスレイブにない

レプリケーションの停止

mysql> STOP SLAVE;

バイナリログの中身を表示

mysql> SHOW BINLOG EVENTS;

スレイブの状態を表示

mysql> SHOW SLAVE STATUS;

DBのバックアップについて簡単にメモ

MySQLデータのバックアップには大きく分けて２つある
１．オフラインバックアップ
オフラインバックアップはMySQLの書き込みをロックもしくはMySQLを停止させた状態でバックアップをとる

  • MySQLの書き込みをロック
# mysql> FLUSH TABLES WITH READ LOCK; ←MySQLの書き込みロック
# /etc/init.d/mysqld stop ←MySQLサーバ停止

対象のＤＢのMySQLデータディレクトリをバックアップする

  • これはcp,rsync,tar,gzip等でバックアップ

MySQLの書き込みロックを解除するもしくはMySQLサーバを開始する
２．オンラインバックアップ
オンラインバックアップはMySQLを止めずにバックアップを行う方法

  • mysqlhotcopy、mysqlsnapshot、mysqldump等の方法がある（下記例文）
# mysqlhotcopy -u ユーザＩＤ -p パスワード データベース名 /tmp
   ↑ISAMテーブルやMyISAMテーブルが対象
# ./mysqlsnapshot -u ユーザＩＤ -p パスワード -s /tmp --split -n 
   ↑すべてのISAMテーブルまたはMyISAMテーブルを、データベースごとに1つのtarファイルにまとめてバックアップ
下記DB全てをバックアップ
# mysqldump -u ユーザＩＤ -p パスワード -x --all-databases > /tmp/mysql.dump ←mysqldumpはすべての種類のMySQLテーブルで使用可
   ↑これが一番メジャーだが時間がかかりファイルサイズがでかくなる
# mysqldump -u ユーザＩＤ -p パスワード -x --all-databases | gzip > / tmp/mysql.dump.gz
   ↑このように出力をパイプしてgzipをかけると多少処理時間とサイズを減らすことができる