MySQLのレプリケーションユーザ名を修正する

MySQLのレプリケーションユーザ名を間違っていたので、mainの稼働を止めずに修正する。

やること概要

• レプリケーション停止して、設定をリセットする
• mainでユーザ名を変える
• mainからバックアップを取得する
• replicaにリストアする
• レプリケーションの接続設定を新規で作成する
• レプリケーション開始

レプリケーション停止して、設定をリセットする

# 設定確認
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
# 略
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
# 略

# レプリケーション停止
mysql> STOP SLAVE;

# 設定確認
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
# 略
           Slave_IO_Running: No
          Slave_SQL_Running: No
# 略

# 設定リセット
mysql> RESET SLAVE;
mysql> quit

# MySQL再起動
$ sudo systemctl restart mysql

mainのレプリケーションユーザ名を変更する

# 確認
mysql> SELECT User, Host FROM mysql.user WHERE User LIKE "%repluser%";
+----------------+-------------+
| User           | Host        |
+----------------+-------------+
| mysql_repluser | 192.168.0.% |
+----------------+-------------+
1 row in set (0.00 sec)

# ユーザ名変更
mysql> RENAME USER 'mysql_repluser'@'192.168.0.%' TO 'repluser'@'192.168.0.%';

# 確認
mysql> SELECT User, Host FROM mysql.user WHERE User LIKE "%repluser%";
+----------+-------------+
| User     | Host        |
+----------+-------------+
| repluser | 192.168.0.% |
+----------+-------------+
1 row in set (0.00 sec)

mainからダンプファイルを取得する

mysqldump --all-databases --single-transaction --routines --triggers --events --master-data=2 --hex-blob -u root -h localhost -p > dump.sql

• --all-databases
  • 全DBのダンプを取得
• --single-transaction
  • innoDBの場合はこれでOK。つけないとデータがずれる
  • MyISAMの場合は --lock-all-tables で。
• --routines --triggers --events
  • NODOC
• --master-data=2
  • SHOW MASTER STATUS の値(ポジション値)をダンプファイルに含める
• --hex-blob
  • 非UTF8な環境でもリストア時にデータが壊れることがない。でもデータサイズはでかくなる。

replicaにリストアする

# リストア
mysql -u root -h localhost -p < dump.sql

# 権限設定を再ロード(リストアだけだとロードされないので)
mysql -u root -h localhost -e "FLUSH PRIVILEGES;" -p

replicaでレプリケーションの設定をする

ダンプファイルからポジションを確認する

cat dump.sql | grep "MASTER_LOG_FILE" | head -n 1
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000063', MASTER_LOG_POS=559;

mainへの接続設定してレプリケーションを開始する

# 接続設定
mysql> CHANGE MASTER TO
  MASTER_HOST='192.168.0.1',
  MASTER_USER='repluser',
  MASTER_PASSWORD='xxxxx',
  MASTER_LOG_FILE='mysql-bin.000063',
  MASTER_LOG_POS=559;

# 確認
mysql> SHOW SLAVE STATUS\G

# レプリケーション開始
mysql> START SLAVE;

# レプリケーションが開始されたことを確認
mysql> SHOW SLAVE STATUS\G
# 略
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
# 略

レプリケーションされていることを確認

main

# mainでDBを作る
mysql> create database hoge;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hoge               |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

replace

# replacaに反映されていることを確認
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hoge               |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)