概要
環境
1 号機
[ope@radius0 ~]$ uname -a Linux radius0.tk.net 4.18.0-240.el8.x86_64 #1 SMP Fri Sep 25 19:48:47 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux [ope@radius0 ~]$ [ope@radius0 ~]$ cat /etc/*release CentOS Linux release 8.3.2011 NAME="CentOS Linux" VERSION="8" ID="centos" ID_LIKE="rhel fedora" VERSION_ID="8" PLATFORM_ID="platform:el8" PRETTY_NAME="CentOS Linux 8" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:centos:centos:8" HOME_URL="https://centos.org/" BUG_REPORT_URL="https://bugs.centos.org/" CENTOS_MANTISBT_PROJECT="CentOS-8" CENTOS_MANTISBT_PROJECT_VERSION="8" CentOS Linux release 8.3.2011 CentOS Linux release 8.3.2011 [ope@radius0 ~]$ [ope@radius0 ~]$ ip a | grep "inet " inet 127.0.0.1/8 scope host lo inet 192.168.100.102/24 brd 192.168.100.255 scope global noprefixroute enp0s3 [ope@radius0 ~]$
2 号機
[ope@radius1 ~]$ uname -a Linux radius1.tk.net 4.18.0-240.el8.x86_64 #1 SMP Fri Sep 25 19:48:47 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux [ope@radius1 ~]$ [ope@radius1 ~]$ cat /etc/*release CentOS Linux release 8.3.2011 NAME="CentOS Linux" VERSION="8" ID="centos" ID_LIKE="rhel fedora" VERSION_ID="8" PLATFORM_ID="platform:el8" PRETTY_NAME="CentOS Linux 8" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:centos:centos:8" HOME_URL="https://centos.org/" BUG_REPORT_URL="https://bugs.centos.org/" CENTOS_MANTISBT_PROJECT="CentOS-8" CENTOS_MANTISBT_PROJECT_VERSION="8" CentOS Linux release 8.3.2011 CentOS Linux release 8.3.2011 [ope@radius1 ~]$ [ope@radius1 ~]$ ip a | grep "inet " inet 127.0.0.1/8 scope host lo inet 192.168.100.103/24 brd 192.168.100.255 scope global noprefixroute enp0s3 [ope@radius1 ~]$
Ref
MariaDB install
なんでもいい
公式のここを見るといい。
とりあえず、リポジトリは追加済み
[ope@radius0 ~]$ sudo yum repolist | grep -i maria mariadb-main MariaDB Server mariadb-maxscale MariaDB MaxScale mariadb-tools MariaDB Tools [ope@radius0 ~]$
yum install (1 and 2)
[ope@radius0 ~]$ sudo yum install MariaDB-server MariaDB-client ... Upgraded: MariaDB-client-10.5.10-1.el8.x86_64 Installed: MariaDB-server-10.5.10-1.el8.x86_64 boost-program-options-1.66.0-10.el8.x86_64 galera-4-26.4.8-1.el8.x86_64 socat-1.7.3.3-2.el8.x86_64 Complete! [ope@radius0 ~]$
どうやら、入れていた Client が更新されたようだ
自動起動設定
[ope@radius0 ~]$ sudo systemctl enable mariadb.service Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service. [ope@radius0 ~]$
start
[ope@radius0 ~]$ sudo systemctl start mariadb
Errorlog を syslog に吐かせる
https://mariadb.com/kb/en/systemd/#configuring-mariadb-to-write-the-error-log-to-syslog
[ope@radius0 ~]$ sudo tee /etc/systemd/system/mariadb.service.d/syslog.conf <<EOF > [Service] > > StandardOutput=syslog > StandardError=syslog > SyslogFacility=daemon > SysLogLevel=err > EOF [Service] StandardOutput=syslog StandardError=syslog SyslogFacility=daemon SysLogLevel=err [ope@radius0 ~]$ [ope@radius0 ~]$ sudo systemctl daemon-reload [ope@radius0 ~]$
初期設定
[ope@radius0 ~]$ mysql_secure_installation
前回入れてたやつが残っていたのでそれを使う
[ope@radius0 ~]$ cat mariadb_root.cnf [client] host=<hostname or IP> user=root password='<PASSWORD>' [ope@radius0 ~]$
動作確認
[ope@radius0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 7 Server version: 10.5.10-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | radius | +--------------------+ 4 rows in set (0.001 sec) MariaDB [(none)]>
radius データベースが入っているが、まぁ、気にしない
入れたので良し
実際に Replication 設定をしていく
Ref
Enable MariaDB Replication
[ope@radius0 ~]$ sudo cp -av /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf.$(date +%F_%H-%M-%S) '/etc/my.cnf.d/server.cnf' -> '/etc/my.cnf.d/server.cnf.2021-06-13_12-20-30' [ope@radius0 ~]$ [ope@radius0 ~]$ sudo vim /etc/my.cnf.d/server.cnf [ope@radius0 ~]$
[ope@radius0 ~]$ sudo diff -uprN /etc/my.cnf.d/server.cnf.2021-06-13_12-20-30 /etc/my.cnf.d/server.cnf --- /etc/my.cnf.d/server.cnf.2021-06-13_12-20-30 2021-05-06 06:00:20.000000000 +0900 +++ /etc/my.cnf.d/server.cnf 2021-06-13 12:25:26.852880021 +0900 @@ -10,6 +10,8 @@ # this is only for the mysqld standalone daemon [mysqld] +log-bin +server_id=1 # # * Galera-related settings [ope@radius0 ~]$
replication_user を作る (1 and 2)
[ope@radius0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.5.10-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> CREATE USER 'replication_user'@'%' IDENTIFIED BY 'bigs3cret'; Query OK, 0 rows affected (0.011 sec) MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%'; Query OK, 0 rows affected (0.016 sec) MariaDB [(none)]> quit;
設定を確認する
公式より引用
There are a number of options that may impact or break replication. Check the following settings to avoid problems.
skip-networking. If skip-networking=1, the server will limit connections to localhost only, and prevent all remote slaves from connecting. bind-address. Similarly, if the address the server listens for TCP/IP connections is 127.0.0.1 (localhost), remote slaves connections will fail.
DeepL で翻訳した
レプリケーションに影響を与えたり、壊れたりする可能性のあるオプションがいくつかあります。以下の設定を確認して、問題を回避してください。
skip-networking。skip-networking=1の場合、サーバーは接続先をlocalhostのみに制限し、すべてのリモートスレーブが接続できないようにします。 bind-address。同様に、サーバーがTCP/IP接続をリッスンするアドレスが127.0.0.1(localhost)の場合、リモートスレーブの接続は失敗します。
というわけで
[ope@radius0 ~]$ sudo grep -i skip-network /etc/my.cnf.d/server.cnf [ope@radius0 ~]$ sudo grep -i bind-address /etc/my.cnf.d/server.cnf #bind-address=0.0.0.0 [ope@radius0 ~]$
skip-network の説明
https://mariadb.com/kb/en/server-system-variables/#skip_networking
原文
If set to 1, (0 is the default), the server does not listen for TCP/IP connections. All interaction with the server by be through socket files (Unix) or named pipes or shared memory (Windows). It's recommended to use this option if only local clients are permitted to connect to the server. Enabling this option also prevents a server from functioning as a replication client.
DeepL 翻訳
1に設定すると（デフォルトは0）、サーバーはTCP/IPの接続を待ちません。サーバーとのやりとりはすべて、ソケットファイル（Unix）または名前付きパイプや共有メモリ（Windows）を介して行われます。このオプションは、ローカルクライアントのみがサーバーへの接続を許可されている場合に使用することをお勧めします。このオプションを有効にすると、サーバーがレプリケーションクライアントとして機能しなくなります。
bind-address の説明
https://mariadb.com/kb/en/server-system-variables/#bind_address
原文
By default, the MariaDB server listens for TCP/IP connections on a network socket bound to a single address, 0.0.0.0. You can specify an alternative when the server starts using this option; either a host name, an IPv4 or an IPv6 address. In Debian and Ubuntu, the default bind_address is 127.0.0.1, which binds the server to listen on localhost only. bind_address has always been available as a mysqld option, from MariaDB 10.3.3 its also available as a system variable.
DeepL 翻訳
デフォルトでは、MariaDBサーバーは、0.0.0.0という1つのアドレスにバインドされたネットワークソケットでTCP/IP接続を待ち受けます。このオプションを使って、サーバーの起動時にホスト名、IPv4またはIPv6アドレスのいずれかを指定することができます。Debian と Ubuntu では、デフォルトの bind_address は 127.0.0.1 で、これはサーバが localhost のみを listen するようにバインドします。 bind_address は mysqld のオプションとして常に利用可能でしたが、MariaDB 10.3.3 からはシステム変数としても利用できるようになりました。
127.0.0.1 だと、自分自身のみだから NG だけど、 0.0.0.0 なら OK なの？かな？
問題なさそう？だな
Slave の設定
原文
Give the slave a unique server_id. All servers, whether masters or slaves, are given a server_id. This can be a number from 1 to 232-1, and must be unique for each server in the replicating group. The server will need to be restarted in order for a change in this option to take effect.
DeepL 翻訳
スレーブに一意のserver_idを与える。マスターでもスレーブでも、すべてのサーバーにはserver_idが与えられます。これは1から232-1までの数字で、レプリケートグループ内の各サーバーで一意である必要があります。このオプションの変更を有効にするためには、サーバーを再起動する必要があります。
[ope@radius1 ~]$ sudo cp -aiv /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf.$(date +%F_%H-%M-%S) '/etc/my.cnf.d/server.cnf' -> '/etc/my.cnf.d/server.cnf.2021-06-13_12-43-25' [ope@radius1 ~]$ [ope@radius1 ~]$ sudo vim /etc/my.cnf.d/server.cnf [ope@radius1 ~]$
[ope@radius1 ~]$ sudo diff -uprN /etc/my.cnf.d/server.cnf.2021-06-13_12-43-25 /etc/my.cnf.d/server.cnf --- /etc/my.cnf.d/server.cnf.2021-06-13_12-43-25 2021-05-06 06:00:20.000000000 +0900 +++ /etc/my.cnf.d/server.cnf 2021-06-13 12:43:55.141704371 +0900 @@ -10,6 +10,8 @@ # this is only for the mysqld standalone daemon [mysqld] +log-bin +server_id=2 # # * Galera-related settings [ope@radius1 ~]$
[ope@radius1 ~]$ sudo systemctl restart mariadb.service [ope@radius1 ~]$ sudo systemctl status mariadb.service | grep -iE "Active|PID" Active: active (running) since Sun 2021-06-13 12:45:37 JST; 28s ago Main PID: 55864 (mariadbd) [ope@radius1 ~]$
Master バイナリログの座標？の取得
ファイル名とバイナリログのポジションを確認
原文
Now you need prevent any changes to the data while you view the binary log position. You'll use this to tell the slave at exactly which point it should start replicating from.
- On the master, flush and lock all tables by running FLUSH TABLES WITH READ LOCK. Keep this session running - exiting it will release the lock.
- Get the current position in the binary log by running SHOW MASTER STATUS:
DeepL 翻訳
これで、バイナリログの位置を見ている間は、データに変更が加えられないようにする必要があります。これを使って、スレーブにどの時点からレプリケーションを開始すべきかを正確に伝えることができます。
- マスターでは、「FLUSH TABLES WITH READ LOCK」を実行して、すべてのテーブルをフラッシュしてロックします。このセッションを実行したままにしておきます。セッションを終了するとロックが解除されます。
- SHOW MASTER STATUSを実行して、バイナリログの現在の位置を取得します。
[ope@radius0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf ... MariaDB [(none)]> SHOW MASTER STATUS; Empty set (0.001 sec) MariaDB [(none)]>
？なにかおかしい。restart 飛ばしていたかも
[ope@radius0 ~]$ sudo systemctl restart mariadb.service [ope@radius0 ~]$ sudo systemctl status mariadb.service | grep -E "Active|PID" Active: active (running) since Sun 2021-06-13 12:52:41 JST; 16s ago Main PID: 8409 (mariadbd) [ope@radius0 ~]$
retry
[ope@radius0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 10.5.10-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | radius0-bin.000001 | 330 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.001 sec) MariaDB [(none)]> MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.105 sec) MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | radius0-bin.000001 | 330 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.000 sec) MariaDB [(none)]>
こうなるのか
原文続き
- Record the File and Position details. If binary logging has just been enabled, these will be blank.
- Now, with the lock still in place, copy the data from the master to the slave. See Backup, Restore and Import for details on how to do this.
- Note for live databases: You just need to make a local copy of the data, you don't need to keep the master locked until the slave has imported the data.
- Once the data has been copied, you can release the lock on the master by running UNLOCK TABLES.
DeepL 翻訳
- ファイルとポジションの詳細を記録します。バイナリロギングを有効にしたばかりの場合、これらは空白になります。
- 次に、ロックがかかっている状態で、マスターからスレーブにデータをコピーします。この方法の詳細については、「バックアップ、リストア、インポート」を参照してください。
- ライブデータベースの場合の注意点。データのローカル コピーを作成するだけで、スレーブがデータをインポートするまでマスターをロックしておく必要はありません。
- データのコピーが完了したら、UNLOCK TABLESを実行してマスターのロックを解除することができます。
メモ
ファイル: radius0-bin.000001
ポジション: 330
データのバックアップ
詳細への直リンクを書いてくれていればいいのに
注意点とかあったらどうしよう。ここで知りたかった感ある
フルバックアップでいいのか？？？？？
データをコピーするって言っているけど？？？
悩んでも仕方ないので、 --all-databases オプション付けて全て dump する
別の terminal を開く
mysqldump --defaults-extra-file=mariadb_root.cnf --all-databases \ --flush-logs --single-transaction --master-data=2 > \ all_databases_$(date +%F_%H-%M-%S).sql
こんな感じ？
--flush-logs :
ダンプを開始する前に、MariaDBサーバーのログファイルをフラッシュします。
--single-transaction :
データをダンプする前に、START TRANSACTION SQLステートメントをサーバーに送信します。アプリケーションをブロックすることなく、BEGINが発行された時点でのデータベースの一貫した状態をダンプすることができるため、InnoDBなどのトランザクションテーブルでのみ有効です。
このオプションを使用する際には、InnoDBテーブルのみが一貫した状態でダンプされることを念頭に置く必要があります。シングル トランザクション機能は、エンジンがトランザクションであり、REPEATABLE-READが可能であるだけでなく、START TRANSACTION WITH CONSISTENT SNAPSHOTにも依存します。他のストレージエンジンでは、ダンプの一貫性は保証されません。例えば、このオプションを使用してダンプされたTokuDB、MyISAM、MEMORYのテーブルは、まだ状態が変わる可能性があります。
--master-data :
バイナリ ログの位置とファイル名を出力に追加します。マスター レプリケーション サーバーをダンプしてダンプ ファイルを作成し、マスターのスレーブとして別のサーバーをセットアップするのに使用できます。これらは、スレーブにダンプファイルをロードした後、スレーブがレプリケーションを開始する際のマスターサーバの座標です。このオプションを1に設定すると（デフォルト）、CHANGE MASTERコマンドとして出力され、2に設定すると、そのコマンドの前にコメント記号が付きます。この--master-dataオプションは、-single-transactionが指定されていない限り、--lock-all-tablesをオンにします。MariaDB 5.3以前では、ダンプの開始時に短時間だけグローバルリードロックがかかっていました。) いずれの場合も、ログに対するアクションは、ダンプの瞬間に発生します。このオプションは自動的に --lock-tables をオフにします。
やってみる
[ope@radius0 ~]$ mysqldump --defaults-extra-file=mariadb_root.cnf --all-databases \ > --flush-logs --single-transaction --master-data=2 > \ > all_databases_$(date +%F_%H-%M-%S).sql [ope@radius0 ~]$ [ope@radius0 ~]$ ls -l --full-time all* -rw-rw-r--. 1 ope ope 1398996 2021-06-13 14:27:50.491444172 +0900 all_databases_2021-06-13_14-27-50.sql [ope@radius0 ~]$
SLAVE に Backup data を転送
[ope@radius0 ~]$ sftp ope@192.168.100.103 Connected to ope@192.168.100.103. sftp> lls all_databases_2021-06-13_14-27-50.sql mariadb_radius.cnf mariadb_root.cnf nagios php_test.php setup sftp> put all_databases_2021-06-13_14-27-50.sql Uploading all_databases_2021-06-13_14-27-50.sql to /home/ope/all_databases_2021-06-13_14-27-50.sql all_databases_2021-06-13_14-27-50.sql 100% 1366KB 28.5MB/s 00:00 sftp> ls all_databases_2021-06-13_14-27-50.sql mariadb_radius.cnf mariadb_root.cnf sftp> exit [ope@radius0 ~]$
SLAVE でリストア
[ope@radius1 ~]$ ll 合計 1376 -rw-rw-r--. 1 ope ope 1398996 6月 13 14:39 all_databases_2021-06-13_14-27-50.sql -rw-rw-r--. 1 ope ope 70 5月 25 01:44 mariadb_radius.cnf -rw-rw-r--. 1 ope ope 58 6月 13 12:09 mariadb_root.cnf [ope@radius1 ~]$ [ope@radius1 ~]$ mysql --defaults-extra-file=mariadb_root.cnf < all_databases_2021-06-13_14-27-50.sql [ope@radius1 ~]$
Table を Unlock する
MariaDB [(none)]> UNLOCK TABLES; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> quit Bye [ope@radius0 ~]$
SLAVE Start
原文
Once the data has been imported, you are ready to start replicating. Begin by running a CHANGE MASTER TO, making sure that MASTER_LOG_FILE matches the file and MASTER_LOG_POS the position returned by the earlier SHOW MASTER STATUS. For example:
DeepL 翻訳
データのインポートが完了したら、レプリケーションを開始する準備が整いました。CHANGE MASTER TOを実行して、MASTER_LOG_FILEがファイルと一致していること、MASTER_LOG_POSが先のSHOW MASTER STATUSで返された位置であることを確認します。例えば、以下のようになります。
CHANGE MASTER TO MASTER_HOST='master.domain.com', MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master1-bin.000096', MASTER_LOG_POS=568, MASTER_CONNECT_RETRY=10;
なので、コマンドとしてはこうなるかな
CHANGE MASTER TO MASTER_HOST='192.168.100.102', MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='radius0-bin.000001', MASTER_LOG_POS=330, MASTER_CONNECT_RETRY=10;
やってみる
[ope@radius1 ~]$ mysql --defaults-extra-file=mariadb_root.cnf Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 10.5.10-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.100.102', -> MASTER_USER='replication_user', -> MASTER_PASSWORD='bigs3cret', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='radius0-bin.000001', -> MASTER_LOG_POS=330, -> MASTER_CONNECT_RETRY=10; Query OK, 0 rows affected (0.099 sec) MariaDB [(none)]>
Use Global Transaction Id (GTID)
原文
MariaDB 10.0 introduced global transaction IDs (GTIDs) for replication. It is generally recommended to use (GTIDs) from MariaDB 10.0, as this has a number of benefits. All that is needed is to add the MASTER_USE_GTID option to the CHANGE MASTER statement, for example:
DeepL 翻訳
MariaDB 10.0では、レプリケーションにグローバル・トランザクションID（GTID）が導入されました。多くの利点があるため、一般的にはMariaDB 10.0からの(GTIDs)を使用することが推奨されています。必要なのは、例えばCHANGE MASTERステートメントにMASTER_USE_GTIDオプションを追加することです。
CHANGE MASTER TO MASTER_USE_GTID = slave_pos
See Global Transaction ID for a full description.
Global Transaction ID - MariaDB Knowledge Base
と、いうことで、これもやっておく
その前に、 slave_pos との事なので、SLAVE の Position を確認する
これは
レプリカがプライマリに接続すると、レプリカに最後にレプリケートされたGTIDの位置からレプリケーションを開始します。
との事であるため
MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | radius1-bin.000001 | 1402559 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.000 sec) MariaDB [(none)]>
なので、コマンドは以下
CHANGE MASTER TO MASTER_HOST='192.168.100.102', MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='radius0-bin.000001', MASTER_LOG_POS=330, MASTER_CONNECT_RETRY=10, MASTER_USE_GTID=1402559;
Do it
MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.100.102', -> MASTER_USER='replication_user', -> MASTER_PASSWORD='bigs3cret', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='radius0-bin.000001', -> MASTER_LOG_POS=330, -> MASTER_CONNECT_RETRY=10, -> MASTER_USE_GTID=1402559; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1402559' at line 9 MariaDB [(none)]>
どういうこと？
これ単体だと？
CHANGE MASTER TO MASTER_USE_GTID = 1402559;
MariaDB [(none)]> CHANGE MASTER TO MASTER_USE_GTID = 1402559; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1402559' at line 1 MariaDB [(none)]>
こういうの泣ける。ドキュメントのとおりに流しているのに、 Syntax Error とかね。
なるほど。Syntax はこれっぽい
CHANGE MASTER TO master_use_gtid = { slave_pos | current_pos | no }
POS 値を指定するのではないらしい
CHANGE MASTER TO MASTER_USE_GTID = slave_pos;
Do it
MariaDB [(none)]> SHOW SLAVE STATUS\G
...
Using_Gtid: No
...
MariaDB [(none)]>
MariaDB [(none)]> CHANGE MASTER TO MASTER_USE_GTID = slave_pos;
Query OK, 0 rows affected (0.048 sec)
MariaDB [(none)]>
MariaDB [(none)]> SHOW SLAVE STATUS\G
...
Using_Gtid: Slave_Pos
...
MariaDB [(none)]>
Now start the slave
START SLAVE;
Do it
MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.080 sec) MariaDB [(none)]>
Check that the replication is working
SHOW SLAVE STATUS \G;
原文
If replication is working correctly, both the values of Slave_IO_Running and Slave_SQL_Running should be Yes:
DeepL 翻訳
レプリケーションが正常に動作していれば、「Slave_IO_Running」と「Slave_SQL_Running」の両方の値が「Yes」になっているはずです。
Slave_IO_Running: Yes Slave_SQL_Running: Yes
MariaDB [(none)]> SHOW SLAVE STATUS \G;
...
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
接続中か
気になるのはこれ
Last_IO_Error: error connecting to master 'replication_user@192.168.100.102:3306' - retry-time: 10 maximum-retries: 86400 message: Can't connect to server on '192.168.100.102' (113 "No route to host")
firewall かな？そう言えば、Firewall の port/(tcp/udp) について振れられてなかった
firewall の確認と設定
1 号機
[ope@radius0 ~]$ sudo firewall-cmd --list-all public (active) target: default icmp-block-inversion: no interfaces: enp0s3 sources: services: cockpit dhcpv6-client http ssh ports: 1812/udp 1813/udp protocols: masquerade: no forward-ports: source-ports: icmp-blocks: rich rules: [ope@radius0 ~]$
3306 空いていない
2 号機
[ope@radius1 ~]$ sudo firewall-cmd --list-all public (active) target: default icmp-block-inversion: no interfaces: enp0s3 sources: services: ssh ports: 1812/udp 1813/udp 80/tcp 3306/tcp 4567/tcp 4444/tcp protocols: masquerade: no forward-ports: source-ports: icmp-blocks: rich rules: [ope@radius1 ~]$
以前練習した時の設定が残っている
https://qiita.com/wakoit/items/216fbed19d42d4b9dd35#%E5%86%97%E9%95%B7%E6%A7%8B%E6%88%90
ここに書いてあるように、firewall の設定を行う
[ope@radius0 ~]$ sudo firewall-cmd --add-port=3306/tcp --add-port=4567/tcp --add-port =4444/tcp --permanent success [ope@radius0 ~]$ sudo firewall-cmd --reload success [ope@radius0 ~]$ [ope@radius0 ~]$ sudo firewall-cmd --list-all public (active) target: default icmp-block-inversion: no interfaces: enp0s3 sources: services: cockpit dhcpv6-client http ssh ports: 1812/udp 1813/udp 3306/tcp 4567/tcp 4444/tcp protocols: masquerade: no forward-ports: source-ports: icmp-blocks: rich rules: [ope@radius0 ~]$
再度確認する
SHOW SLAVE STATUS \G;
一部抽出
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Error: も空になった。
全log
MariaDB [(none)]> SHOW SLAVE STATUS \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.102 Master_User: replication_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: radius0-bin.000002 Read_Master_Log_Pos: 375 Relay_Log_File: radius1-relay-bin.000002 Relay_Log_Pos: 676 Relay_Master_Log_File: radius0-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 375 Relay_Log_Space: 987 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.001 sec) ERROR: No query specified MariaDB [(none)]>
これで準備は出来たと思うのだけど、本当に更新が反映されるのだろうか？
更新テスト
現状確認
1 号機
[ope@radius0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf ... MariaDB [(none)]> use radius; ... Database changed MariaDB [radius]> MariaDB [radius]> MariaDB [radius]> select * from radcheck; +----+----------+--------------------+----+-----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+-----------+ | 1 | user1 | Cleartext-Password | := | user1pass | +----+----------+--------------------+----+-----------+ 1 row in set (0.002 sec) MariaDB [radius]>
2 号機
MariaDB [(none)]> use radius; ... Database changed MariaDB [radius]> MariaDB [radius]> select * from radcheck; +----+----------+--------------------+----+-----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+-----------+ | 1 | user1 | Cleartext-Password | := | user1pass | +----+----------+--------------------+----+-----------+ 1 row in set (0.002 sec) MariaDB [radius]>
1 号機で Insert してみる
INSERT INTO radcheck (username, attribute, op, value) VALUES ('user2', 'Cleartext-Password', ':=', 'user2pass') ;
MariaDB [radius]> INSERT INTO radcheck -> (username, attribute, op, value) -> VALUES -> ('user2', 'Cleartext-Password', ':=', 'user2pass') -> ; Query OK, 1 row affected (0.010 sec) MariaDB [radius]> MariaDB [radius]> select * from radcheck; +----+----------+--------------------+----+-----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+-----------+ | 1 | user1 | Cleartext-Password | := | user1pass | | 2 | user2 | Cleartext-Password | := | user2pass | +----+----------+--------------------+----+-----------+ 2 rows in set (0.001 sec) MariaDB [radius]>
2 号機で確認する
SELECT * FROM radcheck;
MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+-----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+-----------+ | 1 | user1 | Cleartext-Password | := | user1pass | | 2 | user2 | Cleartext-Password | := | user2pass | +----+----------+--------------------+----+-----------+ 2 rows in set (0.001 sec) MariaDB [radius]>
できた！
failover どうしたらいいん？
Ref
Changing a Slave to Become the Master - MariaDB Knowledge Base
あるやん？
master を止める
原文
First one needs to take down the original master in such a way that the slave has all information on the master.
If you are using Semisynchronous Replication you can just stop the server with the SHUTDOWN command as the slaves should be automatically up to date.
If you are using MariaDB MaxScale proxy, then you can use MaxScale to handle the whole process of taking down the master and replacing it with one of the slaves.
If neither of the above is true, you have to do this step manually:
DeepL 翻訳
まず最初に、オリジナルのマスターを停止して、スレーブがマスターのすべての情報を持つようにする必要があります。
Semisynchronous Replicationを使用している場合は、SHUTDOWNコマンドでサーバを停止するだけで、スレーブは自動的に最新の状態になるはずです。
MariaDB MaxScaleプロキシを使用している場合は、MaxScaleを使用してマスターを停止し、スレーブの1つと交換するというプロセス全体を処理することができます。
上記のどちらにも当てはまらない場合は、このステップを手動で行う必要があります。
手動で master を止める
原文
First we have to set the master to read only to ensure that there are no new updates on the master:
DeepL 翻訳
まず、マスターを読み取り専用にして、マスターに新しいアップデートがないことを確認します。
FLUSH TABLES WITH READ LOCK;
原文
Note that you should not disconnect this session as otherwise the read lock will disappear and you have to start from the beginning.
DeepL 翻訳
なお、このセッションを切断してはいけません。そうすると、読み取りロックが消えてしまい、最初からやり直さなければならなくなります。
気をつけよう
[ope@radius0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.5.10-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.175 sec) MariaDB [(none)]>
そして、現在のポジションを確認する
SHOW MASTER STATUS;
SELECT @@global.gtid_binlog_pos;
Do it
MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | radius0-bin.000002 | 660 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT @@global.gtid_binlog_pos; +--------------------------+ | @@global.gtid_binlog_pos | +--------------------------+ | 0-1-1 | +--------------------------+ 1 row in set (0.001 sec) MariaDB [(none)]>
slave 側でも同じポジションになるのを待つ
connection_name を特に指定していない場合無視できる
SHOW SLAVE [connection_name] STATUS\G
# or
SHOW SLAVE STATUS\G
Do it
[ope@radius1 ~]$ mysql --defaults-extra-file=mariadb_root.cnf Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.5.10-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SHOW SLAVE STATUS\G ... Master_Log_File: radius0-bin.000002 Read_Master_Log_Pos: 660 ... Exec_Master_Log_Pos: 660 ... Gtid_IO_Pos: 0-1-1
full log
MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.102 Master_User: replication_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: radius0-bin.000002 Read_Master_Log_Pos: 660 Relay_Log_File: radius1-relay-bin.000002 Relay_Log_Pos: 961 Relay_Master_Log_File: radius0-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 660 Relay_Log_Space: 1272 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-1-1 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 1 1 row in set (0.005 sec) MariaDB [(none)]>
OK
原文
The most important information to watch are Master_Log_File and Exec_Master_Log_Pos as when this matches the master, it signals that all transactions has been committed on the slave.
Note that Gtid_IO_Pos on slave can contain many different positions separated with ',' if the slave has been connected to many different masters. What is important is that all the sequences that are on the master is also on the slave.
DeepL 翻訳
注目すべき最も重要な情報は、
Master_Log_FileとExec_Master_Log_Posです。これがマスターと一致した場合、すべてのトランザクションがスレーブ上でコミットされたことを示します。スレーブに接続されたGtid_IO_Posには、多くの異なるマスターに接続されている場合、「,」で区切られた多くの異なるポジションが含まれていることに注意してください。重要なことは、マスターにあるすべてのシーケンスがスレーブにもあるということです。
原文
When slave is up to date, you can then take the MASTER down. This should be on the same connection where you executed FLUSH TABLES WITH READ LOCK.
DeepL 翻訳
スレーブが最新の状態になったら、マスターをダウンさせることができます。これは、FLUSH TABLES WITH READ LOCKを実行したのと同じ接続で行う必要があります。
SHUTDOWN;
Do it
MariaDB [(none)]> SHUTDOWN; Query OK, 0 rows affected (0.006 sec) MariaDB [(none)]>
Slave を Master にする準備
原文
Stop all old connections to the old master(s) and reset read only mode, if you had it enabled. You also want to save the values of SHOW MASTER STATUS and gtid_binlog_pos, as you may need these to setup new slaves.
DeepL 翻訳
古いマスターへの古い接続をすべて停止し、リードオンリーモードを有効にしていた場合はそれをリセットします。また、SHOW MASTER STATUSとgtid_binlog_posの値も保存しておきます。これは新しいスレーブをセットアップする際に必要になるかもしれないからです。
command
STOP ALL SLAVES; RESET SLAVE ALL; SHOW MASTER STATUS; SELECT @@global.gtid_binlog_pos; SET @@global.read_only=0;
Master は停止しているので、 Slave でコマンドを打つのかな?
Slave にて
MariaDB [(none)]> STOP ALL SLAVES; Query OK, 0 rows affected, 1 warning (0.036 sec) MariaDB [(none)]> RESET SLAVE ALL; Query OK, 0 rows affected (0.008 sec) MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | radius1-bin.000001 | 1402559 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.001 sec) MariaDB [(none)]> SELECT @@global.gtid_binlog_pos; +--------------------------+ | @@global.gtid_binlog_pos | +--------------------------+ | 0-2-156 | +--------------------------+ 1 row in set (0.001 sec) MariaDB [(none)]> SET @@global.read_only=0; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]>
この時点で Slave が Master になっている
なので、old Slave で SHOW SLAVE STATUS しても出てこない
MariaDB [(none)]> SHOW SLAVE STATUS\G Empty set (0.001 sec) MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | radius1-bin.000001 | 1402559 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.000 sec) MariaDB [(none)]>
Reconnect Other Slaves to the New Master
他に Slave が居る場合下記のコマンドで Slave として Master 配下？に参加させる
今回は Master 1 台、 Slave 1 台であるため、実行しなくて良い。と思っている
STOP SLAVE [connection_name]; CHANGE MASTER [connection_name] TO MASTER_HOST="new_master_name or IP address", MASTER_PORT=3306, MASTER_USER='<user>', MASTER_USE_GTID=current_pos, MASTER_LOG_FILE="XXX", MASTER_LOG_POS=XXX; START SLAVE;
後で気づくが、ここで PASSWORD を入力してあげる必要がある
MASTER_USER='<password>',
Changing the Old Master to be a Slave
原文
When starting the original master, it's good to start the mysqld executable with the --with-skip-slave-start and --read-only options to ensure that no old slave configurations could cause any conflicts.
For the same reason it's also good to execute the following commands on the old master (same as for other slaves, but with some extra security). The read_only option below is there to ensure that old applications doesn't by accident try to update the old master by mistake. It only affects normal connections to the slave, not changes from the new master.
DeepL 翻訳
オリジナルのマスターを起動する際には、古いスレーブの設定がコンフリクトの原因にならないように、mysqld 実行ファイルを --with-skip-slave-start および --read-only オプションを付けて起動するとよいでしょう。
同じ理由で、古いマスターに対して以下のコマンドを実行するのも良いでしょう（他のスレーブと同じですが、いくつかの追加のセキュリティがあります）。以下のread_onlyオプションは、古いアプリケーションが誤って古いマスターを更新しようとしないようにするためのものです。このオプションはスレーブへの通常の接続にのみ影響し、新しいマスターからの変更には影響しません。
set @@global.read_only=1; STOP ALL SLAVES; RESET MASTER; RESET SLAVE ALL; CHANGE MASTER [connection_name] TO MASTER_HOST="new_master_name", MASTER_PORT=3306, MASTER_USER='root', MASTER_USE_GTID=current_pos, MASTER_LOG_FILE="XXX", MASTER_LOG_POS=XXX; START SLAVE;
なので、コマンドは
set @@global.read_only=1; STOP ALL SLAVES; RESET MASTER; RESET SLAVE ALL; CHANGE MASTER TO MASTER_HOST="192.168.100.103", MASTER_PORT=3306, MASTER_USER='replication_user', MASTER_USE_GTID=slave_pos, MASTER_LOG_FILE="radius1-bin.000001", MASTER_LOG_POS=1402559; START SLAVE;
Do it on old Master
MariaDB [(none)]> set @@global.read_only=1; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) ERROR: Can't connect to the server unknown [(none)]> STOP ALL SLAVES; No connection. Trying to reconnect... ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) ERROR: Can't connect to the server unknown [(none)]> RESET MASTER; No connection. Trying to reconnect... ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) ERROR: Can't connect to the server unknown [(none)]> RESET SLAVE ALL; No connection. Trying to reconnect... ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) ERROR: Can't connect to the server unknown [(none)]> unknown [(none)]> CHANGE MASTER TO -> MASTER_HOST="192.168.100.103", -> MASTER_PORT=3306, -> MASTER_USER='replication_user', -> MASTER_USE_GTID=slave_pos, -> MASTER_LOG_FILE="radius1-bin.000001", -> MASTER_LOG_POS=1402559; No connection. Trying to reconnect... ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) ERROR: Can't connect to the server unknown [(none)]>
???
SHUTDOWN したままだった
しかも、 help shutdown したら、 shell におちた
mariadb の status を見る
[ope@radius0 ~]$ sudo systemctl status mariadb.service ● mariadb.service - MariaDB 10.5.10 database server Loaded: loaded (/usr/lib/systemd/system/./mariadb.service; enabled; vendor preset> Drop-In: /etc/systemd/system/mariadb.service.d └─migrated-from-my.cnf-settings.conf, syslog.conf Active: inactive (dead) since Sun 2021-06-13 17:32:47 JST; 24min ago Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ Process: 8424 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_PO> Process: 8409 ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSRE> Process: 8393 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||> Process: 8388 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POS> Main PID: 8409 (code=exited, status=0/SUCCESS) Status: "MariaDB server is down"
見事に止まっている
起動させるよ
sudo systemctl start mariadb.service --with-skip-slave-start --read-only
Do it on old Master
[ope@radius0 ~]$ sudo systemctl start mariadb.service --with-skip-slave-start --read-only systemctl: unrecognized option '--with-skip-slave-start' [ope@radius0 ~]$
つかえないじゃん？
ドキュメントにも特に書いていないじゃん？
普通に起動する
[ope@radius0 ~]$ sudo systemctl start mariadb.service [ope@radius0 ~]$ sudo systemctl status mariadb.service ● mariadb.service - MariaDB 10.5.10 database server Loaded: loaded (/usr/lib/systemd/system/./mariadb.service; enabled; vendor preset> Drop-In: /etc/systemd/system/mariadb.service.d └─migrated-from-my.cnf-settings.conf, syslog.conf Active: active (running) since Sun 2021-06-13 18:04:47 JST; 2s ago Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ Process: 10059 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_P> Process: 10028 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= |> Process: 10026 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_PO> Main PID: 10044 (mariadbd) Status: "Taking your SQL requests now..." Tasks: 14 (limit: 12415) Memory: 69.1M CGroup: /system.slice/mariadb.service └─10044 /usr/sbin/mariadbd Jun 13 18:04:47 radius0.tk.net mariadbd[10044]: 2021-06-13 18:04:47 0 [Note] InnoDB:> Jun 13 18:04:47 radius0.tk.net mariadbd[10044]: 2021-06-13 18:04:47 0 [Note] Plugin > Jun 13 18:04:47 radius0.tk.net mariadbd[10044]: 2021-06-13 18:04:47 0 [Note] InnoDB:> Jun 13 18:04:47 radius0.tk.net mariadbd[10044]: 2021-06-13 18:04:47 0 [Note] InnoDB:> Jun 13 18:04:47 radius0.tk.net mariadbd[10044]: 2021-06-13 18:04:47 0 [Note] Server > Jun 13 18:04:47 radius0.tk.net mariadbd[10044]: 2021-06-13 18:04:47 0 [Note] Reading> Jun 13 18:04:47 radius0.tk.net mariadbd[10044]: 2021-06-13 18:04:47 0 [Note] Added n> Jun 13 18:04:47 radius0.tk.net mariadbd[10044]: 2021-06-13 18:04:47 0 [Note] /usr/sb> Jun 13 18:04:47 radius0.tk.net mariadbd[10044]: Version: '10.5.10-MariaDB-log' sock> Jun 13 18:04:47 radius0.tk.net systemd[1]: Started MariaDB 10.5.10 database server. [ope@radius0 ~]$ [ope@radius0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 10.5.10-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
再度コマンド
set @@global.read_only=1; STOP ALL SLAVES; RESET MASTER; RESET SLAVE ALL; CHANGE MASTER TO MASTER_HOST="192.168.100.103", MASTER_PORT=3306, MASTER_USER='replication_user', MASTER_USE_GTID=slave_pos, MASTER_LOG_FILE="radius1-bin.000001", MASTER_LOG_POS=1402559; START SLAVE;
Do it on old Master
MariaDB [(none)]> set @@global.read_only=1; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> STOP ALL SLAVES; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> RESET MASTER; Query OK, 0 rows affected (0.060 sec) MariaDB [(none)]> RESET SLAVE ALL; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST="192.168.100.103", -> MASTER_PORT=3306, -> MASTER_USER='replication_user', -> MASTER_USE_GTID=slave_pos, -> MASTER_LOG_FILE="radius1-bin.000001", -> MASTER_LOG_POS=1402559; Query OK, 0 rows affected (0.119 sec) MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.072 sec) MariaDB [(none)]>
うん。出来た。
確認
Do it on old Master
MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.100.103 Master_User: replication_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: radius1-bin.000001 Read_Master_Log_Pos: 1402559 Relay_Log_File: radius0-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: radius1-bin.000001 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1402559 Relay_Log_Space: 256 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1045 Last_IO_Error: error connecting to master 'replication_user@192.168.100.103:3306' - retry-time: 60 maximum-retries: 86400 message: Access denied for user 'replication_user'@'192.168.100.102' (using password: NO) Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.001 sec) MariaDB [(none)]>MariaDB [(none)]> use radius; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [radius]> select * from radcheck; +----+----------+--------------------+----+-----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+-----------+ | 1 | user1 | Cleartext-Password | := | user1pass | | 2 | user2 | Cleartext-Password | := | user2pass | +----+----------+--------------------+----+-----------+ 2 rows in set (0.003 sec) MariaDB [radius]>
Do it on old Slave (new Master)
MariaDB [(none)]> use radius; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [radius]> MariaDB [radius]> select * from radcheck; +----+----------+--------------------+----+-----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+-----------+ | 1 | user1 | Cleartext-Password | := | user1pass | | 2 | user2 | Cleartext-Password | := | user2pass | +----+----------+--------------------+----+-----------+ 2 rows in set (0.002 sec) MariaDB [radius]>
INSERT する
INSERT INTO radcheck (username, attribute, op, value) VALUES ('user3', 'Cleartext-password', ':=', 'user3pass') ; SELECT * FROM radcheck;
Do it on old Slave (new Master)
MariaDB [radius]> INSERT INTO radcheck -> (username, attribute, op, value) -> VALUES -> ('user3', 'Cleartext-password', ':=', 'user3pass') -> ; Query OK, 1 row affected (0.010 sec) MariaDB [radius]> SELECT * FROM radcheck; +----+----------+--------------------+----+-----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+-----------+ | 1 | user1 | Cleartext-Password | := | user1pass | | 2 | user2 | Cleartext-Password | := | user2pass | | 3 | user3 | Cleartext-password | := | user3pass | +----+----------+--------------------+----+-----------+ 3 rows in set (0.001 sec) MariaDB [radius]>
Do it on old Master ( new Slave )
MariaDB [radius]> select * from radcheck; +----+----------+--------------------+----+-----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+-----------+ | 1 | user1 | Cleartext-Password | := | user1pass | | 2 | user2 | Cleartext-Password | := | user2pass | +----+----------+--------------------+----+-----------+ 2 rows in set (0.001 sec) MariaDB [radius]>
あれ?
MariaDB [radius]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.100.103 Master_User: replication_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: radius1-bin.000001 Read_Master_Log_Pos: 1402559 Relay_Log_File: radius0-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: radius1-bin.000001 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1402559 Relay_Log_Space: 256 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1045 Last_IO_Error: error connecting to master 'replication_user@192.168.100.103:3306' - retry-time: 60 maximum-retries: 86400 message: Access denied for user 'replication_user'@'192.168.100.102' (using password: NO) Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.000 sec) MariaDB [radius]>
見てなかった
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Last_IO_Error: error connecting to master 'replication_user@192.168.100.103:3306' - retry-time: 60 maximum-retries: 86400 message: Access denied for user 'replication_user'@'192.168.100.102' (using password: NO)
check on old Slave (new master)
MariaDB [mysql]> select host, user from user; +-----------+------------------+ | Host | User | +-----------+------------------+ | % | replication_user | | localhost | mariadb.sys | | localhost | mysql | | localhost | radius | | localhost | root | +-----------+------------------+ 5 rows in set (0.004 sec) MariaDB [mysql]>
ok
firewall check on old slave (new master)
[ope@radius1 ~]$ sudo firewall-cmd --list-all public (active) target: default icmp-block-inversion: no interfaces: enp0s3 sources: services: ssh ports: 1812/udp 1813/udp 80/tcp 3306/tcp 4567/tcp 4444/tcp protocols: masquerade: no forward-ports: source-ports: icmp-blocks: rich rules: [ope@radius1 ~]$
ok
try to connect old slave from old master
[ope@radius0 ~]$ mysql -h 192.168.100.103 -u replication_user -p Enter password: ... MariaDB [(none)]>
ok
port check retry
[ope@radius1 ~]$ ss -altn State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 0.0.0.0:80 0.0.0.0:* LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 80 *:3306 *:* LISTEN 0 128 [::]:22 [::]:* [ope@radius1 ~]$ [ope@radius1 ~]$ ss -altn4 State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 0.0.0.0:80 0.0.0.0:* LISTEN 0 128 0.0.0.0:22 0.0.0.0:* [ope@radius1 ~]$ [ope@radius1 ~]$ ss -altn6 State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 80 *:3306 *:* LISTEN 0 128 [::]:22 [::]:* [ope@radius1 ~]$
?? ipv6 は listen しているけど、 ipv4 では listen していない？
別のコマンドでも
[ope@radius1 ~]$ netstat -alptn4 (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN - tcp 0 0 192.168.100.103:22 192.168.100.1:54686 ESTABLISHED - tcp 0 0 192.168.100.103:22 192.168.100.1:53804 ESTABLISHED - [ope@radius1 ~]$ [ope@radius1 ~]$ netstat -alptn6 (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp6 0 0 :::3306 :::* LISTEN - tcp6 0 0 :::22 :::* LISTEN - tcp6 0 0 192.168.100.103:3306 192.168.100.102:36338 TIME_WAIT - [ope@radius1 ~]$
?? 本当になんで? ipv6 優先なのか。冷静に考えて普通か
old master でも確認する
[ope@radius0 ~]$ ss -alnt4 State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 0.0.0.0:80 0.0.0.0:* LISTEN 0 128 0.0.0.0:22 0.0.0.0:* [ope@radius0 ~]$ [ope@radius0 ~]$ ss -alnt6 State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 80 *:3306 *:* LISTEN 0 128 [::]:22 [::]:* [ope@radius0 ~]$
同じか。
[ope@radius0 ~]$ nmap -p3306 192.168.100.103 Starting Nmap 7.70 ( https://nmap.org ) at 2021-06-13 22:36 JST Nmap scan report for 192.168.100.103 Host is up (0.0022s latency). PORT STATE SERVICE 3306/tcp open mysql Nmap done: 1 IP address (1 host up) scanned in 0.19 seconds [ope@radius0 ~]$
空いているけど、何故か信用ならない
逆向きも見てみる
[ope@radius1 ~]$ nmap -p3306 192.168.100.102 Starting Nmap 7.70 ( https://nmap.org ) at 2021-06-20 11:50 JST Nmap scan report for 192.168.100.102 Host is up (0.0016s latency). PORT STATE SERVICE 3306/tcp open mysql Nmap done: 1 IP address (1 host up) scanned in 0.09 seconds [ope@radius1 ~]$
空いている
ポートじゃない
もう一度エラーメッセージを見てみる
error connecting to master 'replication_user@192.168.100.103:3306' - retry-time: 60 maximum-retries: 86400 message: Access denied for user 'replication_user'@'192.168.100.102' (using password: NO)
SLAVE に設定する時、パスワード設定が抜けていたとかそんなやつかな？
最初に SLAVE Start する時どうしていたか？
こんなコマンドだった
MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.100.102', -> MASTER_USER='replication_user', -> MASTER_PASSWORD='bigs3cret', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='radius0-bin.000001', -> MASTER_LOG_POS=330, -> MASTER_CONNECT_RETRY=10; Query OK, 0 rows affected (0.099 sec)
MASTER HOST の情報を SLAVE 側で書き込んでいた
MASTER を DOWN させてから SLAVE にするときに使ったコマンド
MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST="192.168.100.103", -> MASTER_PORT=3306, -> MASTER_USER='replication_user', -> MASTER_USE_GTID=slave_pos, -> MASTER_LOG_FILE="radius1-bin.000001", -> MASTER_LOG_POS=1402559; Query OK, 0 rows affected (0.119 sec)
-> MASTER_PASSWORD='bigs3cret',
パスワード指定がなかった。これが原因か。
コマンド再掲+PASSWORD 追加
set @@global.read_only=1; STOP ALL SLAVES; RESET MASTER; RESET SLAVE ALL; CHANGE MASTER TO MASTER_HOST="192.168.100.103", MASTER_PORT=3306, MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_USE_GTID=slave_pos, MASTER_LOG_FILE="radius1-bin.000001", MASTER_LOG_POS=1402559; START SLAVE;
現在の MASTER STATUS
MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | radius1-bin.000001 | 1402844 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.000 sec) MariaDB [(none)]>
1回 INSERT 入れているから Position がずれている。
snapshot を取った上で、このままコマンドを入れてどうなるかやってみる
snapshot を取ったので、上記コマンドを入れてみる
MariaDB [(none)]> set @@global.read_only=1; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> STOP ALL SLAVES; Query OK, 0 rows affected, 1 warning (0.027 sec) MariaDB [(none)]> RESET MASTER; Query OK, 0 rows affected (0.074 sec) MariaDB [(none)]> RESET SLAVE ALL; Query OK, 0 rows affected (0.008 sec) MariaDB [(none)]> MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST="192.168.100.103", -> MASTER_PORT=3306, -> MASTER_USER='replication_user', -> MASTER_PASSWORD='bigs3cret', -> MASTER_USE_GTID=slave_pos, -> MASTER_LOG_FILE="radius1-bin.000001", -> MASTER_LOG_POS=1402559; Query OK, 0 rows affected (0.182 sec) MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.062 sec) MariaDB [(none)]>
んー。なんか良さそう
MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.103 Master_User: replication_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: radius1-bin.000001 Read_Master_Log_Pos: 1402844 Relay_Log_File: radius0-relay-bin.000002 Relay_Log_Pos: 1403145 Relay_Master_Log_File: radius1-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1402844 Relay_Log_Space: 1403456 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-2-157 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 142 Slave_Non_Transactional_Groups: 10 Slave_Transactional_Groups: 5 1 row in set (0.001 sec) MariaDB [(none)]>
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Last_SQL_Error:
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
同期されたのか？SLAVE としていきたっぽいことと、アクセスできない問題は解決した
ということで radcheck テーブルを見てみる
MariaDB [(none)]> SELECT * FROM radius.radcheck; +----+----------+--------------------+----+-----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+-----------+ | 1 | user1 | Cleartext-Password | := | user1pass | | 3 | user3 | Cleartext-password | := | user3pass | +----+----------+--------------------+----+-----------+ 2 rows in set (0.001 sec) MariaDB [(none)]>
あれ？
MASTER 側も見てみる
MariaDB [(none)]> SELECT * FROM radius.radcheck; +----+----------+--------------------+----+-----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+-----------+ | 1 | user1 | Cleartext-Password | := | user1pass | | 2 | user2 | Cleartext-Password | := | user2pass | | 3 | user3 | Cleartext-password | := | user3pass | +----+----------+--------------------+----+-----------+ 3 rows in set (0.003 sec) MariaDB [(none)]>
なるほど。
SLAVE でレプリケーションに参加させる場合 CHANGE MASTER TO する前に MASTER のバックアップを取ってデータの同期を取っていないと間がすっぽ抜けるのか
というわけで、そのへんをちゃんとやる
new MASTER のデータをバックアップ、new SLAVE にリストアし、SLAVE START する
new MASTERで
テーブルの書き込みロック
FLUSH TABLES WITH READ LOCK;
MASTER STATUS を記録する
SHOW MASTER STATUS;
SELECT @@global.gtid_binlog_pos;
new MASTER に別ターミナルでアクセスして backup
mysqldump --defaults-extra-file=mariadb_root.cnf --all-databases \ --flush-logs --single-transaction --master-data=2 > \ all_databases_on_db2_$(date +%F_%H-%M-%S).sql
Backup data を転送 new MASTER to new SLAVE
sftp ope@192.168.100.102 put FILENAME
new SLAVE でリストア
mysql --defaults-extra-file=mariadb_root.cnf < FILENAME
new MASTER で ロックを解除
UNLOCK TABLES;
new SLAVE で CHANGE MASTER TO する
set @@global.read_only=1; STOP ALL SLAVES; RESET MASTER; RESET SLAVE ALL; CHANGE MASTER TO MASTER_HOST="192.168.100.103", MASTER_PORT=3306, MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_USE_GTID=slave_pos, MASTER_LOG_FILE="<filename>", MASTER_LOG_POS=<position>; START SLAVE;
実際にやってみる
new MASTER
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.241 sec) MariaDB [(none)]> MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | radius1-bin.000001 | 1402844 | | | +--------------------+----------+--------------+------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT @@global.gtid_binlog_pos; +--------------------------+ | @@global.gtid_binlog_pos | +--------------------------+ | 0-2-157 | +--------------------------+ 1 row in set (0.001 sec) MariaDB [(none)]>
[ope@radius1 ~]$ mysqldump --defaults-extra-file=mariadb_root.cnf --all-databases \ > --flush-logs --single-transaction --master-data=2 > \ > all_databases_on_db2_$(date +%F_%H-%M-%S).sql [ope@radius1 ~]$ [ope@radius1 ~]$ [ope@radius1 ~]$ ls -l all_databases_on_db2_2021-06-20_14-21-55.sql -rw-rw-r--. 1 ope ope 1399150 6月 20 14:21 all_databases_on_db2_2021-06-20_14-21-55.sql [ope@radius1 ~]$ [ope@radius1 ~]$ [ope@radius1 ~]$ sftp ope@192.168.100.102 ope@192.168.100.102's password: Connected to ope@192.168.100.102. sftp> put all all_databases_2021-06-13_14-27-50.sql all_databases_on_db2_2021-06-20_14-21-55.sql sftp> put all_databases_on_db2_2021-06-20_14-21-55.sql Uploading all_databases_on_db2_2021-06-20_14-21-55.sql to /home/ope/all_databases_on_db2_2021-06-20_14-21-55.sql all_databases_on_db2_2021-06-20_14-21-55.sql 100% 1366KB 26.9MB/s 00:00 sftp> exit [ope@radius1 ~]$
new SLAVE
[ope@radius0 ~]$ ls -l all_databases_on_db2_2021-06-20_14-21-55.sql -rw-rw-r--. 1 ope ope 1399150 Jun 20 14:23 all_databases_on_db2_2021-06-20_14-21-55.sql [ope@radius0 ~]$ [ope@radius0 ~]$ [ope@radius0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf < all_databases_on_db2_2021-06-20_14-21-55.sql [ope@radius0 ~]$
new MASTER
MariaDB [(none)]> UNLOCK TABLES; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]>
new SLAVE で CHANGE MASTER TO する
set @@global.read_only=1; STOP ALL SLAVES; RESET MASTER; RESET SLAVE ALL; CHANGE MASTER TO MASTER_HOST="192.168.100.103", MASTER_PORT=3306, MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_USE_GTID=slave_pos, MASTER_LOG_FILE="radius1-bin.000001", MASTER_LOG_POS=1402844; START SLAVE;
[ope@radius0 ~]$ mysql --defaults-extra-file=mariadb_root.cnf Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 25 Server version: 10.5.10-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> set @@global.read_only=1; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> STOP ALL SLAVES; Query OK, 0 rows affected, 1 warning (0.028 sec) MariaDB [(none)]> RESET MASTER; Query OK, 0 rows affected (0.062 sec) MariaDB [(none)]> RESET SLAVE ALL; Query OK, 0 rows affected (0.005 sec) MariaDB [(none)]> MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST="192.168.100.103", -> MASTER_PORT=3306, -> MASTER_USER='replication_user', -> MASTER_PASSWORD='bigs3cret', -> MASTER_USE_GTID=slave_pos, -> MASTER_LOG_FILE="radius1-bin.000001", -> MASTER_LOG_POS=1402844; Query OK, 0 rows affected (0.104 sec) MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.053 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.103 Master_User: replication_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: radius1-bin.000002 Read_Master_Log_Pos: 389 Relay_Log_File: radius0-relay-bin.000002 Relay_Log_Pos: 690 Relay_Master_Log_File: radius1-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: ...
MariaDB [(none)]> SELECT * FROM radius.radcheck; +----+----------+--------------------+----+-----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+-----------+ | 1 | user1 | Cleartext-Password | := | user1pass | | 2 | user2 | Cleartext-Password | := | user2pass | | 3 | user3 | Cleartext-password | := | user3pass | +----+----------+--------------------+----+-----------+ 3 rows in set (0.001 sec) MariaDB [(none)]>
OK
ちかく、まとめる
