MySQL8 の変更点

• 追加された行はこの色です。
• 削除された行はこの色です。
• MySQL8 へ行く。
• MySQL8 の差分を削除

#author("2022-06-22T07:24:42+00:00","default:src128","src128")
#author("2022-06-22T07:25:00+00:00","default:src128","src128")
&tag(MySQL8);
*目次 [#n451abf1]
#contents

*関連ページ [#o3976052]
-[[./utf8mb4]]

*参考情報 [#fc93aae4]

*設定 [#d805ca20]


**export/importでcollateが異なってしまう [#r7f695c4]
-mysqldumpによるexport時、create tableのcharsetがutf8mb4で、collateが指定していない場合、
-import時にcollateがutf8mb4_0900_ai_ciになってしまう(collation-serverがutf8mb4_binでも)。
-これはcharsetだけ指定されている場合、そのcharsetのデフォルトのcollateが指定されるというmysqlの仕様らしい。[[MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.3.3 テーブル文字セットおよび照合順序:https://dev.mysql.com/doc/refman/5.6/ja/charset-table.html]]
-これを避けるためには、charsettとcollateをcreate tableで明示しておいたほうがよいっぽい。

**既存のテーブルのcollateを一括設定 [#zd9a2c93]
-[[mysql - How to convert all tables in database to one collation? - Stack Overflow:https://stackoverflow.com/questions/10859966/how-to-convert-all-tables-in-database-to-one-collation/10860122#10860122]]
-以下のコマンドでcollateを変更するSQLを生成できる。以下をcollate.sqlとして保存。
#pre{{
SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE utf8mb4_bin;") AS ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="mydb"
AND TABLE_TYPE="BASE TABLE";
}}
-以下のように実行してファイルに保存。
 mysql --skip-column-names < collate.sql > collate_change.sql
-collate_change.sqlの内容を確認。。
 ALTER TABLE mydb.test_table COLLATE utf8mb4_bin;
 (...)
-実行。環境によって少し時間が必要かも。
 mysql < collate_change.sql

**caching_sha2_password関連 [#e4f398d7]
-古いライブラリから認証できない場合がある(PHP 7.3以前。PHP 7.4は対応しているはずだがMacPortsから接続できない)。
-もうしばらく安定するまでmysql_native_passwordに設定しておくほうが良いかも(2020/10/26(月)現在)。
-[[MySQL8.0 認証方式を変更する(Laravel5) - Qiita:https://qiita.com/ucan-lab/items/3ae911b7e13287a5b917]]

***状態の確認 [#abc97b85]
-以下のSQLで確認できる
 SELECT user, host, plugin FROM mysql.user;

***新規ユーザーの認証方法を変更 [#y8f706be]
-/etc/my.cnfを変更。
 [mysqld]
 default_authentication_plugin=mysql_native_password

***既存ユーザーの認証方法を変更 [#ofaece70]
-以下のSQLで実行。
 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pass';

**バイナリログを無効化する [#c82f6c12]
-MySQL8ではバイナリログがデフォルトで有効になっている。
-開発マシンでバイナリログが不要な場合、以下の手順で無効化できる。[[MySQL8.0でバイナリログを無効化する | Tiny Garage:https://tiny-garage.net/disable-binary-log-in-mysql8/]]
-バイナリログ確認。mysqlコマンドで接続し以下のSQLを実行。
 SHOW MASTER LOGS;
-バイナリログ削除。
 PURGE MASTER LOGS before now();
-my.cnfに以下を追加しサーバー再起動。
 disable-log-bin

*トラブルシューティング [#e0e84702]

**MySQLアップグレードで起動しなくなった [#sb76f324]
-MySQL 8のマイナーバージョンアップで以下のエラーが発生。
#pre{{
2022-06-22T05:20:50.904777Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Copyright (c) 2018, 2021, Oracle and/or its affiliates. -- -- This program is free software; you can redistribute it and/or modify -- it under the terms of the GNU General Public License as published by -- the Free Software Foundation; version 2 of the License. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the -- GNU General Public License for more details. -- -- You should have received a copy of the GNU General Public License -- along with this program; if not, write to the Free Software -- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -- -- Alters the sys_config table for upgrades -- ALTER TABLE sys_config CHARACTER SET utf8mb4; ' failed with error code = 3958, error message = 'Failed to delete sdi for sys.sys_config in sys/sys_config due to missing record.'.
2022-06-22T05:20:50.907978Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
}}
-MySQL 8はアップグレード時にそれ自体でアップグレード処理を行う(mysql_upgradeは使われなくなった)。[[MySQL :: MySQL 8.0 Reference Manual :: 4.4.5 mysql_upgrade — Check and Upgrade MySQL Tables:https://dev.mysql.com/doc/refman/8.0/en/mysql-upgrade.html]]
-以下の方法で手動で起動できた。「--upgrade=FORCE」がデフォルト。MINIMALは最小限でアップグレード処理を実行しない。
　/opt/local/lib/mysql8/bin/mysqld --user=_mysql --upgrade=MINIMAL
 /opt/local/lib/mysql8/bin/mysqld --user=_mysql --upgrade=MINIMAL
-原因はわからいけれどsysデータベースがエラーだということで、他のマシンのmysqlサーバー(同じバージョン8.0.29)からsysデータベースをdumpして、restoreした。
 $ mysqldump --no-data --routines --databases sys > sys_dump.sql #別のマシン
 $ mysql < sys_dump.sql # エラーが発生したマシン

**「grant all privileges」でエラー [#l935e464]
-MySQL 8ではcreate userしてからgrantしないとだめらしい。
-[[How to grant all privileges to root user in MySQL 8.0 - Stack Overflow:https://stackoverflow.com/questions/50177216/how-to-grant-all-privileges-to-root-user-in-mysql-8-0]]
#pre{{
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
}}

**unknown variable 'validate_password.policy=LOW'が表示される [#t420709a]
-どうやらvalidate_password componentが有効でないとこのエラーが表示されるらしい。[[MySQL :: MySQL 8.0 Reference Manual :: 6.4.3.3 Transitioning to the Password Validation Component:https://dev.mysql.com/doc/refman/8.0/en/validate-password-transitioning.html]]
-以下のコマンドで確認できる。[[MySQL8.0 小ネタ集 | スマートスタイル TECH BLOG｜データベース＆クラウドの最新技術情報を配信:https://www.s-style.co.jp/blog/2018/07/2106/]]
 SELECT * FROM mysql.component;
-CentOS 8ではデフォルトでvalidate_passwordコンポーネントが有効だが、macportsでは無効?(要確認)。


**リモートから接続できない [#l5d6c2e6]
-skip-networking、bind-addressの設定を確認する。以下はリモートアクセスを許可する設定。
#pre{{
bind-address=0.0.0.0
skip-networking=0
}}
-現在の設定はこれで確認できる。
 $ mysql -b -e "SHOW GLOBAL VARIABLES like 'bind_address'"
-それでもだめな場合ユーザー(rootなど)のアクセス許可がlocalhostに限定されているせいかもしれない。
 select user,host from mysql.user;
 | root             | localhost |
-以下で作成する。 
 create user root@'%' identified by 'hogehoge';
 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
**mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces [#he9ca9a4]
-[[mysqldumpでPROCESS権限(PROCESS privilege)を要求される - いっさいがっさい:https://isgs-lab.com/424/]]
-以下を実行。
 mysql > GRANT PROCESS ON *.* TO "your-user";

**PHP 7.3から接続できない [#u3e3a5a0]
-「PHP Warning:  mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to \
the client」エラーが発生。
-PHP 7.2以前のバージョンは、MySQL8の新しいにんしょうほうcaching_sha2_passwordに対応しておらず、mysql_native_password
-[[【PHP】いつの間にやらPDOはMySQLの認証方法 caching_sha2_password に対応済み – 株式会社シーポイントラボ ｜ 浜松のシステム・RTK-GNSS開発:https://cpoint-lab.co.jp/article/202004/14783/]]
-PHP 7.4なら大丈夫？と思ったがPHP 7.4からも同様のエラーで接続できない場合あり(MacPorts。2020/10/26(月))。
--MacPortsはだめ
--UbuntuはOK
-面倒をさけるためしばらくmysql_native_passwordをデフォルトにしておいたほうが良いかも。実際CentOS 8.2.2004のデフォルトはmysql_native_password。