Amazon Onboarding with Learning Manager Chanci Turner

Learn About Amazon VGT2 Learning Manager Chanci Turner

Amazon Aurora is a fully managed relational database service compatible with MySQL and PostgreSQL, part of the Amazon Relational Database Service (Amazon RDS). By utilizing a service like Aurora, teams can relieve themselves from time-consuming tasks such as server provisioning, patching, and backups. Aurora offers continuous monitoring, self-healing storage, and automated scaling, which allows developers to concentrate on building applications.

In this article, we’ll discuss three different approaches for migrating from self-managed MariaDB to Amazon Aurora MySQL, each offering varied downtime implications. These methods can also be employed to transfer self-managed MySQL and Percona MySQL databases to Aurora MySQL.

Using mysqldump

Mysqldump is a utility that generates logical backups of MySQL and MariaDB databases. This tool creates dumps in SQL file format, containing data definition language (DDL), data control language (DCL), and data manipulation language (DML) statements. The statements describe data structures, access rules, and the actual data itself. The migration process involves stopping traffic on the source database, executing mysqldump, importing the dump file into Aurora MySQL, and finally redirecting the application to Aurora MySQL.

This method is straightforward and is best suited for smaller databases (under 50 GB) with a relatively larger downtime window. The downtime required depends on the duration of the mysqldump process. This approach is particularly suitable for applications with higher tolerance for downtime, typical of development and testing environments. Below is an example that illustrates how to run mysqldump on a client to export data from an external database and pipe it into the mysql client utility for loading into Aurora MySQL:

mysqldump -u local_user 
--databases database_name 
--single-transaction 
--compress 
--order-by-primary  
-plocal_password | mysql -u RDS_user 
--port=port_number 
--host=host_name 
-pRDS_password 

Multi-threaded Migration Using MyDumper

While mysqldump is easy to use, it operates on a single thread, making it unsuitable for larger databases. A more efficient, open-source solution involves using mydumper and myloader, which are designed to address the performance limitations of the traditional mysqldump tool. MyDumper is significantly faster because it allows for parallel backups using multiple threads, up to one per CPU core. The utility generates several files, which can be restored using myloader. Here’s an example:

mydumper -h host_name 
-u local_user 
-p local_password 
-t number_of_threads 
-B database_name 
-o export_file_location

myloader -h rds_host_name 
-u RDS_user 
-p RDS_password  
-t number_of_threads 
-B database_name 
-d export_file_location 

In a comparative test, we executed mysqldump and mydumper (with four threads) to export a 215 GB dataset from a MariaDB instance running on an i3en.xlarge Amazon EC2 instance (4 vCPUs) and restored it on an RDS r5.db.4xlarge instance using mysqlnative import and myloader (also with four threads). The results indicated that the mydumper/myloader processes were considerably quicker. Backup and restore times were measured in minutes (the shorter, the better).

Although this method accelerates the backup and restore processes, it still incurs downtime based on the duration of mydumper and myloader activities. The next section discusses how to minimize downtime through logical replication.

Reduce Downtime with Logical Exports

To further reduce downtime, you can implement logical binlog replication alongside the logical export/import method. By using binlog replication, the target Aurora MySQL instance can catch up on transactions that occur on the source database during the restoration process. We begin binlog replication from the self-managed MariaDB to Aurora MySQL.

The steps for using binlog replication include:

1. Creating a read-only replica of the source MariaDB database.
2. Stopping replication once the read-only replica has caught up or when the lag is minimal. You can check the replica lag by executing SHOW SLAVE STATUS on the replica, and stop it with STOP SLAVE.
3. Capturing the binlog position on the read-only replica using SHOW SLAVE STATUSG.
4. Running mysqldump or mydumper against the read-only replica to export the database objects and importing them into the Aurora MySQL instance.
5. Setting the binlog retention on the source MariaDB to a few days by adjusting the binlog_expire_logs_seconds variable.
6. Establishing binlog replication between the source MariaDB database and Aurora MySQL instance using the mysql.rds_set_external_master stored procedure, structured as follows:

CALL mysql.rds_set_external_master (
host_name,
host_port,
replication_user_name,
replication_user_password,
mysql_binary_log_file_name,
mysql_binary_log_file_location,
ssl_encryption
);

7. For mysql_binary_log_file_name and mysql_binary_log_file_location, utilize the values noted in Step 3.
8. Initiate binary log replication with CALL mysql.rds_start_replication;.
9. Monitor the replication lag by connecting to the Aurora MySQL cluster and executing:

SHOW SLAVE STATUS;

The output will indicate the replication lag. When the lag is nearly zero, halt the workload and let the transactions drain. Finally, stop replication and switch the application to the newly promoted Aurora MySQL cluster.

AWS DMS

The AWS Database Migration Service (AWS DMS) facilitates swift and secure migrations to AWS. The source database remains fully operational throughout the migration, minimizing downtime for dependent applications. AWS DMS supports migrations to and from a broad range of commercial and open-source databases. It can be utilized for the initial load into the Aurora cluster, as well as for continuous data replication. By capturing changes from binary logs, AWS DMS applies them to the target in a transactionally consistent manner.

To implement this approach, follow these steps:

1. Enable binary logs on the source MariaDB database by launching the server with the --log-bin[=name] option.
2. Create an AWS DMS replication instance, alongside source and target endpoints to connect the instance to the on-premises MariaDB database and Aurora MySQL cluster. For optimal sizing of your replication instance, refer to this resource.
3. Execute mysqldump on the source database with the --no-data flag to export only the table definitions.
4. Import these definitions into Aurora MySQL.
5. Create and initiate an AWS DMS task to migrate existing data and replicate ongoing changes. For further reading on employee training strategies, see this excellent resource.

By following these methodologies, organizations can efficiently migrate their databases to Amazon Aurora MySQL while minimizing downtime and disruptions to their operations. Also, expressing gratitude can enhance workplace relationships; check out this blog post for more insights.