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

SRG Study #4 そろそろMySQL8.0を考えませんか

Yuta Kikai
August 23, 2022

SRG Study #4 そろそろMySQL8.0を考えませんか

そろそろ5.7のEOLが見えてきた...
MySQL8.0に備えましょう。

Yuta Kikai

August 23, 2022
Tweet

More Decks by Yuta Kikai

Other Decks in Technology

Transcript

  1. MySQL8.0 について
 大きな変更点とか
 8.0では開発ポリシーが大きく変わりマイナーバージョンでも 新機能追加が行われる
 そのためマイナーバージョンアップグレードも慎重に検証が必要 
 近年の機能追加一部
 - 8.0.22

    
 - 非同期レプリのフェイルオーバー発生時にスレーブが新マスターに自動的に接続確立機能 
 - スキーマ単位でのREAD ONLY機能 
 - 8.0.23 
 - 非GTIDからGTIDへのレプリケーション機能 
 - Invisible Columns機能 
 - 8.0.27 マルチスレッドアプライアー(スレーブ)のデフォルト有効化 
 - 8.0.29 INSTANT DROP COLUMN機能追加、INSTANT ADD COLUMNの制限緩和 
 - 8.0.30 Generated Invisible Primary Keys機能追加 

  2. 8.0.22から置換された名称
 Master  →  Source
 Slave  →  Replica
 Muliti Thread Slave(MTS) → Multi

    Thread Applier(MTA)
 
 
 
 ※この勉強会では便宜上、Master,Slave呼称のまま進めます 

  3. どのMySQL8.0を使うか
 LINE Developper Meetup #73での発表より 
 - 8.0.29からINSTANT DROP COLUMN機能

    
 - デフォルトの挙動も変更になった 
 - その周辺のbug reportも積み上がっている 
 - その機能の回避が難しい 
 - LINE社はMySQL8.0.28を選択
 
 https://speakerdeck.com/line_developers/mysql80-which-we-choose 「ぼくらが選んだ次のMySQL 8.0 / MySQL80 Which We Choose」 P.37 より引用
  4. MySQL8.0.29以上はXtrabackupが実質利用不能
 8.0.29でINSTANT COLUMNの仕様変更でInnoDB REDO ログ形式も仕様変更 
 INSTANT ADD/DROP COLUMNしたテーブルが存在するとXtrabackupがエラーを出す 


    - すべてのDDLにALGORITHM=INPLACE/COPY;を指定しないとINSTANTになるので実質無理 
 
 
 INSTANT ADDしたテーブルもOPTIMIZE TABLE実行すれば実行は可能だが... 
 そもそもpercona-xtrabackup-80-8.0.29までしか出ていなくて8.0.30では実行できない 
 https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/ 
 [ERROR] [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns
  5. 本日紹介するMySQL8.0の機能
 - InnoDB Cluster
 - InnoDB ReplicaSet
 - CLONE PLUGIN


    - インスタントDDL
 - Invisible Index
 - EXPLAIN ANALYZE
 
 ※以下今日は扱わない新規能 
 ウィンドウ関数、共通テーブル式(CTE)、CHECK 制約 

  6. InnoDB Cluster
 MySQLのグループレプリケーション+MySQL Router+MySQL Shellを組み合わせた総称 
 - MySQL Shell
 -

    開発・運用を効率化するMySQL拡張クライアント 
 - 2017年に1.0.9 GAリリース 
 - MySQL Router
 - アプリケーションとMySQL間をルーティングするもの 
 - 2015年に2.0.2 GAリリース 
 - 両者ともMySQL8.0リリース後は バージョン表記を追従
 - 8.0からMySQL, MySQL Shell, MySQL Routerの一体感を強調 
 
                                      なので5.7でも構築できる 

  7. InnoDB Cluster
 MySQL1 MySQL2 MySQL3 MySQL間で レプリケーションと 相互監視 MySQL Router

    アプリケーション アプリはRouterにリクエスト Routerが分散してリクエスト
  8. InnoDB Clusterの例(シングルプライマリ)
 MySQL Shellからクラスタの作成 
 
 
 クラスタにノードを追加(8.0.17以降であればCLONE機能で自動でデータ同期される) 
 


    
 クラスタステータスでプライマリがどのDBか確認 
 
 # mysqlsh -uroot -h MySQL1 MySQL JS> cluster = dba.createCluster('testcluster') A new InnoDB cluster will be created on instance 'root@MySQL1:3306'. MySQL JS> cluster.addInstance('root@MySQL2') MySQL JS> cluster.addInstance('root@MySQL3') MySQL JS> cluster.status() ~省略~ "primary": "MySQL1:3306", ~省略~
  9. InnoDB Clusterの例(シングルプライマリ)
 MySQL Router初期設定で先ほどプライマリになっていたDBに接続する 
 
 
 接続するための情報が表示される 
 


    
 
 # mysqlrouter --bootstrap root@MySQL1 --user=mysqlrouter The following connection information can be used to connect to the cluster after MySQL Router has been started with generated configuration.. Classic MySQL protocol connections to cluster 'testcluster': - Read/Write Connections: localhost:6446 - Read/Only Connections: localhost:6447 ~省略~
  10. InnoDB ReplicaSet
 MySQL1 MySQL2 MySQL3 非同期 レプリケーション MySQL Router アプリケーション

    アプリはRouterにリクエスト Routerが分散してリクエスト
  11. InnoDB ReplicaSetの例
 MySQL Shellからレプリカセットの作成 
 
 
 レプリカセットにノードを追加 
 


    
 
 Routerの設定は先ほどのClusterの手順と同じ 
 # mysqlsh -uroot -h MySQL1 MySQL JS> dba.createReplicaSet("repl1") A new replicaset with instance 'MySQL1:3306' will be created. MySQL JS> dba.getReplicaSet().addInstance("root@MySQL2:3306”) MySQL JS> dba.getReplicaSet().addInstance("root@MySQL3:3306”)
  12. CLONE PLUGIN
 MySQL 8.0.17で追加された機能 
 既存のMySQLのデータから物理スナップショットをとる 
 ローカルコピーも他サーバからのクローンも可能 
 制限事項


    DDL実行中は実行できない 
 バイナリログはコピーされない 
 InnoDBのみ対象
 my.cnfなどの設定ファイルもコピーはされない 
 同じバージョン間のみ

  13. CLONE PLUGINの例
 ~ MySQL1のデータをMySQL2で復元 ~
 CLONE PLUGINの有効化 
 
 CLONEに必要な権限もったユーザを作成

    
 
 
 MySQL2でコピー元となるMySQL1の情報をドナーリストに登録 
 
 CLONE実行
 MYSQL1,2> INSTALL PLUGIN clone SONAME 'mysql_clone.so' MYSQL1,2> CREATE USER clone_user@"%" IDENTIFIED BY 'pass'; MYSQL1,2> GRANT BACKUP_ADMIN ON *.* TO clone_user@"%"; MySQL2> SET GLOBAL clone_valid_donor_list = 'MySQL1:3306'; MySQL2> CLONE INSTANCE FROM clone_user@MySQL1:3306 IDENTIFIED BY 'pass';
  14. CLONE PLUGIN速度比較
 DBサイズ24GBのVM環境でダンプ&リストア 
 
 コマンド ダンプ時間 リストア時間 mysqldump 3

    min 57sec 23 min 46 sec CLONE 1 min 57sec 1 min 55 sec MySQL Shell Dump Utility (8.0.21~) 2 min 36 sec 41 min 7 sec
  15. インスタントDDL
 ALTER TABLEでカラム追加などを行う際にメタデータの更新のみを行う機能 
 非常に高速だができる操作の制限・制約事項多め 
 • インデックスオプションの変更 • テーブル名の変更

    • SET/DROP DEFAULT • MODIFY COLUMN • virtual column の追加、削除 • カラム追加(8.0.28まではテーブル最後の列に追加する場合のみ可。8.0.29からどこでも可能に) • カラム削除 (8.0.29から) 
 8.0.29からこの記述がなくてもデフォルトが INSTANT
  16. EXPLAIN ANALYZE
 8.0.18で実装された機能 
 EXPLAIN ANALYZE は実際にクエリを実行して各ステップの実行時間を測定 
 
 cost

    推定実行コスト ( どれだけ重い処理なのか )  
 rows 返される行数の推定 
 actual time 最初の行とすべての行を取得するのにかかった時間(ms) 
 rows クエリで取得した実際の行数 
 loop 何回実行されたかのループ数 

  17. EXPLAIN ANALYZEはJOINの解析に便利
 mysql> EXPLAIN ANALYZE SELECT * FROM users u

    JOIN points p ON u.id = p.user_id WHERE u.id < 30;
 -> Nested loop inner join (cost=171768.65 rows=381192) (actual time=0.054..145.394 rows=26 loops=1) 
 -> Filter: (p.user_id < 30) (cost=38351.45 rows=381192) (actual time=0.037..145.318 rows=29 loops=1) 
 -> Table scan on p (cost=38351.45 rows=381192) (actual time=0.036..128.402 rows=381796 loops=1) 
 -> Single-row index lookup on u using PRIMARY (id=p.user_id) (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=29)
 結果をネストで表示してくれるので見やすい 

  18. EXPLAIN ANALYZEはJOINの解析に便利
 mysql> EXPLAIN ANALYZE SELECT * FROM users u

    JOIN points p ON u.id = p.user_id WHERE u.id < 30;
 -> Nested loop inner join (cost=171768.65 rows=381192) (actual time=0.054..145.394 rows=26 loops=1) 
 -> Filter: (p.user_id < 30) (cost=38351.45 rows=381192) (actual time=0.037..145.318 rows=29 loops=1) 
 -> Table scan on p (cost=38351.45 rows=381192) (actual time=0.036..128.402 rows=381796 loops=1) 
 -> Single-row index lookup on u using PRIMARY (id=p.user_id) (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=29)
 (1)pointsテーブルをテーブルスキャン
  19. EXPLAIN ANALYZEはJOINの解析に便利
 mysql> EXPLAIN ANALYZE SELECT * FROM users u

    JOIN points p ON u.id = p.user_id WHERE u.id < 30;
 -> Nested loop inner join (cost=171768.65 rows=381192) (actual time=0.054..145.394 rows=26 loops=1) 
 -> Filter: (p.user_id < 30) (cost=38351.45 rows=381192) (actual time=0.037..145.318 rows=29 loops=1)
 -> Table scan on p (cost=38351.45 rows=381192) (actual time=0.036..128.402 rows=381796 loops=1) 
 -> Single-row index lookup on u using PRIMARY (id=p.user_id) (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=29)
 (2) 先程のスキャン結果から user_id 30以下をフィルター   rows=29件
  20. EXPLAIN ANALYZEはJOINの解析に便利
 mysql> EXPLAIN ANALYZE SELECT * FROM users u

    JOIN points p ON u.id = p.user_id WHERE u.id < 30;
 -> Nested loop inner join (cost=171768.65 rows=381192) (actual time=0.054..145.394 rows=26 loops=1) 
 -> Filter: (p.user_id < 30) (cost=38351.45 rows=381192) (actual time=0.037..145.318 rows=29 loops=1)
 -> Table scan on p (cost=38351.45 rows=381192) (actual time=0.036..128.402 rows=381796 loops=1) 
 -> Single-row index lookup on u using PRIMARY (id=p.user_id) (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=29)
 (3) users テーブルをPRIMARY KEY使って検索   loops=29 なので29回実行されている
  21. EXPLAIN ANALYZEはJOINの解析に便利
 mysql> EXPLAIN ANALYZE SELECT * FROM users u

    JOIN points p ON u.id = p.user_id WHERE u.id < 30;
 -> Nested loop inner join (cost=171768.65 rows=381192) (actual time=0.054..145.394 rows=26 loops=1) 
 -> Filter: (p.user_id < 30) (cost=38351.45 rows=381192) (actual time=0.037..145.318 rows=29 loops=1)
 -> Table scan on p (cost=38351.45 rows=381192) (actual time=0.036..128.402 rows=381796 loops=1) 
 -> Single-row index lookup on u using PRIMARY (id=p.user_id) (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=29)
 (4) 2と3の結果をNested loopで結合
  22. 8.0からの変更で気をつけたいポイント1
 - default_authentication_plugin 
 - 5.7 mysql_native_password 
 - 8.0

    caching_sha2_password 
 - default charset
 - 5.7 latin1
 - 8.0 utf8mb4
 - default collation(照合順序) 
 - 5.7 utf8mb4_general_ci     🍣= 🍺問題
 - 8.0 utf8mb4_0900_ai_ci   「びょういん」= 「びよういん」問題 
 - GROUP BYのASC,DESC削除 
 - ちゃんとORDER BY指定する 
 - mysql_upgradeコマンドの非推奨化(8.0.16以降) 
 - 8.0初回起動時に同様の処理が走るようになった 
 - MySQL ShellのUpgradeChecker機能でアプグレチェック可能 

  23. 8.0からの変更で気をつけたいポイント2
 - sql_mode
 - 5.7 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISI ON_BY_ZERO,NO_AUTO_CREATE_USER ,NO_ENGINE_SUBSTITUTION 
 -

    8.0 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISI ON_BY_ZERO,NO_ENGINE_SUBSTITUTION 
 - ※参考
 - 5.6.6~ NO_ENGINE_SUBSTITUTION
 - 5.6.5以前 空白(モード設定なし)
 - innodb_autoinc_lock_mode
 - 5.7 = 1 (連続ロックモード) 
 - STATEMENTレプリでも安全に利用可能 
 - 8.0 = 2(インターリーブロックモード) 
 - MIXED,ROWで安全に利用可能で高速 バイナリログからのSQL適用は安全ではない 
 各sql_modeの意味はこちらの公式ドキュメントを参照
 https://dev.mysql.com/doc/refman/8.0/ja/sql-mode.html

  24. アップグレードの方法
 既存のスレーブを8.0に置き換える方法を以下のように検証
 - LBからスレーブ1台切り離す
 - 切り離したスレーブのMySQLを8.0にする
 - LBにスレーブを戻す
 - もう1台のスレーブを切り離して8.0にする


    - LBにスレーブを戻す
 - 8.0にしたスレーブをマスターに昇格する
 - 元マスターを切り戻し用に5.7のままスレーブにする
 
 アップグレード方式について詳細は以前のSRG Studyの資料を参照
 SRG Study #2 MySQLバージョンアップと パブリッククラウド移設

  25. アップグレードのコマンド 1
 MySQL 57repo削除・80repo追加 & GPG Keyインポート 
 # yum

    remove mysql57-community-release-el7-7.noarch # yum install https://dev.mysql.com/get/mysql80-community-release-el7-6.noarch.rpm # rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022 MySQL Shellのインストール 
 # yum install mysql-shell ※MySQL本体とMySQL Shellのバージョンはあわせる必要ないので5.7環境でもMySQL Shell8.0.30を入れる 
 MySQL Upgrade Checker機能で8.0にしても問題ないかチェック 
 # mysqlsh -uroot mysqlsh> util.checkForServerUpgrade()
  26. アップグレードのコマンド 3
 稼働中のMySQL5.7を安全に停止 
 # mysql -uroot -p mysql> set

    global innodb_fast_shutdown = 0; #低速シャットダウンを指定 mysql> select @@innodb_fast_shutdown; mysql> exit # systemctl stop mysqld
  27. アップグレードのコマンド 4
 8.0にアップグレード
 # version=8.0.30-1.el7.x86_64 # yum install mysql-community-{server,client,common,devel,libs,libs-compat}-${version} #

    yum list installed |grep mysql my.cnfに5.7との互換性のための設定を追加 
 # vim /etc/my.cnf character_set_server = utf8mb4 collation_server = utf8mb4_general_ci skip_character_set_client_handshake default_authentication_plugin = mysql_native_password MySQL8.0起動
 # systemctl start mysqld
  28. アップグレード(おまけ)
 charset, collationを変更していないと8.0→5.7でレプリエラー 
 Last_SQL_Error: Error 'Character set '#255' is

    not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file' on query. Default database: 'test'. Query: 'BEGIN’ default_authentication_pluginを変更していないと8.0→5.7でレプリエラー
 Error 'Plugin 'caching_sha2_password' is not loaded' on query. Default database: ''. Query: 'CREATE USER 'hoge'@'10.%.%.%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$6R\'Cg;SVYYu*
  29. Aurora MySQL Version 3
 Aurora v3 はMySQL8.0.23を元に作られている 
 現時点ではAurora v3のマイナーバージョンが上がっても8.0.23は変わらず

    
 
 MySQL8.0マイナーバージョンの新機能追従はAWSの中の人大変そうだなぁ
 春に発表されたAurora Serverless v2を使うには version 3.02.0が必要 

  30. sql_mode
 - v2,v3ともにデフォルトは0なので規制なし(MySQL 5.5と同じ) 
 - 一方MySQL8.0ではデフォは規制厳しめ 
 - Aurora移行が視野に入っているならMySQL8.0も0にすることも検討

    
 default_authentication_plugin 
 - Aurora v3 はmysql_native_passwordがデフォで変更不可 
 
 その他詳細は公式ドキュメント参照 
 https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.MySQL80.html#Aurora.AuroraMySQL.Compare-80- v3
 Aurora MySQL v3とMySQL8.0の違い(の一部)

  31. Aurora MySQL v2からv3にアップグレード
 2022年8月現在の注意点  
 v2からv3のインプレースアップグレード機能は現時点では 未対応
 2022/09/26 インプレースアップグレード対応されました 


    バックトラック機能はv3ではまだ 未対応
 これが有効になってるv2のスナップショットからの復元もできない 
 Percona XtraBackupで取得したバックアップデータのv3への展開まだ 未対応
 Aurora v3のLTSバージョンも現時点で 出ていない
  ※v2のLTSバージョンはupdates 2021-11-24 (version 2.07.7) 
 現時点で利用できるアップグレード手順を紹介 

  32. - v2クラスタのスナップショット取得してv3クラスタで復元 
 - メリット
 - 最小限の操作でアップグレード可能 
 - デメリット


    - サービスのメンテインが長くなりがち 
 
 - v2クラスタ→ v3クラスタ間でレプリケーション 
 - メリット
 - メンテイン時間を最小に抑えられる 
 - デメリット
 - レプリケーションを組む手間と費用がかかる 
 この2つの方法が考えられる 
 【追記】インプレースアップグレード対応したので上記含めて3つの方法となった 
 Aurora MySQL v2からv3にアップグレード

  33. 1. サービスをメンテナンスモードにする等でDBの書き込みを停止 
 2. DBインスタンスを選択して「スナップショットの取得」 
 
 
 
 


    3. スナップショットページから「スナップショットを復元」 
 
 v2クラスタのスナップショット取得してv3クラスタで復元1
 クラスタA Aurora v2(5.7) クラスタB Aurora v3(8.0) アプリケーション アプリのDB読み書きの向 き先
  34. 4. Aurora MySQL 3.02.0を選択 
 5. その他も入力して「クラスターを作成」 
 6. アプリケーションのDB向き先を新クラスターに

    
 7. サービスのメンテナンスモード解除 
 
 v2クラスタのスナップショット取得してv3クラスタで復元2

  35. 1. クラスタAからスナップショットの復元の手順でv3クラスタ(クラスタB)を作成 
 2. クラスタAのクラスタパラメータグループでbinlogとgtid-modeを有効化する 
 a. binlog_format: MIXED 


    b. enforce_gtid_consistency: ON
 c. gtid-mode: ON
 3. 適用のためクラスタAのライターインスタンスを再起動する 
 4. クラスタAのバイナリログの保持期間を24時間にする 
 
 5. クラスタBのクラスタパタメータグループでbinlogとgtid-modeを有効化する(上と同様) 
 6. 適用のためクラスタBのライターインスタンスを再起動する 
 v2クラスタ→ v3クラスタ間でレプリケーション2
 
 mysqlA> call mysql.rds_set_configuration('binlog retention hours', 24);
  36. 7. クラスタAでレプリ用のユーザ追加 
 
 8. クラスタBでレプリケーション設定 
 
 
 9.

    レプリ完成
 10. サービスメンテ日にアプリの向き先をクラスタBに切り替える 
 11. ※切り戻し用にクラスタB→クラスタAの逆方向レプリを貼る場合は前章のcollation設定等に注意
 mysqlA> CREATE USER 'mysqlrepl'@'%' IDENTIFIED BY 'mysqlrepl'; mysqlA> GRANT REPLICATION SLAVE ON *.* TO 'mysqlrepl'@'%'; mysqlB> CALL mysql.rds_set_external_source_with_auto_position ('クラスタAのライターエンドポイント ',3306,'mysqlrepl','mysqlrepl',0); mysqlB> call mysql.rds_start_replication; v2クラスタ→ v3クラスタ間でレプリケーション3
 

  37. まとめ
 • MySQL5.7のEOLは2023年10月、Aurora v2(5.7)も最速では 2024年2月
 
 • MySQL8.0は新機能盛り盛りだがアップグレード時の罠に注意 
 


    • 運用するなら8.0.28を選択したい
 
 • Aurora v3は未対応機能いくつかあるがv2からのアップグレードの道はある 
 
 • そろそろMySQL5.7(Aurora v2)からアップグレードを考え始めてみてもいいのでは! 

  38. 参考資料 MySQL 8.0: InnoDB now supports Instant ADD COLUMN Aurora.VersionPolicy.MajorVersionLifetime

    塩漬けにしている MySQL 8.0.xxをバージョンアップしたくなる、ここ数年での MySQL 8.0の改善点 / MySQL Update 202208 MySQL Shellを使ってもっと楽をしようの会 Aurora MySQL long-term support (LTS) releases MySQL 8.0 で追加、非推奨または削除されたサーバーおよびステータスの変数とオプション Aurora MySQL バージョン 2 と 3 の特徴の違い Use the TempTable storage engine on Amazon RDS for MySQL and Amazon Aurora MySQL New temporary table behavior in Aurora MySQL version 3 MySQLのEXPLAIN ANALYZEの読み方を勉強したよ