👻
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を整理していく。
参考
Discussion