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

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

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

Tech-Verse2022
PRO

November 17, 2022
Tweet

More Decks by Tech-Verse2022

Other Decks in Technology

Transcript

  1. None
  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

  89. None
  90. None
  91. None
  92. Online Migration - Switchover

  93. None
  94. None
  95. None
  96. None
  97. None
  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!