<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Markus Städler on Medium]]></title>
        <description><![CDATA[Stories by Markus Städler on Medium]]></description>
        <link>https://medium.com/@der_markus?source=rss-40fc08087cb8------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*0pczDRWV-BN6VpBaDs5e3A.jpeg</url>
            <title>Stories by Markus Städler on Medium</title>
            <link>https://medium.com/@der_markus?source=rss-40fc08087cb8------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 22:20:00 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@der_markus/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Cloud Migration: A Journey from On-Premises MySQL to AWS Aurora MySQL]]></title>
            <link>https://medium.com/deloitte-uk-cloud-blog/cloud-migration-a-journey-from-on-premises-mysql-to-aws-aurora-mysql-f74bdf5f22f9?source=rss-40fc08087cb8------2</link>
            <guid isPermaLink="false">https://medium.com/p/f74bdf5f22f9</guid>
            <category><![CDATA[cloud-migration]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[aurora]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[cloud-and-architecture]]></category>
            <dc:creator><![CDATA[Markus Städler]]></dc:creator>
            <pubDate>Wed, 26 Feb 2025 16:47:46 GMT</pubDate>
            <atom:updated>2025-02-26T16:47:46.284Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="A stock image depicting the process of migrating to the cloud" src="https://cdn-images-1.medium.com/max/1024/1*pS2PvCa2nb6ub5IG6u-vyA.jpeg" /></figure><p>Co-Authors : <a href="https://medium.com/u/f404060b0f53">Kevin Alex Kunnampallil</a>, <a href="https://medium.com/u/1ee5eb9ee5ba">Geeta Puri</a></p><p>Migrating enterprise workloads from on-premises infrastructure to the cloud is a significant undertaking, often involving complex database migrations. In this article, we share insights gleaned from our recent experience supporting a client’s ambitious cloud migration project. Database migration was one of the biggest workstreams in that exercise, and for good reason. We share our experience and showcase how to migrate a MySQL on-premises database to AWS Aurora with minimal downtime.</p><p>It is no secret: Data migrations often go unnoticed in the IT world, silently enabling critical application upgrades and system overhauls. Let us be honest: migrating a database, especially a hefty on-premises MySQL instance, to the Cloud can be daunting. Downtime anxieties, compatibility nightmares, and the looming threat of data loss can turn even the most seasoned cloud architect’s hair grey.</p><p>But fear not fellow Cloud warriors! This article is here to shed light on the path to a smoother MySQL to Aurora MySQL migration. Aurora MySQL, a fully managed, high-performance, and scalable relational database engine, presents a compelling choice for businesses migrating their databases to the cloud. We will delve into the migration options, steps, and best practices to not only survive, but thrive, in this migration journey. So, grab a cup of your favourite stress-relieving beverage, and let us conquer that database migration challenge together!</p><h4><strong>Why we moved to managed relational databases</strong></h4><p>Tasked with architecting a cloud data migration solution for a client with limited cloud maturity and nascent cloud-native development practices, we recognised the benefit of a managed service model. This approach, as opposed to burdening the existing operations team with new responsibilities, allows them to maintain focus on core business objectives and immediate priorities. Aurora emerged as a compelling option, offering robust performance, scalability and availability. This outweighed the tradeoffs that Amazon Aurora may not encompass all features or configurability of native database engines and can be more expensive than AWS RDS native offerings. For the use case of our customer, it was the right choice. Some of the benefits we observed are below:</p><ul><li><strong>Simplified Management:</strong> Ditches the burden of hardware, OS, and database software maintenance. Aurora offers easy administration with built-in monitoring.</li><li><strong>Scalability:</strong> Aurora allows the seamless scalability of its storage without any active interaction of the user. The storage just naturally grows with the size of the stored databases up to a maximum of 128 Tebibytes (TiB) for a single Aurora cluster. Single tables can grow up to a maximum size of 64 TiB for MySQL.</li><li><strong>Performance:</strong> Aurora will provide you enterprise-level performance for MySQL and PostgreSQL databases. Independent benchmarks have shown significant performance improvements of Aurora over RDS based databases. Though, any concrete numbers stated in benchmarks are only meaningful to very similar setups and environmental factors stated in the benchmark.</li><li><strong>Availability &amp; Durability: </strong>Automatic multi-AZ data replication guarantees high availability. Automated backups, snapshots, and failovers ensure your data is almost always accessible and resilient with only minimal downtime.</li><li><strong>Security &amp; Compliance: </strong>Data encryption at rest and in transit safeguards your information. Aurora provides tools to help you achieve compliance goals, simplifying regulatory adherence.</li></ul><h3><strong>Migration Options</strong></h3><p>Database migrations can be categorised by whether the source and target databases are similar (<em>homogeneous</em>) or different (<em>heterogeneous</em>). Homogeneous migrations, such as migrating between different MySQL versions, are simpler due to the consistent underlying structure. In contrast, heterogeneous migrations, such as Oracle to PostgreSQL, require transforming the schema and code to match the target system, due to their fundamental differences. This added complexity often involves specialised tools for smooth data transfer.</p><p>The two main requirements for this specific database migration were the following:</p><ol><li>Only minimal downtime was allowed. Even though, not a concrete number was determined, the downtime was meant to be in the seconds rather than minutes.</li><li>The database migration should work smoothly within the holistic migration of the whole platform and allowing for granular control throughout each phase.</li></ol><p>So, for the actual migration we are basically left with the two options described below:</p><h4><strong>Restore from Backups and Replication:</strong></h4><p>Moving a database to a similar environment (homogeneous migration) can be done quickly and efficiently with a restore from a backup. There are multiple third-party tools available to facilitate and enhance this process like Percona XtraBackup2 as a well-established open-source third-party program.</p><p><strong><em>Pros:</em></strong></p><ul><li><strong>Speed:</strong> Physical restore is the fastest way to migrate your database, minimising downtime.</li><li><strong>Compatibility:</strong> Works with MySQL versions 5.6, 5.7, and 8.0 as the source database.</li><li><strong>Minimal Downtime:</strong> Leverages native binlog replication to capture changes during the migration, ensuring minimal downtime during the switch.</li><li><strong>Control:</strong> You have the maximum control over each phase and entity down to the very details.</li></ul><p><strong><em>Cons:</em></strong></p><ul><li><strong>Manual intervention: </strong>The process involves manual steps such as taking backups, transferring files, and restoring.</li><li><strong>Risk of data loss:</strong> if the backup process is not handled properly there is a risk of corruption.</li><li><strong>Lack of incremental changes: </strong>Unlike continuous replication, backup and restore does not support incremental changes.</li></ul><p><strong>Steps:</strong></p><ol><li>Create backup.</li><li>Securely upload your backup files to a cloud storage service.</li><li>Perform a physical restore of the backup on the target server.</li><li>Prepare the on-premises cluster for replication by enabling binary logging.</li><li>Setup target server to replicate from original server.</li><li>Wait for the replication to catch up to normal replication lag.</li><li>Stop traffic on original server.</li><li>Verify briefly that replication executed all outstanding changes and both servers contain the same data.</li><li>Switch traffic routing to target server (and enable traffic again)</li><li>Delete/Destroy original server and conclude migration.</li></ol><p>This approach leverages existing tools and minimises downtime, making it the most efficient choice for homogeneous MySQL migrations with Percona XtraBackup. Then there is also the other option:</p><h4><strong>Cloud-Based Migration Services:</strong></h4><p>E.g., AWS Database Migration Service (DMS) is a managed migration and replication service that helps move your database and analytics workload to AWS quickly, securely and with minimal downtime and zero data loss. Typically, a preferred option for heterogeneous migration, complex and heavy workloads.</p><p><strong><em>Pros:</em></strong></p><ul><li><strong>Simplicity: </strong>As a managed tool DMS<strong> </strong>is easy to use<strong> and </strong>manages a lot of the tedious details.</li><li><strong>Schema migration/conversion: </strong>DMS allows to relatively easy handle schema migrations/conversions when migrating a database.</li></ul><p><strong><em>Cons:</em></strong></p><ul><li><strong>Control: </strong>In complex scenarios where you need control over every little detail a managed tool like DMS may not allow enough detailed control.</li><li><strong>Replication:</strong> In a situation where the replication is necessary to keep the data state between original and target server consistent and a lot of data changes occur, DMS may come to its limits.</li></ul><p><strong>Steps:</strong></p><ol><li>Prepare the source database and enable binary logging.</li><li>Create a replication instance that handles the data migration process.</li><li>Create source and target data provider.</li><li>Create a migration task of type “Full load and change data capture”</li><li>Start the migration task.</li><li>Stop traffic on the source database.</li><li>Verify the data migration so that both servers contain the same data.</li><li>Switch traffic routing from the source database to the target database.</li><li>Enable traffic on target database.</li><li>Clean up source database and conclude the migration.</li></ol><p>The solution approach, however, depends on specific needs for downtime, control, and complexity. Since in our case, Percona XtraBackup was the tool of choice in previous Cloud migrations, there was a decision to remain with this tool as we performed a homogeneous migration with very specific requirements and the necessity to control all the details. So, we decided to go with the “Restore from Backup and Replication” approach.</p><h3><strong>Migration Pre-requisites</strong></h3><h4><strong>Prerequisites</strong></h4><ul><li>An active AWS account</li><li>Permissions to create AWS Identity and Access Management (IAM) roles and policies</li><li>Network connectivity between the on-premises MySQL database and the virtual private cloud (VPC) on AWS</li><li>Please refer to vendor guideline for product version (MySQL)</li></ul><h4><strong>Limitations</strong></h4><ul><li>The pattern supports Percona XtraBackup full backup and incremental backup only. It does not support partial backups that use; — tables, — tables-exclude, — tables-file, — databases, — databases-exclude, or — databases-file.</li><li>Aurora does not restore users, functions, stored procedures, or time zone information from the source MySQL database (at the time of writing).</li></ul><h3><strong>Migration approach</strong></h3><p>In the context of database migrations, read and write cutover refers to the point in time when the application stops making read and write requests to the source database and starts making these requests to the target database. This process is critical to ensure data consistency and minimise downtime during migration.</p><p>For the read traffic cutover, the read traffic can be redirected to the Aurora MySQL cluster by setting the application’s MySQL connection string to the reader endpoint of the new Aurora MySQL cluster.</p><p>For the write traffic cutover, an additional fallback cluster will be created in on-premises which is replicating from Aurora MySQL and served as a rollback strategy in case of a failure.</p><p>An illustration of those two procedures can be seen below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PsSkjO5FHb8PA_HKHMLJxA.jpeg" /><figcaption>Figure 1: The read traffic cutover process before and after</figcaption></figure><p>The read traffic cutover image, Figure 1 ,shows where the related resources are deployed on-premises or in AWS and how the traffic is handled before and after the cutover. Prior to migration, the MySQL cluster resided within the on-premises environment, alongside a subset of applications. Concurrently, other applications had already been migrated to AWS, highlighting a hybrid source environment. Additionally, an application delivery controller (ADC) was part of the on-premises environment, which handled the traffic and load balancing.</p><p>An Application Delivery Controller (ADC) is beneficial for migration from on-premises to AWS RDS for the load balancing functionality. Initially, the ADC allowed a small percentage of read traffic to be directed to the new AWS instance, in a rolling deployment strategy, ensuring a smooth transition and minimising disruption. This gradual cutover helped in monitoring the performance and stability of the new system before fully switching over, thereby reducing risks associated with abruptly overloading the database.</p><p>After the read traffic cutover, the read traffic was sent by the ADC to the AWS Aurora MySQL cluster.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aBt36gJn4-tP4R3LxhP9WQ.jpeg" /><figcaption>Figure 2: The write traffic cutover process before and after</figcaption></figure><p>As depicted in Figure 2, which illustrates the write traffic cutover process, an additional fallback cluster was provisioned within the on-premises environment. This fallback cluster-maintained replication from the AWS Aurora MySQL cluster, ensuring data redundancy and DR capabilities. Following the successful cutover of write traffic to Aurora MySQL, replication from the legacy on-premises cluster ceased. The ADC was then reconfigured to direct all write traffic to the Aurora MySQL cluster, establishing it as the primary database instance.</p><p>The write cutover was able to take place once the AWS Aurora MySQL cluster was fully operational, replicated the latest changes from the on-premises MySQL cluster, and the system stability is confirmed by monitoring key metrics like replication lag, read/write latency, error rates, CPU and memory usage, and network throughput.</p><h3><strong>Migration steps</strong></h3><ol><li>The first step is to use the Percona XtraBackup tool¹ to create a backup of the on-premises MySQL cluster and copy the backup data to AWS S3. To create the backup the Percona XtraBackup needs to be installed on the on-premises server and then the following command needs to be executed to create the backup. The command will also split the backup into multiple tar file chunks of 500 MB. Larger data chunks of 500 MB can overload system resources, but are processed faster. Smaller chunks are more resource-friendly, but take longer to process.²<br>xtrabackup --backup-user=&lt;PLACEHOLDER_USER_TO_CREATE_BACKUP&gt; --password=&lt;PLACEHOLDER_PASSWORD_OF_USER_TO_CREATE_BACKUP&gt; --stream=tar --target-dir=&lt;PLACEHOLDER_BACKUP_DIR_FOLDER&gt; | split -d --bytes=500MB — &lt;PLACEHOLDER_BACKUP_DIR_FOLDER&gt;/backup.tar</li><li>Upload backup to an S3 bucket.</li><li>Create the Aurora MySQL cluster from the backup on S3.</li><li>Start replication from on-premises MySQL to Aurora. To initialise the replication, the MySQL RDS commands rds_set_external_master and rds_start_replication are used to set the primary replication database of Aurora to the on-premises MySQL cluster and to start the replication. A corresponding MySQL user also needs to be created for the replication. The rds_set_external_master command needs to be configured in the following way:<br>CALL mysql.rds_set_external_master(host, port, username_for_replication, user_password_for_replication, binlog_file_name, binlog_file_position, bool_ssl_enabled);<br>The binlog_file_name and binlog_file_position can be taken from the output after the Aurora MySQL cluster was created. The replication can then be started by executing the command CALL mysql.rds_start_replication; on the Aurora cluster.</li><li>Verify and set GRANTS so that the same permissions are set in both environments (on-premises MySQL and AWS Aurora MySQL).</li></ol><h4><strong>Read Traffic Cutover</strong></h4><p>Since the Aurora MySQL cluster then replicated from the on-premises MySQL cluster, the Aurora MySQL cluster could be used to handle the read traffic. As we had no use-case that required data that is up to date on a millisecond basis, it did not matter where the apps read the data. As a result, no special preparation was required to execute the actual Read Cutover. We only had to update the connection string for all the apps that required read access and point it to the reader endpoint of the Aurora cluster.</p><p>We saw that the Aurora cluster gradually picked up more connections and once the TTL of the former DNS record expired, all read traffic was handled by the Aurora cluster (except for some longer running transactions). However, since both clusters contained the same data (with a replication latency of under 1 second), we had no downtime.</p><h4><strong>Write Traffic Cutover</strong></h4><p>The Write Cutover was a bit trickier and required more preparation to reduce the potential downtime to a minimum and avoid data inconsistencies. We identified the below steps as the ideal solution to keep downtime to a minimum:</p><ol><li>Make sure that the replication of the Aurora cluster does not lag significantly.</li><li>Verify that the Aurora cluster is not set to read only mode. Thus, the read_only parameter of the cluster parameter group should be set to 0 to allow writes. Additionally, any custom procedures, such as automatic failover mechanisms should be disabled (we had a custom failover mechanism implemented via a Lambda function).</li><li>Stop the Write traffic on the on-prem host. Since we routed all traffic through an on-prem load balancer (Netscaler), we disabled the write traffic to the MySQL cluster on the Netscaler portal itself. Additionally, we set the on-prem database to read only. This means that when the downtime starts the database will be offline.</li><li>Check the replication position again to avoid any data inconsistencies. To do this, you can run this command SHOW SLAVE STATUS;\G on the Aurora cluster. Subsequent executions of this command should show the same value for the replication position key, thus indicating that Aurora replicated the latest changes.</li><li>Stop the replication of the Aurora cluster from the on-premises MySQL host. You can do so by executing the counterpart of the earlier mentioned rds_start_replication MySQL RDS Procedure via this command: CALL rds_start_replication; on the Aurora cluster. Now, you can also reset the external master via CALL mysql.rds_reset_external_master;.</li><li>As the last step, redirect (and enable again) Write traffic on to the Aurora cluster. As we stopped the traffic on our Netscaler, we simply enabled it there again and redirected the traffic to the Aurora cluster endpoint.</li></ol><p>Executing the above-mentioned steps basically concluded the actual write cutover. Of course, we still needed to verify that the cutover had worked as planned. We kept a close eye on several key metrics across all the MySQL clusters, both on-premises and in Aurora. Write IOPS, which measures the number of write operations per second, turned out to be one of the most important metrics for checking if the cutover was successful. And when we finally saw that the Write IOPS on the Aurora cluster reached a similar level compared to the on-premises cluster, we considered the Write Cutover migration a successful transition. Overall, the whole downtime was less than 2 minutes.</p><h3><strong>Rollback Strategy</strong></h3><p>To prepare for the worst-case scenario, we set up our Rollback Strategy via a fallback cluster before the actual Write Cutover. We wanted to be able to switch back to a running on-premises cluster as fast as possible in case of any errors to minimise downtime in our production environment.</p><p>So, we built a chain of MySQL clusters/hosts that are replicating from each other. As described above, we had an Aurora cluster replicating from our initial on-prem MySQL cluster. So, for our Rollback Strategy we spun up a new host in the source environment that was replicating from our Aurora cluster — daisy chaining MySQL clusters. Since we already had moved some of the workload to Aurora, we simply repurposed then unused on-premises MySQL servers as the fallback on-premises cluster. This ensured that we basically had the exact same configuration for the fallback cluster as for our original on-premises source cluster.</p><p>This setup would have allowed us (in case of any substantial errors), to just “fail forward” directly. There would be no need to set up a new cluster in on-premises or make sure that the old cluster had the same data as the Aurora cluster because we already had our fallback cluster replicating from our Aurora cluster. So, if we cutover and initially served Write traffic but then faced errors shortly after, we would have been able to do a second “Cutover” to the fallback on-premises cluster and be back online in seconds.</p><p>Fortunately for us, we did not need to execute our Rollback Strategy.</p><h3><strong>Key Learnings</strong></h3><h4><strong>Incorrect character set</strong></h4><p>One of the problems that can cause the replication of Aurora MySQL and on-premises MySQL to fail is the selection of an incorrect character set, leading to data corruption. We resolved this issue by setting the MySQL parameter init_connect to set names utf8 collate utf8_general_ci on the Aurora cluster. UTF-8’s combination of backward compatibility, efficient encoding, wide support, and robustness make it the preferred choice for character encoding.</p><h4><strong>Role chaining</strong></h4><p>When using Terraform to create an Aurora MySQL cluster from a backup and employing an IAM role used to execute the Terraform apply, which in turn is assuming another IAM role to actually create the cluster with assumed role switching for execution. It must be considered that this is known as role chaining and the role session limits are by default set to a maximum of 1 hour. If the cluster creation takes longer than one hour, one IAM role must be used directly instead of via a general execution role and role chaining.</p><h4><strong>TLS configuration</strong></h4><p>It must be ensured that the TLS configurations are set correctly. If TLS is enforced on the cluster side by setting the cluster parameter require_secure_transport or by setting the GRANTS for a user to REQUIRE SSL, the MySQL clients must use the compatible TLS and verify the server certificate.</p><h3><strong>Conclusion</strong></h3><p>To summarise, a migration from on-premises MySQL to Aurora MySQL allows you to take advantage of the cloud native development. The migration itself is comparatively straightforward but requires detailed planning and a strategy. As the saying goes, there is more than one way to cook an egg. What we have explained is an approach to suit our client migration requirements, and there are other ways as mentioned above to achieve the same outcome. By carefully assessing client requirements, selecting the appropriate DB, and implementing robust data migration and backup strategies, you can minimise downtime and maximise the benefits of cloud-native database services.</p><h3><strong>References</strong></h3><ol><li>Vamsi Ponnekanti <em>Percona XtraBackup</em> [online] Available at: <a href="https://www.percona.com/mysql/software/percona-xtrabackup">https://www.percona.com/mysql/software/percona-xtrabackup</a> [Accessed: 1 Dec. 2024]</li><li><em>Physical migration from MySQL by using Percona XtraBackup and Amazon S3</em>. [online] Available at: <a href="https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.ExtMySQL.S3.html">https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.ExtMySQL.S3.html</a> [Accessed: 5 Dec. 2024].</li></ol><p><strong><em>Note</em></strong><em>: This article speaks only to my personal views / experiences and is not published on behalf of Deloitte LLP and associated firms and does not constitute professional or legal advice. All product names, logos, and brands are property of their respective owners. All company, product and service names used in this website are for identification purposes only. Use of these names, logos, and brands does not imply endorsement.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f74bdf5f22f9" width="1" height="1" alt=""><hr><p><a href="https://medium.com/deloitte-uk-cloud-blog/cloud-migration-a-journey-from-on-premises-mysql-to-aws-aurora-mysql-f74bdf5f22f9">Cloud Migration: A Journey from On-Premises MySQL to AWS Aurora MySQL</a> was originally published in <a href="https://medium.com/deloitte-uk-cloud-blog">Deloitte UK Engineering Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>