Upgrade to Pro — share decks privately, control downloads, hide ads and more …

ぼくたちはMySQL 8.1とどう生きるか

yoku0825
August 28, 2023

ぼくたちはMySQL 8.1とどう生きるか

2023/08/28 MySQL Innovation Day Tokyo 2023
https://www.oracle.com/jp/events/mysql-day/

yoku0825

August 28, 2023
Tweet

More Decks by yoku0825

Other Decks in Technology

Transcript

  1. \こんにちわ/ yoku0825@とある企業のDBA オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter:

    @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会副代表 ‐ MySQL Casual ‐ Oracle ACE Pro ‐ 5/59
  2. MySQL 8.0 継続的リリース() 2016/09 8.0.0, 2018/04 GA GAクオリティ() メンテナンスリリースで機能が増える ‐

    バグFIXを受け入れるために新機能の受け入れも必要 ‐ 新機能どころかSQLレベルの非互換があるバージョンもあった(予約語だったりワーニングだったり) ‐ MySQL 8.0.34とそれ以降ではもう新機能は(サーバーには)入らないはず 6/59
  3. 「サーバーには」 A new password-validation system variable now permits the configuration

    and .. .. This new capability is one several that provide DBAs more complete control over password management. https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-34.html 7/59
  4. 余談 8.0の The series number describes the stable feature set

    は嘘だと思う https://dev.mysql.com/doc/refman/8.0/en/which-version.html 10/59
  5. Before 8.0 開発初期 開発後期 機能凍結(安定版) 5.0 alpha, beta Release Candidate

    Production 5.1 (名前はない) Release Candidate General Availability 5.5~5.7 Development Milestone Release Candidate General Availability 8.0 Development Milestone Release Candidate, General Availability N/A 13/59
  6. After 8.0 開発初期 開発後期 機能凍結(安定版) 8.0 Development Milestone Release Candidate,

    General Availability(~8.0.33) General Availability (8.0.34~) 8.1 N/A N/A Innovation Release is GA(No maintenance release after GA) 8.2(maybe) N/A N/A Innovation Release is GA(No maintenance release after GA) 8.4(maybe) N/A N/A General Availability(Long Term Support) 14/59
  7. つまりは 開発初期 開発後期 機能凍結(安定版) 5.0 alpha, beta Release Candidate Production

    5.5~5.7 Development Milestone Release Candidate General Availability 8.0 Development Milestone Release Candidate, General Availability(~8.0.33) General Availability (8.0.34~) Next LTS(ex.8.4) Innovation Release(ex. 8.0, 8.1, 8.2) Innovation Release (ex. 8.3), Release Candidate? General Availability(Long Term Support) 15/59
  8. つまりは Innovation Release = Development Milestone Release, Release Candidate の

    ような立ち位置 2023/08/25現在、yumリポジトリは mysql80-community-release であって8.1は勝手 にはアップグレード先にならない(aptリポジトリも名前は違うけれど同様) https://dev.mysql.com/downloads/repo/yum/ ‐ https://dev.mysql.com/downloads/repo/apt/ ‐ というよりはMySQL 8.1のビルド済みパッケージが置かれている気配がない http://repo.mysql.com/yum/ ‐ http://repo.mysql.com/apt/debian/dists/buster/ ‐ ダウンロードページにはある https://dev.mysql.com/downloads/mysql/ ‐ 16/59
  9. メンテナンス期間 LTS Releases will follow the Oracle Lifetime Support Policy,

    which includes 5 years of premier and 3 years of e xtended support. Innovation releases will be supported until the next major & minor release. <snip> - About every 2 years a new Long Term Support version will be released (blue). An exception is the 8.x LTS rel ease which will happen well before EOL of 8.0 Introducing MySQL Innovation and Long-Term Support (LTS) versions 19/59
  10. メンテナンス期間 一応8年間はサポート(=セキュリティFixしてくれることは期待しているが彼らがどう思っているかは 謎) 8.4が2024/04にリリースされて(この日付は推測です) 即乗り換えるとすると、EOLは 2032/04 ただし「隣接しないLTS」は直接バージョンアップをサポートしないらしいので、あんまり塩漬けにするとその後大変 そう(後述) ‐ 本当に2年で次のLTSが出るなら、2026/04~2034/04と2028/04~2036/04

    2027年くらいに次のLTSに行って、2030年くらいにまた次のLTSに行くくらいが据わりが良さそう(3年スパンか) ‐ ちなみに8.0のEOLは2026/04 その時点でのアップグレード先候補が8.4, 9.7, ただし9.7はギリギリすぎるのでやっぱり2025年中に8.4(1年経過)に上げるくらいが良 いのか ‐ 20/59
  11. アップグレードパス - Upgrades from MySQL 8.0 to 8.1 are supported

    between General Availability (GA) releases only. For MySQ L 8.1, it is required that you upgrade from a MySQL 8.0 GA release (8.0.11 or higher). Upgrades from non-GA releases of MySQL 8.0 are not supported. - Upgrading to the latest release in the series is recommended before upgrading to the next release series. For ex ample, upgrade to the latest MySQL 8.0 release before upgrading to MySQL 8.1. https://dev.mysql.com/doc/refman/8.1/en/upgrade-paths.html 8.0は8.0.11とそれ以降って書いてあるけど、8.0.30へのアップグレードも結構クラッシュしたみた いだから避けて8.0最新から行った方が良いと思う 本当に8.0.34とそれ以降で変な変更を入れないなら、8.0.34まで行っておけばあとは8.0.40だろうが8.0.41 だろうが少ないインパクトで行けるはず ‐ 23/59
  12. 開発ツリーのイメージ 8.0 └── 8.0.32 └── 8.0.33 ├── 8.0.34 │ └──

    8.0.35 │ └── 8.0.36 └── 8.1.0 └── 8.2.0 └── 8.3.0 24/59
  13. 開発ツリーのイメージ 8.0.34と8.1.0は兄弟 8.0.34と8.1.0のRelease Noteは非常に似通っている https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-34.html https://dev.mysql.com/doc/relnotes/mysql/8.1/en/news-8-1-0.html ‐ What Is New

    in MySQL 8.1の内容は8.1.0のリリースノートとほぼ同じ ‐ 今後乖離が広がっていった時にWhat Is Newはどこから比べてNewになるのか?? MySQL Bugs: #112039: Request for What Is New in the next LTS release ‐ 日本MySQLユーザ会 とみたさん のMySQL Parametersは8.1.0対応済 https://mysql-params.tmtms.net/mysqld/ ‐ 25/59
  14. Both 8.1 and 8.0.34 Account Management Notes A new password-validation

    system variable now permits the configuration and enforcement of a minimum number of characters that users must change when attempting to replace their own MySQL account passwords. ‐ 33/59
  15. Both 8.1 and 8.0.34 Binary Logging Several functions now are

    added to the libmysqlclient.so shared library that enable developers to access a MySQL server binary log: mysql_binlog_open(), mysql_binlog_fetch(), and mysql_binlog_close(). ‐ 34/59
  16. Both 8.1 and 8.0.34 Deprecation and Removal Notes Important Change:

    Since MySQL provides other means of performing database dumps and backups with the same or additional functionality, including mysqldump and MySQL Shell Utilities, the mysqlpump client utility program has become redundant, and is now deprecated ‐ 35/59
  17. Both 8.1 and 8.0.34 Deprecation and Removal Notes Replication: The

    sync_relay_log_info server system variable is deprecated in this release, and getting or setting this variable or its equivalent startup option –sync-relay- log-info now raises a warning. ‐ 36/59
  18. Both 8.1 and 8.0.34 Deprecation and Removal Notes Replication: The

    binlog_format server system variable is now deprecated, and subject to removal in a future version of MySQL. The functionality associated with this variable, that of changing the binary logging format, is also deprecated. ‐ 37/59
  19. Both 8.1 and 8.0.34 Deprecation and Removal Notes Group Replication:

    The group_replication_recovery_complete_at server system variable is now deprecated, and setting it produces a warning. You should expect its removal in a future release of MySQL. (WL #15460) ‐ 38/59
  20. Both 8.1 and 8.0.34 Deprecation and Removal Notes The mysql_native_password

    authentication plugin now is deprecated and subject to removal in a future version of MySQL. ‐ 39/59
  21. Both 8.1 and 8.0.34 Deprecation and Removal Notes The MySQL

    client library currently supports performing an automatic reconnection to the server if it finds that the connection is down and an application attempts to send a statement to the server to be executed. Now, this feature is deprecated and subject to removal in a future release of MySQL. ‐ The related MYSQL_OPT_RECONNECT option is still available but it is also deprecated. C API functions mysql_get_option() and mysql_options() now write a deprecation warning to the standard error output when an application specifies MYSQL_OPT_RECONNECT. (WL #15766) ‐ As of MySQL 8.0.34 and 8.1.0 ‘s libmysqlclients deprecates `MYSQL_OPT_RECONNECT` · Issue #354 · perl5-dbi/DBD-mysql 40/59
  22. Both 8.1 and 8.0.34 Deprecation and Removal Notes CURRENT_USER() can

    now be used as a default value for VARCHAR and TEXT columns in CREATE TABLE and ALTER TABLE … ADD COLUMN statements. ‐ 41/59
  23. MySQL 8.1 vs MySQL 8.0.34 C API Notes Added the

    new mysql_reset_connection_nonblocking() C API function. It is the counterpart of the mysql_reset_connection() synchronous function, for use by applications that require asynchronous communication with the server. Our thanks to Meta for the contribution. (Bug #32202058, WL #15633) ‐ The new mysql_get_connect_nonblocking_stage() C API function permits applications to monitor the progress of asynchronous connections for the purpose of taking appropriate actions based on the progress. Our thanks to Meta for the contribution. (Bug #32202053, WL #15651) ‐ 44/59
  24. MySQL 8.1 vs MySQL 8.0.34 Deprecation and Removal Notes The

    use of the dollar sign ($) as the initial character of an unquoted identifier was deprecated in MySQL 8.0.32. In this release, the use of an unquoted identifier starting with the dollar sign and containing one or more dollar signs in addition to the first one generates a syntax error. ‐ 45/59
  25. MySQL 8.1 vs MySQL 8.0.34 Logging Notes To aid in

    troubleshooting in the event of an excessively long server shutdown, this release introduces a number of new messages that are written to the MySQL error log during shutdown ‐ 46/59
  26. MySQL 8.1 vs MySQL 8.0.34 SQL Syntax Notes JSON: It

    is now possible to capture EXPLAIN FORMAT=JSON output in a user variable using a syntax extension added in this release. EXPLAIN FORMAT=JSON INTO var_name stmt works with any explainable statement stmt to store the output in the user variable var_name ‐ 47/59
  27. MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Important

    Change; Replication: The default value for the SOURCE_RETRY_COUNT option of the CHANGE REPLICATION SOURCE TO statement has been changed to 10. ‐ 48/59
  28. MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Important

    Change: MySQL version numbers used in versioned comments now support a major version consisting of one or two digits (previously, only a single digit was supported for this value). ‐ 49/59
  29. MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Important

    Change: Dropped support for Enterprise Linux 6 (and associated glibc 2.12 generic), SUSE 12, Debian 10, MacOS 12, Ubuntu 18.04 and 20.04, Windows 10 and Server 2012R2; and 32-bit versions are no longer built. ‐ 50/59
  30. MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Group

    Replication: Any statement that fetches values of system status variables fetches them all, and acquires a read lock on them all as well. This meant that a SHOW STATUS started after one of the operations just listed was required to wait until the operation was complete before returning. Now in such cases, the statement fetching status variables immediately returns their cached values instead of waiting. (Bug #35373030) ‐ 51/59
  31. MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Group

    Replication: Before it elects a new primary, group_replication_set_as_primary() waits for all transactions to finish, including all DML operations that are currently being processed. ‐ 52/59
  32. MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Group

    Replication: For better diagnosis and troubleshooting of network instabilities, MySQL Group replication adds a number of variables in this release providing network, control message, and data message statistics for each member of Group Replication. ‐ 53/59
  33. MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed MySQL

    now implements client-side Server Name Indication (SNI), which is an extension to the TLS protocol. ‐ 54/59
  34. MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Comments

    in the mysql client are now enabled by default. To disable them, start mysql with the –skip-comments option. ‐ 55/59
  35. MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Implemented

    a SHOW PARSE_TREE statement in debug builds to display the JSON- formatted parse tree for a SELECT statement. ‐ This statement is not supported in release builds, and is available only in debug builds, or by compiling the server using -DWITH_SHOW_PARSE_TREE. (WL #15426) ‐ 56/59
  36. MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Previously,

    invalid SSL server and CA certificates were not identified as problematic until after the server started or after an invalid certificate was loaded at runtime. ‐ 57/59
  37. MySQL 8.1 vs MySQL 8.0.34 Bugs Fixed Incompatible Change; Replication:

    Setting server variables equal to SQL NULL as options on the command line should not be possible and is not supported. ‐ 58/59