php 7.4 から MySQL 8へPDOでの接続テスト

php 7.2からMySQL 8へ接続するときは認証方式の変更が必要でした。
phpからMySQL 8.0へPDOで接続時「SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client」

Ubuntu 20.04ではデフォルトでphp 7.4、MySQL 8がインストールされるということは
Ubuntu20.04 Server + nginx + php-fpm 7.4 の動作環境を構築する
MySQL 8.0をUbuntu Server 20.04へインストール
この問題が解消しているはず。


接続ライブラリのインストールと確認用のサンプル



phpからmysqlへ接続するためのライブラリをインストールします。


$ sudo apt install php-mysql




接続確認用にこんなサンプルを作成。


  1. <?php
  2. try {
  3.     $pdo = new PDO('mysql:host=localhost;dbname=sample;charset=utf8mb4','admin','P@ssw0rd');
  4. } catch (PDOException $e) {
  5.     exit($e->getMessage().PHP_EOL);
  6. }
  7. echo('ok'.PHP_EOL);




実行してみると、無事接続できました。


$ php sample.php
ok





念のため、MySQLユーザーの認証タイプを確認すると
「caching_sha2_password」
になっています。


mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host     | plugin                |
+------------------+-----------+-----------------------+
| admin            | %         | caching_sha2_password |
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | auth_socket         |
+------------------+-----------+-----------------------+
6 rows in set (0.00 sec)




php 7.4からはMySQL 8系でも問題なく利用できるようです。

Ubuntu Server 20.04 + MySQL 8 ユーザーの作成と別端末からの接続許可

Ubuntu Server 20.04 に MySQL 8 をインストールしてみました。
MySQL 8.0をUbuntu Server 20.04へインストール

MySQLへユーザーを追加し、別端末から接続できるよう構成てみます。
過去記事を参考に作業しました。
MySQL 8.0 外部(他の端末)から接続する


ユーザーの作成



ユーザー名:admin
パスワード:P@ssw0rd
すべての端末から接続可能:「'admin'@'%'」のようにホスト指定を「%」にする
すべてのデータベース操作可能: 「*.*」ですべてのデータベース

上記条件でユーザーを作成します。
まず、コンソールからrootでmysqlに接続


$ sudo mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>




CREATE USERでユーザー作成。GRANTで権限を付与してやります。


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



実行結果


mysql> CREATE USER 'admin'@'%' IDENTIFIED BY 'P@ssw0rd';
Query OK, 0 rows affected (0.10 sec)

mysql> GRANT ALL ON *.* TO 'admin'@'%';
Query OK, 0 rows affected (0.07 sec)





bind-addressの変更



初期設定では、bind-addressが「127.0.0.1」となっているため、自分自身しか接続できません。
設定ファイルを編集。


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



bind-addressを「0.0.0.0」(すべて)に変更します。


# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address         = 127.0.0.1
bind-address            = 0.0.0.0




設定を反映


$ service mysql restart



これでadminユーザーは別の端末からもMySQLに接続できるようになりました。


MySQL 8.0をUbuntu Server 20.04へインストール

Ubuntu Server 20.04へMySQLをインストールしてみます。
過去の記事を参考にしました。
MySQL 8.0をUbuntu Server 18.04へインストール

デフォルトのバージョンがMySQL 8へ変更されたようでリポジトリの追加は不要でした。


インストール



aptでインストールします。


$ sudo apt install mysql-server




MySQL 8.0.20がインストールできました。


$ mysql --version
mysql Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))




sudoでmysqlコマンドを実行すればパスワード無しで接続できます。


$ sudo mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>




続いてユーザーの作成と別端末からの接続許可を試してみます。

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」にまつわる怖い話

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 準同期レプリケーションのインストールと構成

プロフィール

Author:symfo
blog形式だと探しにくいので、まとめサイト作成中です。
Symfoware まとめ

PR




検索フォーム

月別アーカイブ