MySQL8 の履歴(No.10)

• 履歴一覧
• 差分 を表示
• 現在との差分 を表示
• ソース を表示
• MySQL8 へ行く。
  • 1 (2020-07-22 (水) 17:17:36)
  • 2 (2020-07-24 (金) 19:06:47)
  • 3 (2020-09-13 (日) 22:50:40)
  • 4 (2020-10-02 (金) 20:49:55)
  • 5 (2020-10-07 (水) 14:22:16)
  • 6 (2020-10-21 (水) 17:38:10)
  • 7 (2020-10-26 (月) 14:24:44)
  • 8 (2020-10-26 (月) 17:45:28)
  • 9 (2022-04-11 (月) 15:28:00)
  • 10 (2022-06-19 (日) 14:14:43)

---

Tag: MySQL8

目次†

関連ページ†

• ./utf8mb4

参考情報†

設定†

export/importでcollateが異なってしまう†

• 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 テーブル文字セットおよび照合順序
• これを避けるためには、charsettとcollateをcreate tableで明示しておいたほうがよいっぽい。

既存のテーブルのcollateを一括設定†

• mysql - How to convert all tables in database to one collation? - Stack Overflow
• 以下のコマンドでcollateを変更するSQLを生成できる。以下をcollate.sqlとして保存。

  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関連†

• 古いライブラリから認証できない場合がある(PHP 7.3以前。PHP 7.4は対応しているはずだがMacPortsから接続できない)。
• もうしばらく安定するまでmysql_native_passwordに設定しておくほうが良いかも(2020/10/26(月)現在)。
• MySQL8.0 認証方式を変更する(Laravel5) - Qiita

状態の確認†

• 以下のSQLで確認できる

  SELECT user, host, plugin FROM mysql.user;

新規ユーザーの認証方法を変更†

• /etc/my.cnfを変更。

  [mysqld]
  default_authentication_plugin=mysql_native_password

既存ユーザーの認証方法を変更†

• 以下のSQLで実行。

  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pass';

バイナリログを無効化する†

• MySQL8ではバイナリログがデフォルトで有効になっている。
• 開発マシンでバイナリログが不要な場合、以下の手順で無効化できる。MySQL8.0でバイナリログを無効化する | Tiny Garage
• バイナリログ確認。mysqlコマンドで接続し以下のSQLを実行。

  SHOW MASTER LOGS;

• バイナリログ削除。

  PURGE MASTER LOGS before now();

• my.cnfに以下を追加しサーバー再起動。

  disable-log-bin

トラブルシューティング†

「grant all privileges」でエラー†

• MySQL 8ではcreate userしてからgrantしないとだめらしい。
• How to grant all privileges to root user in MySQL 8.0 - Stack Overflow

  mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root';
  mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
  

unknown variable 'validate_password.policy=LOW'が表示される†

• どうやらvalidate_password componentが有効でないとこのエラーが表示されるらしい。MySQL :: MySQL 8.0 Reference Manual :: 6.4.3.3 Transitioning to the Password Validation Component
• 以下のコマンドで確認できる。MySQL8.0 小ネタ集 | スマートスタイル TECH BLOG｜データベース＆クラウドの最新技術情報を配信

  SELECT * FROM mysql.component;

• CentOS 8ではデフォルトでvalidate_passwordコンポーネントが有効だが、macportsでは無効?(要確認)。

リモートから接続できない†

• skip-networking、bind-addressの設定を確認する。以下はリモートアクセスを許可する設定。

  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†

• mysqldumpでPROCESS権限(PROCESS privilege)を要求される - いっさいがっさい
• 以下を実行。

  mysql > GRANT PROCESS ON *.* TO "your-user";

PHP 7.3から接続できない†

• 「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開発
• PHP 7.4なら大丈夫？と思ったがPHP 7.4からも同様のエラーで接続できない場合あり(MacPorts。2020/10/26(月))。
  • MacPortsはだめ
  • UbuntuはOK
• 面倒をさけるためしばらくmysql_native_passwordをデフォルトにしておいたほうが良いかも。実際CentOS 8.2.2004のデフォルトはmysql_native_password。