MySQL Replication - izudon/izudon.github.io GitHub Wiki

結論

  1. GTIDモードにする（ほぼメリットしかないため）
  2. バイナリログは必須（更新系ログがここに保存されてレプリカに転送されるため）
  3. レプリケーション用ユーザの認証方式に用心（パスワード使用の簡易式はデフォルトではない）

１．/etc/mysql/mysql.conf.d/mysqld.conf

ソース側

##### Getting Binary Logs 2023.01.26
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds      = 2592000
max_binlog_size   = 100M
# binlog_do_db          = include_database_name
# binlog_ignore_db      = include_database_name

##### GTID mode 2023.01.26
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates

レプリカ側

##### Getting Binary Logs 2023.01.26
server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds      = 2592000
max_binlog_size   = 100M
# binlog_do_db          = include_database_name
# binlog_ignore_db      = include_database_name

##### This is Replica 2023.01.26
read_only
relay_log_recovery = ON  # 予期せぬ停止などでリレーログが破損していたら捨てる

##### GTID mode 2023.01.26
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates
  • そして再起動 ststemctl restart mysql

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

  1. 認証方式を簡易なパスワード方式とする。
    （代わりに内部ネットワーク側からしかつなげないようにして安全を担保） 
    mysql> CREATE USER 'repl'@'192.168.1.0/24' IDENTIFIED WITH auth_plugin BY 'P@ssw0rd' ;
  2. そして レプリケーション権限 を 付与。 
    mysql> GRANT REPLICATION SLAVE ON *.* to 'repl'@'192.168.1.0/24' ;

３．ソース（参照）先の指定

短い場合

mysql> CHANGE REPLICATION SOURCE TO
-> SOURCE_HOST='192.168.1.2',
-> SOURCE_USER='repl',
-> SOURCE_PASSWORD='P@ssw0rd',
-> SOURCE_AUTO_POSITION=1;

４．レプリケーション開始コマンド

mysql> START REPLICA;

５．レプリケーション状況確認

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: db1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000006
          Read_Master_Log_Pos: 1444
               Relay_Log_File: 74ee0d826a91-relay-bin.000002
                Relay_Log_Pos: 1609
        Relay_Master_Log_File: binlog.000006
                :

ソース ＜－＞ レプリカ

  • レプリカ を ソース に
    1. mysql> STOP REPLICA ;
    2. mysqld.conf から read_only あたりを外して、再起動。
  • ソース を レプリカ に
    1. mysqld.conf に read_only あたりを足して、再起動。
    2. mysql> START REPLICA ;
      （ CHANGE REPLICATION SOURCE TO の設定はなされている前提 ）
  • MySQL :: STOP REPLICA ステートメント

資料

MySQL 公式資料

ブログ等

caching_sha2_password

  • これでわかる人いたら神。

    • MySQL 8.0 から、データベースユーザーのデフォルト認証方式が caching_sha2_password に変更されています
      本資料では、レプリケーションの設定方法に焦点を当てるために、便宜上MySQL 5.7までの認証方式を 使用している前提で解説しています

      • default_authentication_plugin = mysql_native_password を設定している状態を想定
  • 翻訳
    • MySQL のユーザ認証は認証方式ごとに「プラグイン」がありそれを使用している。
    • デフォルトでどのプラグイン＝認証方式を使用するのかを設定する設定項目が、
      default_authentication_plugin である。
    • MySQL 5.7 までのデフォルト値は mysql_native_password すなわち、
      「生のパスワード認証」だった。
    • MySQL 8.0 からは caching_sha2_password すなわち、
      「SHA2 ハッシュ値をキャッシュする方式（と思われる）」に変わった。
    • レプリケーション接続用のユーザを作成する際、新方式だと、
      設定上若干面倒なことをしなければならないので・・・。
    • 資料では旧方式を前提に解説している（［ツッコミ］ひど！ ）。
  • MySQL8.0で新たに追加されているレプリケーション接続オプション – スマートスタイル技術ブログ
    • 対応策としては次の２つになると。
      1. レプリケーション用ユーザーの認証プラグインを mysql_native_password にする。
      2. レプリケーション用ユーザーの認証プラグインを caching_sha2_password のままとし、
        「安全な接続」または「RSAベースのパスワード交換」で接続する。
    • 後者のやり方は上記リンク参照。前者のやり方は下記参照。

認証プラグインを変更する

ALTER USER user IDENTIFIED WITH auth_plugin BY 'password'

この auth_plugin に mysql_native_password を指定する。
（ CREATE USER 時にも同様に指定できる ）

準同期メイン

GTID メイン（記事が古い）