Amazon Onboarding with Learning Manager Chanci Turner

Learn About Amazon VGT2 Learning Manager Chanci Turner

Amazon Aurora is a fully managed relational database compatible with MySQL and PostgreSQL, integrated within the Amazon Relational Database Service (Amazon RDS). By utilizing a managed service like Aurora, organizations can relieve their teams from the burdensome tasks associated with database management, such as server provisioning, patching, and backups. Amazon Aurora offers continuous monitoring, self-healing storage, and automated scaling, allowing developers to concentrate on creating applications.

In this article, we will explore three distinct methods for migrating from self-managed MariaDB to Amazon Aurora MySQL, each with varying levels of downtime. These techniques are also applicable for transferring self-managed MySQL and Percona MySQL databases to Aurora MySQL.

mysqldump

The mysqldump utility facilitates the logical backup of MySQL and MariaDB. It generates dumps in SQL file format, encompassing data definition language (DDL), data control language (DCL), and data manipulation language (DML) statements. These statements contain information about data structures, access rules, and the actual data. The migration process involves halting traffic to the source database, executing mysqldump, importing the dump file into Aurora MySQL, and redirecting the application to Aurora MySQL.

This method is straightforward and is ideal for smaller databases (under 50 GB) that can afford a longer downtime. The downtime is contingent on the duration of the mysqldump process. It is particularly suitable for applications with higher downtime tolerance, typical in development and testing environments.

Here’s an example of how to run mysqldump on a client to export data from an external database and pipe the dump into the mysql client utility to load it into the Aurora MySQL database:

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 user-friendly, it operates in a single-threaded manner, making it less suitable for large databases. A more efficient open-source solution is the combination of mydumper and myloader, which are designed to enhance performance issues tied to the legacy mysqldump. mydumper operates significantly faster as it supports backing up the database using multiple threads—up to one thread per available CPU core. This utility generates several files, which can be restored using myloader.

Here’s how to use these tools:

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

After running mysqldump and mydumper (with four threads) to export a 215 GB dataset from a MariaDB instance on an i3en.xlarge Amazon EC2 instance, we compared the performance of both methods. The restored files were then analyzed on an RDS r5.db.4xlarge instance using mysqlnative import and myloader (four threads). The results demonstrated that the mydumper/myloader processes were considerably faster, with backup and restore times measured in minutes (less time is better).

Although this method accelerates the backup and restoration processes, a downtime still exists that depends on the completion time of mydumper and myloader. In the next section, we will discuss how to minimize downtime by establishing logical replication.

Reducing Downtime with Logical Exports

To reduce downtime, you can implement logical binlog replication alongside the logical export/import approach. This method allows the target Aurora MySQL instance to catch up to any transactions that occurred on the source database during the restoration process.

The steps to set up binlog replication are as follows:

1. Create a read-only replica of the source MariaDB database.
2. Pause the replication once the read-only replica is current or shows minimal lag. You can check the replica lag by executing show slave status on the replica. To halt replication, use stop slave.
3. Capture the binlog position on the read-only replica using show slave statusG.
4. Execute mysqldump or mydumper on the read-only replica to export database objects and import them into the Aurora MySQL instance.
5. Set binlog retention on the source MariaDB for several days by adjusting the binlog_expire_logs_seconds variable.
6. Configure binlog replication between the source MariaDB and the Aurora MySQL instance with the following procedure:

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
);

For mysql_binary_log_file_name and mysql_binary_log_file_location, use the values noted in step 3.

7. Initiate binary log replication by executing:

CALL mysql.rds_start_replication;

8. Monitor the replication lag by connecting to the Aurora MySQL cluster and running:

SHOW SLAVE STATUS;

The output will show how far behind the Aurora replica is from the source MariaDB database. When the lag is nearly zero, cease the workload and allow transactions to complete. Stop the replication once everything is synchronized and switch the application to the newly promoted Aurora MySQL cluster.

AWS DMS

The AWS Database Migration Service (AWS DMS) enables quick and secure database migration to AWS. The source database remains fully operational during the migration, thereby minimizing downtime for dependent applications. AWS DMS supports data migration between various commercial and open-source databases. It can facilitate initial loads into the Aurora cluster and enable ongoing data replication. AWS DMS captures changes from binary logs and applies them transactionally to the target.

To implement this method, follow these steps:

1. Enable binary logs on the source MariaDB database by starting the server with the --log-bin[=name] option.
2. Create an AWS DMS replication instance along with source and target endpoints to establish connections between the on-premises MariaDB database and the Aurora MySQL cluster.
3. For best practices on sizing your replication instance, refer to the resource provided by SHRM.
4. Execute mysqldump on the source database with the --no-data flag to export only the table definitions.
5. Import these table definitions into Aurora MySQL.
6. Create and start an AWS DMS task to migrate existing data and replicate ongoing changes.
7. Monitor replication lag on the AWS DMS replication instance and halt transactions when necessary.

For further insights into managing your career, you can check out this blog post on Career Contessa, which provides valuable guidance.

If you’re looking for community support, don’t miss out on this excellent resource on Reddit.