主従が直面する様々な問題の詳細な解決方法
8687 ワード
マスタスレーブ同期メンテナンス
特殊な状況のため、主サーバーの更新は頻繁で、従サーバーは各種の原因のため、更新が特に遅いことを招いて、このような情況、私達は定期的に主従のデータの同期メンテナンスを行う必要があって、具体的な方法は以下の通りで、負荷が低い時一時的に主データベースの更新をブロックして、強制主従データベースの更新同期
操作1、masterで次の文を実行します.
きろく
SHOW
サーバからコピーされた目的座標である文の出力のログ名とオフセット量.
2.スレーブサーバで、次の文を実行します.MASTER_POS_WAIT()関数のパラメータは、前のステップで得られたコピー座標値です.
これ
SELECT
文は、サーバから指定したログファイルとオフセット量に達するまでブロックされ、戻ります.
0
を返します.
-1
を選択すると、タイムアウトが終了します.クエリの戻り値
0
を選択すると、サーバからプライマリサーバに同期します.
3.プライマリ・サーバで、次の文を実行して、プライマリ・サーバが更新の処理を再開できるようにします.
サーバからのレプリケーションエラーの処理
場合によっては、サーバからのレプリケーションにエラーが発生する場合があります.まず、
1.サーバのテーブルとプライマリサーバの違いによるものかどうかを確認します.テーブル構造が異なる場合は、スレーブ・サーバのテーブルがプライマリ・サーバと同じであることを変更し、START SLAVE文を再実行します.
2、表構造の違いによる更新に失敗しない場合は、手動更新が安全かどうかを確認し、自主サーバの更新に失敗した文を無視する必要があります.自主サーバからの文をスキップするコマンドはSET GLOBAL
SQL_SLAVE_SKIP_COUNTER=nで、nの値は1または2です.自主サーバの更新文にAUTO_を使用しない場合INCREMENTまたはLAST_INSERT_ID()は、n値が1であるべきで、そうでない場合、値は2であるべきである.理由はAUTO_INCREMENTまたはLAST_INSERT_ID()の文は、バイナリ・ログから2つのイベントを取得する必要があります.次の例は、プライマリ・サーバをスキップした2つの更新文をサーバ側からシミュレートする効果です.1.まず、サーバー側でレプリケーションプロセスを停止し、2つの文をスキップするように設定します.
マルチプライマリ・レプリケーション時の自己成長変数の競合の問題
場合によっては、複数のプライマリ・レプリケーション(複数のプライマリ・サーバ対1のセカンダリ・サーバ)を使用する必要がある場合があります.この場合、プライマリ・サーバのテーブルが自動成長変数を使用すると、システム・パラメータauto_のため、サーバの同じテーブルにコピーするとプライマリ・キーの競合が発生する可能性があります.increment_incrementとauto_increment_offsetのデフォルト値は1で、複数のプライマリ・サーバの自己増加変数列が遅かれ早かれ競合します.
シングルプライマリ・レプリケーションでは、デフォルト設定を使用できます.プライマリ・キーの競合は発生しません.ただし、マルチプライマリ・レプリケーションを使用する場合はauto_をカスタマイズする必要があります.increment_incrementとauto_increment_offsetの設定は、複数のプライマリ間でデータベースから重複する競合がないようにコピーされます.たとえば、2つのマスターの場合は以下のように設定できます.
Master 1:auto_increment_increment= 2,auto_increment_offset= 1;(1,3,5,7...シーケンス).
Master 2上:auto_increment_increment= 2,auto_increment_offset= 0;(0,2,4,6...シーケンス).
次の例では、テストテーブルaiが作成され、1つの自己増加フィールドiのみが作成され、この2つのパラメータを変更する効果を実証し始めます.
まずパラメータがデフォルトの場合、表へ
ai
でレコードを挿入すると、自動成長カラムの値が連続して表示されます.
そしてパラメータをauto_increment_incrementの値を10に変更し、レコードを挿入します.
テストの結果から見ると、新しく挿入された記録は連続せず、毎回10増加した.次にauto_を変更しますincrement_offsetパラメータで、レコードを挿入する効果を理解します.
レコードを挿入した結果から、auto_increment_offsetパラメータは、増加するたびにオフセット量を設定します.つまり、10で加算するたびに、5つのオフセット量を増加する必要があります.
この2つのパラメータにより、異なるプライマリ・サーバ上の自動成長カラムの値の範囲を容易に設定できます.これにより、これらのデータがサーバにコピーされると、プライマリ・キーの重複を効果的に回避できます.
プライマリ・スレーブ・サーバの切り替え
1つのプライマリ・データベース・サーバM、2つのスレーブ・データベース・サーバS 1、S 2は、同時にプライマリ・データベース・サーバMを指す.プライマリ・データベースMが何らかの理由で障害が発生した場合、そのうちの1つをデータベース・サーバ(S 1が選択されていると仮定)からプライマリ・データベース・サーバに切り替えるとともに、別のセカンダリ・データベース(S 2)の構成を変更して新しいプライマリ・データベース(S 1)に指向させる必要がある.また、可能であれば、障害が発生したプライマリ・データベース(M)を新しいスレーブ・データベースに修復またはリセットするように、アプリケーションに通知する必要があります.
次に、プライマリ・スレーブ・サーバを切り替える操作手順について詳しく説明します.1、まずすべてのスレーブデータベースがrelay logのすべての更新を実行していることを確認し、各スレーブサーバでSTOP SLAVEIO_を実行するTHREAD、SHOWPROSCESSLISTの出力をチェックし、ステータスがHasread all relay logであることを確認し、更新が完了したことを示します.
スレーブデータベースS 1では、STOP SLAVEを実行してスレーブサービスを停止し、RESETMASTERをプライマリデータベースにリセットする.
にある
S2
を選択します.
STOPSLAVE
サービスから停止し、実行
CHANGE MASTER TO MASTER_HOST = 'S1'
プライマリ・データベースを再設定してから実行
START SLAVE
レプリケーションを開始します.
5、
新しいプライマリ・データベース・サーバの
master.info
および
relay-log.info
ファイルです.そうしないと、次回再起動するときにサーバから起動します.
6、最後に、Mサーバが修復可能であれば、S 2の方法でS 1のスレーブサーバに構成することができる.
注:上記のテストのステップは、デフォルトS 1でlog-binオプションがオンになっているため、プライマリ・データベースにリセットした後、バイナリ・ログを他のサーバから転送することができます.次に、S 1でlog-slave-updatesパラメータが開かれていません.そうしないと、プライマリ・データベースにリセットされると、実行したバイナリ・ログがS 2に繰り返し転送され、S 2の同期エラーが発生する可能性があります.
特殊な状況のため、主サーバーの更新は頻繁で、従サーバーは各種の原因のため、更新が特に遅いことを招いて、このような情況、私達は定期的に主従のデータの同期メンテナンスを行う必要があって、具体的な方法は以下の通りで、負荷が低い時一時的に主データベースの更新をブロックして、強制主従データベースの更新同期
操作1、masterで次の文を実行します.
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000039 | 974 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
きろく
SHOW
サーバからコピーされた目的座標である文の出力のログ名とオフセット量.
2.スレーブサーバで、次の文を実行します.MASTER_POS_WAIT()関数のパラメータは、前のステップで得られたコピー座標値です.
mysql> select MASTER_POS_WAIT('mysql-bin.000039','974');
+-------------------------------------------+
| MASTER_POS_WAIT('mysql-bin.000039','974') |
+-------------------------------------------+
| 0 |
+-------------------------------------------+
1 row in set (0.00 sec)
これ
SELECT
文は、サーバから指定したログファイルとオフセット量に達するまでブロックされ、戻ります.
0
を返します.
-1
を選択すると、タイムアウトが終了します.クエリの戻り値
0
を選択すると、サーバからプライマリサーバに同期します.
3.プライマリ・サーバで、次の文を実行して、プライマリ・サーバが更新の処理を再開できるようにします.
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
サーバからのレプリケーションエラーの処理
場合によっては、サーバからのレプリケーションにエラーが発生する場合があります.まず、
1.サーバのテーブルとプライマリサーバの違いによるものかどうかを確認します.テーブル構造が異なる場合は、スレーブ・サーバのテーブルがプライマリ・サーバと同じであることを変更し、START SLAVE文を再実行します.
2、表構造の違いによる更新に失敗しない場合は、手動更新が安全かどうかを確認し、自主サーバの更新に失敗した文を無視する必要があります.自主サーバからの文をスキップするコマンドはSET GLOBAL
SQL_SLAVE_SKIP_COUNTER=nで、nの値は1または2です.自主サーバの更新文にAUTO_を使用しない場合INCREMENTまたはLAST_INSERT_ID()は、n値が1であるべきで、そうでない場合、値は2であるべきである.理由はAUTO_INCREMENTまたはLAST_INSERT_ID()の文は、バイナリ・ログから2つのイベントを取得する必要があります.次の例は、プライマリ・サーバをスキップした2つの更新文をサーバ側からシミュレートする効果です.1.まず、サーバー側でレプリケーションプロセスを停止し、2つの文をスキップするように設定します.
mysql> select * from repl_test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL SQL_slave_SKIP_COUNTER = 2; Query OK, 0 rows affected (0.01 sec)
mysql> select * from repl_test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> insert into repl_test values(2); Query OK, 1 row affected (0.00 sec)
mysql> insert into repl_test values(3); Query OK, 1 row affected (0.00 sec)
mysql> insert into repl_test values(4); Query OK, 1 row affected (0.00 sec)
mysql> select * from repl_test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from repl_test;
+------+
| id |
+------+
| 1 |
| 4 |
+------+
2 rows in set (0.00 sec)
マルチプライマリ・レプリケーション時の自己成長変数の競合の問題
場合によっては、複数のプライマリ・レプリケーション(複数のプライマリ・サーバ対1のセカンダリ・サーバ)を使用する必要がある場合があります.この場合、プライマリ・サーバのテーブルが自動成長変数を使用すると、システム・パラメータauto_のため、サーバの同じテーブルにコピーするとプライマリ・キーの競合が発生する可能性があります.increment_incrementとauto_increment_offsetのデフォルト値は1で、複数のプライマリ・サーバの自己増加変数列が遅かれ早かれ競合します.
シングルプライマリ・レプリケーションでは、デフォルト設定を使用できます.プライマリ・キーの競合は発生しません.ただし、マルチプライマリ・レプリケーションを使用する場合はauto_をカスタマイズする必要があります.increment_incrementとauto_increment_offsetの設定は、複数のプライマリ間でデータベースから重複する競合がないようにコピーされます.たとえば、2つのマスターの場合は以下のように設定できます.
Master 1:auto_increment_increment= 2,auto_increment_offset= 1;(1,3,5,7...シーケンス).
Master 2上:auto_increment_increment= 2,auto_increment_offset= 0;(0,2,4,6...シーケンス).
次の例では、テストテーブルaiが作成され、1つの自己増加フィールドiのみが作成され、この2つのパラメータを変更する効果を実証し始めます.
まずパラメータがデフォルトの場合、表へ
ai
でレコードを挿入すると、自動成長カラムの値が連続して表示されます.
mysql> CREATE TABLE ai (
-> i bigint(20) NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (i)
-> ) ENGINE=MyISAM DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> insert into ai values(null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from ai;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
そしてパラメータをauto_increment_incrementの値を10に変更し、レコードを挿入します.
mysql> SET @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> insert into ai values(null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from ai;
+----+ | i | +----+ | 1 |
| 2 |
| 3 |
| 11 |
| 21 |
| 31 | +----+
6 rows in set (0.00 sec)
テストの結果から見ると、新しく挿入された記録は連続せず、毎回10増加した.次にauto_を変更しますincrement_offsetパラメータで、レコードを挿入する効果を理解します.
mysql> SET @@auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 5 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> insert into ai values(null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from ai;
+----+ | i | +----+ | 1 |
| 2 |
| 3 |
| 11 |
| 21 |
| 31 |
| 35 |
| 45 |
| 55 | +----+
9 rows in set (0.00 sec)
レコードを挿入した結果から、auto_increment_offsetパラメータは、増加するたびにオフセット量を設定します.つまり、10で加算するたびに、5つのオフセット量を増加する必要があります.
この2つのパラメータにより、異なるプライマリ・サーバ上の自動成長カラムの値の範囲を容易に設定できます.これにより、これらのデータがサーバにコピーされると、プライマリ・キーの重複を効果的に回避できます.
プライマリ・スレーブ・サーバの切り替え
1つのプライマリ・データベース・サーバM、2つのスレーブ・データベース・サーバS 1、S 2は、同時にプライマリ・データベース・サーバMを指す.プライマリ・データベースMが何らかの理由で障害が発生した場合、そのうちの1つをデータベース・サーバ(S 1が選択されていると仮定)からプライマリ・データベース・サーバに切り替えるとともに、別のセカンダリ・データベース(S 2)の構成を変更して新しいプライマリ・データベース(S 1)に指向させる必要がある.また、可能であれば、障害が発生したプライマリ・データベース(M)を新しいスレーブ・データベースに修復またはリセットするように、アプリケーションに通知する必要があります.
次に、プライマリ・スレーブ・サーバを切り替える操作手順について詳しく説明します.1、まずすべてのスレーブデータベースがrelay logのすべての更新を実行していることを確認し、各スレーブサーバでSTOP SLAVEIO_を実行するTHREAD、SHOWPROSCESSLISTの出力をチェックし、ステータスがHasread all relay logであることを確認し、更新が完了したことを示します.
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
Id: 2
User: system user Host:
db: NULL
Command: Connect
Time: 4137
State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL
*************************** 2. row ***************************
……
2 rows in set (0.00 sec)
スレーブデータベースS 1では、STOP SLAVEを実行してスレーブサービスを停止し、RESETMASTERをプライマリデータベースにリセットする.
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.06 sec)
にある
S2
を選択します.
STOPSLAVE
サービスから停止し、実行
CHANGE MASTER TO MASTER_HOST = 'S1'
プライマリ・データベースを再設定してから実行
START SLAVE
レプリケーションを開始します.
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.1.101';
Query OK, 0 rows affected (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
5、
新しいプライマリ・データベース・サーバの
master.info
および
relay-log.info
ファイルです.そうしないと、次回再起動するときにサーバから起動します.
6、最後に、Mサーバが修復可能であれば、S 2の方法でS 1のスレーブサーバに構成することができる.
注:上記のテストのステップは、デフォルトS 1でlog-binオプションがオンになっているため、プライマリ・データベースにリセットした後、バイナリ・ログを他のサーバから転送することができます.次に、S 1でlog-slave-updatesパラメータが開かれていません.そうしないと、プライマリ・データベースにリセットされると、実行したバイナリ・ログがS 2に繰り返し転送され、S 2の同期エラーが発生する可能性があります.