MySQLのレプリケーションを用いてマスターサーバーを無停止でバックアップする

目的

MySQLのレプリケーションを用いてマスターサーバーを無停止でバックアップする🐤

環境

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.27, for debian-linux-gnu (x86_64) using  EditLine wrapper
$ cat /etc/issue
Ubuntu 14.04.3 LTS \n \l

手順

複製したMySQLが接続できるか確認

バックアップサーバーでMySQLを起動

$ mysqld_safe --defaults-file=/var/lib/mysql-3307/my.cnf \
 --datadir=/var/lib/mysql-3307 --port=3307 &

バックアップサーバーでMySQL接続できるか確認

$ mysql -u root -S /var/lib/mysql-3307/mysqld.sock

マスターサーバー疎通確認

バックアップサーバーでMySQLがつながるか確認

$ mysql -hMASTERSERVERIP -urepluser -p

つながらない場合、マスターサーバーでファイアウォールを開けるように設定

$ sudo ufw allow proto tcp from MASTERSERVERIP/24 to any port 3306 

マスターサーバーでファイアウォールの設定が追加されていることを確認

$ sudo ufw status

マスターサーバーでレプリケーション用のユーザーを作成

$ mysql -u root -p

mysql > grant replication slave on *.* to 'repluser'@'MASTERSERVERIP'
 identified by 'REPLUSERPASS';

マスターサーバーでmy.cnfを編集

$ sudo vim /etc/mysql/my.cnf

マスターサーバーでserver-idを設定、bind-addressをコメントアウト

# /etc/mysql/my.cnf

[mysqld]
server-id = 3306
log-bin = mysql-bin

#bind-address    = 127.0.0.1

設定を反映

$ sudo service mysql restart

マスターサーバーサーバーでmysqldumpする

$ mysqldump -u root -p --all-databases --routines \
--master-data=2 --single-transaction  | gzip > /home/technuma/mysql-3307.sql.gz

マスターサーバーサーバーでバックアップサーバーにscpする

$ scp -P 19868 mysql-3307.sql.gz technuma@PASSWORD:/home/technuma/

バックアップサーバーでdumpファイルを解凍

$ gunzip /home/technuma/mysql-3307.sql.gz

バックアップサーバーで"CHANGE MASTER"をgrepしてMASTER_LOG_FILE,MASTER_LOG_POSを記録しておく

$ head -n 50 /home/technuma/mysql-3307.sql | grep "CHANGE MASTER"

バックアップサーバーでMySQL再起動

$ sudo mysqladmin -u root -S /var/lib/mysql-3307/mysqld.sock shutdown
$ sudo mysqld_safe --defaults-file=/var/lib/mysql-3307/my.cnf --skip-slave-start &

バックアップサーバーでインポートする

$ sudo mysql -u root -S /var/lib/mysql-3307/mysqld.sock -p < /home/technuma/mysql-3307.sql

バックアップサーバーでMySQL再起動

$ sudo mysqladmin -u root -S /var/lib/mysql-3307/mysqld.sock shutdown
$ sudo mysqld_safe --defaults-file=/var/lib/mysql-3307/my.cnf --skip-slave-start &

バックアップサーバーでMySQL接続

$ sudo mysql -u root -S /var/lib/mysql-3307/mysqld.sock -p

バックアップサーバーでslaveできているか確認 Slave has read all relay log; が表示され、Exec_Master_Log_Posが増加するのを確認

mysql > show slave status\G

mysql > change master to
master_host='MASTERSERVERIP',
master_user='repluser',
master_password='REPLUSERPASS',
master_port=3306,
master_log_file='MASTER_LOG_FILE',
master_log_pos=MASTER_LOG_POS;

slaveができているか確認

mysql > show slave status\G