🐬
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 アップグレード方法として「インプレースアップグレード」と「論理アップグレード」が存在する
- インプレースアップグレード
- 論理アップグレード
- インプレースアップグレードは、前のバージョンのデータディレクトリを使って 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
- /work
バイナリの取得〜 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が異なる場合を考えると理解できる仕様だが、普段これが一致する状況で使っていて意表を突かれたのでメモ
- collation のデフォルトは
ラップアップ
- 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のまま
- information_schema.tables の作成時刻 ↓ は
- MySQL 5.7 から 8 に論理アップグレードする場合には、何もしないと utf8mb4_general_ci の collate が維持されないので、default collation を明示的に変更するなど注意が必要
-
"インプレースアップグレード 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 のインプレースアップグレードの記事がこちら
-
https://dev.mysql.com/doc/refman/8.0/ja/upgrade-binary-package.html
↩︎- 前のステップでは、サーバーは必要に応じてデータディクショナリをアップグレードします。 ここで、残りのアップグレード操作を実行する必要があります:
- MySQL 8.0.16 の時点では、サーバーは前のステップの一部としてこれを行い、新しい権限または機能を利用できるように、mysql システムデータベースで MySQL 5.7 と MySQL 8.0 の間で必要な変更を行います。 また、パフォーマンススキーマ、INFORMATION_SCHEMA および sys データベースを MySQL 8.0 用に最新の状態にし、すべてのユーザーデータベースで現在のバージョンの MySQL との非互換性を調べます。
- 前のステップでは、サーバーは必要に応じてデータディクショナリをアップグレードします。 ここで、残りのアップグレード操作を実行する必要があります:
Discussion