Share
Embed
Transcript
76 slides
-
アジェンダ - MySQLのバージョンについて - MySQL 8.0について - MySQL5.7から8.0にアップグレード - Aurora
v2からAurora v3のアップグレード - まとめ -
MySQLのバージョンについて MySQLバージョン Extended Support End 5.5.x 2018/12 5.6.x 2021/2 5.7.x
2023/10 8.0.x 2026/4 MySQL5.7 EOLが近づいています -
MySQL8.0 概要 「MySQL5.7より2倍高速」とOracle公式が言っているデータベース GAから4年経つがメディア内でもまだ採用実績は少ない 2016年9月 MySQL8.0.0 リリース
2018年4月 MySQL8.0.11 GAリリース 2022年7月 MySQL8.0.30 リリース 現時点で最新 -
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機能追加 -
8.0.22から置換された名称 Master → Source Slave → Replica Muliti Thread Slave(MTS) → Multi
Thread Applier(MTA) ※この勉強会では便宜上、Master,Slave呼称のまま進めます -
どの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 より引用 -
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 -
本日紹介するMySQL8.0の機能 - InnoDB Cluster - InnoDB ReplicaSet - CLONE PLUGIN
- インスタントDDL - Invisible Index - EXPLAIN ANALYZE ※以下今日は扱わない新規能 ウィンドウ関数、共通テーブル式(CTE)、CHECK 制約 -
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でも構築できる -
InnoDB Cluster MySQL1 MySQL2 MySQL3 MySQL間で レプリケーションと 相互監視 MySQL Router
アプリケーション アプリはRouterにリクエスト Routerが分散してリクエスト -
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", ~省略~ -
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 ~省略~ -
InnoDB Clusterのメリットデメリット メリット 冗長化構成のためにサードパーティーソフトウェア不要 参照分散のためのロードバランサーが不要 データ複製もCLONE機能で容易 デメリット
最大構成数は9ノード 書き込み性能は非同期レプリより数段遅い -
InnoDB ReplicaSet MySQL1 MySQL2 MySQL3 非同期 レプリケーション MySQL Router アプリケーション
アプリはRouterにリクエスト Routerが分散してリクエスト -
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”) -
InnoDB ReplicaSetのメリットデメリット メリット 参照分散のためのロードバランサーが不要 データ複製もCLONE機能で容易 デメリット 冗長化構成にはサードパーティソフトウェアが必要
※プライベートクラウド機能が充実している弊社ではレプリカセットを使う意義は薄そう -
CLONE PLUGIN MySQL 8.0.17で追加された機能 既存のMySQLのデータから物理スナップショットをとる ローカルコピーも他サーバからのクローンも可能 制限事項
DDL実行中は実行できない バイナリログはコピーされない InnoDBのみ対象 my.cnfなどの設定ファイルもコピーはされない 同じバージョン間のみ -
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'; -
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 -
インスタントDDL ALTER TABLEでカラム追加などを行う際にメタデータの更新のみを行う機能 非常に高速だができる操作の制限・制約事項多め • インデックスオプションの変更 • テーブル名の変更
• SET/DROP DEFAULT • MODIFY COLUMN • virtual column の追加、削除 • カラム追加(8.0.28まではテーブル最後の列に追加する場合のみ可。8.0.29からどこでも可能に) • カラム削除 (8.0.29から) 8.0.29からこの記述がなくてもデフォルトが INSTANT -
EXPLAIN ANALYZE 8.0.18で実装された機能 EXPLAIN ANALYZE は実際にクエリを実行して各ステップの実行時間を測定 cost
推定実行コスト ( どれだけ重い処理なのか ) rows 返される行数の推定 actual time 最初の行とすべての行を取得するのにかかった時間(ms) rows クエリで取得した実際の行数 loop 何回実行されたかのループ数 -
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) 結果をネストで表示してくれるので見やすい -
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テーブルをテーブルスキャン -
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件 -
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回実行されている -
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で結合 -
検証の前提 環境 MySQL5.7.28(2019 年 10月) 以降バージョン ※上記バージョンからMySQL内部でOpenSSL利用が必須になっており、 これ未満のバージョンではMySQL8.0→5.7の切り戻し用レプリが貼れないため
上記より古いバージョンの5.7利用中のところは最新マイナーのMySQL 5.7.39まで上げましょう -
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機能でアプグレチェック可能 -
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 -
アップグレードの方法 既存のスレーブを8.0に置き換える方法を以下のように検証 - LBからスレーブ1台切り離す - 切り離したスレーブのMySQLを8.0にする - LBにスレーブを戻す - もう1台のスレーブを切り離して8.0にする
- LBにスレーブを戻す - 8.0にしたスレーブをマスターに昇格する - 元マスターを切り戻し用に5.7のままスレーブにする アップグレード方式について詳細は以前のSRG Studyの資料を参照 SRG Study #2 MySQLバージョンアップと パブリッククラウド移設 -
アップグレードのコマンド 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() -
アップグレードのコマンド 3 稼働中のMySQL5.7を安全に停止 # mysql -uroot -p mysql> set
global innodb_fast_shutdown = 0; #低速シャットダウンを指定 mysql> select @@innodb_fast_shutdown; mysql> exit # systemctl stop mysqld -
アップグレードのコマンド 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 -
アップグレード(おまけ) 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* -
Aurora MySQL Version 3 Aurora v3 はMySQL8.0.23を元に作られている 現時点ではAurora v3のマイナーバージョンが上がっても8.0.23は変わらず
MySQL8.0マイナーバージョンの新機能追従はAWSの中の人大変そうだなぁ 春に発表されたAurora Serverless v2を使うには version 3.02.0が必要 -
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の違い(の一部) -
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) 現時点で利用できるアップグレード手順を紹介 -
- v2クラスタのスナップショット取得してv3クラスタで復元 - メリット - 最小限の操作でアップグレード可能 - デメリット
- サービスのメンテインが長くなりがち - v2クラスタ→ v3クラスタ間でレプリケーション - メリット - メンテイン時間を最小に抑えられる - デメリット - レプリケーションを組む手間と費用がかかる この2つの方法が考えられる 【追記】インプレースアップグレード対応したので上記含めて3つの方法となった Aurora MySQL v2からv3にアップグレード -
1. サービスをメンテナンスモードにする等でDBの書き込みを停止 2. DBインスタンスを選択して「スナップショットの取得」
3. スナップショットページから「スナップショットを復元」 v2クラスタのスナップショット取得してv3クラスタで復元1 クラスタA Aurora v2(5.7) クラスタB Aurora v3(8.0) アプリケーション アプリのDB読み書きの向 き先 -
4. Aurora MySQL 3.02.0を選択 5. その他も入力して「クラスターを作成」 6. アプリケーションのDB向き先を新クラスターに
7. サービスのメンテナンスモード解除 v2クラスタのスナップショット取得してv3クラスタで復元2 -
v2クラスタ→ v3クラスタ間でレプリケーション1 v2,v3のクラスタ間でレプリケーション貼って向き先をスイッチオーバーする方法 クラスタA Aurora v2(5.7) クラスタB Aurora v3(8.0)
アプリケーション バイナリログレプリケーション アプリのDB読み書きの向き先 -
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); -
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 -
【追記】v3にインプレースアップグレード2 1. アップグレードするクラスタを選択して「変更」 2. エンジンバージョンをAurora MySQL 3.02.0を選択 3.
変更をすぐに適用するかメンテナンス時に適用するか選択 -
【追記】v3にインプレースアップグレード3 未対応のインスタンスタイプだとエラーが出るので注意 db.t3の場合medium以上が必要だったので、事前にインスタンスタイプを上げる必要がある
db.r5 , db.r6gの場合は特に最小インスタンスサイズも変わってなさそうに見えるので問題なさそう -
まとめ • MySQL5.7のEOLは2023年10月、Aurora v2(5.7)も最速では 2024年2月 • MySQL8.0は新機能盛り盛りだがアップグレード時の罠に注意
• 運用するなら8.0.28を選択したい • Aurora v3は未対応機能いくつかあるがv2からのアップグレードの道はある • そろそろMySQL5.7(Aurora v2)からアップグレードを考え始めてみてもいいのでは! -
参考資料 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の読み方を勉強したよ