MySQLレプリケーションとは？MySQLレプリケーションの仕組みとその設定手順

この記事でわかること

MySQLレプリケーションとは？

MySQLレプリケーションとは、MySQLのデータベースの内容を1つ、または複数の別のMySQLに複製しながら運用する技術です。

はじめに、MySQLレプリケーションの機能とそのメリットについて紹介します。

そもそもレプリケーションとは

レプリケーションとは、同じソフトウェアを設定したコンピュータを複数台用意し、それらで扱うデータの一貫性を保ちながら、複数台のコンピュータで情報を共有する仕組みです。

複数のコンピュータの間でリアルタイムな一貫性を保ちつつ更新される点が特徴で、負荷分散や障害対策として使われます。

特にデータベースサーバーは負荷が集中しやすく、障害が発生した場合の被害が大きいことから、多くのシステムで使われるデータベースで負荷分散および障害対策としてレプリケーションが適用されています。

なお、レプリケーションは英語の「Replication」が基になったIT用語で、複製や複写などの意味をもつ言葉です。

データベースのレプリケーションの構成

データベースのレプリケーション構成は、1台のサーバーがマスターとして動作し、別のサーバーがマスターのデータの複製を保持するスレーブとして動作します。

そのため、全く同じ構成のデータベースサーバーを複数台用意し、データの新規作成と更新をマスターに担当させ、データの読み取りのみをスレーブに担当させる構成が一般的です。

例えば、本社に設置したマスターでデータの新規作成と更新を実施し、支店に設置したスレーブは「読み出し専用」といった構成で運用します。

また、大規模なWebシステムで使われるデータベースでは、新規作成と更新はマスターの1台だけで担当し、Webページを表示するためにアクセスされるデータベースは複数のスレーブで分担する、といった構成が可能です。

レプリケーションとバックアップの違い

今回紹介しているレプリケーションは、自動的にデータの複製が作られるためバックアップと似ていますが、全く違う仕組みです。

レプリケーションでは、もしマスターに誤ったデータが登録されてしまうと、スレーブにもそれが反映されてしまいます。一方、バックアップは誤ったデータが登録される前の状態に戻せます。

そのため、データベースを復旧するには、スナップショットで過去の履歴管理ができるバックアップを使用するのが一般的です。

障害対策としてレプリケーションを導入したとしても、バックアップが不要ではないので注意してください。

MySQLレプリケーションの設定方法

ここからは、MySQLレプリケーションを設定方法を見て行きましょう。

まず、マスターとなるMyQLサーバーとスレーブとなるMyQLサーバーを設定し、それぞれの設定ファイル「my.cnf」にマスターとスレーブそれぞれの設定を登録し、それを有効にしてマスターのデータをスレーブに複製します。

以下で、この設定の詳細について見て行きましょう。

マスター構成の設定

MySQLレプリケーションのマスターを設定するには、設定ファイルmy.cnfにバイナリログを有効にしてサーバーIDを設定します。

この時、バイナリログを利用してマスターからスレーブへデータベースの変更を複製するので、マスター側で必ず設定してください。また、サーバーIDはMySQLレプリケーションを組むサーバーの中でユニークな数字を設定します。

マスター側my.cnfの設定例

[mysqld] log-bin=mysql-bin server-id=1

1 2 3 4 5

[mysqld] log-bin=mysql-bin server-id=1

上記はバイナリログの出力を有効にして、サーバーIDに「1」を設定した例です。なおサーバーIDの数字を省略したり「０」を指定したりすると、スレーブからの接続を拒否してしまうので、0以外の数字を割り当ててください。

また、MySQLの設定ファイルを編集する前に、MySQLサービスの停止が必要です。そして設定ファイルを編集したらサービスを実行してください。

MySQLレプリケーション用アカウントを作成

マスター側にMySQLレプリケーション専用のアカウントを作成します。なお、MySQLレプリケーションでアクセスするには「REPLICATION SLAVE権限」が必要なので、アカウント作成時に設定してください。

アカウントの作成例

mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

1 2 3 4

mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

スレーブ構成の設定

MySQLレプリケーションのスレーブを設定するには、設定ファイルmy.cnfにサーバーIDを設定します。サーバーIDはMySQLレプリケーションを組むサーバーの中でユニークな数字を設定してください。

スレーブ側my.cnfの設定例

[mysqld] server-id=2

1 2 3 4

[mysqld] server-id=2

なお、MySQLの設定ファイルを編集する前に、必ずMySQLのサービスを停止してください。また、編集を反映するためにサービスの実行が必要です。

スレーブでのマスター構成の設定

スレーブでのマスター構成を設定するには、MySQLのCHANGE MASTER TOステートメントを利用します。なお、MySQLレプリケーションは、スレーブ側からマスター側の更新データを取得してデータベースの複製を作成します。

CHANGE MASTER TOステートメントの引数で次の項目を設定してください。

MASTER_HOST：マスターのホスト名またはIPアドレス MASTER_USER：マスターに接続するユーザ名 MASTER_PASSWORD：マスターに接続するユーザのパスワード MASTER_LOG_FILE：バイナリログのファイル名 MASTER_LOG_POS：バイナリログの現在位置

1 2 3 4 5 6 7

MASTER_HOST：マスターのホスト名またはIPアドレス MASTER_USER：マスターに接続するユーザ名 MASTER_PASSWORD：マスターに接続するユーザのパスワード MASTER_LOG_FILE：バイナリログのファイル名 MASTER_LOG_POS：バイナリログの現在位置

MySQLレプリケーション設定のSQLの例

mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.1.1', -> MASTER_USER='repl', -> MASTER_PASSWORD='replication_password', -> MASTER_LOG_FILE='mysql-bin', -> MASTER_LOG_POS=4;

1 2 3 4 5 6 7 8

mysql> CHANGE MASTER TO     ->     MASTER_HOST='192.168.1.1',     ->     MASTER_USER='repl',     ->     MASTER_PASSWORD='replication_password',     ->     MASTER_LOG_FILE='mysql-bin',     ->     MASTER_LOG_POS=4;

既存のDBをMySQLレプリケーションで複製するには

既存のデータベースをMySQLレプリケーションで複製するには、マスター側の既存のデータベースからダンプファイルを作成し、それをスレーブ側で取り込んでデータベースをコピーする作業が必要です。

なお、MySQLレプリケーションはマスター側のバイナリログから変更点を読み込んで、スレーブ側を同期します。そのため、データベースを移動している最中にデータが更新されないようにデータをロックしてからダンプファイルを作成してください。さらにバイナリログの現在位置の指定も必要です。

以下で、既存のデータベースをMySQLレプリケーションで複製する手順について紹介します。

バイナリログの現在位置

先ほどのスレーブでのマスター構成の設定で設定するバイナリログの現在位置は、SHOW MASTER STATUSステートメントでチェックします。

ただし、データが更新されてしまうとバイナリログの現在位置も変わってしまうので、SHOW MASTER STATUSステートメントを実行する前に、必ずデータをロックしてから実行してください。

なお、データをロックするにはFLUSH TABLES WITH READ LOCKステートメントを実行します。

バイナリログの現在位置をチェックする例

mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 73 | test | manual,mysql | +------------------+----------+--------------+------------------+

1 2 3 4 5 6 7 8 9

mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 73       | test         | manual,mysql     | +------------------+----------+--------------+------------------+

この例では、バイナリログの現在位置が73だとわかります。

ダンプファイルでテーブルをコピーする

続いて、mysqldumpコマンドを用いてダンプファイルを作成し、そのダンプファイルを使ってマスター側のデータをスレーブ側にコピーします。

mysqldumpコマンドの実行例

$ mysqldump --single-transaction -u root -p example > example.dump

1 2 3

$ mysqldump --single-transaction -u root -p example > example.dump

上記の例ではデータベース「example」のダンプファイル「example.dump」を作成します。作成したダンプファイルexample.dumpをスレーブ側にコピーし、mysqlコマンドで取り込んでください。

ダンプデータの取り込み例

mysql> CREATE DATABSE example; mysql> USE example; mysql> SOURCE ~/example.dump;

1 2 3 4 5

mysql> CREATE DATABSE example; mysql> USE example; mysql> SOURCE ~/example.dump;

データベースをコピーした後、スレーブでのマスター構成の設定を実行し、UNLOCK TABLESステートメントでマスター側のロックを解除すればレプリケーションが開始されます。

マスター側のロックを解除するSQL

mysql> UNLOCK TABLES;

1 2 3

mysql> UNLOCK TABLES;

MySQLレプリケーションを設定し運用するには

ここまで紹介したように、MySQLレプリケーションは、データベースの負荷分散と障害対策として有効で、設定も容易です。しかし、それで終わりではありません。

MySQLレプリケーションを設定したら、稼働状況を定期的にチェックし、運用していく必要があります。

稼働状況をチェックするには、MySQLのSHOW SLAVE STATUSステートメントを使います。エラーが発生していないことをチェックしてください。

今回紹介した内容を参考に、MySQLレプリケーションをうまく活用してデータベースを運用してください。

FEnetを運営しているネプラス株式会社はサービス開始から10年以上
『エンジニアの生涯価値の向上』をミッションに掲げ、
多くのインフラエンジニア・ネットワークエンジニアの就業を支援してきました。

ネプラス株式会社はこんな会社です

秋葉原オフィスにはネプラス株式会社をはじめグループのIT企業が集結！
数多くのエンジニアが集まります。

• インフラ業界に特化

  

  ネットワーク・サーバー・データベース等、ITインフラ業界に特化。Cisco Systemsプレミアパートナーをはじめ各種ベンダーのパートナー企業です。

  業界を知り尽くしているからこそ大手の取引先企業、経験豊富なエンジニアに選ばれています。

• 正社員なのにフリーランスのような働き方

  

  正社員の方でも希望を聞いたうえでプロジェクトをアサインさせていただいており、フリーランスのような働き方が可能。帰社日もありません。

  プロジェクト終了後もすぐに次の案件をご紹介させていただきますのでご安心ください。

• 大手直取引の高額案件

  

  案件のほとんどが大手SIerやエンドユーザーからの直取引のためエンジニアの皆様へに高く還元できています。

  Ciscoをはじめ、Juniper、Azure、Linux、AWS等インフラに特化した常時300件以上の案件があります。

• スキルアップ支援

  

  不要なコストを削減し、その分エンジニアの方へのスキルアップ支援(ネットワーク機器貸出、合格時の受験費用支給など)や給与で還元しています。

  受験費用例）CCNP,CCIE:6-20万円、JNCIS:3-4万円、AWS:1-3万円など

  ※業務に関連する一定の資格のみ。各種条件がありますので詳しくは担当者へにお尋ねください。

• 現給与を保証します！※

  

  前職の給与保証しており、昨年度は100%の方が給与アップを実現。収入面の不安がある方でも安心して入社していただけます。

  ※適用にはインフラエンジニアの業務経験1年以上、等一定の条件がございます。

• インセンティブ制度

  

  ネットワーク機器の販売・レンタル事業等、売上に貢献いただいた方にはインセンティブをお支払いしています。

  取引先企業とエンジニア側、双方にメリットがあり大変好評をいただいています。

• 社会保険・福利厚生

  

  社員の方は、社会保険を完備。健康保険は業界内で最も評価の高い「関東ITソフトウェア健康保険組合」です。

  さらに様々なサービスをお得に利用できるベネフィットステーションにも加入いただきます。

• 東証プライム上場企業グループ

  

  ネプラスは東証プライム上場「株式会社夢真ビーネックスグループ」のグループ企業です。

  安定した経営基盤とグループ間のスムーズな連携でコロナ禍でも安定した雇用を実現させています。

ネプラス株式会社に興味を持った方へ

ネプラス株式会社では、インフラエンジニアを募集しています。

年収をアップしたい！スキルアップしたい！大手の上流案件にチャレンジしたい！
まずは話だけでも聞いてみたい場合もOK。お気軽にご登録ください。

MySQL新着案件New Job

• サーバ構築・運用設計/東京都渋谷区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務

  年収588万～588万円
  東京都渋谷区(渋谷駅)
  • Apache
  • Tomcat
  • MySQL
  • Linux

• 【高額年収】/基盤運用保守/東京都千代田区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務

  年収600万～720万円
  東京都千代田区(溜池山王駅)
  • EMC
  • Linux
  • MySQL
  • Oracle
  • Unix
  • VMware

• 【高額年収】/国内クラウドシステムのサーバ運用保守/東京都新宿区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務

  年収600万～720万円
  東京都新宿区(東新宿駅)
  • MySQL
  • PostgreSQL
  • Oracle
  • Apache

• 国内クラウドシステムのサーバ構築のテスター/東京都新宿区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務

  年収300万～420万円
  東京都新宿区(東新宿駅)
  • MySQL
  • PostgreSQL
  • Oracle
  • Apache

• 【高額年収】/社内情報システムインフラ運用保守/Windows/東京都多摩北部/【WEB面談可】/在宅勤務/20代～30代の方活躍中

  年収600万～720万円
  東京都多摩北部(-駅)
  • A10
  • Alaxala
  • Azure
  • Brocade
  • Cisco
  • F5
  • FortiGate
  • Juniper
  • MySQL
  • NetApp
  • Oracle
  • Paloalto
  • PostgreSQL
  • Windows

• 社内情報システムインフラ構築のテスター/Windows/東京都多摩北部/【WEB面談可】/在宅勤務/20代～30代の方活躍中

  年収300万～420万円
  東京都多摩北部(-駅)
  • A10
  • Alaxala
  • Azure
  • Brocade
  • Cisco
  • F5
  • FortiGate
  • Juniper
  • MySQL
  • NetApp
  • Oracle
  • Paloalto
  • PostgreSQL
  • Windows