モーダルを閉じる工作HardwareHub ロゴ画像

工作HardwareHubは、ロボット工作や電子工作に関する情報やモノが行き交うコミュニティサイトです。さらに詳しく

利用規約プライバシーポリシー に同意したうえでログインしてください。

工作HardwareHub ロゴ画像 (Laptop端末利用時)
工作HardwareHub ロゴ画像 (Mobile端末利用時)

MySQL 5.7 レプリケーション設定

モーダルを閉じる

ステッカーを選択してください

モーダルを閉じる

お支払い内容をご確認ください

購入商品
」ステッカーの表示権
メッセージ
料金
(税込)
決済方法
GooglePayマーク
決済プラットフォーム
確認事項

利用規約をご確認のうえお支払いください

※カード情報はGoogleアカウント内に保存されます。本サイトやStripeには保存されません

※記事の執筆者は購入者のユーザー名を知ることができます

※購入後のキャンセルはできません

作成日作成日
2016/11/08
最終更新最終更新
2019/03/14
記事区分記事区分
一般公開

目次

    アカウント プロフィール画像 (サイドバー)

    MySQLの運用やレプリケーション設定など、実用的なノウハウを共有します。

    0
    ステッカーを贈るとは?

    MySQL 5.7 を用いてレプリケーション設定を行います。

    検証用の環境を構築 (mysqld_mutil)

    検証のため、ここでは mysqld_mutil を用いて複数の Port で mysqld を起動します。公式ページはこちらです。yum repository を追加した場合は PATH が通った状態で mysqld_multi コマンドが利用できます。

    $ which mysqld_multi
    /usr/bin/mysqld_multi
    

    コマンドは Perl スクリプトです。

    $ file /usr/bin/mysqld_multi 
    /usr/bin/mysqld_multi: a /usr/bin/perl script text executable
    

    mysqld_multi --help を参考に my.cnf を変更します。

    [mysqld]
    # 本項目は mysqld_multi コマンドからは参照されません。
    port=3306
    socket=/tmp/mysql.sock
    datadir=/data/mysql
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
    [mysql]
    # 本項目は mysqld_multi コマンドからは参照されません。
    socket=/tmp/mysql.sock
    
    [mysqld_multi]
    mysqld=/usr/bin/mysqld_safe
    mysqladmin=/usr/bin/mysqladmin
    
    [mysqld1]
    port=13306
    socket=/tmp/mysql_master.sock
    datadir=/data/mysql_master
    log-error=/var/log/mysqld_master.log
    pid-file=/var/run/mysqld/mysqld_master.pid
    
    [mysqld2]
    port=13307
    socket=/tmp/mysql_slave.sock
    datadir=/data/mysql_slave
    log-error=/var/log/mysqld_slave.log
    pid-file=/var/run/mysqld/mysqld_slave.pid
    

    datadir を初期化します。

    sudo mkdir -p /data/mysql_master
    sudo mkdir -p /data/mysql_slave
    sudo /usr/sbin/mysqld --no-defaults --initialize --user=mysql --datadir=/data/mysql_master
    sudo /usr/sbin/mysqld --no-defaults --initialize --user=mysql --datadir=/data/mysql_slave
    sudo /usr/bin/mysql_ssl_rsa_setup --no-defaults --datadir=/data/mysql_master
    sudo /usr/bin/mysql_ssl_rsa_setup --no-defaults --datadir=/data/mysql_slave
    sudo chown -R mysql: /data/mysql_master
    sudo chown -R mysql: /data/mysql_slave
    

    以下のコマンドで起動します。

    sudo mysqld_multi start
    

    起動したことを確認します。

    sudo mysqld_multi report
    

    root パスワードの変更

    確認した初期パスワードでログインします。

    mysql -uroot -S /tmp/mysql_master.sock -p
    mysql -uroot -S /tmp/mysql_slave.sock -p
    

    以下のコマンドでパスワードを変更します。

    ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
    

    パスワードなしでログインできるようにするためには /etc/my.cnf を編集して再起動します。

    sudo vim /etc/my.cnf
    skip-grant-tables  ← [mysqld1], [mysqld2] セクションに追記
    sudo mysqld_multi stop  ← 初回は停止しないため sudo pkill -9 mysql
    sudo mysqld_multi start
    

    ただし skip-grant-tables による影響で GRANT が使用できなくなります。skip-grant-tables をコメントアウトして再起動してから root のパスワードを空に設定することでパスワード入力なし、かつ GRANT が使用可能な状態になります。

    sudo mysqld_multi stop
    sudo vim /etc/my.cnf
    sudo mysqld_multi start
    mysql -uroot -S /tmp/mysql_master.sock -p
    mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';
    mysql -uroot -S /tmp/mysql_slave.sock -p
    mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';
    

    パスワードが失効しない設定であることを確認

    mysql 5.7 には、一定期間が経過するとパスワードを失効させてログインできなくする機能があります。不要な場合は、以下のコマンド結果が 0 であることを確認して無効になっていることを確認します。

    select @@default_password_lifetime;
    

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

    $ mysql -uroot -S /tmp/mysql_master.sock
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'replpass';
    mysql> SHOW GRANTS FOR 'repl'@'%';
    

    スレッドの種類について

    repl ユーザーでログインした状態でレプリケーションが開始されると master のマスタースレッドから slave の I/O スレッドに binlog が転送され、I/O スレッドによって relaylog に書き込まれます。転送モードには gtid_mode が OFF と ON の二種類があります。slave の SQL スレッドは relaylog から読み出した SQL を実行します。SHOW SLAVE STATUS\G で確認される Seconds_Behind_Master は SQL スレッドの遅れを表現しています。

    GTID を用いたレプリケーション設定

    MySQL 5.6 から導入された GTID は既定では gtid_mode = OFF になっています。これは MASTER_LOG_FILE および MASTER_LOG_POS を利用したレプリケーションを行うモードです。設定方法は MySQL 5.5 と同じです。以降、本ページでは GTID を用いた非同期レプリケーション設定を対象とします。公式ページはこちらです。

    mysql> SHOW VARIABLES LIKE '%gtid%';
    +----------------------------------+-----------+
    | Variable_name                    | Value     |
    +----------------------------------+-----------+
    | binlog_gtid_simple_recovery      | ON        |
    | enforce_gtid_consistency         | OFF       |
    | gtid_executed_compression_period | 1000      |
    | gtid_mode                        | OFF       |
    | gtid_next                        | AUTOMATIC |
    | gtid_owned                       |           |
    | gtid_purged                      |           |
    | session_track_gtids              | OFF       |
    +----------------------------------+-----------+
    8 rows in set (0.00 sec)
    

    my.cnf の編集

    GTID を用いたレプリケーション設定を my.cnf に追記します。オンラインでも変更可能ですが、手順がやや複雑なため本ページでは停止してオフラインで変更しています。

    $ sudo mysqld_multi stop
    $ sudo mysqld_multi report
    Reporting MySQL servers
    MySQL server from group: mysqld1 is not running
    MySQL server from group: mysqld2 is not running
    $ sudo vim /etc/my.cnf
    

    /etc/my.cnf

    [mysqld_multi]
    mysqld=/usr/bin/mysqld_safe
    mysqladmin=/usr/bin/mysqladmin
    
    [mysqld1]
    port=13306
    socket=/tmp/mysql_master.sock
    datadir=/data/mysql_master
    log-error=/var/log/mysqld_master.log
    pid-file=/var/run/mysqld/mysqld_master.pid
    # レプリケーションサーバー間で重複しない ID
    server_id=100001
    # GTID モード有効化
    gtid_mode=ON 
    # GTID 互換のない SQL の実行を禁止 (GTID では SQL に制約がつきます)
    enforce_gtid_consistency=ON
    # binlog 出力設定 (後者二つは master では無意味ですが slave と同じ設定にしたいため)
    log_bin=mysql-bin
    log_slave_updates
    relay_log=mysqld-relay-bin
    # sync_binlog=0 # ディスク使用率を下げたい場合 (ただし、予期せぬクラッシュ時に binlog に書き出されないトランザクションが発生し得ります)
    
    [mysqld2]
    port=13307
    socket=/tmp/mysql_slave.sock
    datadir=/data/mysql_slave
    log-error=/var/log/mysqld_slave.log
    pid-file=/var/run/mysqld/mysqld_slave.pid
    # レプリケーションサーバー間で重複しない ID
    server_id=100002
    # GTID モード有効化
    gtid_mode=ON
    # GTID 互換のない SQL の実行を禁止 (GTID では SQL に制約がつきます)
    enforce_gtid_consistency=ON
    # binlog 出力 (GTID モードでは slave も binlog 出力が代替手段を設定しない限り必要。relay_log の名称も指定しておくとホスト名の変更の影響を受けないため安心)
    log_bin=mysql-bin
    log_slave_updates
    relay_log=mysqld-relay-bin
    # sync_binlog=0 # ディスク使用率を下げたい場合 (ただし、予期せぬクラッシュ時に binlog に書き出されないトランザクションが発生し得ります)
    

    起動して設定を確認します。

    sudo mysqld_multi start
    sudo mysqld_multi report
    mysql -uroot -S /tmp/mysql_master.sock
    mysql -uroot -S /tmp/mysql_slave.sock
    
    mysql> SHOW VARIABLES LIKE '%gtid%';
    +----------------------------------+-----------+
    | Variable_name                    | Value     |
    +----------------------------------+-----------+
    | binlog_gtid_simple_recovery      | ON        |
    | enforce_gtid_consistency         | ON        |
    | gtid_executed_compression_period | 1000      |
    | gtid_mode                        | ON        |
    | gtid_next                        | AUTOMATIC |
    | gtid_owned                       |           |
    | gtid_purged                      |           |
    | session_track_gtids              | OFF       |
    +----------------------------------+-----------+
    8 rows in set (0.00 sec)
    
    mysql> SELECT @@server_id;
    mysql> SHOW MASTER LOGS;
    mysql> SHOW BINLOG EVENTS;
    

    ダンプファイルの取得およびリストア

    master から mysqldump コマンドでダンプを取得します。ただし、mysql.gtid_executed は除外します

    mysqldump --master-data=2 --single-transaction --skip-lock-tables --all-databases --triggers --routines --events --ignore-table=mysql.gtid_executed -uroot -S /tmp/mysql_master.sock | gzip > master.dump.gz
    

    slave にリストアします。

    zcat master.dump.gz | mysql -uroot -S /tmp/mysql_slave.sock
    

    レプリケーションの開始

    slave にログインします。

    mysql -uroot -S /tmp/mysql_slave.sock
    

    以下のコマンドで master に向けます。

    CHANGE MASTER TO
        MASTER_HOST = '127.0.0.1',
        MASTER_PORT = 13306,
        MASTER_USER = 'repl',
        MASTER_PASSWORD = 'replpass',
        MASTER_AUTO_POSITION = 1;
    

    GTID においては MASTER_LOG_FILE および MASTER_LOG_POS を確認する作業は不要です。

    $ zgrep '^-- CHANGE MASTER TO' master.dump.gz
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
    

    レプリケーションを開始します。

    START SLAVE;
    

    GTID レプリケーション開始後の状態確認

    SHOW MASTER STATUS;
    SHOW SLAVE STATUS\G
    SHOW BINLOG EVENTS;
    SHOW BINLOG EVENTS IN 'mysql-bin.000001';
    SHOW RELAYLOG EVENTS;
    

    GTID は event を発生させたサーバーの UUID : トランザクション ID という構成の文字列です。slave は既に適用した GTID を自身の binlog から知ることができるため、同じ GTID の SQL を重複実行することを回避できます。例えば以下の例では UUID c91b2678-a905-11e6-8592-0800276b1cdd の master で発生したトランザクション ID 1 の event が slave でも実行済みであることを表現しています。

    $ mysql -uroot -S /tmp/mysql_master.sock -e 'select @@server_uuid'
    +--------------------------------------+
    | @@server_uuid                        |
    +--------------------------------------+
    | c91b2678-a905-11e6-8592-0800276b1cdd |
    +--------------------------------------+
    
    $ mysql -uroot -S /tmp/mysql_slave.sock -e 'select @@GLOBAL.GTID_EXECUTED'
    +----------------------------------------+
    | @@GLOBAL.GTID_EXECUTED                 |
    +----------------------------------------+
    | c91b2678-a905-11e6-8592-0800276b1cdd:1 |
    +----------------------------------------+
    

    レプリケーション情報をリセットして最初からやりなおす

    GTID レプリケーションが有効な状態でリストアを実行すると、安全のためエラーが出るようになっています。

    $ zcat master.dump.gz | mysql -uroot -S /tmp/mysql_slave.sock
    ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
    

    ダンプファイル内に記載されている以下のクエリによって、ダンプ取得時において最後に実行された SQL の GTID を SET しようとするためです。

    SET @@GLOBAL.GTID_PURGED='c91b2678-a905-11e6-8592-0800276b1cdd:1';
    

    スレーブのデータが本当に不要であり、最初からやりなおしたい場合は以下のコマンドでリセットしてからリストアします。

    レプリケーションの停止

    STOP SLAVE;
    

    binlog の全削除 (GTID をどこまで実行したかという情報を破棄)

    SHOW MASTER STATUS;
    RESET MASTER;  ← バイナリログががすべて削除されます。
    SHOW MASTER STATUS;
    

    リストア

    zcat master.dump.gz | mysql -uroot -S /tmp/mysql_slave.sock
    

    レプリケーションの開始ポジションを再設定

    RESET SLAVE;
    
    CHANGE MASTER TO
        MASTER_HOST = '127.0.0.1',
        MASTER_PORT = 13306,
        MASTER_USER = 'repl',
        MASTER_PASSWORD = 'replpass',
        MASTER_AUTO_POSITION = 1;
    

    レプリケーションの開始

    START SLAVE;
    

    ダンプファイル内には TABLE の DROP は記載されていますが DB の DROP がないため、過去の DB スキーマが残存している状態で CREATE DATABASE が実行されるとエラーになります。

    Last_SQL_Error: Error 'Can't create database 'mydb2'; database exists' on query. Default database: 'mydb2'. Query: 'create database mydb2'
    

    手動で DROP してから再度開始すれば復旧しますが、そもそも DROP してからリストアしたほうが master の状態と実行履歴も含めて完全に同じになるため好ましいです。

    DROP DATABASE mydb2;
    START SLAVE;
    
    0
    詳細設定を開く/閉じる
    アカウント プロフィール画像 (本文下)

    MySQLの運用やレプリケーション設定など、実用的なノウハウを共有します。

    記事の執筆者にステッカーを贈る

    有益な情報に対するお礼として、またはコメント欄における質問への返答に対するお礼として、 記事の読者は、執筆者に有料のステッカーを贈ることができます。

    さらに詳しく →
    ステッカーを贈る コンセプト画像

    Feedbacks

    Feedbacks コンセプト画像

      ログインするとコメントを投稿できます。

      関連記事

      • MySQL レプリケーション設定 (2段, 3段)
        MySQL DB サーバは自身へのクエリをバイナリログとして書き出すことができます。レプリケーションとは、追加の MySQL DB サーバが、別の MySQL DB サーバが出力したバイナリログを自分のリレーログとよばれるログにコピーして、更にリレーログに記載されたクエリを自分自身のテーブルに実行する機能です。バイナリログを出力する DB をマスターとよび、自分のリレーログにコピーする DB をス...
        しおまめしおまめ10/7/2021に更新
        いいねアイコン画像0
      • MySQL 5.7 インストール手順
        2016/11/07 時点、多くの環境では yum レポジトリ等に mysql 5.7 が含まれていません。公式ページからダウンロードしてインストールする手順を二つまとめます。 yum レポジトリを追加する手順 [Installing MySQL on Linux Using the MySQL Yum Repository (mysql 5.7)](http://dev.mysql.com/do...
        しおまめしおまめ1/12/2021に更新
        いいねアイコン画像0
      • MyBatis 3 サンプルコード (Java/MySQL/Gradle)
        MyBatis は、JDBC を直接利用せずに、XML ファイル等で用意した SQL 文を利用して DB にアクセスするための Java ライブラリです。内部的には JDBC が利用されているため、JDBC のインストールは必要です。同様のライブラリに Hibernate ORM があります。DB は特に MySQL を対象として、MyBatis バージョン 3 の
        HARUTOHARUTO8/19/2019に更新
        いいねアイコン画像0
      • MySQL HandlerSocket Plugin の簡単な使用方法
        HandlerSocket は MySQL プロセス内のスレッドとして動作する、MySQL の NoSQL フロントエンドです。独自のプロトコルを用いて TCP 通信を行います。SQL 構文の解析が不要、プロトコルがシンプルなどの理由によって、単純な DB アクセスが高速に実行可能になります。MariaDB であれば[標準プラグインとして付属](https://mariadb.co
        しおまめしおまめ10/7/2021に更新
        いいねアイコン画像0
      • Spring Boot から MyBatis を利用するための設定 (Gradle/MySQL)
        MyBatis を Spring Boot で利用するための基本的な設定およびサンプルコードをまとめます。サンプルコードにおいては、特に MySQL を対象とします。 MyBatis Spring-Boot-Starter チュートリアル
        suzusuzu10/4/2021に更新
        いいねアイコン画像0