Symfoware

Symfowareについての考察blog

MySQL 5.7 レプリケーションの停止とmaster,slaveの切り替え

Ubuntu Server 18.04 + MySQL 5.7でレプリケーションを試してみました。
MySQL 5.7 GTIDによる非同期レプリケーション環境を構築する(Ubuntu 18.04)
MySQL 5.7 GTIDによる準同期レプリケーション環境を構築する(Ubuntu 18.04)

障害発生時の切り替え方法を試してみます。
こちらを参考にさせていただきました。
MySQLのmasterとslaveの手動切り替え
MySQL-5.5/5.6でのレプリケーション利用者に伝えたい「RESET SLAVE」にまつわる怖い話



想定



masterサーバーが停止。
slaveをmasterとして運用継続。
旧masterをslaveとして復旧。

...という想定です。


事前準備



今回の構成は、GTIDによる準同期レプリケーションで、masterとslaveが入れ替わります。
現在masterとして動いているサーバーにslaveのプラグインをインストール。


mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.04 sec)



slaveとして動いているサーバーにmasterのプラグインをインストールしておきます。


mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.04 sec)



また、現時点でレプリケーションしているテーブルの内容は以下のとおりです。


mysql> SELECT * FROM test;
+----+-------+
| id | item |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
+----+-------+
4 rows in set (0.00 sec)





masterサーバーの停止とレプリケーションの停止



masterサーバーがダウンした想定で、mysqlサービスを停止します。

・master側


$ sudo service mysql stop




スレーブ側でレプリケーションの状態を確認してみます。

・slave側


mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Reconnecting after a failed master event read
                 Master_Host: 192.168.1.104
                 Master_User: repl
                 Master_Port: 3306
                Connect_Retry: 60
             Master_Log_File: mysql-bin.000008
         Read_Master_Log_Pos: 194
             Relay_Log_File: mysql-relay-bin.000012
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
...(略)...
                Last_IO_Errno: 2003
                Last_IO_Error: error reconnecting to master 'repl@192.168.1.104:3306' - retry-time: 60 retries: 2
             Last_SQL_Errno: 0
             Last_SQL_Error:
...(略)...
         Master_TLS_Version:
1 row in set (0.00 sec)



masterと接続できないエラーが表示されました。

IOスレッドを停止します。


mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)




「SHOW PROCESSLIST」を実行。
「Slave has read all relay log」が表示されるまで待ちます。


mysql> SHOW PROCESSLIST;
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
| Id | User        | Host     | db | Command | Time | State                                                 | Info             |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
| 2 | system user |         | NULL | Connect | 12228 | Slave has read all relay log; waiting for more updates | NULL             |
| 5 | root        | localhost | NULL | Query |     0 | starting                                             | SHOW PROCESSLIST |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
2 rows in set (0.00 sec)



スレーブを停止。


mysql> STOP SLAVE;
Query OK, 0 rows affected (0.02 sec)



レプリケーションの設定を初期化します。


mysql> RESET SLAVE ALL;
Query OK, 0 rows affected (0.13 sec)

mysql> RESET MASTER;
Query OK, 0 rows affected (0.16 sec)





slaveの昇格



設定ファイルを編集。


$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf



[mysqld]セクションにmasterとして動作する指定を追記しました。


# スレーブSQLスレッドによって実行される更新をバイナリログに書き込む
log_slave_updates

# リレーログファイルの名前
relay_log=/var/log/mysql/mysql-relay-bin

plugin-load=rpl_semi_sync_slave=semisync_slave.so

#rpl_semi_sync_slave_enabledと rpl_semi_sync_master_enabledを同時にonにはできない
#rpl_semi_sync_slave_enabled = 1

#---以下を追記---
#masterとしての動作
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=10000 # 10 second




サービスを再起動します。


$ sudo service mysql restart




テストとして、この時点でデータを2件追加しておきます。


mysql> INSERT INTO test (id, item) VALUES (5, 'test5');
Query OK, 1 row affected (10.06 sec)

mysql> INSERT INTO test (id, item) VALUES (6, 'test6');
Query OK, 1 row affected (0.12 sec)





旧masterをslaveとして復旧



停止している旧masterをslaveとして復旧してみます。
設定ファイルを編集。


$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf



[mysqld]セクションにslaveとして動作する指定を追記しました。


#rpl_semi_sync_slave_enabledと rpl_semi_sync_master_enabledを同時にonにはできない
plugin-load=rpl_semi_sync_master=semisync_master.so
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=10000 # 10 second

#---以下を追記--

# スレーブSQLスレッドによって実行される更新をバイナリログに書き込む
log_slave_updates

# リレーログファイルの名前
relay_log=/var/log/mysql/mysql-relay-bin

plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled = 1




データベースを起動。


$ sudo service mysql start



この時点では、testテーブルの情報は古いままです。


mysql> select * from test;
+----+-------+
| id | item |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
+----+-------+
4 rows in set (0.00 sec)



新masterのデータベースを「CHANGE MASTER TO」で追加します。


mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.1.105',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='P@ssw0rd',
    -> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.57 sec)




「START SLAVE」でレプリケーションを開始します。


mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)




もう一度testテーブルを検索してみます。


mysql> select * from test;
+----+-------+
| id | item |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
| 6 | test6 |
+----+-------+
6 rows in set (0.00 sec)




ちゃんと同期されましたね。
これでslaveとしての復帰完了です。


【参考URL】
MySQLのmasterとslaveの手動切り替え
MySQL-5.5/5.6でのレプリケーション利用者に伝えたい「RESET SLAVE」にまつわる怖い話

テーマ:サーバ - ジャンル:コンピュータ

  1. 2018/08/19(日) 22:53:28|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

MySQL 5.7 GTIDによる準同期レプリケーション環境を構築する(Ubuntu 18.04)

MySQL 5.7 + GTIDで非同期レプリケーションを試してみました。
MySQL 5.7 GTIDによる非同期レプリケーション環境を構築する(Ubuntu 18.04)

この環境を準同期に変更してみようと思います。
Ubuntu Server 18.04
MySQL 5.7.23
の環境です。

こちらを参考にさせていただきました。
MySQL5.6で準同期レプリケーションを試す
17.3.8.2 準同期レプリケーションのインストールと構成


前提条件



まず、非同期レプリケーションを構築しておきます。
MySQL 5.7 GTIDによる非同期レプリケーション環境を構築する(Ubuntu 18.04)

非同期レプリケーションの環境を準同期に昇格するイメージです。


マスター側の操作



準同期レプリケーションはプラグインで実装されています。
マスター側でプラグインをインストール。


mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.05 sec)



準同期を有効化。
タイムアウトを10秒に設定しました。


mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_master_timeout = 10000;
Query OK, 0 rows affected (0.00 sec)



設定内容を確認。


mysql> SHOW GLOBAL VARIABLES LIKE "rpl%";
+-------------------------------------------+------------+
| Variable_name                             | Value     |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled             | ON         |
| rpl_semi_sync_master_timeout             | 10000     |
| rpl_semi_sync_master_trace_level         | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1         |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point         | AFTER_SYNC |
| rpl_stop_slave_timeout                    | 31536000 |
+-------------------------------------------+------------+
7 rows in set (0.03 sec)





スレーブ側での操作



続いてスレーブ側での操作です。
マスターと同様、プラグインをインストール。
※インストールするプラグイン名がマスターと異なることに注意。


mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.10 sec)



準同期レプリケーションを有効化。


mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)



IOスレッドの再起動。


mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)



これで同期が開始されました。


mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name             | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)





同期の確認



スレーブでテスト用のテーブルを検索してみます。


mysql> SELECT * FROM test;
+----+-------+
| id | item |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
+----+-------+
3 rows in set (0.01 sec)




マスターでデータ登録。


mysql> INSERT INTO test (id, item) VALUES (4, 'test4');
Query OK, 1 row affected (0.06 sec)




スレーブで再度検索実行。


mysql> SELECT * FROM test;
+----+-------+
| id | item |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
+----+-------+
4 rows in set (0.00 sec)



ちゃんと同期されていますね。



設定の永続化



これが地味にはまりました。
マスター側の設定ファイルを編集します。


$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf



[mysqld]のセクションに必要な設定を追記します。


[mysqld]
...
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=10000 # 10 second




設定を変更したら、サーバーをリスタート。


$ sudo service mysql restart



「plugin-load」がミソです。
この指定がないと起動時に以下のエラーが発生します。


$ sudo service mysql restart
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.




続いてスレーブ側の設定ファイルを編集。


$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf



[mysqld]のセクションに必要な設定を追記します。


[mysqld]
...
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled = 1



設定を変更したら、サーバーをリスタート。


$ sudo service mysql restart



これで設定内容が永続化できました。



【参考URL】
MySQL5.6で準同期レプリケーションを試す
17.3.8.2 準同期レプリケーションのインストールと構成

テーマ:サーバ - ジャンル:コンピュータ

  1. 2018/08/19(日) 19:12:17|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

MySQL 5.7 GTIDによる非同期レプリケーション環境を構築する(Ubuntu 18.04)

以前、MariaDBでレプリケーションを行ってみました。
MariaDB 10のレプリケーション設定(Debian 8)

今回、MySQL 5.7でGTIDを使用したレプリケーション環境を構築してみようと思います。
Ubuntu Server 18.04
MySQL 5.7.23
の環境です。

マスター(mysql1):192.168.1.104
スレーブ(mysql2):192.168.1.105

インストールはこちらの手順で実行しました。
MySQL 5.7 インストール後のrootパスワードと変更方法(Ubuntu 18.04)

構築手順はこちらを参考にさせていただきました。
MySQL 5.7 GTID レプリケーション設定メモ



レプリケーションするデータベースの用意



マスター(192.168.1.104)にレプリケーションするデータベースを作成します。

sampleというデータベースを作成。
testテーブルを用意し、適当にデータを入れておきます。


$ sudo mysql -uroot

mysql> CREATE DATABASE sample;
Query OK, 1 row affected (0.00 sec)

mysql> use sample;
Database changed
mysql> CREATE TABLE test (id int not null PRIMARY KEY, item varchar(100) not null);
Query OK, 0 rows affected (0.22 sec)

mysql> INSERT INTO test (id, item) VALUES (1, 'test1');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO test (id, item) VALUES (2, 'test2');
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM test;
+----+-------+
| id | item |
+----+-------+
| 1 | test1 |
| 2 | test2 |
+----+-------+
2 rows in set (0.00 sec)





マスター側での操作



レプリケーション用のユーザー「repl」をパスワード「P@ssw0rd」で作成。
接続可能なネットワークセグメントは「192.168.1.0/255.255.255.0」とします。


mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'P@ssw0rd';
Query OK, 0 rows affected, 1 warning (0.02 sec)




作成できたかSHOW GRANTSで確認。


mysql> SHOW GRANTS FOR 'repl'@'192.168.1.0/255.255.255.0';
+----------------------------------------------------------------------+
| Grants for repl@192.168.1.0/255.255.255.0                            |
+----------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.0/255.255.255.0' |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)




レプリケーション用のユーザーが準備出来ました。
次に、mysqld.cnfにレプリケーション用の設定を記載します。


$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf



[mysqld]のセクションに必要な設定を追記します。


# スレーブからもアクセスできるように変更
#bind-address            = 127.0.0.1
bind-address            = 0.0.0.0

# スレーブと重複しないIDを採番
server-id = 1001

# GTIDの有効化
gtid_mode=ON

# GTIDと併用できないSQL文を禁止する
enforce_gtid_consistency

# バイナリログの有効化(ログの出力パスを記載)
log_bin = /var/log/mysql/mysql-bin.log

# バイナリログをコミットと同時にディスクに書込む
sync_binlog=1




設定ファイルを変更したら、mysqlをリスタート。


$ sudo service mysql restart




続いて、スレーブ用にデータベースのフルダンプを取得します。


$ sudo mysqldump -u root -p --all-databases --single-transaction --flush-logs \
--hex-blob --triggers --routines --events \
> /tmp/dbdump.db



scpコマンドでスレーブサーバーに転送します。


$ scp /tmp/dbdump.db [user]@192.168.1.105:/tmp/dbdump.db



これでマスター側での作業は終了です。




スレーブ側での操作



続いてスレーブ側での設定です。
mysqld.cnfにレプリケーション用の設定を記載します。


$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf



[mysqld]のセクションに必要な設定を追記します。


# 外部アクセスを許可
bind-address            = 0.0.0.0

# マスターと重複しないIDを採番
server-id = 1002

# GTIDの有効化
gtid_mode=ON

# GTIDと併用できないSQL文を禁止する
enforce_gtid_consistency

# バイナリログの有効化(ログの出力パスを記載)
log_bin = /var/log/mysql/mysql-bin.log

# バイナリログをコミットと同時にディスクに書込む
sync_binlog=1

# 更新を禁止する
# 今回は書き込み可能で構成
#read_only

# スレーブSQLスレッドによって実行される更新をバイナリログに書き込む
log_slave_updates

# リレーログファイルの名前
relay_log=/var/log/mysql/mysql-relay-bin




設定ファイルを変更したら、mysqlをリスタート。


$ sudo service mysql restart



GTIDをマスターと揃えるため、「RESET MASTER」で一旦クリアします。


$ sudo mysql -uroot

mysql> RESET MASTER;
Query OK, 0 rows affected (0.10 sec)



マスターで取得したバックアップから復元。


$ sudo mysql -u root < /tmp/dbdump.db



マスターのデータベースを「CHANGE MASTER TO」で指定します。


CHANGE MASTER TO
MASTER_HOST='192.168.1.104',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='P@ssw0rd',
MASTER_AUTO_POSITION=1;



「START SLAVE」でレプリケーションを開始します。


mysql> START SLAVE;
Query OK, 0 rows affected (0.02 sec)





動作確認



ちゃんとデータが動悸されるか確認してみます。
作成したテーブルの内容を表示。


mysql> use sample;
Database changed

mysql> SELECT * FROM test;
+----+-------+
| id | item |
+----+-------+
| 1 | test1 |
| 2 | test2 |
+----+-------+
2 rows in set (0.00 sec)




マスター側でデータを追加。


mysql> INSERT INTO test (id, item) VALUES (3, 'test3');
Query OK, 1 row affected (0.06 sec)



スレーブ側でSELECT実行。


mysql> SELECT * FROM test;
+----+-------+
| id | item |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
+----+-------+
3 rows in set (0.00 sec)



ちゃんと変更が反映されていますね。


【参考URL】
MySQL 5.7 GTID レプリケーション設定メモ

テーマ:サーバ - ジャンル:コンピュータ

  1. 2018/08/19(日) 18:01:32|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

MySQL 個人的チートシート

よく忘れるコマンドのメモ。

初期設定編



インストール


$ sudo apt install mysql-server



初期rootパスワード設定。


$ sudo mysql_secure_installation



※参考
MySQL 5.7 インストール後のrootパスワードと変更方法(Ubuntu 18.04)


文字コード変更



現在の文字コード確認クエリー


show variables like '%char%';



設定ファイルの変更箇所

・/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]のセクションに1行追記。


[mysqld]
...
character-set-server=utf8mb4



※参考
MySQL 5.7 文字コードの確認とmy.confでutf8mb4への変更



ユーザー追加と外部接続



ユーザー追加クエリ。


CREATE USER 'admin'@'%' IDENTIFIED BY 'P@ssw0rd';
GRANT ALL ON *.* TO 'admin'@'%';




外部からの接続を行うため、listenしているipを変更。

・/etc/mysql/mysql.conf.d/mysqld.cnf

bind-addressを変更


#bind-address = 127.0.0.1
bind-address = '*'





文字コード、称号順序を指定したデータベース作成




CREATE DATABASE example CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


テーマ:サーバ - ジャンル:コンピュータ

  1. 2018/08/18(土) 18:52:23|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

MySQL 5.7 文字コードの確認とmy.confでutf8mb4への変更

MySQLの文字コードの確認と、my.confへの設定方法を忘れるのでメモしておきます。

Ubuntu Server 18.04へインストールしたMySQL 5.7での設定例です。

現在の文字コード



現在の文字コードを確認するSQL


show variables like '%char%';



実行例


mysql> show variables like '%char%';
+--------------------------------------+----------------------------+
| Variable_name                        | Value                     |
+--------------------------------------+----------------------------+
| character_set_client                 | utf8                     |
| character_set_connection             | utf8                     |
| character_set_database             | latin1                     |
| character_set_filesystem             | binary                     |
| character_set_results                | utf8                     |
| character_set_server                 | latin1                     |
| character_set_system                 | utf8                     |
| character_sets_dir                 | /usr/share/mysql/charsets/ |
| validate_password_special_char_count | 1                         |
+--------------------------------------+----------------------------+
9 rows in set (0.01 sec)



データベースの初期設定は「latin1」になっています。



mysqld.cnf



設定ファイル
「/etc/mysql/mysql.conf.d/mysqld.cnf 」
を編集します。


$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf



[mysqld]のセクションに1行追記します。


[mysqld]
...
character-set-server=utf8mb4




編集がおわったらmysqlを再起動。


$ sudo service mysql reload



変更されました。


mysql> show variables like '%char%';
+--------------------------------------+----------------------------+
| Variable_name                        | Value                     |
+--------------------------------------+----------------------------+
| character_set_client                 | utf8                     |
| character_set_connection             | utf8                     |
| character_set_database             | utf8mb4                    |
| character_set_filesystem             | binary                     |
| character_set_results                | utf8                     |
| character_set_server                 | utf8mb4                    |
| character_set_system                 | utf8                     |
| character_sets_dir                 | /usr/share/mysql/charsets/ |
| validate_password_special_char_count | 1                         |
+--------------------------------------+----------------------------+
9 rows in set (0.02 sec)


テーマ:サーバ - ジャンル:コンピュータ

  1. 2018/08/18(土) 18:47:26|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
次のページ