Our Automation Tool for Migrating 1,800 MySQL Instances in Only Six Months

Yoshinobu Harachi (LINE / MySQL1 team / MySQL DBA)

https://tech-verse.me/ja/sessions/61
https://tech-verse.me/en/sessions/61
https://tech-verse.me/ko/sessions/61

Transcript

2. Target Audience - Database Administrator Especially MySQL DBA - Developer Who leads MySQL Migration
3. Goal - Get knowledge to migrate MySQL safely
4. Agenda - Self Introduction - MySQL Migration in LINE - MySQL Upgrade Helper (MUH) - Internal Operation for Safety MySQL Migration - Summary
5. Self Introduction
6. Yoshinobu Harachi @yy_harachi - LINE Mid-Career (2020~) - DBA, ITSC DB dept MySQL1 team - MySQL Operation - Develop Operation Tools (PHP/Go) - Funny Experience - Won 2t Truck Driving Competition in my school days Hello, I'm …
7. MySQL Migration in LINE
8. 6,000+ MySQL Instances in LINE Number of MySQL Instances 0 1000 2000 3000 4000 5000 6000 7000 2016 2017 2018 2019 2020 2021 2022
9. Today's topic MySQL Migration
10. Why do we have to do MySQL Migration?
11. HW Scale Up New MySQL Platform in Verda MySQL Operation ⎯ 5.6 → 5.7 ⎯ 5.7 → 8.0 ⎯ Need more performance ⎯ Support for data growth ⎯ Load distribution ⎯ Commonize operations ⎯ Need more utilities MySQL Version Up Database Sharding
12. MySQL 5.7 EOL CentOS6 EOS Closing Old MySQL Platform Responsibility with Deadline ⎯ Feb. 2021 ⎯ Oct. 2023 ⎯ Instances will be power-off ⎯ Not supported any more MySQL 5.6 EOL IDC Room Renovation ⎯ Nov. 2020
13. Currently we must migrate 1,800 instances by Jun. 2023 !?
14. How to do MySQL Migration?
15. Migration Tasks - Create New MySQL Instances - Add User ACL - Set MySQL Variables - Export/Import Data - Start Replication - Compare Data - Inspect Query Performance - Switch to New MySQL (in Midnight...?) - Shutdown Old MySQL
16. Piles of MySQL × Migration Tasks × Deadline = ???
17. !
18. Need to Automate Migrations!
19. MySQL Upgrade Helper (MUH)
20. My Boss said … • Manual operation is dangerous • Operate MySQL migration via web browser • Everyone can do MySQL migration safely I want to do migration without SSH login!
21. What can MUH do? Before Migration
22. What can MUH do? Set Replication
23. What can MUH do? Switchover
24. Architecture
25. MUH API MySQL Query Replayer (MQR) Simple-Switchover API Role ⎯ `CREATE USER` for MUH ⎯ Inspect Configuration ⎯ Set Variables ⎯ Export/Import Data ⎯ Start Replication ⎯ Check Metadata ⎯ Compare Data ⎯ etc. ⎯ Get/Set Variables ⎯ Get MySQL Status ⎯ etc. ⎯ Inspect Query Performance in Target MySQL ⎯ Switchover to Target MySQL Grant User API MyInfo API
26. MUH History ⎯ 2020/10~ (900 migrations) ⎯ DBA can manage MySQL migration. DBA-MUH
27. MUH History Too many MySQL migrations… ⎯ 2020/10~ (900 migrations) ⎯ DBA can manage MySQL migration. ⎯ Increasing MySQL instances ⎯ CentOS6 EOS / IDC room renovation / Closing old Verda platform DBA-MUH
28. MUH History Too many MySQL migrations… MUH-Prod ⎯ 2020/10~ (900 migrations) ⎯ DBA can manage MySQL migration. ⎯ Increasing MySQL instances ⎯ CentOS6 EOS / IDC room renovation / Closing old Verda platform ⎯ 2022/11~ ⎯ Developers can migrate MySQL themselves. DBA-MUH
29. How can developers migrate MySQL safely without DBA?
30. Workflow in MUH-Prod
31. Migration Steps - Create New MySQL Instances - Spec Selection API return optimized instance spec (CPU/Memory/Disk)
32. Register
33. Migration Steps Just execute Steps from left to right
34. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
35. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
36. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
37. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
38. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
39. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
40. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
41. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
42. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
43. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
44. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
45. Migration Steps Just execute Steps from left to right
46. Internal Operation for Safety MySQL Migration
47. Inspection
48. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
49. Inspection Purpose - Inspect both MySQL configuration - Request to DBA if there are any issues - Can the Source and Target MySQL be migrated?
50. Inspection Confirm the Source/Target MySQL can be migrated
51. Non-Primary Key Table i_s.TABLES i_s.COLUMNS WHERE COLUMN_KEY='PRI' Non-InnoDB Table i_s.TABLES WHERE ENGINE != 'InnoDB' DEFINER ACL Exists TRIGGER/PROCEDURE/FUNCTION/VIEW/EVENT DEFINER ACL must exist in Target MySQL Inspection
52. DROP SUPER_priv User mysql.user WHERE Super_priv = 'Y' lower_case_table_names != 0 https://dev.mysql.com/doc/refman/8.0/ja/identifier- case-sensitivity.html FTS Parameters innodb_ft_enable_stopword innodb_ft_min_token_size innodb_ft_total_cache_size innodb_ft_result_cache_limit mecab_rc_file ngram_token_size Inspection
53. time_zone time_zone default-time-zone Reserved words https://mysql-params.tmtms.net/keyword GTID Violation in mysqld.err Inspection
54. Set Variables
55. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
56. Set Variables Purpose - Same parameters after migration - Same SQL behavior after migration - Set Target MySQL Variables like Source MySQL
57. Set Variables
58. sql_mode Affects behavior of SQL character_set_server Affects result set collation_server Affects result set explicit_defaults_for_timestamp Affects TIMESTAMP type max_connections Target should be rather than Source MySQL lock_wait_timeout Metadata Lock innodb_lock_wait_timeout InnoDB row lock Set Variables
59. sql_mode Affects behavior of SQL character_set_server Affects result set collation_server Affects result set explicit_defaults_for_timestamp Affects TIMESTAMP type max_connections Target should be rather than Source MySQL lock_wait_timeout Metadata Lock innodb_lock_wait_timeout InnoDB row lock Set Variables
60. Data Export/Import
61. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
62. Data Export/Import
63. Data Export/Import How to Execute Safely - Avoid Source/Target MySQL loads - Avoid Target MySQL disk full - Use logical backup for MySQL upgrade
64. Data Export/Import Reasons for Adoption ⎯ https://github.com/mydumper/mydumper ⎯ Faster than mysqldump because it can be perform in parallel. ⎯ MySQL Shell's dump loading utility did not support MySQL 5.6 at that time. MyDumper
65. Data Export/Import Reasons for Adoption Ingenuity ⎯ https://github.com/mydumper/mydumper ⎯ Faster than mysqldump because it can be perform in parallel. ⎯ MySQL Shell's dump loading utility did not support MySQL 5.6 at that time. ⎯ Dynamically change the number of threads with the number of CPUs. ⎯ Purging binlogs and turn off slow query log to free up space in Target MySQL. MyDumper
66. Metadata Check
67. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
68. Metadata Check
69. SCHEMA SELECT TABLE_SCHEMA, TABLE_NAME, IFNULL(TABLE_COLLATION,'none') FROM TABLES WHERE TABLE_TYPE='VIEW' AND TABLE_SCHEMA=? TABLE SELECT TABLE_SCHEMA, TABLE_NAME, IFNULL(TABLE_COLLATION, 'none') FROM TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA=? VIEW SELECT SCHEMA_NAME, SCHEMA_NAME, IFNULL(DEFAULT_COLLATION_NAME, 'none') FROM SCHEMATA WHERE SCHEMA_NAME=? TRIGGER SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DATABASE_COLLATION FROM TRIGGERS WHERE TRIGGER_SCHEMA=? ROUTINE SELECT ROUTINE_SCHEMA, ROUTINE_NAME, DATABASE_COLLATION FROM ROUTINES WHERE ROUTINE_SCHEMA=? EVENT SELECT EVENT_SCHEMA, EVENT_NAME, DATABASE_COLLATION FROM EVENTS WHERE EVENT_SCHEMA=? Metadata Check Get Metadata from information_schema
70. TABLE SHOW CREATE TABLE INDEX SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX), MAX(INDEX_TYPE) columns, MAX(NON_UNIQUE) FROM statistics WHERE TABLE_SCHEMA=? AND TABLE_NAME=? GROUP BY INDEX_NAME PARTITION SELECT CONCAT(PARTITION_NAME,':',IFNULL(SUBPARTITION_NAME,'none')), PARTITION_NAME, IFNULL(SUBPARTITION_NAME,’none’), IFNULL(PARTITION_METHOD,'none’), UPPER(IFNULL(PARTITION_EXPRESSION,'none’)) FROM partitions WHERE table_schema=? and table_name=? and PARTITION_NAME is not null COLUMN SELECT COLUMN_NAME, IFNULL(COLUMN_DEFAULT,'no_defaults'), IS_NULLABLE, IFNULL(CHARACTER_SET_NAME,'none'), IFNULL(COLLATION_NAME,'none'), DATA_TYPE, ORDINAL_POSITION, IFNULL(CHARACTER_MAXIMUM_LENGTH,0) FROM COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME=? ORDER BY ORDINAL_POSITION FOREIGN KEY SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM TABLE_CONSTRAINTS WHERE TABLE_SCHEMA=? AND TABLE_NAME=? Metadata Check About Table
71. Data Comparison
72. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
73. Data Comparison Purpose - Notice different rows between Source and Target - Compare Source and Target MySQL data.
74. • Snapshot • Comparing Data Data Comparison Workflow
75. #1 • Check Source&Target are replicating from same Master • Check no Replication delay Snapshot
76. #2 • STOP SQL_THREAD; Snapshot
77. #3 • Get Position/GTID from Master • START SLAVE SQL_THREAD UNTIL ... ; Snapshot
78. #4 • START TRANSACTION WITH CONSISTENT SNAPSHOT; Snapshot
79. #5 • START SLAVE SQL_THREAD; • Ready for comparing Snapshot
80. Comparing ⎯ Error if table doesn't have PK Primary Key
81. Comparing Comparing Rows ⎯ Error if table doesn't have PK ⎯ SELECT COUNT(*) FROM table; Primary Key
82. Comparing Comparing Rows Comparing Hash Value ⎯ Error if table doesn't have PK ⎯ SELECT COUNT(*) FROM table; ⎯ SELECT SHA2(CONCAT_WS('|', col1, col2…)) FROM table ORDER BY PK ASC/DESC LIMIT 500000; Primary Key
83. Migration
84. Migration Steps - Add User ACL - Inspection - Set Variables - Prepare Data Import - Data Import - Check Metadata - Start Replication - Data Comparison - Migration - Enable HA
85. Online Migration (without Maintenance)
86. Online Migration Purpose - Avoid data lost - Keep service running - Easy as possible
87. 2 steps of Online Migration - Switchover - Prepare
88. Online Migration - Prepare
92. Online Migration - Switchover
98. Constraint of Online Migration ⎯ Source and Target MySQL must be same Port number Same Port
99. Constraint of Online Migration Same User ACL ⎯ Source and Target MySQL must be same Port number ⎯ Source and Target MySQL's User ACL must be same User/Host/Password/Privileges Same Port
100. Constraint of Online Migration Same User ACL Disable DNS Cache ⎯ Source and Target MySQL must be same Port number ⎯ Source and Target MySQL's User ACL must be same User/Host/Password/Privileges ⎯ Apps will continue to query Source MySQL after Switchover Same Port
101. Summary
102. Migration Steps Just execute Steps from left to right Easy! Safe!
103. Summary - DBA reduces operation and communication costs! - And… - Developers can operate MySQL Migration safely without DBA!
104. My Boss become satisfied! !
105. Thank you!