Best Practices for Migrating MySQL to the Cloud

在本课程中,我们将回顾在将MySQL迁移到云中时要考虑的关键元素。我们将与全球许多不同的客户分享我们的工作经验,描述最有效的程序。
-IAAS与DBAA
-正在迁移数据
-本地和云之间的复制
-测试云环境
-负载平衡器
-高可用性
-监测
-备份

展开查看详情

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