$30 off During Our Annual Pro Sale. View Details »

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

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

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

yoku0825
PRO

August 28, 2023
Tweet

More Decks by yoku0825

Other Decks in Technology

Transcript

  1. ぼくたちはMySQL 8.1とどう生きるか
    2023/08/28
    yoku0825
    MySQL Innovation Day Tokyo 2023

    View Slide

  2. おことわり
    このセッションは個人の見解を述べたものであり、所属する組織または所属しない組織の意見と
    同じかもしれませんし違うかもしれません
    2023/08/28時点の推測、想像を多分に含みます。未来から見るとトンチンカンなことを言って
    る可能性があります。
    1/59

    View Slide

  3. TL;DR
    https://twitter.com/yoku0825/status/1686258400135655424
    2/59

    View Slide

  4. MySQL 8.1
    リリースおめでとう
    ございます
    3/59

    View Slide

  5. MySQL 8.1
    ほぼ7年ぶりの新
    (?)バージョン
    4/59

    View Slide

  6. \こんにちわ/
    yoku0825@とある企業のDBA
    オラクれない

    ポスグれない

    マイエスキューエる

    生息域
    Twitter: @yoku0825

    Blog: 日々の覚書

    日本MySQLユーザ会副代表

    MySQL Casual

    Oracle ACE Pro

    5/59

    View Slide

  7. MySQL 8.0
    継続的リリース()
    2016/09 8.0.0, 2018/04 GA
    GAクオリティ()
    メンテナンスリリースで機能が増える

    バグFIXを受け入れるために新機能の受け入れも必要

    新機能どころかSQLレベルの非互換があるバージョンもあった(予約語だったりワーニングだったり)

    MySQL 8.0.34とそれ以降ではもう新機能は(サーバーには)入らないはず
    6/59

    View Slide

  8. 「サーバーには」
    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

    View Slide

  9. MySQL 8.1
    2023/07 Innovation Release
    メンテナンスリリースは存在しない(8.1.0が唯一の8.1系のリリース)
    リリース系列が上がっているので8.0と互換性がなくても文句は言わない

    新機能(?)が入る
    8/59

    View Slide

  10. 余談
    MySQL 5.1までは先頭の1文字がファイルフォーマットを示していたらしいんだけど5.5とそれ以降では
    消えてた
    9/59

    View Slide

  11. 余談
    8.0の The series number describes the stable feature set は嘘だと思う
    https://dev.mysql.com/doc/refman/8.0/en/which-version.html
    10/59

    View Slide

  12. MySQL 8.0 vs MySQL 8.1
    単にrelease noteのバイト数だけで比較
    11/59

    View Slide

  13. Innovation Releaseの意義?
    8.0に慣れすぎたおれ「これくらいの非互換もない機能追加だったらまだ8.0に入れても良かったん
    では?」
    目を覚ます必要がある
    ちなみに5.7.0(リリースなし)もそうでしたね
    12/59

    View Slide

  14. 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

    View Slide

  15. 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

    View Slide

  16. つまりは
    開発初期 開発後期 機能凍結(安定版)
    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

    View Slide

  17. つまりは
    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

    View Slide

  18. の割には
    17/59

    View Slide

  19. メンテナンス期間
    Introducing MySQL Innovation and Long-Term Support (LTS) versions
    18/59

    View Slide

  20. メンテナンス期間
    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.

    - 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

    View Slide

  21. メンテナンス期間
    一応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

    View Slide

  22. アップグレードパス
    Introducing MySQL Innovation and Long-Term Support (LTS) versions
    21/59

    View Slide

  23. アップグレードパス
    インプレースアップグレード(datadirをそのまま、バイナリだけパッケージを入れ替える)とレプリケー
    ションが公式にサポートされるのは
    隣接するLTS(ex.8.4 -> 9.7)

    同じLTS系列同士(ex. 8.4.11 -> 8.4.20)

    LTSをまたぐ前のInnovation Release同士(ex. 8.1 -> 8.2, 8.1 -> 8.3)

    LTSをまたぐ前のInnovation ReleaseからLTS(ex. 9.1 -> 9.7)

    22/59

    View Slide

  24. アップグレードパス
    - 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

    View Slide

  25. 開発ツリーのイメージ
    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

    View Slide

  26. 開発ツリーのイメージ
    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

    View Slide

  27. まとめ
    8.1とそれ以降のInnovation Releaseは強いモチベーションがない限り考えなくて良い
    次のLTSに軟着陸するために、今のうちに8.0.34とそれ以降あたりに着地をしておきたい
    (2026/04までには)
    次のLTSが出る頃にはまた知見がインターネットにも出てくると思うので、心配しすぎるよりはその
    時ちゃんと情報収集すれば良いのかなと
    知見をインターネットに出す側の人大歓迎

    26/59

    View Slide

  28. ( ゚д゚)
    27/59

    View Slide

  29. ( ゚д゚ )
    28/59

    View Slide

  30. (゚д゚ )
    29/59

    View Slide

  31. もしちゃんと時間内に
    収められたらここから自
    分へのご褒美タイム
    30/59

    View Slide

  32. MySQL 8.1につ
    いてしゃべっていい
    よ!
    31/59

    View Slide

  33. その前に8.0にも
    8.1にも入ってし
    まった何か
    32/59

    View Slide

  34. 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

    View Slide

  35. 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

    View Slide

  36. 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

    View Slide

  37. 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

    View Slide

  38. 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

    View Slide

  39. 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

    View Slide

  40. 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

    View Slide

  41. 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

    View Slide

  42. 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

    View Slide

  43. バグ修正だけ #と
    はなんだったのか
    42/59

    View Slide

  44. MySQL 8.1 vs MySQL 8.0.34
    こっちにリストしてるってことは8.0のツリーには入れなかったということなので、それは良いこと
    43/59

    View Slide

  45. 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

    View Slide

  46. 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

    View Slide

  47. 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

    View Slide

  48. 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

    View Slide

  49. 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

    View Slide

  50. 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

    View Slide

  51. 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

    View Slide

  52. 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

    View Slide

  53. 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

    View Slide

  54. 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

    View Slide

  55. 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

    View Slide

  56. 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

    View Slide

  57. 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

    View Slide

  58. 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

    View Slide

  59. 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

    View Slide

  60. Any Question
    and/or
    Suggestion?
    59/59

    View Slide