データベースのレプリケーション覚書

データベースのレプリケーション覚書です

いろいろ検索して調べてみて、やったが、今後忘れそうなので、メモしておくことにした。

マスター側の作業　と　レプリケーションされる側（スレーブ側）の作業とに分かれてやることが多い。

最初にやっておくこととして、

レプリケーション用ユーザーの作成とレプリケーションサーバーの実行について

1.マスターサーバーでレプリケーション専用のユーザーを作成します。

CREATE USER ‘replication_user’@‘%’ IDENTIFIED BY ‘password’;
GRANT REPLICATION SLAVE ON *.* TO ‘replication_user’@‘%’;
FLUSH PRIVILEGES;

mysql > show master status; を行い必要な情報を取得します。（ここだけ画像になります　等間隔のフォントで表示がうまくいかなかった。）

2.レプリカ側

mysql >
CHANGE MASTER TO
MASTER_HOST=’hostname’,
MASTER_USER=’replication_user‘,
MASTER_PASSWORD=’password’,
MASTER_LOG_FILE=’mysql-bin-changelog.263422′,
MASTER_LOG_POS=154;

実際に動かす時は、

START SLAVE;

方法が３つくらいある。

その１　データベースの容量が小さい場合

ステップ 1: マスターのデータをダンプ

1. マスターサーバーでデータベースのダンプを作成します。
  レプリケーションの設定には、--master-dataオプションを使用して、バイナリログの位置情報も含めることが重要です。
  これにより、スレーブはこのバイナリログの位置からレプリケーションを開始できます。

mysqldump -u root -p --all-databases --master-data=2 > master_dump.sql

-pオプションの後にはパスワードを入力するか、プロンプトで入力します。
--all-databasesは全てのデータベースをダンプしますが、特定のデータベースのみをダンプしたい場合は、このオプションを適宜変更してください。

ステップ 2: ダンプファイルをスレーブに転送

1. 作成したダンプファイルをスレーブサーバーに転送します。
  scpやrsyncなどのコマンドを使用できます。

scp master_dump.sql user@slave_server:/path/to/directory

ステップ 3: スレーブでダンプファイルをインポート

1. スレーブサーバーで、転送したダンプファイルをMySQLデータベースにインポートします。
  mysql -u root -p < master_dump.sql

ステップ 4: レプリケーションの設定

1. スレーブサーバーでレプリケーションを設定します。
  まず、MySQLにログインし、レプリケーション用のユーザーとその権限をマスターサーバーで作成していることを確認します。
  次に、スレーブサーバーで以下のコマンドを実行し、レプリケーションを開始します。
  CHANGE MASTER TO
  MASTER_HOST=’master_server_ip’,
  MASTER_USER=’replication_user’,
  MASTER_PASSWORD=’replication_password’,
  MASTER_LOG_FILE=’記録されたバイナリログファイル名’,
  MASTER_LOG_POS=記録されたバイナリログの位置;
  START SLAVE;
  
  MASTER_LOG_FILEとMASTER_LOG_POSは、master_dump.sqlファイル内に記載されているバイナリログのファイル名と位置です。

ステップ 5: レプリケーションの状態を確認

1. レプリケーションの状態を確認します。
  SHOW SLAVE STATUS\G
  Slave_IO_RunningとSlave_SQL_Runningの両方がYesになっていれば、レプリケーションは正常に設定されています。

　プロセスを通じて、マスターサーバーのデータがスレーブサーバーにコピーされ、マスターからの更新がリアルタイムでスレーブに反映されるようになります。

その２　データベースの容量が大きい場合

ほとんどの場合がデータベースがデカくて、引っ越しが大変時間かかる、なんとかマスターから引っ張り出して、同期させるレプリケーションサーバーに入れ込みたい。
そこで、私は、テーブルごとにデータを移行するアプローチの方法を選択しました。
この方法では、データベース全体を一度に移行する代わりに、個々のテーブルを順番にダンプしてレプリケーションサーバーへと移行します。
このプロセスは、システムへの負荷を最小限に抑えると同時に、もし移行中に問題が発生した場合に影響範囲を限定しやすくなります。

テーブルごとのデータ移行手順

ステップ 1: テーブルの選定

- 移行を開始する前に、どのテーブルを最初に移行するかを決定します。
  最も重要なデータ、またはサイズが小さく移行が比較的簡単なテーブルから始めるのが良いでしょう。

ステップ 2: テーブルのダンプ

- mysqldumpツールを使用して、選択したテーブルのダンプを作成します。
  例えば、database_nameデータベースのtable_nameテーブルをダンプするには、以下のコマンドを使用します。
  mysqldump -u username -p database_name table_name > table_name.sql

ステップ 3: ダンプファイルの転送とインポート

- ダンプファイルをレプリケーションサーバーに転送し、MySQLにインポートします。
  mysql -u username -p database_name < table_name.sql

注意点

- 移行中のデータ整合性: データをテーブルごとに移行する場合、移行中にデータが更新される可能性があります。
  これを管理するためには、--single-transactionオプションを使用して一貫性のあるスナップショットを取得するか、移行中のテーブルを読み取り専用に設定することが重要です。
- 移行作業の計画: 移行作業を実施する際は、データベースのダウンタイムやパフォーマンスへの影響を最小限に抑えるため、適切なタイミングを選択してください。

テーブルごとに移行するアプローチは、特に大規模なデータベースや、ダウンタイムを最小限に抑えたい場合に有効です。
計画的に進めることで、移行プロセスをスムーズに、かつ効率的に行うことが可能になります。

使い方

config.sh をつくる

mkdir replication
しておく。

table2table.shは、設定ファイルに機密情報（パスワードなど）を含める場合は、ファイルのアクセス権限を適切に設定して、不正アクセスを防ぐ必要があります。
chmod 600 config.sh
ファイルの所有者のみが読み書きできるように設定します。

table2table.shの権限を変更する
chmod +x ./table2table.sh

./table2table databese_name table_name

で実行する

例 config.sh
# config.sh
#master setting
USERNAME=’username’
PASSWORD=’master-password’
HOST=’master-host’
#replication setting
REPL_SERVER=’localhost’
REPL_PATH=’./replication’
REPL_DB_USERNAME=’replication-username’
REPL_DB_PASSWORD=’replication-password’

例 table2table.sh
# table2table.sh
#!/bin/bash
# 引数の確認
if [ “$#” -ne 2 ]; then
echo “Usage: $0 <db_name> <table_name>”
exit 1
fi
# コマンドライン引数からテーブル名を取得
DATABASE=$1
TABLE=$2
# 設定ファイルの読み込み
source ./config.sh
# 現在の日付をファイル名に使用
CURRENT_DATE=$(date +%Y%m%d_%H%M%S)
# 以下、スクリプトの内容…
# テーブルのダンプ
echo “Dumping table: $TABLE from database: $DATABASE”
mysqldump -u $USERNAME -h $HOST -p$PASSWORD $DATABASE $TABLE > “$REPL_PATH/${TABLE}_${CURRENT_DATE}.sql”
if [ $? -ne 0 ]; then
echo “Failed to dump table: $TABLE”
exit 1
else
echo “Dumped successfully: ${TABLE}_${CURRENT_DATE}.sql”
fi
# レプリケーションサーバーでのダンプファイルのインポート
echo “Importing dump file on replication server…”
echo “mysql -u $REPL_DB_USERNAME -p$REPL_DB_PASSWORD $DATABASE < $REPL_PATH/${TABLE}_${CURRENT_DATE}.sql”
mysql -u $REPL_DB_USERNAME -p$REPL_DB_PASSWORD $DATABASE < “$REPL_PATH/${TABLE}_${CURRENT_DATE}.sql”

if [ $? -ne 0 ]; then
echo “Failed to import dump file on replication server”
exit 1
else
echo “Import successful”
fi
echo “Process completed successfully”

ツール置き場

データベースのレプリケーション覚書