MySQL データベースの同期

1. MySQL データベースの同期
2. ソース マシン ファイアウォールの構成
3. ソース データベースの構成
4. レプリケーション ユーザーの作成
5. スレーブ サーバーの構成

この記事は、バックアップと記録保持のためにデータベースの同期を実現する方法を示すことを目的としています。

MySQL データベースの同期

大量のデータを処理する場合、データが危険にさらされる可能性があるシナリオを考慮することが重要になる場合があります。

侵害された理由は、この特定の問題に取り組むためのさまざまな戦略を設計または計画する際に考慮しなければならない多くの要因に依存する可能性があります。

検索手段の実装を選択することは非常に重要です。 データの価値によっては、データが永久に失われるため、バックアップの予約なしでデータが技術的な障害で失われた場合、企業または施設に莫大な損失をもたらす可能性があります。

ソリューション

この問題の解決策は、条件や制限によって異なります。 全体として、データベースの最も一般的なアプローチの 1つを以下に示します。

1. クライアント/サーバー モデル - データベースを使用する代わりに、サーバーと直接通信するアプリケーションを使用してデータにアクセス/保存します。
2. マスター/スレーブ モデル - 1つのサーバーがデータの書き込み (マスター) に割り当てられ、もう 1つのサーバー (複数の場合もある) はデータの読み取り専用 (スレーブ) に割り当てられます。
3. オフライン モデル - 一定時間後、必要な処理をローカルで行い、サーバーに送信します。

この記事では、マスター/スレーブ モデルの実装について説明します。

前提条件

1. 2 台のマシンが Ubuntu (20.0+) を実行するサーバーとして機能します。 各マシンには、管理者以外のユーザーとsudo権限が適切に構成されている必要があります。 また、ファイアウォールを UFW で構成する必要があります。
2. MySQL が両方のマシンにインストールされています。 できれば最新バージョン (8.0+)。

ソース マシン ファイアウォールの構成

UFW を使用してファイアウォールを設定すると、ファイアウォールはレプリカ サーバーからの着信接続をすべてブロックします。 これを修正するには、ソース マシンで次のコマンドを実行します。

sudo ufw allow REPLICA_SERVER_IP_ADDR to any port MySQL_PORT

正常に実行された場合、出力が表示されます。

Rule Added

次のコマンドでは、次のことに注意してください。

1. REPLICA_SERVER_IP_ADDR - レプリカ サーバーの IP アドレス。
2. MySQL_PORT - MySQL サーバーのポート (デフォルトは 3306)。

上記のコマンドでは、IP REPLICA_SERVER_IP_ADDRからポートMySQL_PORTへの着信接続を許可するように指示するルールをUFWに追加しています。

ソース データベースの構成

ソース データベースがデータ レプリケーションを続行するには、一部の設定を微調整する必要があります。

そのためには、mysqld.cnf という名前の MySQL サーバー構成ファイルにアクセスする必要があります。 Ubuntu 20 以降では、/etc/mysql/mysql.conf.d/ ディレクトリにあります。

好みのテキスト エディタを使用してこのファイルを開きます。 または、次のコマンドを使用します。

sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf

mysql.cnf ファイルで、次の行を検索します。

. . .
bind-address            = 127.0.0.1
. . .

bind-address プロパティは、データベースが localhost を表す 127.0.0.1 からの接続を受け入れる場所を指定します。 これは、サーバーが現在インストールされているマシンからの接続要求のみをサーバーが受け入れることを意味します。

レプリカ サーバーからの接続を受け入れるには、bind-address プロパティに指定されたアドレスを変更する必要があります。 ソースサーバーのIPアドレスに変更するとうまくいきます。

IP アドレスを変更すると、ファイルは次のようになります。

. . .
bind-address            = SRC_SERVER_IP_ADDR
. . .

SRC_SERVER_IP_ADDR は、ソース マシンの IP アドレスです。

次に、マスターに一意の ID を割り当てる必要があります。 各サーバーは複製された環境の一部になるため、サーバー間で競合が発生しないように、各サーバーに一意の ID を割り当てる必要があります。

これを行うには、mysql.cnf ファイルで server-id を検索します。 結果は次のようになります。

. . .
# server-id             = 1
. . .

# 記号は、この行が現在コメント アウトされていることを示します。 記号を削除し、サーバーに割り当てる任意の整数値を選択します。 変更を加えると、行は次のようになります。

. . .
server-id             = 10
. . .

その後、log_bin ディレクティブを検索し、コメントを外します。 log_bin は、レプリカ サーバーがデータベースの複製方法を理解するために読み取るために必要です。

変更後、行は次のようになります。

. . .
log_bin                       = /var/log/mysql/mysql-bin.log
. . .

最後に、binlog_do_db ディレクティブを検索します。 この行にもコメントが付けられます。コメントを解除し、レプリケートするデータベースの名前としてその値を設定します。 変更後は、次のようになります。

. . .
binlog_do_db          = DB_NAME

# For multiple DBs
binlog_do_db          = DB_NAME_1
binlog_do_db          = DB_NAME_2
. . .

または、特定のデータベースを除外し、binlog_ignore_db を使用して残りを複製することもできます。

. . .
binlog_ignore_db          = DB_NAME_TO_IGNORE
. . .

mysqld.cnf でこれらの変更を行った後、ファイルを保存し、mysql サービスを再起動します。

以下のコマンドを使用してこれを行うことができます。

sudo systemctl restart mysql

レプリケーション ユーザーの作成

レプリカ サーバーは、ソース サーバーに接続するためのユーザー名とパスワードを提供する必要があります。 レプリカ サーバーは、ソース マシンで使用可能な任意のユーザー プロファイルを使用して接続できますが、このチュートリアルでは、レプリケーション専用のユーザーを作成します。

手順に従って、レプリケーション用のユーザーを作成します。

ソース マシンで、次のコマンドを実行します。

sudo mysql -u USER -p PASS

どこ：

1. USER - ユーザーのユーザー名
2. PASS - ユーザーのパスワード

MySQL プロンプト内で、次のコマンドを使用して新しいユーザーを作成できます。

mysql> CREATE USER 'REPLICA_USER'@'REPLICA_IP' IDENTIFIED WITH mysql_native_password BY 'PASS';

どこ：

1. REPLICA_USER - レプリカ サーバーのユーザーのユーザー名
2. REPLICA_IP - レプリカ マシンの IP アドレス
3. PASS - ユーザーのパスワード

ユーザーが正常に作成されたら、適切な権限を付与する必要があります。 ユーザーは、少なくとも REPLICATION SLAVE を持っている必要があります。

次のコマンドを使用してユーザー アプリケーションを許可します。

mysql> GRANT REPLICATION SLAVE ON *.* TO 'REPLICA_USER'@'REPLICA_IP';

その後、GRANT および CREATE USER コマンドを使用して、使用されているメモリとキャッシュをクリアするために権限をフラッシュする必要があります。

mysql> FLUSH PRIVILEGES;

最後に、データベースのステータスを確認し、読み取りロックを使用して、ログ ステータス キャプチャの進行中に書き込み操作が発生しないようにする必要があります。

これを行うには、次のコマンドを実行します。

mysql> FLUSH TABLES WITH READ LOCK;

次に、次のコマンドを使用してサーバーのステータスを確認します。

mysql> SHOW MASTER STATUS;

次の形式で出力が返されます。

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      899 | db           |                  |                   |
| mysql-bin.000002 |      900 | db2          |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
2 rows in set (0.01 sec)

この後、mysqldump を使用してデータベース全体を複製できます。

sudo mysqldump -u root db > db.sql

new_db.sql という名前のファイルが生成されます。これをスレーブ マシンに転送して、バックアップ プロセスを完了することができます。

バックアップが完了すると、テーブルのロックを解除できます。

mysql> UNLOCK TABLES

スレーブ サーバーの構成

スレーブ サーバーでは、データベースが複製される新しいデータベースを作成する必要があります。 次のように実行できます。

mysql> CREATE DATABASE DATABASE_NAME

エクスポートされた sqldump をソース マシンからインポートします。

sudo mysql db < /PATH/db.sql

データベースがスレーブ マシンにインポートされたら、mysqld.cnf のディレクティブを次のように構成します。

server-id = 11
log_bin = /data/mysql/mysql-bin.log
binlog_do_db = DATABASE_NAME

必要な変更を行った後、MySQL サービスを再起動します。

sudo systemctl restart mysql

最後に、サーバーからのデータの複製を開始するには、次のコマンドを実行します。

ログイン後の MySQL プロンプトで、次のように実行します。

mysql> CHANGE REPLICATION SOURCE TO
mysql> SOURCE_HOST='SRC_SERVER_IP_ADDR',
mysql> SOURCE_USER='REP_USER',
mysql> SOURCE_PASSWORD='PASS',
mysql> SOURCE_LOG_FILE='mysql-bin.000001',
mysql> SOURCE_LOG_POS=899;

最後に、スレーブサーバーを起動します。

mysql > START REPLICA

これで、サーバーはソース サーバーからの変更のレプリケートを開始します。

レプリカ サーバーのステータスを表示するには、次のコマンドを使用します。

SHOW REPLICA STATUS\G

\G は、読みやすくするために出力を再フォーマットします。

上記のコマンドの出力は、次の形式になります。