mysql主従バックアップと原理分析
6130 ワード
一.mysql主従バックアップ(レプリケーション)の基本原理
mysqlは一方向、非同期レプリケーションをサポートし、レプリケーション中に1つのサーバがプライマリサーバとして機能し、1つ以上の他のサーバがセカンダリサーバとして機能します.mysqlレプリケーションは、プライマリ・サーバがバイナリ・ログでデータベースへの変更(更新、削除など)をすべて追跡することに基づいています.したがって、レプリケーションを行うには、プライマリ・サーバでバイナリ・ログを有効にする必要があります.各スレーブ・サーバは、プライマリ・サーバからバイナリ・ログに記録された保存された更新を受信する.サーバからプライマリ・サーバに接続すると、プライマリ・サーバにログから最後に正常に更新された場所を通知します.サーバからその時点で発生した更新を受信し、ホスト上で同じ更新を実行します.次に、プライマリ・サーバが新しい更新を通知するのをブラックアウトし、待機します.サーバからのバックアップはプライマリ・サーバに干渉せず、バックアップ中にプライマリ・サーバは更新の処理を継続できます.
二.mysqlプライマリ・セカンダリ・バックアップ構成方法
mysqlプライマリ・セカンダリ・バックアップを行う場合は、プライマリ・セカンダリ・サーバのバージョンが互換性があることを確認したほうがいいです.スレーブ・サーバは、少なくともプライマリ・サーバのバージョンと同じまたはそれ以上です.
ホスト構成:
1.mysqlプロファイルmyを変更します.cnf
[mysqld]タブに次の行を追加します.
log-bin、server-idはプロファイルに追加する必要がある内容です.プライマリ・サーバはデフォルトですべてのデータベースをバックアップします.特定のデータベースのみをバックアップするか、バックアップしないかを指定する必要がある場合は、binlog-do-dbおよびbinlog-ignore-dbオプションを追加できます.関連(log-binの詳細は付録1参照)
テストホストでは、実際に次の内容を追加します.
2.サーバからmysqlアカウントを追加し、権限を設定する
プライマリ・サーバでは、プライマリ・サーバに接続するユーザーをサーバから作成し、replication slave権限を設定する必要があります.具体的なコマンドは次のとおりです.
3.プライマリ・サーバ・データのエクスポート
プライマリ・サーバ・データをエクスポートして、プライマリ・セカンダリ・サーバの初期状態を一貫させるために、セカンダリ・サーバにインポートします.
テストマシンでは、実際に実行します.
4.FileおよびPositionアイテムの値を記録する
mysqlを再起動し、show master statusGを使用します.プライマリ・サーバのステータスを表示し、ファイルおよびPositionアイテムの値を記録して、セカンダリ・サーバを後で構成します.
テストマシンで実際に実行した結果は次のとおりです.
*************************** 1. row ***************************
File: simba-bin.000008
Position: 79
Binlog_Do_DB: test
Binlog_Ignore_DB:
1 row in set (0.00 sec)
ここでFileはimba-binである.000008;Positionは79です.
これでプライマリサーバの構成が完了しました
スレーブ構成:
1.mysqlプロファイルmyを変更します.cnf
[mysqld]タブの下に次の行を追加します.
2.ホストデータベースのインポート
テストマシンでは、実際に実行します.
mysql test < test.bak
3.mysqlデータベースを再起動し、関連パラメータを設定する
show slave statusGを使用関連パラメータが正しいかどうかを確認します.
実際に実行すると、マシンから見た結果は次のようになります(出力の一部のみを抜粋します).
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.146.133
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: darkstar-bin.000006
Read_Master_Log_Pos: 79
Relay_Log_File: simba-relay-bin.000003
Relay_Log_Pos: 171
Relay_Master_Log_File: darkstar-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……
一部のパラメータの説明:
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
以上のパラメータはslaveが正常に動作していることを聞いて、プライマリサーバからのメッセージを待っています.このときnetstateコマンドを使用すると、スレーブとホストの間にエッジが確立されていることがわかります.
特に注意すべき2つのパラメータは次のとおりです.
Master_Log_FileとRead_Master_Log_Pos.Master_Log_Fileはホスト上でプライマリ・スタンバイ同期に使用されるログ・ファイル名を表し、Read_Master_Log_Posは、前回正常に同期されたログ・ファイルの場所を表します.
この2つが、以前にプライマリ・サーバで見たFileおよびPositionの値と一致しない場合は、正しく同期できません.
三.プライマリ・セカンダリ・バックアップの実装の詳細
mysqlは、3つのスレッドを使用してレプリケーション機能を実行します(そのうちの1つはプライマリサーバ上、もう2つはセカンダリサーバ上).start slaveが発行されると、サーバからI/Oスレッドが作成され、プライマリ・サーバに接続され、バイナリ・ログに記録された文が送信されます.プライマリ・サーバは、バイナリ・ログの内容をセカンダリ・サーバに送信するスレッドを作成します.このスレッドは、プライマリサーバ上のshow processlist出力のBinlog Dumpスレッドとすることができる.メインサーバBinlog Dumpスレッドから送信されたコンテンツをサーバI/Oスレッドから読み出し、サーバデータディレクトリからローカルファイルである中継ログにコピーします.3番目のスレッドはsqlスレッドで、サーバから作成され、中継ログを読み取り、ログに含まれる更新を実行します.サーバからの更新文の読み取りと実行は、2つの独立したタスクに分けられます.サーバから起動すると、I/Oスレッドは、プライマリ・サーバからすべてのバイナリ・ログ・コンテンツをすばやく要求できます.
1.スレッドステータスのコピー
コピースレッドのステータスはshow slave statusGとshow master statusで確認できます.一般的なスレッドのステータスは次のとおりです.
(1)マスタサーバBinlog Dumpスレッド
Has sent all binlog to slave; waiting for binlog to be updated
スレッドは、バイナリ・ログからすべての主要な更新を読み込み、サーバに送信しました.スレッドが空き、プライマリ・サーバ上の新しい更新によるバイナリ・ログに表示される新しいイベントを待機しています.
(2)サーバI/Oスレッドからの状態
Waiting for master to send event
スレッドはプライマリ・サーバに接続され、バイナリ・ログ・イベントの到着を待っています.プライマリ・サーバが空いている場合は、時間がかかります.待ち続けるならslave_read_timeout秒でタイムアウトが発生します.このとき,スレッドは接続が中断されたと判断し,再接続を企図する.
(3)サーバからのSQLスレッドの状態
Reading event from the relay log
スレッドは中継ログからイベントを読み込み、イベントを処理できます.
Has read all relay log; waiting for the slave I/O thread to update it
スレッドはすでに中継ログファイルのすべてのイベントを処理しており、I/Oスレッドが新しいイベントを中継ログに書き込むのを待っています.
2.コピー中に使用する転送とステータスファイル
デフォルトでは、中継ログはhost_を使用します.name-relay-bin.nnnnnn形式のファイル名、host_nameはサーバホスト名、nnnnnnはシリアル番号です.中継ログはバイナリログと同じフォーマットでmysqlbinlogで読み込むことができます.
サーバからdataディレクトリに2つの小さなファイルを追加します.これらのステータスファイルのデフォルト名はマスターです.infoとrelay-log.info.ステータスファイルはハードディスク(HDD)に保存され、サーバーがシャットダウンしても失われません.次にサーバから起動すると、これらのファイルを読み込んで、プライマリ・サーバからどれだけのバイナリ・ログが読み込まれたか、および自分の中継ログが処理された程度を決定します.
付録1
ロゴについてbinログ
my.confファイルの[mysqld]タブの下のlog_binはログファイルを指定し、ファイル名が指定されていない場合、mysqlは自分でデフォルトのファイル名を生成します.mysqlはファイル名の後ろにデジタルインデックスを自動的に追加し、サービスを開始するたびに新しいバイナリファイルを再生成します.また、log-bin-indexを使用してインデックスファイルを指定します.binlog-do-dbを使用して、レコードのデータベースを指定します.binlog-ignore-dbを使用して、記録されていないデータベースを指定します.binlog-do-dbとbinlog-ignore-dbは一度に1つのデータベースのみを指定し、複数のデータベースに複数の文が必要であることを指定します.また、MySQLはすべてのデータベース名を小文字に変更し、データベースを指定するときはすべて小文字を使用する必要があります.そうしないと機能しません.
以降、データベースに対する操作はbinlogに記録されます.
付録2
試験環境:
ホストmyslqバージョン4.1.22
スレーブmyslqバージョン4.0.23 a
mysqlは一方向、非同期レプリケーションをサポートし、レプリケーション中に1つのサーバがプライマリサーバとして機能し、1つ以上の他のサーバがセカンダリサーバとして機能します.mysqlレプリケーションは、プライマリ・サーバがバイナリ・ログでデータベースへの変更(更新、削除など)をすべて追跡することに基づいています.したがって、レプリケーションを行うには、プライマリ・サーバでバイナリ・ログを有効にする必要があります.各スレーブ・サーバは、プライマリ・サーバからバイナリ・ログに記録された保存された更新を受信する.サーバからプライマリ・サーバに接続すると、プライマリ・サーバにログから最後に正常に更新された場所を通知します.サーバからその時点で発生した更新を受信し、ホスト上で同じ更新を実行します.次に、プライマリ・サーバが新しい更新を通知するのをブラックアウトし、待機します.サーバからのバックアップはプライマリ・サーバに干渉せず、バックアップ中にプライマリ・サーバは更新の処理を継続できます.
二.mysqlプライマリ・セカンダリ・バックアップ構成方法
mysqlプライマリ・セカンダリ・バックアップを行う場合は、プライマリ・セカンダリ・サーバのバージョンが互換性があることを確認したほうがいいです.スレーブ・サーバは、少なくともプライマリ・サーバのバージョンと同じまたはそれ以上です.
ホスト構成:
1.mysqlプロファイルmyを変更します.cnf
[mysqld]タブに次の行を追加します.
log-bin #
server-id=id # id
binlog-do-db=db_nameA # db_nameA
binlog-ignore-db=db_namB # db_namB log-bin、server-idはプロファイルに追加する必要がある内容です.プライマリ・サーバはデフォルトですべてのデータベースをバックアップします.特定のデータベースのみをバックアップするか、バックアップしないかを指定する必要がある場合は、binlog-do-dbおよびbinlog-ignore-dbオプションを追加できます.関連(log-binの詳細は付録1参照)
テストホストでは、実際に次の内容を追加します.
log-bin
server-id=1
binlog-do-db=test2.サーバからmysqlアカウントを追加し、権限を設定する
プライマリ・サーバでは、プライマリ・サーバに接続するユーザーをサーバから作成し、replication slave権限を設定する必要があります.具体的なコマンドは次のとおりです.
grant replication slave
on *.*
to ' ' @ ' IP' identified by ' ';grant replication slave
on *.*
to 'replication'@'%' identified by 'sosotest';select * from user where user = 'replication' \G;3.プライマリ・サーバ・データのエクスポート
プライマリ・サーバ・データをエクスポートして、プライマリ・セカンダリ・サーバの初期状態を一貫させるために、セカンダリ・サーバにインポートします.
テストマシンでは、実際に実行します.
mysqldump test > test.bak4.FileおよびPositionアイテムの値を記録する
mysqlを再起動し、show master statusGを使用します.プライマリ・サーバのステータスを表示し、ファイルおよびPositionアイテムの値を記録して、セカンダリ・サーバを後で構成します.
テストマシンで実際に実行した結果は次のとおりです.
*************************** 1. row ***************************
File: simba-bin.000008
Position: 79
Binlog_Do_DB: test
Binlog_Ignore_DB:
1 row in set (0.00 sec)
ここでFileはimba-binである.000008;Positionは79です.
これでプライマリサーバの構成が完了しました
スレーブ構成:
1.mysqlプロファイルmyを変更します.cnf
[mysqld]タブの下に次の行を追加します.
server-id=id # idserver-id=22.ホストデータベースのインポート
テストマシンでは、実際に実行します.
mysql test < test.bak
3.mysqlデータベースを再起動し、関連パラメータを設定する
change master to
master_host = '10.1.146.133',
master_user = 'replication',
master_password = 'sosotest',
master_log_file = 'darkstar-bin.000008',
master_log_pos = 79;
slave start;show slave statusGを使用関連パラメータが正しいかどうかを確認します.
実際に実行すると、マシンから見た結果は次のようになります(出力の一部のみを抜粋します).
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.146.133
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: darkstar-bin.000006
Read_Master_Log_Pos: 79
Relay_Log_File: simba-relay-bin.000003
Relay_Log_Pos: 171
Relay_Master_Log_File: darkstar-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……
一部のパラメータの説明:
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
以上のパラメータはslaveが正常に動作していることを聞いて、プライマリサーバからのメッセージを待っています.このときnetstateコマンドを使用すると、スレーブとホストの間にエッジが確立されていることがわかります.
特に注意すべき2つのパラメータは次のとおりです.
Master_Log_FileとRead_Master_Log_Pos.Master_Log_Fileはホスト上でプライマリ・スタンバイ同期に使用されるログ・ファイル名を表し、Read_Master_Log_Posは、前回正常に同期されたログ・ファイルの場所を表します.
この2つが、以前にプライマリ・サーバで見たFileおよびPositionの値と一致しない場合は、正しく同期できません.
三.プライマリ・セカンダリ・バックアップの実装の詳細
mysqlは、3つのスレッドを使用してレプリケーション機能を実行します(そのうちの1つはプライマリサーバ上、もう2つはセカンダリサーバ上).start slaveが発行されると、サーバからI/Oスレッドが作成され、プライマリ・サーバに接続され、バイナリ・ログに記録された文が送信されます.プライマリ・サーバは、バイナリ・ログの内容をセカンダリ・サーバに送信するスレッドを作成します.このスレッドは、プライマリサーバ上のshow processlist出力のBinlog Dumpスレッドとすることができる.メインサーバBinlog Dumpスレッドから送信されたコンテンツをサーバI/Oスレッドから読み出し、サーバデータディレクトリからローカルファイルである中継ログにコピーします.3番目のスレッドはsqlスレッドで、サーバから作成され、中継ログを読み取り、ログに含まれる更新を実行します.サーバからの更新文の読み取りと実行は、2つの独立したタスクに分けられます.サーバから起動すると、I/Oスレッドは、プライマリ・サーバからすべてのバイナリ・ログ・コンテンツをすばやく要求できます.
1.スレッドステータスのコピー
コピースレッドのステータスはshow slave statusGとshow master statusで確認できます.一般的なスレッドのステータスは次のとおりです.
(1)マスタサーバBinlog Dumpスレッド
Has sent all binlog to slave; waiting for binlog to be updated
スレッドは、バイナリ・ログからすべての主要な更新を読み込み、サーバに送信しました.スレッドが空き、プライマリ・サーバ上の新しい更新によるバイナリ・ログに表示される新しいイベントを待機しています.
(2)サーバI/Oスレッドからの状態
Waiting for master to send event
スレッドはプライマリ・サーバに接続され、バイナリ・ログ・イベントの到着を待っています.プライマリ・サーバが空いている場合は、時間がかかります.待ち続けるならslave_read_timeout秒でタイムアウトが発生します.このとき,スレッドは接続が中断されたと判断し,再接続を企図する.
(3)サーバからのSQLスレッドの状態
Reading event from the relay log
スレッドは中継ログからイベントを読み込み、イベントを処理できます.
Has read all relay log; waiting for the slave I/O thread to update it
スレッドはすでに中継ログファイルのすべてのイベントを処理しており、I/Oスレッドが新しいイベントを中継ログに書き込むのを待っています.
2.コピー中に使用する転送とステータスファイル
デフォルトでは、中継ログはhost_を使用します.name-relay-bin.nnnnnn形式のファイル名、host_nameはサーバホスト名、nnnnnnはシリアル番号です.中継ログはバイナリログと同じフォーマットでmysqlbinlogで読み込むことができます.
サーバからdataディレクトリに2つの小さなファイルを追加します.これらのステータスファイルのデフォルト名はマスターです.infoとrelay-log.info.ステータスファイルはハードディスク(HDD)に保存され、サーバーがシャットダウンしても失われません.次にサーバから起動すると、これらのファイルを読み込んで、プライマリ・サーバからどれだけのバイナリ・ログが読み込まれたか、および自分の中継ログが処理された程度を決定します.
付録1
ロゴについてbinログ
my.confファイルの[mysqld]タブの下のlog_binはログファイルを指定し、ファイル名が指定されていない場合、mysqlは自分でデフォルトのファイル名を生成します.mysqlはファイル名の後ろにデジタルインデックスを自動的に追加し、サービスを開始するたびに新しいバイナリファイルを再生成します.また、log-bin-indexを使用してインデックスファイルを指定します.binlog-do-dbを使用して、レコードのデータベースを指定します.binlog-ignore-dbを使用して、記録されていないデータベースを指定します.binlog-do-dbとbinlog-ignore-dbは一度に1つのデータベースのみを指定し、複数のデータベースに複数の文が必要であることを指定します.また、MySQLはすべてのデータベース名を小文字に変更し、データベースを指定するときはすべて小文字を使用する必要があります.そうしないと機能しません.
以降、データベースに対する操作はbinlogに記録されます.
付録2
試験環境:
ホストmyslqバージョン4.1.22
スレーブmyslqバージョン4.0.23 a