![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
#author("2019-12-20T12:23:23+00:00","default:wikiadmin","wikiadmin") #author("2021-01-06T22:42:40+00:00","default:wikiadmin","wikiadmin") -MySQLのレプリケーションについて。試験的にVPSから自宅へのレプリケーションを導入中 #contents *用語まとめ [#y6814bd8] |GTID|Global Transaction IDの略。サーバーIDと合わせて世界で一意となるID。MySQL5.6から導入。これを利用した設定情報公開はまだ少数派| |MHA|マスター自動昇格のためのツール。GTID導入以前はこのツールが使われていたが、今でも十分| |I/Oスレッド|バイナリろぐの転送| |SQLスレッド|SQLの差分実行。こちらがボトルネックになることが多く、SBM(SecondsBehindMaster)を発生させる。5.7では同一DB内の平行SQLスレッドにより改善された。| |トポロジ|masterに対してslaveが複数や、slaveが他のslaveのmasterになる多段構成、マルチマスタも可能| |準同期レプリケーション|非同期が基本だが、IOスレッドまでは同期を保証する。設定が必要でslaveのDiskI/Oに影響されるため導入は慎重に！5.7の改良でロスレスになった！| |マルチソースレプリケーション|複数のmasterからのレプリケーション| |グループコミット|sync_binlog=1にしたときに複数のトランザクションをまとめる。単位はパラメータで調整可能| |MaxScale|Proxyとなって複数台のMySQLを更新はmasterへ、リードはslaveへ振り分けできるツール| *GTIDを利用したレプリケーション [#pe936c16] **注意点 [#ra5b84b5] -create table ... selectなどが使えない。enforce_gtid_sconsistency = WARNにして様子を見て切り替える。 -master/slaveともbinlogの設定が必要。 -MySQL 5.6から実行可能で再起動必要。5.7からはオンラインで有効化できる模様。 *設定 [#o64fbf93] 参考ページ https://www.server-world.info/query?os=CentOS_7&p=mariadb&f=3 http://qiita.com/suzutsuki0220/items/e5be03ea8f44ad2f6533 **遅延対策 [#gea39cca] 救われた面もあるのでなんともだが、遅延が酷いと１時間近くにもなることがある。 slave stop & start すると復帰するが、原因は突き止めたい。 ***slaveの状態を確認 [#acb193a4] -Seconds_Behind_Masterの値が0であればよい。 SHOW SLAVE STATUS¥G 中略 Seconds_Behind_Master: 1 ***Seconds_Behind_Masterが0なのに、遅延が発生している場合 [#w11220d5] -master側のbinログのポジションを確認 SHOW MASTER STATUS¥G |master|slave| |File: mysql-bin.000024|Master_Log_File: mysql-bin.000023| |Position: 198937070|Read_Master_Log_Pos: 198837070| ファイルに差分があれば、ネットワーク転送でボトルネックがあることになる。 **AWSのRDSの場合(専用のプロシージャーを利用する) [#g1fe5e4c] 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オプションを利用して、移行対象を絞るべし **マスターの設定 [#ke095c1f] ***ユーザーの追加(IPをスレーブのものに限定) [#bf336d22] GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.2/255.255.255.255' IDENTIFIED BY 'hogehoge'; 上記の操作でユーザーの追加と権限付与が一度に行える！ ***マスターのmy.cnf変更して再起動 [#l47d04ec] [mysqld] log-bin=mysql-bin server-id=1 expire_logs_days=7 mysql-binでバイナリログを有効にして、server-idはかぶらないものをつける。expire_log_daysは7日間はバイナリログを保存する（それ以上にレプリケーションがとまるとダンプしなおし) **スレーブの設定 [#kc2387a4] [mysqld] log-bin=mysql-bin server-id=102 read_only=1 # 自身のホスト名を定義(show slave hostsのときに出てくる) report-host=slavehost **ダンプとリカバリー [#pfca2f5f] mysqldumpにスレーブスタート情報が載っている。。 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000018', MASTER_LOG_POS=245 あとはユーザーとホスト情報を追加 master_user='newrepl', master_password='hogehoge',master_host='MASTER_IP', ***マスター [#q4a95ea7] 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; -状態の確認 ***スレーブ [#w8dfc974] $ 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ファイルに記載がある状態からもスタート可能。自分の環境だとcronの時間かそれとも初期値なのかわからないがMASTER_LOG_POSは毎日245 -- -- 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; *トラブルシューティング [#yb6d68d2] **レプリケーションが止まる [#c02e0b92] -仮想マシンでやっていた時に発生した模様。エラーは出てなくてもレプリケーションが進まない。そのうちログの保存期間すぎてしまったという。ネットワーク断が長いとスレーブリスタートしたほうがよい。 **レプリケーションユーザーでつながらない [#mc6b978e] ユーザー登録時にパスワードが設定されてないユーザーで接続しようとしていた。作成時にパスワード設定忘れた模様！ 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' [#u90a4740] ダンプからレプリケーションをスタートさせてしばらくは順調だったが、１週間後にのぞいたらこのエラー。 *slaveのmaster昇格 [#d48f6e84] **ポイント [#m3ab39fb] 手動の確認手順が多いため、VPS同士であればバックアップからのリカバリーの方が楽だろう。 -master側でpositionの確認 -slave側でpositionの一致確認 -切り替え ***masterのposition確認 [#u288d954] # プロセス(クエリ)が流れてないことを確認(なかなか消えないので下の数値が一致していればよしとした) 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確認 [#vfcb6c69] # レプリケーション（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で設定クリア [#bb81fcb6] -両方で行う。 # レプリケーション系の設定を初期化 mysql> RESET SLAVE ALL; mysql> RESET MASTER; ***mysqlのreadonlyの設定変更 [#p2a407cf] ***アプリの向き先変更 [#aa447b9b] ansibleタスク利用。 旧マスターは新マスターのIPへ 新マスターはlocalhostへ #counter
