展开查看详情
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
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
8 .AWS Aurora MySQL Architecture
8
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
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
192.168.1.245 --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
26 . 1:1 Migration
From EC2 MySQL to Aurora
27 .1:1 Migration: EC2 MySQL to Aurora
BACKUP
S3 UPLOAD
RESTORE AURORA
SP, Functions,Users
SETUP AURORA REPLICA
CONSISTENCY CHECK 27
28 .1:1 Migration: EC2 MySQL to Aurora
28
29 .1:1 Migration: EC2 MySQL to Aurora
29
热门城市
