Slide 1
Slide 1 text
No content
Slide 2
Slide 2 text
Target
Audience
- Database Administrator
Especially MySQL DBA
- Developer
Who leads MySQL Migration
Slide 3
Slide 3 text
Goal - Get knowledge to migrate MySQL safely
Slide 4
Slide 4 text
Agenda
- Self Introduction
- MySQL Migration in LINE
- MySQL Upgrade Helper (MUH)
- Internal Operation for Safety MySQL Migration
- Summary
Slide 5
Slide 5 text
Self Introduction
Slide 6
Slide 6 text
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 …
Slide 7
Slide 7 text
MySQL Migration in LINE
Slide 8
Slide 8 text
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
Slide 9
Slide 9 text
Today's topic
MySQL Migration
Slide 10
Slide 10 text
Why do we have to do
MySQL Migration?
Slide 11
Slide 11 text
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
Slide 12
Slide 12 text
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
Slide 13
Slide 13 text
Currently we must migrate
1,800 instances by Jun. 2023 !?
Slide 14
Slide 14 text
How to do
MySQL Migration?
Slide 15
Slide 15 text
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
Slide 16
Slide 16 text
Piles of MySQL × Migration Tasks ×
Deadline = ???
Slide 17
Slide 17 text
!
Slide 18
Slide 18 text
Need to Automate Migrations!
Slide 19
Slide 19 text
MySQL Upgrade Helper (MUH)
Slide 20
Slide 20 text
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!
Slide 21
Slide 21 text
What can MUH do?
Before Migration
Slide 22
Slide 22 text
What can MUH do?
Set Replication
Slide 23
Slide 23 text
What can MUH do?
Switchover
Slide 24
Slide 24 text
Architecture
Slide 25
Slide 25 text
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
Slide 26
Slide 26 text
MUH History
⎯ 2020/10~ (900 migrations)
⎯ DBA can manage MySQL migration.
DBA-MUH
Slide 27
Slide 27 text
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
Slide 28
Slide 28 text
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
Slide 29
Slide 29 text
How can developers migrate MySQL safely
without DBA?
Slide 30
Slide 30 text
Workflow in MUH-Prod
Slide 31
Slide 31 text
Migration Steps
- Create New MySQL Instances
- Spec Selection API return optimized instance spec (CPU/Memory/Disk)
Slide 32
Slide 32 text
Register
Slide 33
Slide 33 text
Migration Steps
Just execute Steps from left to right
Slide 34
Slide 34 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 35
Slide 35 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 36
Slide 36 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 37
Slide 37 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 38
Slide 38 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 39
Slide 39 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 40
Slide 40 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 41
Slide 41 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 42
Slide 42 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 43
Slide 43 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 44
Slide 44 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 45
Slide 45 text
Migration Steps
Just execute Steps from left to right
Slide 46
Slide 46 text
Internal Operation
for Safety MySQL Migration
Slide 47
Slide 47 text
Inspection
Slide 48
Slide 48 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 49
Slide 49 text
Inspection
Purpose
- Inspect both MySQL configuration
- Request to DBA if there are any issues
- Can the Source and Target MySQL be migrated?
Slide 50
Slide 50 text
Inspection
Confirm the Source/Target MySQL can be migrated
Slide 51
Slide 51 text
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
Slide 52
Slide 52 text
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
Slide 53
Slide 53 text
time_zone
time_zone
default-time-zone
Reserved words https://mysql-params.tmtms.net/keyword
GTID Violation in mysqld.err
Inspection
Slide 54
Slide 54 text
Set Variables
Slide 55
Slide 55 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 56
Slide 56 text
Set Variables
Purpose
- Same parameters after migration
- Same SQL behavior after migration
- Set Target MySQL Variables like Source MySQL
Slide 57
Slide 57 text
Set Variables
Slide 58
Slide 58 text
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
Slide 59
Slide 59 text
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
Slide 60
Slide 60 text
Data Export/Import
Slide 61
Slide 61 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 62
Slide 62 text
Data Export/Import
Slide 63
Slide 63 text
Data Export/Import
How to Execute Safely
- Avoid Source/Target MySQL loads
- Avoid Target MySQL disk full
- Use logical backup for MySQL upgrade
Slide 64
Slide 64 text
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
Slide 65
Slide 65 text
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
Slide 66
Slide 66 text
Metadata Check
Slide 67
Slide 67 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 68
Slide 68 text
Metadata Check
Slide 69
Slide 69 text
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
Slide 70
Slide 70 text
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
Slide 71
Slide 71 text
Data Comparison
Slide 72
Slide 72 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 73
Slide 73 text
Data Comparison
Purpose
- Notice different rows between Source and Target
- Compare Source and Target MySQL data.
Slide 74
Slide 74 text
• Snapshot
• Comparing Data
Data Comparison
Workflow
Slide 75
Slide 75 text
#1
• Check Source&Target are replicating from
same Master
• Check no Replication delay
Snapshot
Slide 76
Slide 76 text
#2
• STOP SQL_THREAD;
Snapshot
Slide 77
Slide 77 text
#3
• Get Position/GTID from Master
• START SLAVE SQL_THREAD UNTIL ... ;
Snapshot
Slide 78
Slide 78 text
#4
• START TRANSACTION WITH
CONSISTENT SNAPSHOT;
Snapshot
Slide 79
Slide 79 text
#5
• START SLAVE SQL_THREAD;
• Ready for comparing
Snapshot
Slide 80
Slide 80 text
Comparing
⎯ Error if table doesn't have PK
Primary Key
Slide 81
Slide 81 text
Comparing
Comparing Rows
⎯ Error if table doesn't have PK
⎯ SELECT COUNT(*) FROM table;
Primary Key
Slide 82
Slide 82 text
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
Slide 83
Slide 83 text
Migration
Slide 84
Slide 84 text
Migration Steps
- Add User ACL
- Inspection
- Set Variables
- Prepare Data Import
- Data Import
- Check Metadata
- Start Replication
- Data Comparison
- Migration
- Enable HA
Slide 85
Slide 85 text
Online Migration
(without Maintenance)
Slide 86
Slide 86 text
Online Migration
Purpose
- Avoid data lost
- Keep service running
- Easy as possible
Slide 87
Slide 87 text
2 steps of Online Migration
- Switchover
- Prepare
Slide 88
Slide 88 text
Online Migration - Prepare
Slide 89
Slide 89 text
No content
Slide 90
Slide 90 text
No content
Slide 91
Slide 91 text
No content
Slide 92
Slide 92 text
Online Migration - Switchover
Slide 93
Slide 93 text
No content
Slide 94
Slide 94 text
No content
Slide 95
Slide 95 text
No content
Slide 96
Slide 96 text
No content
Slide 97
Slide 97 text
No content
Slide 98
Slide 98 text
Constraint of Online Migration
⎯ Source and Target MySQL must be same Port number
Same Port
Slide 99
Slide 99 text
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
Slide 100
Slide 100 text
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
Slide 101
Slide 101 text
Summary
Slide 102
Slide 102 text
Migration Steps
Just execute Steps from left to right
Easy! Safe!
Slide 103
Slide 103 text
Summary
- DBA reduces operation and communication costs!
- And…
- Developers can operate MySQL Migration safely without DBA!
Slide 104
Slide 104 text
My Boss become satisfied! !
Slide 105
Slide 105 text
Thank you!