Best Practices for Migrating MySQL to the Cloud

1 .Best Practices for Migrating MySQL to the Cloud Juan Pablo Arruti Percona 

2 .Agenda ● IaaS vs DBaaS ● Migrating Data ● Replication between On-Premises and Cloud ● Testing Cloud Environments ● High Availability ● Monitoring ● Backups 2 

3 .IaaS vs DBaaS 

4 .What is IaaS? Infrastructure as a Service (IaaS) ● Fundamentals Compute Resources ○ Servers, Storage, Network ● Provisioned and managed over the internet ● Complete control of servers ● AWS Services ○ EC2, EBS, VPC 4 

5 .And DBaaS? Database as a Service (DBaaS) ● Provides database service (Instance or Cluster) ● Targeted to easily ○ Setup, Operate, Scale ● Manages common administration tasks ○ Backups, Patching, Failure Detection, Failover ● No OS access ● No Super privilege ● AWS ○ RDS, DynamoDB, Redshift 5 

6 .Pros and Cons of IaaS Pros ● More control and flexibility ● Wide Instance Types ● Cheaper than RDS Cons ● More operational work 6 

7 .Pros and Cons of DBaaS Pros ● Easy to Manage ● Less operational tasks Cons ● Less control and flexibility ● More expensive ● Limited Instance types 7 

8 .Which Do You Choose? IaaS ● Database needs specific tuning or feature ● Available resources for operational tasks DBaaS ● Need focus on data and code ● Generic setups are okay 8 

9 .Migrating Data 

10 .Best Practices ● Make it simple ● Migrate to same or higher minor version ● Avoid major version upgrades 1 0 

11 .Migrating Data to IaaS ● Similar to on-premises databases ● Use Physical Backups for large databases ○ XtraBackup, Cold Backups ● Logical Backups for small databases 1 1 

12 .Migrating Data to DBaaS ● Logical Backups ○ Access through MySQL Client ○ mysqldump, mysqlpump, mydumper ● Physical Backups are possible ○ XtraBackup* Only available for AWS RDS 1 2 

13 .Migrating Data to DBaaS Available MySQL Client Tools ● mysqldump ○ Most adopted tool ○ Single-threaded ● mysqlpump ○ Introduced in MySQL 5.7 ○ Parallel backups ○ Restores are Single-threaded ● mydumper/myloader ○ Parallel backups and restores 1 3 

14 .Migrating Data to DBaaS Best Practices for MySQL Clients ● Export all objects first ○ --no-data --routines --events --triggers ● Then export only data ○ --no-create-info --no-create-db ○ --routines=no --events=no --triggers=no ● Enable log_bin_trust_function_creators if log_bin=1 ● Change object definer ○ DEFINER=`user`@`host` ● Force load and check all errors ○ --force 1 4 

15 .Migrating Data to RDS Best Practices for MySQL Clients ● Increase max_allowed_packet (Default 4 MB) ● time_zone can be modified in parameter group (Default UTC) ○ RDS uses mysql schema Time Zone Tables ○ Recommended ■ Set session time_zone to match source database 1 5 

16 .Migrating Data to RDS Speeding Up Logical Restore ● EC2 and RDS in same AZ ● Disable Multi-AZ ● Increase IOPS ● Modify Default Settings ○ Relax Durability ■ sync_binlog != 1 ■ innodb_flush_log_at_trx_commit != 1 ○ Tune InnoDB ■ Increase innodb_log_file_size (Default 128 MB) ■ Increase innodb_buffer_pool_size (Default DBInstanceClassMemory*3/4) 1 6 

17 .Restore Amazon RDS from Xtrabackup ● Overview ○ Take backup from database ○ Upload into S3 bucket ○ Create new instance from the backup ■ Amazon MySQL RDS ■ Amazon Aurora MySQL 1 7 

18 .Restore Amazon RDS from Xtrabackup ● Limitations ○ Supported MySQL 5.6 and 5.7 ○ Source/Target major versions must match ■ Target minor version must be higher ○ Source tables defined within default datadir ○ 6 TB database size limit ○ Source database can't be encrypted ○ User accounts, functions, stored procedures and time zone info are not imported automatically 1 8 

19 . Replication Between On-Premises and Cloud 

20 .Replicating to the Cloud ● IaaS ○ Same as replication in on-premises ● DBaaS ○ Implementation and its limitations depends on the cloud provider 2 0 

21 .Replicating to the Cloud Best Practices ● If latency is high ○ Use compression for Master/Slave protocol ■ slave_compressed_protocol=1 ○ Monitor replication lag with pt-heartbeat ● Ensure tables have Primary Key ○ binlog_format = ROW 2 1 

22 .External Master on AWS RDS ● Easy to set ● NO binlog_format constraints (MIXED, ROW, STATEMENT) ○ Recommended ROW to avoid time_zone mismatch ● Log File Position or GTID based ● No filtered Replication is allowed ● Replication administration using procedures ○ mysql.rds_set_external_master ○ mysql.rds_start_replication ... 2 2 

23 .Testing Cloud Environments 

24 .Why Benchmark Cloud? ● Cloud resources may not map directly ● Validate if cloud instance is able to handle traffic ● Choose between IaaS and DBaaS ● Available tools ○ sysbench, pt-upgrade, Query Playback, ProxySQL 2 4 

25 .Query Playback Key aspects ● Percona Labs GitHub repository ○ No active development ● Executes Queries in logs ○ Slow Query log, General log ● Compares execution results with Log ● Servers data should be consistent ● NO read-only option ● Multi-threaded ○ Queries executed at arrival time 2 5 

26 .Query Playback Example Report SELECT c FROM sbtest37 WHERE id=505; --> thread 67 slower query was run in 86 microseconds instead of 34 Detailed Report ---------------- SELECTs : 1858522 queries (19297 faster, 1839225 slower) ... Report ------ Executed 2161872 queries Spent 00:09:08.631886 executing queries versus an expected 00:04:43.697328 time. 23610 queries were quicker than expected, 2138262 were slower A total of 0 queries had errors. Expected 40606531 rows, got 40606533 (a difference of 2) Number of queries where number of rows differed: 2. Average of 113782.74 queries per connection (19 connections). 2 6 

27 .ProxySQL What is ProxySQL? ● GPL High Performance MySQL Proxy ● MySQL Protocol Aware ○ Clients connect to ProxySQL ○ Requests are evaluated ○ Actions are performed ■ Routing, Re-write, Mirroring 2 7 

28 .ProxySQL Query Mirroring How does it work? ● Each client executes a query ● ProxySQL receives each query ● Query Processor identifies if the query is Mirrored ● Associates the Query to a Thread Pool ● Executes each Query in the Pool 2 8 

29 .ProxySQL Query Mirroring 2 9