Migrating Aurora Monitoring PMM

AmazonWebServices(AWS)Aurora是最流行的基于云的RDBMS解决方案之一。Aurora成功的主要原因是它基于InnoDB存储引擎。在本课程中,我们将讨论如何使用Terraform和Percona产品和解决方案有效地计划迁移到Aurora。我们将共享用于启动AWS Aurora集群的地形代码,查看检查数据一致性的技巧,验证迁移路径,并使用PMM有效地监控环境。本课程的主题包括:为什么选择AWS Aurora?AWS Aurora的未来是什么?-构建Aurora基础设施-使用TerraForm(无数据)-使用TerraForm和Percona Xtrabackup恢复(使用AWS S3 Bucket)-验证数据一致性-Aurora迁移-1:1迁移-多:1迁移使用Percona服务器多源复制-显示基准和PMM仪表板-演示


1.Migrating to Aurora MySQL and Monitoring with PMM Percona Technical Webinars August 1, 2018


3.Introduction Vineet Khanna (Autodesk) Tate McDaniel (Percona) Senior Database Engineer Senior MySQL DBA vineet.khanna@autodesk.com tate.mcdaniel@percona.com 3

4.This talk’s agenda

5.Agenda What is this talk about? ● A real life migration from EC2 backed MySQL instances to managed Aurora clusters ○ How do I determine if Aurora is the correct solution for my application? ○ What does the decision making process look like? ○ What does a POC look like? ● Implementing code reusable version and infrastructure tracking ○ Adapting Terraform to AWS Aurora ○ Open source development of tools for devops - code reusable ○ Auditing changes to the database infrastructure. ● Monitoring and evaluating the effectiveness of a major infrastructure change ○ Using PMM and other tools to monitor the new environment ○ Comparison of old and new environments ○ Justification of the infrastructure shift 5

6. In this talk: Aurora = AWS Aurora MySQL 5.6

7.AWS Aurora - What?

8.AWS Aurora MySQL Architecture 8

9.AWS Aurora - Why?

10.Why Aurora? Pros... General benefits of AWS Aurora MySQL: ● Replication Lag less than 100 ms within same region ● 6 copies of your data across 3 Availability Zones ● Scale storage size automatically to 64 TB ● Supports up to 15 Replicas ● Isolates the database buffer cache from database processes ● Does not require crash recovery replay of database redo logs ● Multi-AZ is built-in and automatic ● Continuous backup to S3 ● Allows storage encryption ● Zero Downtime Patching available without binlog ● Fast Database Cloning ● Auto Scaling - Read Replica 10

11.Why Aurora? Cons... General downsides of AWS Aurora MySQL: ● Cross-region replica based on binlog ● Table level corruption suffers across the cluster ● Point in Time Recovery not possible ○ However AWS provides Point in Time Restore ● Delayed slave not possible to survive drop table disaster ● No change buffering ● Stick to MySQL version 5.6.10 & 5.7.12 ● Performance Schema disabled for Aurora MySQL 5.7 ● External Plugins not supported ● Supports only InnoDB storage engine ● Closed Source 11

12.MySQL RDS vs Aurora Performance Test Machines: ● Client Machine: Instance Type: m4.4xlarge (CPU 16, Mem. 60G) ● Database Machine: Instance Class: db.r3.8xlarge (CPU 32, Mem. 244G) ○ Master & Read Replica Benchmarking Tool: ● Sysbench 1.0 ○ Tables: 10 tables x 250 million rows (around 50G each table) Benchmark Test: ● OLTP RW/RO ● Failover Test ● Latency(Slave Lag) Test Test Duration: ● 60 min each test (OLTP) 12

13.MySQL RDS vs Aurora Failover Test ● 13

14.MySQL RDS vs Aurora OLTP RW Test ● 14

15.MySQL RDS vs Aurora OLTP RO Test ● 15

16.MySQL RDS Latency Test 16


18.MySQL Aurora Latency Test 18


20.The Future of Aurora

21.The Future of Aurora Upcoming features to be rolled out in Q2 or beyond: ● Multi Master Multi Region AWS Aurora ● AWS Serverless Aurora ● Parallel Query ● Database backtrack 21

22.Verify Schema/Data Consistency

23.Verify Schema Consistency Why we need to verify Schema Consistency ● Functions are not imported automatically ● Stored procedures are not imported automatically. ● User accounts are not imported automatically. ● Views with definer root@localhost are not accessible Tools & Commands ● MySQL Utilities: mysqldiff mysqldiff --difftype=sql --changes-for=server2 --server1=$USER:$PASS@$EC2IP --server2=$USER:$PASS@$AURORAIP $DBNAME:$DBNAME ● MySQL Utilities: mysqldbcompare mysqldbcompare --difftype=sql --server1=$USER:$PASS@$EC2IP --server2=$USER:$PASS@$AURORAIP $DBNAME:$DBNAME --changes-for=server2 23

24.Verify Data Consistency Verify Data Consistency Using pt-table-checksum ● If you are running MySQL on STATEMENT binlog format ● Run pt-table-checksum directly from EC2 based MySQL Master ● pt-table-checksum pt-table-checksum --recursion-method dsn=h=localhost,D=percona,t=dsns --nocheck-replication-filters --ignore-databases mysql,performance_schema,information_schema > checksum.log 2>&1 ● pt-table-sync pt-table-sync --replicate percona.checksums --sync-to-master --user restore --pass 'PASSWORD' --print --verbose Verify Data Consistency Using Paused Slave ● Create Aurora Cluster as a Slave of Slave ● Pause Slave and checksum table 24

25.Aurora Migration

26. 1:1 Migration From EC2 MySQL to Aurora


28.1:1 Migration: EC2 MySQL to Aurora 28

29.1:1 Migration: EC2 MySQL to Aurora 29