MySQL を 8.0.11に yum upgrade
May 9, 2018 – 10:21 amMySQL 8.0 が 4月19日付で正式リリースされたということで、早速、我がサーバのMySQLをアップグレードした。
このエントリでは、upgrade時の手順とログを記録しておいた。
yum repository の確認:
MySql専用のyum repository(/etc/yum.repos.d/mysql-community.repo)に[mysql80-community]セクションが含まれていることを確認。
[mysql-community80] セクション部を以下に掲げる
[mysql80-community] name=MySQL 8.0 Community Server baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/ enabled=0 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
MySql 8.0 に yum update:
updateに先立って、mysqldを停止：
#systemctl stop mysqld
yum updateの実行。コマンドとログを以下に掲げる：
#yum update --enablerepo=mysql80-community mysql-community-server mysql80-community | 2.5 kB 00:00:00 mysql80-community/x86_64/primary_db | 16 kB 00:00:00 Resolving Dependencies --> Running transaction check ---> Package mysql-community-server.x86_64 0:5.7.21-1.el7 will be updated ---> Package mysql-community-server.x86_64 0:8.0.11-1.el7 will be an update --> Processing Dependency: mysql-community-common(x86-64) = 8.0.11-1.el7 for package: mysql-community-server-8.0.11-1.el7.x86_64 --> Processing Dependency: mysql-community-client(x86-64) >= 8.0.0 for package: mysql-community-server-8.0.11-1.el7.x86_64 --> Running transaction check ---> Package mysql-community-client.x86_64 0:5.7.21-1.el7 will be updated ---> Package mysql-community-client.x86_64 0:8.0.11-1.el7 will be an update --> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.0 for package: mysql-community-client-8.0.11-1.el7.x86_64 ---> Package mysql-community-common.x86_64 0:5.7.21-1.el7 will be updated ---> Package mysql-community-common.x86_64 0:8.0.11-1.el7 will be an update --> Running transaction check ---> Package mysql-community-libs.x86_64 0:5.7.21-1.el7 will be updated ---> Package mysql-community-libs.x86_64 0:8.0.11-1.el7 will be an update --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================= Package Arch Version Repository Size ================================================================================================================================= Updating: mysql-community-server x86_64 8.0.11-1.el7 mysql80-community 341 M Updating for dependencies: mysql-community-client x86_64 8.0.11-1.el7 mysql80-community 26 M mysql-community-common x86_64 8.0.11-1.el7 mysql80-community 537 k mysql-community-libs x86_64 8.0.11-1.el7 mysql80-community 2.2 M Transaction Summary ================================================================================================================================= Upgrade 1 Package (+3 Dependent packages) Total download size: 369 M Is this ok [y/d/N]: y Downloading packages: Delta RPMs disabled because /usr/bin/applydeltarpm not installed. (1/4): mysql-community-common-8.0.11-1.el7.x86_64.rpm | 537 kB 00:00:00 (2/4): mysql-community-libs-8.0.11-1.el7.x86_64.rpm | 2.2 MB 00:00:00 (3/4): mysql-community-client-8.0.11-1.el7.x86_64.rpm | 26 MB 00:00:04 (4/4): mysql-community-server-8.0.11-1.el7.x86_64.rpm | 341 MB 00:00:33 --------------------------------------------------------------------------------------------------------------------------------- Total 11 MB/s | 369 MB 00:00:33 Running transaction check Running transaction test Transaction test succeeded Running transaction Updating : mysql-community-common-8.0.11-1.el7.x86_64 1/8 Updating : mysql-community-libs-8.0.11-1.el7.x86_64 2/8 Updating : mysql-community-client-8.0.11-1.el7.x86_64 3/8 Updating : mysql-community-server-8.0.11-1.el7.x86_64 4/8 Cleanup : mysql-community-server-5.7.21-1.el7.x86_64 5/8 Cleanup : mysql-community-client-5.7.21-1.el7.x86_64 6/8 Cleanup : mysql-community-libs-5.7.21-1.el7.x86_64 7/8 Cleanup : mysql-community-common-5.7.21-1.el7.x86_64 8/8 Verifying : mysql-community-server-8.0.11-1.el7.x86_64 1/8 Verifying : mysql-community-client-8.0.11-1.el7.x86_64 2/8 Verifying : mysql-community-libs-8.0.11-1.el7.x86_64 3/8 Verifying : mysql-community-common-8.0.11-1.el7.x86_64 4/8 Verifying : mysql-community-client-5.7.21-1.el7.x86_64 5/8 Verifying : mysql-community-libs-5.7.21-1.el7.x86_64 6/8 Verifying : mysql-community-server-5.7.21-1.el7.x86_64 7/8 Verifying : mysql-community-common-5.7.21-1.el7.x86_64 8/8 Updated: mysql-community-server.x86_64 0:8.0.11-1.el7 Dependency Updated: mysql-community-client.x86_64 0:8.0.11-1.el7 mysql-community-common.x86_64 0:8.0.11-1.el7 mysql-community-libs.x86_64 0:8.0.11-1.el7 Complete!
データベースの互換性をチェック・維持するためmysql_upgrade を実行：
mysql_upgradeに先立ち、mysqldを開始
#systemctl start mysqld
MySQLサーバのupgrade後の標準的な手続きとして、mysql_upgradeを実行するが、その役割、機能は以下（リファレンス・マニュアルから関連部を抜粋）：
mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL Server. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.
You should execute mysql_upgrade each time you upgrade MySQL.
因みに、MySQL5.7からMySQL8.0へのupgradeにおいては、mysql_upgradeを実行しないとこれまで使用していたデータベースは8.0に非互換でエラーになってしまう。mysql_upgaradeはMUST。
以下、musql_upgradeの実行コマンドラインとログを掲げる：
# mysql_upgrade -u root -p Enter password: Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Upgrading system table data. Checking system database. mysql.columns_priv OK mysql.component OK mysql.db OK mysql.default_roles OK mysql.engine_cost OK mysql.func OK mysql.general_log OK mysql.global_grants OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.password_history OK mysql.plugin OK mysql.procs_priv OK mysql.proxies_priv OK mysql.role_edges OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Found outdated sys schema version 1.5.1. Upgrading the sys schema. Checking databases. AccessDB.access OK AccessDB.mypage OK AmazonBanner01.item OK AmazonBanner01.item_asin OK STOCKDB.company OK STOCKDB.stock_price OK (省 略） sys.sys_config OK test.stock_price OK Upgrade process completed successfully. Checking if update is needed.
設定ファイルmy.cnfの修正：
設定ファイル/etc/my.cnfについて、character set 並びにauthentication（認証）に関わり以下のように修正変更。
[musqld]セクション内に以下を追記：
collation-server = utf8mb4_ja_0900_as_cs_ks
上記措置でcollationをひらがなとカタカナを判別するものにしている。なお、上記のようにCollation-serverを明確に指定しないとphpとのconnection()が正常に行われない。
同じく[musqld]セクション内でコメントアウトされていたAuthintication関連部を復帰させ以下の形式に：
# Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin default-authentication-plugin=mysql_native_password
MySQL8.0になって、default の Authentication Pluginが、以前のmysql_native_passwordから caching_sha2_passwordに変更された。従来からのAuthentication方式を維持するため、上記の修正をおこなった。
因みに、このあたりReference Manualに詳しい。