MySQL 5.7 から 8 へのインプレースアップグレードをやってみる

tl;dr

• インプレースアップグレードっていうと AWS の便利機能としてばかり取り沙汰されて^[1]でどういうことなのかあんまり意識してなかったのでやってみた
• インプレースアップグレードするとテーブルの create time がアップグレードしたタイミングになる
  • update time は NULL になる
• MySQL 5.7 から 8 への変更点として utf8mb4 の default collation が 'utf8mb4_general_ci' から 'utf8mb4_0900_ai_ci' になったけど、インプレースアップグレードでは当然ながら既存のデータベース、テーブル、カラムの collation はかわらない

インプレースアップグレードを試したきっかけ

MySQL のドキュメント記載のとおり、 MySQL アップグレード方法として「インプレースアップグレード」と「論理アップグレード」が存在する
https://dev.mysql.com/doc/refman/8.0/ja/upgrade-binary-package.html

• インプレースアップグレード
• 論理アップグレード

• インプレースアップグレードは、前のバージョンのデータディレクトリを使って MySQL のバイナリを新たなバージョンのものに更新して起動することで、新バージョン側(8.0.16 以前までは mysql_upgrade が必要)がデータベースをアップグレードするもの
• 論理アップグレードは、mysqlpump/mysqldump でデータベースの論理バックアップを取得して、新たな環境にインポートするもの

このようにインプレースアップグレードと論理アップグレードでは、データの作られかたが違うので、データベースのメタ情報が違ってくる。
これが MySQL8 での utf8mb4 の default collation の変更によって実用的な差異としてあらわれてくることになる。

特にアプリの開発環境では、データベースサーバを docker 環境に構築している DB サイズが小さい、開発中にダンプリストアすることも多い、などからインプレースアップグレードについて知るきっかけがなく、データベースの復元の延長での論理アップグレード = verup と思ってしまっていたので、試してみた。

アップグレードを試した経過

環境

• WSL の Ubuntu 18.04
  • wsl --install ubuntu-18.04
• MySQL は変更前後のいずれもパッケージ(apt)からインストールするのではなく、バイナリ形式で配布されているプログラムを利用
• 次の構造のディレクトリのそれぞれにバイナリを展開し MySQL 5.7 でデータを作って、これを MySQL 8 にコピーして起動させる
  • /work
    • /mysql57
    • /mysql8

バイナリの取得〜 MySQL の起動確認まで

• バイナリの取得

~$ sudo mkdir /work
~$ cd /work
/work$ sudo chmod 777 /work
/work$ wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.40-linux-glibc2.12-x86_64.tar
--2023-02-01 15:08:54--  https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.40-linux-glibc2.12-x86_64.tar
/work$ wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
--2023-02-01 15:16:19--  https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
/work$ tar -xvf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz > /dev/null
/work$ tar -zxvf mysql-5.7.40-linux-glibc2.12-x86_64.tar.gz > /dev/null
/work$ mv mysql-5.7.40-linux-glibc2.12-x86_64 mysql57
/work$ mv mysql-8.0.32-linux-glibc2.12-x86_64 mysql8

• ドキュメントに従って MySQL がそれぞれ起動できるようにする

• まずはユーザ、グループの設定

/work$ sudo groupadd mysql
/work$ sudo useradd -r -g mysql mysql
/work$ sudo chown -R mysql mysql57
/work$ sudo chgrp -R mysql mysql57
/work$ sudo chown -R mysql mysql8
/work$ sudo chgrp -R mysql mysql8

• ひとまず MySQL 5.7 の起動を試す
  • libaio がなかったので途中でインストール
  • data ディレクトリを指定・配置していなかったので途中で用意

/work$ cd mysql57
/work/mysql57$ sudo mkdir mysql-files
/work/mysql57$ sudo chown mysql:mysql mysql-files
/work/mysql57$ sudo chmod 750 mysql-files
/work/mysql57$ sudo bin/mysqld --initialize --user=mysql
bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
# ^ libaio がないのでインストール
/work/mysql57$ sudo apt-get install libaio1 libaio-dev -y
#... snip ...
/work/mysql57$ sudo bin/mysqld --initialize --user=mysql
mysqld: Can't create directory '/usr/local/mysql/data/' (Errcode: 2 - No such file or directory)
2023-02-01T06:47:39.762508Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-02-01T06:47:39.763101Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2023-02-01T06:47:39.763588Z 0 [ERROR] Aborting
# デフォルトのパスに data を作ろうとしているので、配下に data を作って、起動オプションで指定
/work/mysql57$ sudo mkdir data
/work/mysql57$ sudo chown mysql:mysql data
/work/mysql57$ sudo bin/mysqld --initialize --user=mysql --datadir=/work/mysql57/data --basedir=/work/mysql57
2023-02-01T06:52:07.454677Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-02-01T06:52:07.998958Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-02-01T06:52:08.089510Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-02-01T06:52:08.104617Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: f1ed51c4-a1fc-11ed-b83f-00155da69af3.
2023-02-01T06:52:08.110321Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-02-01T06:52:08.357699Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-02-01T06:52:08.357736Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-02-01T06:52:08.358410Z 0 [Warning] CA certificate ca.pem is self signed.
2023-02-01T06:52:08.450452Z 1 [Note] A temporary password is generated for root@localhost: **{成功すると初期パスワードが出力される}**

• ログインして root のパスワードを設定

/work/mysql57$ sudo bin/mysqld_safe --user=mysql --datadir=/work/mysql57/data --basedir=/work/mysql57 --lc-messages-dir=/work/mysql57/share &
/work/mysql57$ bin/mysql -uroot -p************
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.40

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> alter user root@localhost identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
/work/mysql57$ bin/mysql -uroot -ppassword
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> exit
Bye
/work/mysql57$ sudo bin/mysqladmin shutdown -uroot -ppassword
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
2023-02-01T07:17:32.648070Z mysqld_safe mysqld from pid file /work/mysql57/data/RATON.pid ended
[1]+  Done                    sudo bin/mysqld_safe --user=mysql --datadir=/work/mysql57/data --basedir=/work/mysql57 --lc-messages-dir=/work/mysql57/share
/work/mysql57$ cd /work

• 今作ったデータディレクトリを使って MySQL8 をアップグレードして起動できるか確認
  • 8.0.16 からはユーザが mysql_upgrade を別途実行する必要がない^[2]

/work$ sudo cp -r mysql57/data mysql8/data
/work$ cd mysql8
/work/mysql8$ sudo chown -R mysql:mysql data
/work/mysql8$ sudo mkdir mysql-files
/work/mysql8$ sudo chown mysql:mysql mysql-files
/work/mysql8$ sudo chmod 750 mysql-files
/work/mysql8$ sudo bin/mysqld_safe --user=mysql --datadir=/work/mysql8/data --basedir=/work/mysql8 --lc-messages-dir=/work/mysql8/share &

• アップグレード状況を見るためログを確認
  • 最初の空行までが 5.7 のログ

2023-02-01T07:17:32.508016Z 0 [Note] Shutting down plugin 'binlog'
2023-02-01T07:17:32.508456Z 0 [Note] /work/mysql57/bin/mysqld: Shutdown complete

2023-02-01T07:37:32.981676Z 0 [System] [MY-010116] [Server] /work/mysql8/bin/mysqld (mysqld 8.0.32) starting as process 1631
2023-02-01T07:37:33.011801Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023-02-01T07:37:33.011898Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-02-01T07:37:33.966373Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-02-01T07:37:36.304288Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2023-02-01T07:37:37.907745Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80032' started.
2023-02-01T07:37:45.511613Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80032' completed.
2023-02-01T07:37:45.693491Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-02-01T07:37:45.693520Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-02-01T07:37:45.705432Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/work' in the path is accessible to all OS users. Consider choosing a different directory.
2023-02-01T07:37:45.719310Z 0 [System] [MY-010931] [Server] /work/mysql8/bin/mysqld: ready for connections. Version: '8.0.32'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2023-02-01T07:37:45.719291Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock

• 起動とアップグレードを見ることができたので一旦 data を削除する
  • 空のデータベースではあるが「インプレースアップグレード」ができたことになる

/work/mysql8$ bin/mysqladmin -uroot -ppassword shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
2023-02-01T07:44:58.201449Z mysqld_safe mysqld from pid file /work/mysql8/data/RATON.pid ended
[1]+  Done                    sudo bin/mysqld_safe --user=mysql --datadir=/work/mysql8/data --basedir=/work/mysql8 --lc-messages-dir=/work/mysql8/share
/work/mysql8$ sudo rm -rf data

MySQL 5.7 で動作確認のためのテーブルを作る

• DB の起動やシャットダウンのコマンドは割愛
• データベースを作成し character set および collate の指定をそれぞれ変える
  • d_1: character set 指定なし
  • d_2: character set のみ指定
  • d_3: collate も指定
  • d1_: 5.7 でテーブルを作る
  • d2_: 5.7 ではデータベースのみ作り、8 でテーブルを作る
  • d3_: 8 でデータベースとテーブルを作る
• テーブル(test)を d1_ に作成
  • テーブルおよびカラムには character set や collate は付けていない

mysql> create database d11;
Query OK, 1 row affected (0.00 sec)

mysql> create database d21;
Query OK, 1 row affected (0.00 sec)

mysql> create database d12 default character set utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> create database d22 default character set utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> create database d13 default character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> create database d23 default character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> create table d11.test(c1 int not null auto_increment primary key, c2 varchar(64) not null default '', c3 nvarchar(64) not null default '');
Query OK, 0 rows affected (0.01 sec)

mysql> create table d12.test(c1 int not null auto_increment primary key, c2 varchar(64) not null default '', c3 nvarchar
(64) not null default '');
Query OK, 0 rows affected (0.02 sec)

mysql> create table d13.test(c1 int not null auto_increment primary key, c2 varchar(64) not null default '', c3 nvarchar
(64) not null default '');
Query OK, 0 rows affected (0.02 sec)

• information_schema でデータベース、テーブル、カラムの情報を確認

# データベース
mysql> select * from information_schema.schemata where schema_name like 'd__';
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def          | d11         | latin1                     | latin1_swedish_ci      | NULL     |
| def          | d12         | utf8mb4                    | utf8mb4_general_ci     | NULL     |
| def          | d13         | utf8mb4                    | utf8mb4_general_ci     | NULL     |
| def          | d21         | latin1                     | latin1_swedish_ci      | NULL     |
| def          | d22         | utf8mb4                    | utf8mb4_general_ci     | NULL     |
| def          | d23         | utf8mb4                    | utf8mb4_general_ci     | NULL     |
+--------------+-------------+----------------------------+------------------------+----------+
6 rows in set (0.00 sec)

# テーブル
mysql> select table_schema, table_name, create_time, update_time, table_collation from information_schema.tables where table_schema like 'd__';
+--------------+------------+---------------------+-------------+--------------------+
| table_schema | table_name | create_time         | update_time | table_collation    |
+--------------+------------+---------------------+-------------+--------------------+
| d11          | test       | 2023-02-01 17:13:59 | NULL        | latin1_swedish_ci  |
| d12          | test       | 2023-02-01 17:14:08 | NULL        | utf8mb4_general_ci |
| d13          | test       | 2023-02-01 17:14:13 | NULL        | utf8mb4_general_ci |
+--------------+------------+---------------------+-------------+--------------------+
3 rows in set (0.00 sec)

# カラム
mysql> select table_schema, table_name, column_name, collation_name from information_schema.columns where table_name = '
test';
+--------------+------------+-------------+--------------------+
| table_schema | table_name | column_name | collation_name     |
+--------------+------------+-------------+--------------------+
| d11          | test       | c1          | NULL               |
| d11          | test       | c2          | latin1_swedish_ci  |
| d11          | test       | c3          | utf8_general_ci    |
| d12          | test       | c1          | NULL               |
| d12          | test       | c2          | utf8mb4_general_ci |
| d12          | test       | c3          | utf8_general_ci    |
| d13          | test       | c1          | NULL               |
| d13          | test       | c2          | utf8mb4_general_ci |
| d13          | test       | c3          | utf8_general_ci    |
+--------------+------------+-------------+--------------------+
9 rows in set (0.00 sec)

mysql> show variables like 'character_set_%';
+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| character_set_client     | utf8                          |
| character_set_connection | utf8                          |
| character_set_database   | utf8                          |
| character_set_filesystem | binary                        |
| character_set_results    | utf8                          |
| character_set_server     | latin1                        |
| character_set_system     | utf8                          |
| character_sets_dir       | /work/mysql57/share/charsets/ |
+--------------------------+-------------------------------+
8 rows in set (0.01 sec)

mysql> show variables like 'collation_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'character_set_%';
+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| character_set_client     | utf8mb4                       |
| character_set_connection | utf8mb4                       |
| character_set_database   | utf8                          |
| character_set_filesystem | binary                        |
| character_set_results    | utf8mb4                       |
| character_set_server     | latin1                        |
| character_set_system     | utf8                          |
| character_sets_dir       | /work/mysql57/share/charsets/ |
+--------------------------+-------------------------------+
8 rows in set (0.00 sec)

mysql> show variables like 'collation_%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8_general_ci    |
| collation_server     | latin1_swedish_ci  |
+----------------------+--------------------+
3 rows in set (0.00 sec)

作成したデータベースのアップグレード適合性をチェック

/work$ https://dev.mysql.com/get/mysql-apt-config_0.8.24-1_all.deb
# ...snip...
/work$ sudo dpkg -i mysql-apt-config_0.8.24-1_all.deb
# ...snip...
/work$ sudo apt-get update
# ...snip...
/work$ sudo apt-get install mysql-shell -y
# ...snip...
/work$ mysqlsh --version
mysqlsh   Ver 8.0.32 for Linux on x86_64 - for MySQL 8.0.32 (MySQL Community Server (GPL))
/work/mysql57$ mysqlsh root:password@localhost:3306 -e "util.checkForServerUpgrade();"
WARNING: Using a password on the command line interface can be insecure.
The MySQL server at localhost:3306, version 5.7.40 - MySQL Community Server
(GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.32...

1) Usage of old temporal type
  No issues found

2) MySQL 8.0 syntax check for routine-like objects
  No issues found

3) Usage of db objects with names conflicting with new reserved keywords
  No issues found

4) Usage of utf8mb3 charset
  Warning: The following objects use the utf8mb3 character set. It is
    recommended to convert them to use utf8mb4 instead, for improved Unicode
    support.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html

  d11.test.c3 - column's default character set: utf8
  d12.test.c3 - column's default character set: utf8
  d13.test.c3 - column's default character set: utf8

5) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

6) Partitioned tables using engines with non native partitioning
  No issues found

7) Foreign key constraint names longer than 64 characters
  No issues found

8) Usage of obsolete MAXDB sql_mode flag
  No issues found

9) Usage of obsolete sql_mode flags
  Notice: The following DB objects have obsolete options persisted for
    sql_mode, which will be cleared during upgrade to 8.0.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals

  global system variable sql_mode - defined using obsolete NO_AUTO_CREATE_USER
    option

10) ENUM/SET column definitions containing elements longer than 255 characters
  No issues found

11) Usage of partitioned tables in shared tablespaces
  No issues found

12) Circular directory references in tablespace data file paths
  No issues found

13) Usage of removed functions
  No issues found

14) Usage of removed GROUP BY ASC/DESC syntax
  No issues found

15) Removed system variables for error logging to the system log configuration
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging

16) Removed system variables
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

17) System variables with new default values
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

18) Zero Date, Datetime, and Timestamp values
  No issues found

19) Schema inconsistencies resulting from file removal or corruption
  No issues found

20) Tables recognized by InnoDB that belong to a different engine
  No issues found

21) Issues reported by 'check table x for upgrade' command
  No issues found

22) New default authentication plugin considerations
  Warning: The new default authentication plugin 'caching_sha2_password' offers
    more secure password hashing than previously used 'mysql_native_password'
    (and consequent improved client connection authentication). However, it also
    has compatibility implications that may affect existing MySQL installations.
    If your MySQL installation must serve pre-8.0 clients and you encounter
    compatibility issues after upgrading, the simplest way to address those
    issues is to reconfigure the server to revert to the previous default
    authentication plugin (mysql_native_password). For example, use these lines
    in the server option file:

    [mysqld]
    default_authentication_plugin=mysql_native_password

    However, the setting should be viewed as temporary, not as a long term or
    permanent solution, because it causes new accounts created with the setting
    in effect to forego the improved authentication security.
    If you are using replication please take time to understand how the
    authentication plugin changes may impact you.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication

23) Columns which cannot have default values
  No issues found

24) Check for invalid table names and schema names used in 5.7
  No issues found

25) Check for orphaned routines in 5.7
  No issues found

26) Check for deprecated usage of single dollar signs in object names
  No issues found

Errors:   0
Warnings: 13
Notices:  1

NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.

作成したデータベースを MySQL 8 にインプレースアップグレード

• ログ
  • 空データベースと比べて特に差異はない(ここでは表示させていませんが、数十 MB 程度の開発環境データをインポート済み)

2023-02-01T13:25:29.634693Z 0 [System] [MY-010116] [Server] /work/mysql8/bin/mysqld (mysqld 8.0.32) starting as process 3759
2023-02-01T13:25:29.657202Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023-02-01T13:25:29.657912Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-02-01T13:25:30.343302Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-02-01T13:25:41.463870Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2023-02-01T13:25:44.780060Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80032' started.
2023-02-01T13:25:53.821008Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80032' completed.
2023-02-01T13:25:53.933868Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-02-01T13:25:53.933902Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-02-01T13:25:53.946249Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/work' in the path is accessible to all OS users. Consider choosing a different directory.
2023-02-01T13:25:53.960394Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2023-02-01T13:25:53.960456Z 0 [System] [MY-010931] [Server] /work/mysql8/bin/mysqld: ready for connections. Version: '8.0.32'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server -

差分の動作確認のためデータベースとテーブルを追加で作成

• データベース d2_ にテーブルを作る
• データベース d3_ を作成し、テーブルを作る

mysql> create table d21.test(c1 int not null auto_increment primary key, c2 varchar(64) not null default '', c3 nvarchar
(64) not null default '');
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> create table d22.test(c1 int not null auto_increment primary key, c2 varchar(64) not null default '', c3 nvarchar
(64) not null default '');
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> create table d23.test(c1 int not null auto_increment primary key, c2 varchar(64) not null default '', c3 nvarchar
(64) not null default '');
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> create database d31;
Query OK, 1 row affected (0.01 sec)

mysql> create database d32 default character set utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> create database d33 default character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.01 sec)

mysql> create table d31.test(c1 int not null auto_increment primary key, c2 varchar(64) not null default '', c3 nvarchar
(64) not null default '');
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> create table d32.test(c1 int not null auto_increment primary key, c2 varchar(64) not null default '', c3 nvarchar
(64) not null default '');
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> create table d33.test(c1 int not null auto_increment primary key, c2 varchar(64) not null default '', c3 nvarchar
(64) not null default '');
Query OK, 0 rows affected, 1 warning (0.03 sec)

テーブルの状態を確認

# データベース
mysql> select * from information_schema.schemata where schema_name like 'd__';
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| def          | d11         | latin1                     | latin1_swedish_ci      |     NULL | NO                 |
| def          | d12         | utf8mb4                    | utf8mb4_general_ci     |     NULL | NO                 |
| def          | d13         | utf8mb4                    | utf8mb4_general_ci     |     NULL | NO                 |
| def          | d21         | latin1                     | latin1_swedish_ci      |     NULL | NO                 |
| def          | d22         | utf8mb4                    | utf8mb4_general_ci     |     NULL | NO                 |
| def          | d23         | utf8mb4                    | utf8mb4_general_ci     |     NULL | NO                 |
| def          | d31         | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | d32         | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | d33         | utf8mb4                    | utf8mb4_general_ci     |     NULL | NO                 |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
9 rows in set (0.00 sec)

# テーブル
mysql> select table_schema, table_name, table_collation, create_time, update_time from information_schema.tables where table_name = 'test';
+--------------+------------+--------------------+---------------------+-------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION    | CREATE_TIME         | UPDATE_TIME |
+--------------+------------+--------------------+---------------------+-------------+
| d11          | test       | latin1_swedish_ci  | 2023-02-01 22:25:31 | NULL        |
| d12          | test       | utf8mb4_general_ci | 2023-02-01 22:25:31 | NULL        |
| d13          | test       | utf8mb4_general_ci | 2023-02-01 22:25:31 | NULL        |
| d21          | test       | latin1_swedish_ci  | 2023-02-01 22:37:42 | NULL        |
| d22          | test       | utf8mb4_general_ci | 2023-02-01 22:37:47 | NULL        |
| d23          | test       | utf8mb4_general_ci | 2023-02-01 22:37:52 | NULL        |
| d31          | test       | utf8mb4_0900_ai_ci | 2023-02-01 22:40:04 | NULL        |
| d32          | test       | utf8mb4_0900_ai_ci | 2023-02-01 22:40:08 | NULL        |
| d33          | test       | utf8mb4_general_ci | 2023-02-01 22:40:18 | NULL        |
+--------------+------------+--------------------+---------------------+-------------+
9 rows in set (0.01 sec)

# カラム
mysql> select table_schema, table_name, column_name, collation_name from information_schema.columns where table_name = 'test';
+--------------+------------+-------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME     |
+--------------+------------+-------------+--------------------+
| d11          | test       | c1          | NULL               |
| d11          | test       | c2          | latin1_swedish_ci  |
| d11          | test       | c3          | utf8mb3_general_ci |
| d12          | test       | c1          | NULL               |
| d12          | test       | c2          | utf8mb4_general_ci |
| d12          | test       | c3          | utf8mb3_general_ci |
| d13          | test       | c1          | NULL               |
| d13          | test       | c2          | utf8mb4_general_ci |
| d13          | test       | c3          | utf8mb3_general_ci |
| d21          | test       | c1          | NULL               |
| d21          | test       | c2          | latin1_swedish_ci  |
| d21          | test       | c3          | utf8mb3_general_ci |
| d22          | test       | c1          | NULL               |
| d22          | test       | c2          | utf8mb4_general_ci |
| d22          | test       | c3          | utf8mb3_general_ci |
| d23          | test       | c1          | NULL               |
| d23          | test       | c2          | utf8mb4_general_ci |
| d23          | test       | c3          | utf8mb3_general_ci |
| d31          | test       | c1          | NULL               |
| d31          | test       | c2          | utf8mb4_0900_ai_ci |
| d31          | test       | c3          | utf8mb3_general_ci |
| d32          | test       | c1          | NULL               |
| d32          | test       | c2          | utf8mb4_0900_ai_ci |
| d32          | test       | c3          | utf8mb3_general_ci |
| d33          | test       | c1          | NULL               |
| d33          | test       | c2          | utf8mb4_general_ci |
| d33          | test       | c3          | utf8mb3_general_ci |
+--------------+------------+-------------+--------------------+
27 rows in set (0.01 sec)

追加確認

MySQL 5.7 で dump したとき utf8mb4_general_ci の collate は省略される

• utf8mb4_general_ci がデフォルトの照合順序のため

/work/mysql57$ sudo bin/mysqlpump -uroot -ppassword > /work/mysql57.dmp
/work/mysql57$ cat /work/mysql57.dmp | grep "CREATE DATABASE.+d\d\d.+"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `d11` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `d12` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `d13` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `d21` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `d22` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `d23` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

MySQL 5.7 での set names の効果

https://dev.mysql.com/doc/refman/5.7/en/set-names.html のはなし

• MySQL 5.7 のデフォルトの文字セットは utf8
• set names utf8mb4 とすることで、下記の文字セットが変更される
  • character_set_client
  • character_set_connection
    • character_set_connction が設定されることで utf8mb4 のデフォルト照合順序である utf8mb4_general_ci が collation_connection に設定される
  • character_set_results

mysql> show variables like 'character_set_%';
+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| character_set_client     | utf8                          |
| character_set_connection | utf8                          |
| character_set_database   | utf8                          |
| character_set_filesystem | binary                        |
| character_set_results    | utf8                          |
| character_set_server     | latin1                        |
| character_set_system     | utf8                          |
| character_sets_dir       | /work/mysql57/share/charsets/ |
+--------------------------+-------------------------------+
8 rows in set (0.01 sec)

mysql> show variables like 'collation_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'character_set_%';
+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| character_set_client     | utf8mb4                       |
| character_set_connection | utf8mb4                       |
| character_set_database   | utf8                          |
| character_set_filesystem | binary                        |
| character_set_results    | utf8mb4                       |
| character_set_server     | latin1                        |
| character_set_system     | utf8                          |
| character_sets_dir       | /work/mysql57/share/charsets/ |
+--------------------------+-------------------------------+
8 rows in set (0.00 sec)

mysql> show variables like 'collation_%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8_general_ci    |
| collation_server     | latin1_swedish_ci  |
+----------------------+--------------------+
3 rows in set (0.00 sec)

MySQL 8 での set names の効果

• MySQL 8 のデフォルトの文字セットは utf8mb4
• set names utf8mb4 とすることで、下記の文字セットが変更されるが、変数が変化しない
  • character_set_client
  • character_set_connection
    • character_set_connction が設定されることで utf8mb4 のデフォルト照合順序である utf8mb4_0900_ai_ci が collation_connection に設定される
  • character_set_results
• set names utf8mb4 collate utf8mb4_general_ci とすることで character_set_connection が utf8mb4_general_ci に変更され、 5.7 に set names をしたのと同じ状態になる
  • ただし character_set_connection はリテラルどうしの比較で参照される設定なので、あまり意味がない。

mysql> show variables like 'character_set_%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | utf8mb4                      |
| character_set_connection | utf8mb4                      |
| character_set_database   | utf8mb4                      |
| character_set_filesystem | binary                       |
| character_set_results    | utf8mb4                      |
| character_set_server     | utf8mb4                      |
| character_set_system     | utf8mb3                      |
| character_sets_dir       | /work/mysql8/share/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.01 sec)

mysql> show variables like 'collation_%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'character_set_%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | utf8mb4                      |
| character_set_connection | utf8mb4                      |
| character_set_database   | utf8mb4                      |
| character_set_filesystem | binary                       |
| character_set_results    | utf8mb4                      |
| character_set_server     | utf8mb4                      |
| character_set_system     | utf8mb3                      |
| character_sets_dir       | /work/mysql8/share/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.01 sec)

mysql> show variables like 'collation_%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

mysql> set names utf8mb4 collate utf8mb4_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'character_set_%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | utf8mb4                      |
| character_set_connection | utf8mb4                      |
| character_set_database   | utf8mb4                      |
| character_set_filesystem | binary                       |
| character_set_results    | utf8mb4                      |
| character_set_server     | utf8mb4                      |
| character_set_system     | utf8mb3                      |
| character_sets_dir       | /work/mysql8/share/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.01 sec)

mysql> show variables like 'collation_%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

MySQL 8 での default_collation_for_utf8mb4 サーバ変数

• 先のとおり MySQL 5.7 の dump では collate utf8mb4_general_ci が保存されないため、MySQL の論理バックアップを MySQL 8 に戻すことが頻繁にあるといった場合には SET [PERSIST] default_collation_for_utf8mb4=utf8mb4_general_ci; をする対応をする必要がある
  • なお SET PERSIST はSYSTEM_VARIABLES_ADMIN もしくは SUPERの特権を必要とする

mysql> SET PERSIST default_collation_for_utf8mb4=utf8mb4_general_ci;
Query OK, 0 rows affected, 1 warning (0.00 sec)
# ↑ がデータの保存先 ↓
/work/mysql8$ sudo cat data/mysqld-auto.cnf
{"Version": 2, "mysql_dynamic_variables": {"default_collation_for_utf8mb4": {"Value": "utf8mb4_general_ci", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1675259252987522}}}}

• 備忘として collation_server を utf8mb4_general_ci とすることは default_collation_for_utf8mb4 の代替にはならない
• create database に文字セットおよび照合順序を指定せず実行したときの character set と collate は、それぞれ character_set_server と collation_server が利用されるが、character set のみが指定された場合 collate はその character set の default が利用されるから
  • collation のデフォルトは SHOW CHARACTER SET の Default collation列や SHOW COLLATION の Default 列が 'Yes' であることで確認可能
  • これは character_set_server と create database で指定された character set が異なる場合を考えると理解できる仕様だが、普段これが一致する状況で使っていて意表を突かれたのでメモ

ラップアップ

• MySQL でのインプレースアップグレードは、前バージョンのデータディレクトリを使い、バイナリを置き換えて MySQL にデータのアップグレードを行ってもらうもの
• インプレースアップグレードをするとテーブルの create time は、アップグレードを実行した時刻になり update time は NULL になる
  • information_schema.tables の作成時刻 ↓ は

    | d11 | test | latin1_swedish_ci | 2023-02-01 22:25:31 | NULL |

  • アップグレード時の ↓ ログの時刻に合致している

    2023-02-01T13:25:29.657202Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.

  • 比較として、論理アップグレードをしてダンプデータをインポートすると create date がインポート時点になるのはもちろん update time も埋まる
  • なお、空のテーブルはダンプのインポートでも update time が NULL になるが、インプレースアップグレード後はデータのあるテーブルでも update time が NULL のまま
• MySQL 5.7 から 8 に論理アップグレードする場合には、何もしないと utf8mb4_general_ci の collate が維持されないので、default collation を明示的に変更するなど注意が必要

脚注

1. "インプレースアップグレード mysql"で検索しても AWS というか Aurora の記事を中心にヒットする

   • https://aws.amazon.com/jp/about-aws/whats-new/2022/09/amazon-aurora-supports-in-place-upgrades-mysql-5-7-8-0/
   • https://www.sunnycloud.jp/column/20221003-01/
   • https://dev.classmethod.jp/articles/amazon-aurora-supports-in-place-upgrades-mysql-5-6-to-5-7/
   • https://techblog.nhn-techorus.com/archives/21234
   • https://labs.snaq.me/entry/2021/01/14/103148
   • https://cloud.google.com/sql/docs/mysql/upgrade-major-db-version-inplace?hl=ja
     これは CloudSQL
   • https://yakst.com/ja/posts/5190
     本記事でやっている MySQL のインプレースアップグレードの記事がこちら

2. https://dev.mysql.com/doc/refman/8.0/ja/upgrade-binary-package.html

   9. 前のステップでは、サーバーは必要に応じてデータディクショナリをアップグレードします。 ここで、残りのアップグレード操作を実行する必要があります:
      • MySQL 8.0.16 の時点では、サーバーは前のステップの一部としてこれを行い、新しい権限または機能を利用できるように、mysql システムデータベースで MySQL 5.7 と MySQL 8.0 の間で必要な変更を行います。 また、パフォーマンススキーマ、INFORMATION_SCHEMA および sys データベースを MySQL 8.0 用に最新の状態にし、すべてのユーザーデータベースで現在のバージョンの MySQL との非互換性を調べます。