DB/MySQL/レプリケーション の履歴(No.16)

• 履歴一覧
• 差分 を表示
• 現在との差分 を表示
• ソース を表示
• DB/MySQL/レプリケーション へ行く。
  • 1 (2017-05-18 (木) 17:26:31)
  • 2 (2017-05-18 (木) 17:34:00)
  • 3 (2017-05-19 (金) 15:04:39)
  • 4 (2017-07-14 (金) 01:36:15)
  • 5 (2017-11-01 (水) 01:11:23)
  • 6 (2018-04-29 (日) 22:11:14)
  • 7 (2018-05-04 (金) 05:40:34)
  • 8 (2018-08-18 (土) 07:13:30)
  • 9 (2018-08-19 (日) 17:28:43)
  • 10 (2018-08-24 (金) 09:49:43)
  • 11 (2018-09-05 (水) 23:34:11)
  • 12 (2018-09-22 (土) 10:22:32)
  • 13 (2018-09-22 (土) 18:01:56)
  • 14 (2018-10-25 (木) 07:52:41)
  • 15 (2018-11-14 (水) 15:58:55)
  • 16 (2019-01-24 (木) 10:53:47)
  • 17 (2019-08-12 (月) 15:33:57)
  • 18 (2019-12-20 (金) 21:23:23)

---

• MySQLのレプリケーションについて。試験的にVPSから自宅へのレプリケーションを導入中

用語まとめ†

GTIDを利用したレプリケーション†

注意点†

• create table ... selectなどが使えない。enforce_gtid_sconsistency = WARNにして様子を見て切り替える。
• master/slaveともbinlogの設定が必要。

設定†

参考ページ https://www.server-world.info/query?os=CentOS_7&p=mariadb&f=3 http://qiita.com/suzutsuki0220/items/e5be03ea8f44ad2f6533

遅延対策†

救われた面もあるのでなんともだが、遅延が酷いと１時間近くにもなることがある。 slave stop & start すると復帰するが、原因は突き止めたい。

slaveの状態を確認†

• Seconds_Behind_Masterの値が0であればよい。

SHOW SLAVE STATUS¥G
中略
Seconds_Behind_Master: 1

Seconds_Behind_Masterが0なのに、遅延が発生している場合†

• master側のbinログのポジションを確認

SHOW MASTER STATUS¥G

ファイルに差分があれば、ネットワーク転送でボトルネックがあることになる。

AWSのRDSの場合(専用のプロシージャーを利用する)†

http://takeshiyako.blogspot.jp/2014/12/mysqlamazon-rds.html

mysql> CALL mysql.rds_set_external_master('100.100.100.100',3306,'userrds','passwordrds','mysql-bin.000855',846543983,0);

mysql> CALL mysql.rds_start_replication;

すべてのデータベースをdumpするとmysqlデータベースインポート中にエラーになるので個別にやるしかない。databasesオプションを利用して、移行対象を絞るべし

マスターの設定†

ユーザーの追加(IPをスレーブのものに限定)†

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.2/255.255.255.255' IDENTIFIED BY 'hogehoge';

上記の操作でユーザーの追加と権限付与が一度に行える！

マスターのmy.cnf変更して再起動†

[mysqld]
log-bin=mysql-bin
server-id=1
expire_logs_days=7

mysql-binでバイナリログを有効にして、server-idはかぶらないものをつける。expire_log_daysは7日間はバイナリログを保存する（それ以上にレプリケーションがとまるとダンプしなおし)

スレーブの設定†

[mysqld]
log-bin=mysql-bin
server-id=102
read_only=1
# 自身のホスト名を定義(show slave hostsのときに出てくる)
report-host=slavehost

ダンプとリカバリー†

マスター†

MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]>  show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 22826177 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

この番号は更新がなければ変わらないようだ。

• 別ターミナルでdump

  $ mysqldump -u root -p --all-databases --lock-all-tables --events | gzip > mysql_dump.sql.gz

• 終わったらunlock

unlock tables; 

• 状態の確認

スレーブ†

$ mysql -u root -p < /var/tmp/mysql_dump.sql

MariaDB [(none)]> change master to master_host='MASTER_IP', master_user='newrepl', master_password='hogehoge',master_log_file='mysql-bin.000001',master_log_pos=245;

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

MariaDB [(none)]> start slave;

• 状態の確認

show slave status\G

• dumpファイルに記載がある状態からもスタート可能

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=245;

change master to master_host='HOST', master_user='repl', master_password='hogehoge',master_log_file='mysql-bin.000015',master_log_pos=245;

トラブルシューティング†

レプリケーションが止まる†

• 仮想マシンでやっていた時に発生した模様。エラーは出てなくてもレプリケーションが進まない。そのうちログの保存期間すぎてしまったという。ネットワーク断が長いとスレーブリスタートしたほうがよい。

レプリケーションユーザーでつながらない†

ユーザー登録時にパスワードが設定されてないユーザーで接続しようとしていた。作成時にパスワード設定忘れた模様！

SHOW GRANTS FOR 'repl'@'%';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'

Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'†

ダンプからレプリケーションをスタートさせてしばらくは順調だったが、１週間後にのぞいたらこのエラー。

slaveのmaster昇格†

ポイント†

手動の確認手順が多いため、VPS同士であればバックアップからのリカバリーの方が楽だろう。

• master側でpositionの確認
• slave側でpositionの一致確認
• 切り替え

masterのposition確認†

# プロセス(クエリ)が流れてないことを確認(なかなか消えないので下の数値が一致していればよしとした)
mysql> SHOW PROCESSLIST;
# 書き込みロックを実行する（ロックが外れるのでmysqlから出ない）
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
# positonの確認
MariaDB [(none)]> SHOW MASTER STATUS\G
*************************** 1. row ***************************
           File: mysql-bin.000037
        Position: 3524806
    Binlog_Do_DB: 
Binlog_Ignore_DB:
1 row in set (0.00 sec)

slaveのposition確認†

# レプリケーション（IO_THREAD）を停止する
mysql> STOP SLAVE IO_THREAD;
# Has read all relay logが出るまで待機
mysql> SHOW PROCESSLIST;
# Master_Log_FileとRead_Master_Log_Posをメモ
mysql> SHOW SLAVE STATUS\G

master/slaveで設定クリア†

• 両方で行う。

# レプリケーション系の設定を初期化
mysql> RESET SLAVE ALL;
mysql> RESET MASTER;

mysqlのreadonlyの設定変更†

アプリの向き先変更†

ansibleタスク利用。

旧マスターは新マスターのIPへ 新マスターはlocalhostへ