MySQLデータベースのリアルタイムバックアップの知識ポイントを詳しく解説

序文

リアルタイムのデータベース バックアップの必要性は非常に一般的です。MySQL 自体はレプリケーション メカニズムを提供します。公式の紹介は次のとおりです。

MySQL レプリケーションは、マスター データベースから 1 つ以上のスレーブ データベースにデータを同期できます。また、この同期プロセスはデフォルトで非同期で動作し、マスター データベースとスレーブ データベース間のリアルタイム接続を維持する必要はありません (つまり、接続の中断が許可されます)。また、同期する必要があるデータベースとデータ テーブルのカスタム構成も可能です。

MySQL レプリケーションの利点と適用シナリオは次のとおりです。

1. MySQL レプリケーションを使用して、負荷分散と読み取り/書き込み分離 (マスター データベースは更新のみ、スレーブ データベースは読み取りのみ) を実現し、データベースのパフォーマンスを向上させます。

2. データのセキュリティを確保するために、MySQL レプリケーションを通じてリアルタイムのデータ バックアップが実現されます。

3. MySQL レプリケーションを介してオフライン データ分析を実装します (マスター データベースがデータを生成し、スレーブ データベースの分析と計算はマスター データベースのパフォーマンスに影響を与えません)。

4. データの配信。

MySQL レプリケーションの完全な公式ドキュメントについては、https://dev.mysql.com/doc/refman/5.7/en/replication.html を参照してください。

仕組み

1111

1. マスター内のすべてのデータベース変更イベントはバイナリログファイルに書き込まれます。

2. スレーブで「SLAVE START」コマンドが実行されると、スレーブI/Oスレッドが開始され、マスターに接続されます。

3. マスターはスレーブI/Oスレッドの接続を検出し、ログジャンプスレッドを開いて応答します。

4. マスターバイナリログは、マスターログジャンプスレッドとスレーブI/Oスレッドを介してスレーブリレーログに送信されます。

5. スレーブSQLスレッドがリレーログをデータに復元し、同期が完了します。

注: 「SHOW PROCESSLIST」コマンドを使用して、マスターとスレーブの対応するスレッドの実行ステータスを表示できます。

マスターの設定

バイナリ ログを有効にし、ServerID を設定します。ServerID は一意である必要があり、1 から 232-1 の範囲にすることができます。

[mysqld]
# バイナリログを有効にする
ログ bin = mysql bin
# グローバルIDを設定する
サーバーID=1

# 同期するデータベースを指定します (データベース名にカンマが含まれる場合があるため、複数のデータベースはカンマで区切るのではなく、複数回設定する必要があります)
binlog-do-db=データベース名
#同期を禁止するデータベースを指定します binlog-ignore-db=database_name
# バイナリログ形式を指定する binlog_format=MIXED

同期アカウントを作成する

各スレーブはマスター データベースに接続するためにアカウントとパスワードを使用する必要があるため、マスター データベースにアカウントを提供する必要があります。独立したアカウントを使用し、データ同期の権限のみを承認することをお勧めします。

'password' で識別されるユーザー 'repl'@'%.example.com' を作成します。
*.* のレプリケーションスレーブを 'repl'@'%.example.com' に付与します。

バイナリログ情報を取得する

スレーブが I/O スレッドを開始するときは、バイナリ ログからいくつかの情報を渡す必要があるため、次のバイナリ ログ情報を取得する必要があります。

マスターステータスを表示します。

SHOW MASTER STATUS コマンドを実行してバイナリ ログ情報を取得し、ファイルおよび位置フィールドの値を記録します。

同期前にマスターとスレーブのデータが一致していることを確認する

スレーブが I/O スレッドを開始する前に、マスターとスレーブのデータが一貫していることを確認する必要があります。そのため、最初にマスターをロックし (データの変更を防ぐため)、データの一貫性を確保した後、手動で同期してロックを解除します。

読み取りロック付きでテーブルをフラッシュします。

手動データ同期関連の操作について簡単に説明します...

テーブルのロックを解除します。

スレーブの設定

ServerID を設定するには、BinLog を有効にする必要はありません。

[mysqld]
# グローバルIDを設定する
サーバーID=2

# 同期されたデータベースを指定します replicate-do-db=database_name
#同期を禁止するデータベースを指定しますreplicate_ignore_db=database_name

マスター情報を設定するには、次のコマンドを実行します。

mysql> マスターを変更する
  -> MASTER_HOST='マスターホスト名',
  -> MASTER_PORT='master_host_port',
  -> MASTER_USER='レプリケーションユーザー名',
  -> MASTER_PASSWORD = 'レプリケーションパスワード'、
  -> MASTER_LOG_FILE='記録されたログファイル名',
  -> MASTER_LOG_POS=記録されたログの位置;

I/Oスレッドの開始

スレーブを起動します。

同期ステータスを確認します。

スレーブステータスを表示します。

マスターのbinlog_formatパラメータ

binlog_format はバイナリ ログの形式を構成するために使用され、次の 3 つのタイプをサポートします。

行

データ行に基づいて変更を記録します。このモードは、SQL ステートメント、ストアド プロシージャ、関数、トリガーなどとは関係ありません。各行のデータが変更されたかどうかのみを考慮します。変更された場合は記録されます。したがって、行モードの精度は最も高くなります。しかし、欠点としては、テーブル構造が変更された場合など、場合によっては大量のコンテンツが生成され、効率が低下することがあります。

声明

SQL ステートメントによる記録は明らかに行モードの欠点を解決しますが、SQL ステートメントは非常に複雑で予期しない状況が発生しやすいため、精度が十分に高くないという問題があります。

混合

行とステートメントの混合モードでは、MySQL は行を使用するタイミングとステートメントを使用するタイミングを自動的に決定します。これはデフォルトのモードでもあります。

Replicate-do-db ノート

スレーブで replicate-do-db および replicate-ignore-db 構成項目を使用する場合、次のようなデータベース間の SQL ステートメントは同期されないことに注意してください。

複製するDB=a
b を使用します。
a.some_table を更新し、some_field を 'some value' に設定します。

解決策は、次のように、replicate_wild_do_table と replicate_wild_ignore_table を使用することです。

replicate_wild_do_table=データベース名.%
replicate_wild_ignore_table=データベース名.%