MySQL:移行手順(5.7 → 8.0)

virtualboxにてMySQL5.7 → 8.0の移行を行った。
手順の整理、影響の洗い出しを目的とするため、mysqlの設定(my.cnf)については最低限のものとした。

MySQL 5.7 構築

OS環境

[root@node3 ~]# uname -a
Linux node3 3.10.0-1160.el7.x86_64 #1 SMP Mon Oct 19 16:18:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
[root@node3 ~]#

インストール

# リポジトリ追加
[root@node3 ~]# yum localinstall http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

# 登録されている全てのリポジトリを表示
[root@node3 ~]# yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64  MySQL Cluster 7.5 Community     無効
mysql-cluster-7.5-community-source  MySQL Cluster 7.5 Community - S 無効
mysql-cluster-7.6-community/x86_64  MySQL Cluster 7.6 Community     無効
mysql-cluster-7.6-community-source  MySQL Cluster 7.6 Community - S 無効
mysql-connectors-community/x86_64   MySQL Connectors Community      有効:    192
mysql-connectors-community-source   MySQL Connectors Community - So 無効
mysql-tools-community/x86_64        MySQL Tools Community           有効:     90
mysql-tools-community-source        MySQL Tools Community - Source  無効
mysql-tools-preview/x86_64          MySQL Tools Preview             無効
mysql-tools-preview-source          MySQL Tools Preview - Source    無効
mysql55-community/x86_64            MySQL 5.5 Community Server      無効
mysql55-community-source            MySQL 5.5 Community Server - So 無効
mysql56-community/x86_64            MySQL 5.6 Community Server      無効
mysql56-community-source            MySQL 5.6 Community Server - So 無効
mysql57-community/x86_64            MySQL 5.7 Community Server      有効:    584
mysql57-community-source            MySQL 5.7 Community Server - So 無効
mysql80-community/x86_64            MySQL 8.0 Community Server      無効
mysql80-community-source            MySQL 8.0 Community Server - So 無効
[root@node3 ~]#

# インストール可能パッケージの確認
[root@node3 ~]#  yum list | grep mysql-community-server
mysql-community-server.x86_64             5.7.38-1.el7                 mysql57-community
[root@node3 ~]#

# インストール
[root@node3 ~]# yum -y install mysql-community-server
 Failing package is: mysql-community-common-5.7.38-1.el7.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
 
 ## GPGの鍵がなくてインストールがコケた。仕方がないので鍵をインストールする。
[root@node3 ~]#
[root@node3 ~]# rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
[root@node3 ~]#
参考：https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/

# 再インストール
[root@node3 ~]# yum -y install mysql-community-server
インストール:
  mysql-community-libs.x86_64 0:5.7.38-1.el7                                 mysql-community-libs-compat.x86_64 0:5.7.38-1.el7                                 mysql-community-server.x86_64 0:5.7.38-1.el7

依存性関連をインストールしました:
  mysql-community-client.x86_64 0:5.7.38-1.el7    mysql-community-common.x86_64 0:5.7.38-1.el7    net-tools.x86_64 0:2.0-0.25.20131004git.el7     perl.x86_64 4:5.16.3-299.el7_9              perl-Carp.noarch 0:1.26-244.el7
  perl-Encode.x86_64 0:2.51-7.el7                 perl-Exporter.noarch 0:5.68-3.el7               perl-File-Path.noarch 0:2.09-2.el7              perl-File-Temp.noarch 0:0.23.01-3.el7       perl-Filter.x86_64 0:1.49-3.el7
  perl-Getopt-Long.noarch 0:2.40-3.el7            perl-HTTP-Tiny.noarch 0:0.033-3.el7             perl-PathTools.x86_64 0:3.40-5.el7              perl-Pod-Escapes.noarch 1:1.04-299.el7_9    perl-Pod-Perldoc.noarch 0:3.20-4.el7
  perl-Pod-Simple.noarch 1:3.28-4.el7             perl-Pod-Usage.noarch 0:1.63-3.el7              perl-Scalar-List-Utils.x86_64 0:1.27-248.el7    perl-Socket.x86_64 0:2.010-5.el7            perl-Storable.x86_64 0:2.45-3.el7
  perl-Text-ParseWords.noarch 0:3.29-4.el7        perl-Time-HiRes.x86_64 4:1.9725-3.el7           perl-Time-Local.noarch 0:1.2300-2.el7           perl-constant.noarch 0:1.27-2.el7           perl-libs.x86_64 4:5.16.3-299.el7_9
  perl-macros.x86_64 4:5.16.3-299.el7_9           perl-parent.noarch 1:0.225-244.el7              perl-podlators.noarch 0:2.5.1-3.el7             perl-threads.x86_64 0:1.87-4.el7            perl-threads-shared.x86_64 0:1.43-6.el7

設定

# my.cnfの設定(最低限)
[root@node3 ~]# cp /etc/my.cnf /etc/my.cnf.org
[root@node3 ~]#
[root@node3 ~]# vi /etc/my.cnf
[root@node3 ~]#
[root@node3 ~]# diff /etc/my.cnf /etc/my.cnf.org
28d27
< character-set-server=utf8
[root@node3 ~]#
[root@node3 ~]# systemctl start mysqld
[root@node3 ~]#
[root@node3 ~]# grep password /var/log/mysqld.log
2022-06-11T16:19:22.794817Z 1 [Note] A temporary password is generated for root@localhost: azfR!oOku1u/
[root@node3 ~]#
[root@node3 ~]# mysql -uroot -p
Enter password:
mysql>
mysql> SET PASSWORD = PASSWORD('パスワード');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> quit
Bye

# テストデータ作成
参考：https://dev.mysql.com/doc/index-other.html
[root@node3 ~]# cd /var/tmp
[root@node3 tmp]#
[root@node3 tmp]# ls -l　
-rw-r--r--. 1 root root 93021  6月 12 01:25 world-db.zip #Teratermにてファイル転送
[root@node3 tmp]#
[root@node3 tmp]# unzip world-db.zip
[root@node3 tmp]#
[root@node3 tmp]# cd world-db
[root@node3 tmp]#
[root@node3 world-db]# ls -l
合計 392
-rw-r--r--. 1 root root 398635  6月  1 07:05 world.sql
[root@node3 world-db]#
[root@node3 world-db]# mysql -uroot -p
Enter password:
mysql> 
mysql> source world.sql
mysql> 
Query OK, 0 rows affected (0.00 sec)
・・・略・・・
mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql> use world;
Database changed
mysql>
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
mysql> quit
Bye

アップグレードチェック

# MySQL Shell インストール
[root@node3 world-db]# yum install mysql-shell
インストール:
  mysql-shell.x86_64 0:8.0.29-1.el7

#　ログイン(ソケット指定)
[root@node3 world-db]# mysqlsh -uroot -S /var/lib/mysql/mysql.sock
Please provide the password for 'root@/var%2Flib%2Fmysql%2Fmysql.sock': ***************
Save password for 'root@/var%2Flib%2Fmysql%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No):
MySQL Shell 8.0.29

Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
Creating a session to 'root@/var%2Flib%2Fmysql%2Fmysql.sock'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 5
Server version: 5.7.38 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
 MySQL  localhost  JS >
 
 # アップグレードチェック
 MySQL  localhost  JS > util.checkForServerUpgrade("root@localhost:3306", {"targetVersion":"8.0.26","configPath":"/etc/my.cnf" })
Please provide the password for 'root@localhost:3306': ***************
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No):
The MySQL server at localhost:3306, version 5.7.38 - MySQL Community Server
(GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.26...

1) Usage of old temporal type
  No issues found

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

3) 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

  mysql - schema's default character set: utf8

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

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

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

7) Usage of obsolete MAXDB sql_mode flag
  No issues found

8) 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

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

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

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

12) Usage of removed functions
  No issues found

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

14) Removed system variables for error logging to the system log configuration
  No issues found

15) Removed system variables
  No issues found

16) System variables with new default values
  Warning: Following system variables that are not defined in your
    configuration file will have new default values. Please review if you rely on
    their current values and if so define them before performing upgrade.
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

  back_log - default value will change
  collation_server - default value will change from latin1_swedish_ci to
    utf8mb4_0900_ai_ci
  event_scheduler - default value will change from OFF to ON
  explicit_defaults_for_timestamp - default value will change from OFF to ON
  innodb_autoinc_lock_mode - default value will change from 1 (consecutive) to
    2 (interleaved)
  innodb_flush_method - default value will change from NULL to fsync (Unix),
    unbuffered (Windows)
  innodb_flush_neighbors - default value will change from 1 (enable) to 0
    (disable)
  innodb_max_dirty_pages_pct - default value will change from 75 (%)  90 (%)
  innodb_max_dirty_pages_pct_lwm - default value will change from_0 (%) to 10
    (%)
  innodb_undo_log_truncate - default value will change from OFF to ON
  innodb_undo_tablespaces - default value will change from 0 to 2
  log_bin - default value will change from OFF to ON
  log_error_verbosity - default value will change from 3 (Notes) to 2 (Warning)
  log_slave_updates - default value will change from OFF to ON
  master_info_repository - default value will change from FILE to TABLE
  max_allowed_packet - default value will change from 4194304 (4MB) to 67108864
    (64MB)
  max_error_count - default value will change from 64 to 1024
  optimizer_trace_max_mem_size - default value will change from 16KB to 1MB
  performance_schema_consumer_events_transactions_current - default value will
    change from OFF to ON
  performance_schema_consumer_events_transactions_history - default value will
    change from OFF to ON
  relay_log_info_repository - default value will change from FILE to TABLE
  server_id - default value will change from 0 to 1
  slave_rows_search_algorithms - default value will change from 'INDEX_SCAN,
    TABLE_SCAN' to 'INDEX_SCAN, HASH_SCAN'
  table_open_cache - default value will change from 2000 to 4000
  transaction_write_set_extraction - default value will change from OFF to
    XXHASH64

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

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

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

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

21) 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

Errors:   0
Warnings: 27
Notices:  1

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  localhost  JS >

データ抽出

[root@node3 ~]# mysqldump --single-transaction --hex-blob --user=root --password  --all-databases > /var/tmp/mysql57.sql
Enter password:
[root@node3 ~]#
[root@node3 ~]# ls -l /var/tmp/mysql57.sql
-rw-r--r--. 1 root root 1119907  6月 12 02:44 /var/tmp/mysql57.sql
[root@node3 ~]#

MySQL8 構築

OS環境

[root@node1 ~]# uname -a
Linux node1 4.18.0-372.9.1.el8.x86_64 #1 SMP Fri Apr 15 22:12:19 EDT 2022 x86_64 x86_64 x86_64 GNU/Linux
[root@node1 ~]#

インストール

[root@node1 ~]# yum module install mysql:8.0/server
インストール済み:
  mariadb-connector-c-config-3.1.11-2.el8_3.noarch                                mecab-0.996-1.module+el8+2459+7cb96738.9.x86_64                                 mysql-8.0.26-1.module+el8.4.0+12359+b8928c02.x86_64
  mysql-common-8.0.26-1.module+el8.4.0+12359+b8928c02.x86_64                      mysql-errmsg-8.0.26-1.module+el8.4.0+12359+b8928c02.x86_64                      mysql-server-8.0.26-1.module+el8.4.0+12359+b8928c02.x86_64
  perl-Carp-1.42-396.el8.noarch                                                   perl-Data-Dumper-2.167-399.el8.x86_64                                           perl-Digest-1.17-395.el8.noarch
  perl-Digest-MD5-2.55-396.el8.x86_64                                             perl-Encode-4:2.97-3.el8.x86_64                                                 perl-Errno-1.28-421.el8.x86_64
  perl-Exporter-5.72-396.el8.noarch                                               perl-File-Path-2.15-2.el8.noarch                                                perl-File-Temp-0.230.600-1.el8.noarch
  perl-Getopt-Long-1:2.50-4.el8.noarch                                            perl-HTTP-Tiny-0.074-1.el8.noarch                                               perl-IO-1.38-421.el8.x86_64
  perl-IO-Socket-IP-0.39-5.el8.noarch                                             perl-IO-Socket-SSL-2.066-4.module+el8.3.0+6446+594cad75.noarch                  perl-MIME-Base64-3.15-396.el8.x86_64
  perl-Mozilla-CA-20160104-7.module+el8.3.0+6498+9eecfe51.noarch                  perl-Net-SSLeay-1.88-2.module+el8.6.0+13392+f0897f98.x86_64                     perl-PathTools-3.74-1.el8.x86_64
  perl-Pod-Escapes-1:1.07-395.el8.noarch                                          perl-Pod-Perldoc-3.28-396.el8.noarch                                            perl-Pod-Simple-1:3.35-395.el8.noarch
  perl-Pod-Usage-4:1.69-395.el8.noarch                                            perl-Scalar-List-Utils-3:1.49-2.el8.x86_64                                      perl-Socket-4:2.027-3.el8.x86_64
  perl-Storable-1:3.11-3.el8.x86_64                                               perl-Term-ANSIColor-4.06-396.el8.noarch                                         perl-Term-Cap-1.17-395.el8.noarch
  perl-Text-ParseWords-3.30-395.el8.noarch                                        perl-Text-Tabs+Wrap-2013.0523-395.el8.noarch                                    perl-Time-Local-1:1.280-1.el8.noarch
  perl-URI-1.73-3.el8.noarch                                                      perl-Unicode-Normalize-1.25-396.el8.x86_64                                      perl-constant-1.33-396.el8.noarch
  perl-interpreter-4:5.26.3-421.el8.x86_64                                        perl-libnet-3.11-3.el8.noarch                                                   perl-libs-4:5.26.3-421.el8.x86_64
  perl-macros-4:5.26.3-421.el8.x86_64                                             perl-parent-1:0.237-1.el8.noarch                                                perl-podlators-4.11-1.el8.noarch
  perl-threads-1:2.21-2.el8.x86_64                                                perl-threads-shared-1.58-2.el8.x86_64                                           protobuf-lite-3.5.0-13.el8.x86_64

移行

# my.cnf転送
[root@node3 ~]# scp /etc/my.cnf root@node1のIPアドレス:/etc/my.cnf

# 起動
[root@node1 ~]# systemctl start mysqld.service
[root@node1 ~]#

# data 転送
[root@node3 ~]# scp /var/tmp/mysql57.sql root@node1のIPアドレス:/var/tmp/

# データ挿入
[root@node1 tmp]# mysql -u root < mysql57.sql
ERROR 3554 (HY000) at line 318: Access to system table 'mysql.innodb_index_stats' is rejected.
[root@node1 tmp]#
## データディクショナリが変更となるので想定通りのエラー
## 抽出時に対象のDBのみ抽出を行う or 除外する

# 再データ抽出
[root@node3 ~]# mysqldump --single-transaction --hex-blob --user=root --password  --all-databases --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats > /var/tmp/mysql57.sql
Enter password:

# 再データ挿入
[root@node1 tmp]# mysql -u root < mysql57.sql
[root@node1 tmp]#

# mysqlupgrade
[root@node1 tmp]# mysql_upgrade
The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.
To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade.
The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.
It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.
## mysql_upgradeは8.0.1xで廃止になったので想定通り

# 再mysqlupgrade
[root@node1 tmp]# mysqld --upgrade=FORCE
2022-06-11T17:59:14.662935Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2022-06-11T17:59:14.663634Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.26) starting as process 1716
2022-06-11T17:59:14.664491Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2022-06-11T17:59:14.665019Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
2022-06-11T17:59:14.665061Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-06-11T17:59:14.665141Z 0 [System] [MY-010910] [Server] /usr/libexec/mysqld: Shutdown complete (mysqld 8.0.26)  Source distribution.
[root@node1 tmp]#
## rootだとダメらしい

# 三度目の正直mysqlupgrade
[root@node1 tmp]# su - piguo
[piguo@node1 ~]$
[piguo@node1 ~]$ mysqld --upgrade=FORCE
[piguo@node1 ~]$

# 起動
[root@node1 tmp]# systemctl start mysqld.service
[root@node1 tmp]#

# DB確認
[root@node1 tmp]# mysql -uroot -p
Enter password:
mysql> show databases;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
mysql>
## root ユーザーとしてmysqldを実行すること上記エラーが発生するらしい

# 
[root@node1 ~]# vi /etc/my.cnf
[mysqld]
user=mysql
[root@node1 ~]# 
[root@node1 ~]# systemctl start mysqld.service
[root@node1 ~]# 
[root@node1 ~]# systemctl stop mysqld.service
[root@node1 ~]# 

# 再DB確認
[root@node1 ~]# mysql -uroot -p
Enter password:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
5 rows in set (0.00 sec)

mysql>
mysql> use world;
Database changed
mysql>
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql>

アップグレードチェックで表示されたwarningを整理していく。

参考

https://trios.pro/mysql-centos7-install/
https://qiita.com/saki-engineering/items/c38709df3690f53f04a4
https://access.redhat.com/documentation/ja-jp/red_hat_enterprise_linux/8/html/deploying_different_types_of_servers/installing-mysql_assembly_using-mysql
https://dev.mysql.com/doc/refman/5.6/ja/changing-mysql-user.html