Symfoware

Symfowareについての考察blog

phpからMySQL 8.0へPDOで接続時「SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client」

Ubuntu 18.04にnginx + phpの動作環境を作成したり、
MySQL 8.0をインストールしたりしてみました。

Ubuntu18.04 Server + nginx + php-fpmの動作環境を構築する
MySQL 8.0をUbuntu Server 18.04へインストール
MySQL 8.0 外部(他の端末)から接続する

今回は、この環境を利用してphp 7.2からMySQL 8.0に接続してみようと思います。


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



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
SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client



なんでだ?




認証方式の変更



色々調べてわかったことは、
・MySQLは接続時の認証方式を変更可能
・MySQL 8.0のデフォルトは「caching_sha2_password」
・今のところ、PHPの接続ライブラリは「caching_sha2_password」に対応していない。

こちらが参考になりました。
MySQL 8.0.4におけるデフォルト認証形式の変更

2点対応を行います。


ユーザーの認証方式変更



まず、作成したユーザーの認証方式は何になっているか。
mysql.userを検索してみます。


mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host     | plugin                |
+------------------+-----------+-----------------------+
| admin            | %     &nbp;   | caching_sha2_password |
| mysql.infoschema | localhost | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)




作成したadminユーザーの認証方式が「caching_sha2_password」になっているのでこ
「mysql_native_password」に変更します。

書式は以下のとおり。


ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';



これでadminユーザーの認証方式が変更できました。


mysql> ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password;
Query OK, 0 rows affected (0.19 sec)

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





デフォルトの認証方式変更



MySQLが接続時に期待している認証方式のデフォルト値を変更します。
設定ファイルを編集。


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



[mysqld]セクションの末尾に
「default-authentication-plugin=mysql_native_password」
を追記します。


[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket         = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error     = /var/log/mysql/error.log
default-authentication-plugin=mysql_native_password



変更した内容を反映。


$ sudo service mysql reload



これでクライアントから接続が行われた時、mysql_native_passwordで認証してくれます。



サンプルの実行



再度PDO接続するサンプルを実行してみます。


$ php sample.php
ok



接続できました!


【参考URL】

MySQL 8.0.4におけるデフォルト認証形式の変更
13.7.1.1 ALTER USER Syntax
Caching SHA-2 (or 256) Pluggable Authentication for MySQL 8
Upgrading to MySQL 8.0 : Default Authentication Plugin Considerations

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

  1. 2018/04/29(日) 15:41:42|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

MySQL 8.0 外部(他の端末)から接続する

Ubuntu 18.04 ServerにMySQL 8.0をインストールしました。
MySQL 8.0をUbuntu Server 18.04へインストール

他の端末からこのデータベースに接続できるようにしてみます。

ユーザーの作成



別の端末からtelnetしてみると、応答はありますがすぐに切断されます。


$ telnet 192.168.1.101 3306
Trying 192.168.1.101...
Connected to 192.168.1.101.
Escape character is '^]'.
Host '192.168.1.4' is not allowed to connect to this MySQL serverConnection closed by foreign host.




これは、外部からの接続を許可されたユーザーが存在しないからです。


mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host     |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)




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

こんなユーザーを作ってみます。
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.12 sec)

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




これで別の端末から接続できるようになりました。


$ mysql -u admin -h 192.168.1.101 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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>




【参考URL】

MySQLに外部ホストから接続できるように設定する
ユーザ権限の確認・追加

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

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

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

MySQL 8.0をUbuntu Server 18.04へaptでインストールしてみます。
16.04での手順を参考にしました。
MySQL 8.0をUbuntu Server 16.04へインストール


リポジトリの追加とインストール



こちらを参考に作業を進めます。
A Quick Guide to Using the MySQL APT Repository

まず、MySQLのリポジトリをaptに追加するためのdebパッケージをダウンロードしてインストールします。
Download MySQL APT Repository


$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb
$ sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb



パッケージのインストール中設定画面が表示されます。
矢印キーで「Ok」を選択。
タブキーを押してカーソルを「<Ok>」に移動し、エンターキーを押下します。

867_01.png


リポジトリの追加が終わったら、update & install


$ sudo apt update
$ sudo apt install mysql-server



インストール中、確認画面が表示されます。
パスワードの入力と確認のための再入力。

867_02.png

867_03.png

パスワード強度設定の選択。
デフォルトの「Use Strong Password Encription」を選択しました。

867_04.png

867_05.png

これでインストール完了です。
mysqlコマンドで接続してみます。


$ mysql -uroot -p
Enter password: [MySQLインストール時に指定したパスワード]
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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 8.0.11がインストールできたようです。



データベース、テーブルの作成



データベースやテーブルを作成してみます。


mysql> CREATE DATABASE sample CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.17 sec)

mysql> use sample;
Database changed
mysql> create table t (id int, value text);
Query OK, 0 rows affected (0.50 sec)

mysql> insert into t (id, value) values (1, '登録テスト');
Query OK, 1 row affected (0.17 sec)

mysql> select * from t;
+------+-----------------+
| id | value         |
+------+-----------------+
|    1 | 登録テスト     |
+------+-----------------+
1 row in set (0.00 sec)

mysql>



いい感じです。

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

  1. 2018/04/29(日) 14:06:29|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

MySQL 8.0の新機能(ウィンドウ関数,JSON_TABLE,JSON_ARRAYAGG,JSON_OBJECTAGG)

MySQL 8.0をインストールしました。
MySQL 8.0をUbuntu Server 16.04へインストール

気になる新機能を試してみます。


ウィンドウ関数



やっとウィンドウ関数が実装されました。集計クエリが書きやすくなりますね。
こちらを参考に動作を確認してみます。
MySQL 8.0.2: Introducing Window Functions


サンプルのテーブルを作成し、データを登録します。


CREATE TABLE sales(employee VARCHAR(50), `date` DATE, sale INT);

INSERT INTO sales VALUES
('odin', '2017-03-01', 200),
('odin', '2017-04-01', 300),
('odin', '2017-05-01', 400),
('thor', '2017-03-01', 400),
('thor', '2017-04-01', 300),
('thor', '2017-05-01', 500);




「odin」「thor」各々のsale値の合計を取得したい。


mysql> SELECT employee, SUM(sale) FROM sales GROUP BY employee;
+----------+-----------+
| employee | SUM(sale) |
+----------+-----------+
| odin     |     900 |
| thor     |     1200 |
+----------+-----------+
2 rows in set (0.00 sec)




顧客名毎に日付の昇順で表示したい。


SELECT employee, date, sale, SUM(sale) OVER (PARTITION BY employee) AS sum FROM sales;
+----------+------------+------+------+
| employee | date     | sale | sum |
+----------+------------+------+------+
| odin     | 2017-03-01 | 200 | 900 |
| odin     | 2017-04-01 | 300 | 900 |
| odin     | 2017-05-01 | 400 | 900 |
| thor     | 2017-03-01 | 400 | 1200 |
| thor     | 2017-04-01 | 300 | 1200 |
| thor     | 2017-05-01 | 500 | 1200 |
+----------+------------+------+------+
6 rows in set (0.00 sec)




「OVER」と「PARTITION BY」、集計レポートを作成している人は待ち望んでいた機能だと思います。





JSON_TABLE



JSON Table Functions

「JSON_TABLE」でjsonな文字列をテーブルとして扱えるようになります。


SELECT * FROM JSON_TABLE(
    '[{"id":1, "value":"val-1"}, {"id":2, "value":"val-2"}, {"id":3, "value":"val-3"}]',
    "$[*]"
    COLUMNS(
        id INT PATH "$.id",
        value TEXT PATH "$.value"
    )
)as t1;




実行結果


mysql> SELECT * FROM JSON_TABLE(
    ->     '[{"id":1, "value":"val-1"}, {"id":2, "value":"val-2"}, {"id":3, "value":"val-3"}]',
    ->     "$[*]"
    ->     COLUMNS(
    ->         id INT PATH "$.id",
    ->         value TEXT PATH "$.value"
    ->     )
    -> )as t1;
+------+-------+
| id | value |
+------+-------+
|    1 | val-1 |
|    2 | val-2 |
|    3 | val-3 |
+------+-------+
3 rows in set (0.00 sec)




もちろん、既存のテーブルと結合も可能です。
こんなテーブルを作成。


mysql> create table t (id int, name text);
Query OK, 0 rows affected (0.50 sec)

mysql> insert into t values (1,'a'), (2,'b');
Query OK, 2 rows affected (0.13 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from t;
+------+------+
| id | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)




JSON_TABLEで作成した仮想表と結合してみます。


SELECT * FROM JSON_TABLE(
    '[{"id":1, "value":"val-1"}, {"id":2, "value":"val-2"}, {"id":3, "value":"val-3"}]',
    "$[*]"
    COLUMNS(
        id INT PATH "$.id",
        value TEXT PATH "$.value"
    )
)as t1
LEFT OUTER JOIN
t
ON
t1.id = t.id;




実行結果


mysql> SELECT * FROM JSON_TABLE(
    ->     '[{"id":1, "value":"val-1"}, {"id":2, "value":"val-2"}, {"id":3, "value":"val-3"}]',
    ->     "$[*]"
    ->     COLUMNS(
    ->         id INT PATH "$.id",
    ->         value TEXT PATH "$.value"
    ->     )
    -> )as t1
    -> LEFT OUTER JOIN
    -> t
    -> ON
    -> t1.id = t.id;
+------+-------+------+------+
| id | value | id | name |
+------+-------+------+------+
|    1 | val-1 |    1 | a    |
|    2 | val-2 |    2 | b    |
|    3 | val-3 | NULL | NULL |
+------+-------+------+------+
3 rows in set (0.00 sec)




MySQL クエリで連続した日付の仮想表を作成する
こちらではがんばって連続した日付の仮想表を作成して集計結果を得ましたが、
JSONで必要な日付分レコードを定義してjoinする方法も使えそうです。




JSON_ARRAYAGG,JSON_OBJECTAGG



複数の行をjsonオブジェクトとして1行にまとめる機能です。
JSON_ARRAYAGG(col_or_expr)
JSON_OBJECTAGG(key, value)

項目名と値を保存しているテーブル「t3」を作成。
値を登録します。


create table t3 (o_id int, attribute text, value text);

insert into t3 values
(2, 'color', 'red'),
(2, 'fabric', 'silk'),
(3, 'color', 'green'),
(3, 'shape', 'square');


select * from t3;
+------+-----------+--------+
| o_id | attribute | value |
+------+-----------+--------+
|    2 | color     | red    |
|    2 | fabric    | silk |
|    3 | color     | green |
|    3 | shape     | square |
+------+-----------+--------+
4 rows in set (0.00 sec)




o_id毎にattributeを1行にまとめて取得したい場合、今までは「GROUP_CONCAT」を
使用することが多かったと思います。


mysql> select o_id, GROUP_CONCAT(attribute) as attribute from t3 group by o_id;
+------+--------------+
| o_id | attribute    |
+------+--------------+
|    2 | color,fabric |
|    3 | color,shape |
+------+--------------+
2 rows in set (0.00 sec)




「JSON_ARRAYAGG」を使用すれば、json形式に変換して値をまとめてくれます。


mysql> select o_id, JSON_ARRAYAGG(attribute) as attribute from t3 group by o_id;
+------+---------------------+
| o_id | attribute         |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"] |
+------+---------------------+
2 rows in set (0.00 sec)




これは便利。
さらに「JSON_OBJECTAGG」を使用すれば、key:valueの形式で2フィールドをまとめてくれます。


mysql> select o_id, JSON_OBJECTAGG(attribute, value) as setting from t3 group by o_id;
+------+---------------------------------------+
| o_id | setting                             |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)



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

  1. 2018/04/22(日) 14:25:06|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

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

MySQL 8.0をUbuntu Server 16.04へaptでインストールしてみます。


リポジトリの追加とインストール



こちらを参考に作業を進めます。
A Quick Guide to Using the MySQL APT Repository

まず、こちらのページからMySQLのリポジトリをaptに追加するためのdebパッケージを
ダウンロードしてインストールします。
※リポジトリの追加のみで、MySQL本体のインストールが行われるわけではない。
Download MySQL APT Repository


$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb
$ sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb



ここでパッケージの設定画面が表示されます。
矢印キーで「Ok」を選択。
タブキーを押してカーソルを「<了解>」に移動し、エンターキーを押下します。

852_01.png


リポジトリの追加が終わったら、update & install


$ sudo apt update
$ sudo apt install mysql-server



インストール中、確認画面が表示されます。
パスワードの指定。

852_02.png

852_03.png


パスワード強度設定の選択。

852_04.png

852_05.png

デフォルトの選択肢をそのまま選びました。
これでインストール完了です。



接続テスト



mysqlコマンドで接続してみます。


$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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 8.0.11がインストールできたようです。




データベース、テーブルの作成



ついでにデータベースやテーブルを作成してみます。


mysql> CREATE DATABASE sample CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.14 sec)

mysql> use sample;
Database changed

mysql> create table t (id int, value text);
Query OK, 0 rows affected (0.43 sec)

mysql> insert into t (id, value) values (1, '登録テスト');
Query OK, 1 row affected (0.16 sec)

mysql> select * from t;
+------+-----------------+
| id | value         |
+------+-----------------+
|    1 | 登録テスト     |
+------+-----------------+
1 row in set (0.01 sec)




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

  1. 2018/04/22(日) 13:23:42|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
次のページ