MySQL is a relational database management system (RDBMS) owned by Oracle. It is open source and supports multiple platforms including Linux, Windows, and MacOS.
MySQL is a key component of the open-source enterprise stack LAMP, which stands for Linux, Apache, MySQL, and PHP. The LAMP stack is highly popular because it can be downloaded and used for free, is highly customizable, and is a mature stack that is proven at any scale.
Databases are a key component of any cloud migration strategy. If you are running a MySQL database, and migrating systems to the Amazon cloud, you’ll need to understand hosting options on AWS and how to migrate your database smoothly to an Amazon service.
MySQL Hosting Options on AWS
There are three options for hosting MySQL on AWS: self-managed on Amazon EC2, hosting on the serverless Amazon Aurora service, part of the AWS serverless ecosystem, or fully managed MySQL on Amazon RDS.
Hosting MySQL on Amazon EC2
Relational databases have mostly been on-premises but as organizations pivoted towards the cloud for their storage needs, Amazon’s EC2 service was an initial popular choice. The primary advantage of opting for Amazon EC2 is greater availability and scalability. However, administration tasks like database setup, tuning, patching, and backups remained the same as in an on-premise data center.
Hosting MySQL on Amazon Aurora
Amazon Aurora is a relational database system built for the cloud and compatible with PostgreSQL and MySQL. However, it is three times as fast as PostgreSQL databases and hundreds of times faster than a typical MySQL database. It provides the advantages of traditional enterprise-level databases (availability and performance) with those of open-source options (cost-effectiveness and simplicity).
Inside Amazon Aurora is a self-healing storage system that is distributed, fault-tolerant, ensures high availability and performance, and scales automatically up to 128TB. It is managed by Amazon Relational Database Service (RDS) which handles administrative tasks like database setup, hardware provisioning, and backups.
Hosting a Fully Managed MySQL Instance on Amazon RDS
Amazon’s Relational Database Service simplifies the process of setting up, operating and scaling relational databases in the cloud. It manages administrative tasks and provides a cost-effective and resizable storage capacity.
Amazon RDS has features similar to other RDBMS like Oracle MySQL, SQL Server, or PostgreSQL. Hence, code and applications that can work with these technologies will easily work with Amazon RDS. It also backs up the database and updates its software automatically.
Additionally, RDS automatically adapts the storage and computational resources to changing requirements.
Amazon RDS uses replication for enhancing the database’s availability. It can scale the database’s capacity to accommodate read-heavy database workloads. Its cost is dependent on the resources used, with no upfront investment.
3 Strategies for Migrating MySQL to AWS
Migrating with mysqldump
mysqldump is a popular tool for backing up MySQL databases. Through this tool, users can create a backup and export a database’s data and schema definitions from a MySQL server. Users can also import a database backup into MySQL server through the SOURCE SQL command (run from inside the database) or the MySQL command line.
To import data from a MySQL-compatible database to RDS through mysqldump in one step:
Use this command to dump data into a target database:
mysqldump –host=<demo_hostname> –user=<demo_source> \
–password=<demo_password> –databases <demo_database> \
–single-transaction –compress –order-by-primary \
| mysql -u <demo_RDS_user> –port=<demo_RDS_port> \
–host=<demo_db_id> -p<demo_RDS_password>
The command requires a consistent low-latency and high-speed internet connection as it is a single-step migration.
To import data from MySQL-compatible database to RDS through mysqldump by creating data dumps:
1. Use this command to produce backup of your source database:
mysqldump –host=<demo_hostname> –user=<demo_source> \
–password=<demo_password> –databases <demo_database> \
–single-transaction –compress –order-by-primary \
–master-data=2 -p<demo_local_password> -r mysql_backup.sql
The single-transaction flag ensures the backup gets created and dumps all tables in one go. It is advisable to create a read replica and read from it to avoid affecting the production database.
2. Connect to the target database server and use the following command from inside it to source the database backup:
source mysql_backup.sql;
Performing Multi-threaded Migrations with mydumper and myloader
mydumper and myloader are open-source import/export tools for MySQL databases. They are designed to handle performance issues that come with the mysqldump utility. Some of their features are:
- Loading and dumping data in parallel threads
- Creating chunked dumps using the multiple-files-per-table and file-per-table techniques
- Dumping metadata and data in a separate file for simpler management
- Configuring the transaction size for imports
To migrate using mydumper/myloader:
1. Use this command to create dumps of two databases using the mydumper tool with basic configurations:
mydumper –host=<demo_host> –user=<demo_source> \
–password=<demo_password> –directory=<demo_output_dir> \
–threads=4 –compress –events –triggers –routines \
–regex ‘^(demoDB1\.|demoDB2\.)’ \
–logfile /<demo_output_dir>/demo_mydumper_log_file.txt
2. Use this command to use myloader and restore your backup:
myloader –host=<demo_host> –user=<demo_source> \
–password=<demo_password> –directory=<demo_output_dir> \
–queries-per-transaction=450 –threads=3 –verbose=2 \
–-logfile /<demo_output_dir>/myloader_log_files.txt
Migrating with AWS Database Migration Service
The Database Migration Service is Amazon’s dedicated service for simplifying migration of databases to AWS. There are multiple options while migrating, such as advanced data filtering, remapping table names, and migrating multiple database servers to a single Aurora MySQL cluster. It allows you to perform simple database migrations with almost zero downtime. Hence, it can be an option for MySQL-compatible databases if the alternative migration techniques fail.
Conclusion
In this article, I explained the benefits of managing a MySQL database in the AWS cloud, and presented three common strategies for migration:
- Migrating with mysqldump – using the standard command line tool to export MySQL data and import it into Amazon RDS
- Multi-threaded Migrations with mydumper and myloader – using open source tools to export large volumes of data from MySQL and overcome performance issues.
- Migrating with AWS Database Migration Service – using Amazon’s automated tool to migrate a MySQL database to an Aurora MySQL cluster with no downtime.
I hope this will be useful as you evaluate options for moving your database to the cloud.
Read Next: 6 long-term planning trends by Gartner for cloud and edge computing
