Best DBaaS Solution for MySQL

As the most popular open source database, MySQL has been implemented in many places, from small startups to very large organizations. Use cases vary from simple website applications to mission critical environments with 99.999% uptime requirements. MySQL just gets the job done, and is easy to work with. 

Although MySQL is relatively easy to manage, it does not run on its own. There is a certain management overhead – software needs to be patched every now and then, the database needs to be monitored for failures or anomalies in performance or security, failures need to be handled and recovered from, backups need to be managed.. and the list goes on.. So it should be of no surprise that the largest cloud vendors offer public DBaaS services based on MySQL.

Three cloud providers that offer MySQL as a service include :

• Amazon Web Service with RDS for MySQL
• Google Compute Engine with CloudSQL for MySQL
• Microsoft Azure, Microsoft Azure MySQL

In this blog, we will compare the solutions from these cloud providers.

MySQL Version & Patches

The latest version of MySQL that is available in Amazon Web Services (AWS) is MySQL 8.0.20, which is quite close to the latest version of the official Oracle MySQL ( 8.0.21 at the time of writing). Beside the latest version, AWS RDS also provides the older version of MySQL (major version 5.5, 5.6, and 5.7), so you can deploy the exact version that is compatible with your application.

In Google Cloud Platform (GCP), the MySQL version that supported in CloudSQL for MySQL is still MySQL 5.6 & 5.7, the latest minor version of release 5.6 is 5.6.42 while for version 5.7, the latest minor version is 5.7.25.

Azure database for MySQL supports versions 5.6, 5.7, 8.0, unfortunately they do not provide the minor version (or the bug fix version of the database, as Azure calls it) when deploying from the console. To determine the version of your MySQL server instance, one can use the SELECT VERSION(); command at the MySQL prompt.

Known Issues and Limitations

There are some known issues and restrictions that exist on the database as a service while it does not happen in MySQL on premise or VM’s. On RDS, some of limitations are:

• MySQL keyring plugin is not supported.
• Maximum storage limit size for a table is 16TB when using the InnoDB storage engine.
• There are some parameters that require special considerations when using RDS, eg: long_query_time, lower_case_table_name.

There are some limitations and issues known in CloudSQL for MySQL, divided into different categories, for example: data durability and availability issues, instance connection issues, administrative issues, and issues with exporting and importing data. Each categories have specific issues and limitations, some of them are: 

• Long running operations cannot be cancelled or stopped.
• Instance names cannot be used immediately after we delete the instance.
• The DEFINER clause will cause import to fail.

Azure database for MySQL have some limitations and known issues related to upgrade, privileges, and storage engine. Some of the detail are:

• Major database upgrade is currently not supported. You need to do a dump and restore into a new server for a major upgrade.
• Azure database for MySQL currently supports InnoDB and Memory storage engines.
• System database in azure database for MySQL is set to read-only. You can not change anything in the mysql system database.

You need to check the limitations and known issues for MySQL on each cloud provider, and compare with your requirements to understand whether it impacts the application.

Backup and Restore

Amazon RDS for MySQL runs the automated backup as per the schedule, it takes a volume snapshot of the database instance. The default of the backup retention period is 7 days. Not only that, RDS uploads your transaction logs for database instances to S3 every 5 minutes to keep the point in time recovery.  

You can restore a backup to a specific point in time by creating a new instance within the backup retention period. You can choose the latest restorable time to restore to the latest possible time, or you can choose a custom to define a specific time for restoring the data.

Backup that occurs in CloudSQL for MySQL is incremental. It contains only the changes of data after previous backup. The oldest backup is similar to your current database size. When the oldest backup is removed, the size of the following oldest backup is increasing, so that the full backup still exists.

Automated backup occurs every day and it is retained by 7 days by default. CloudSQL stores backup data in 2 regions for redundancy. One region can be in the same as the instance is running, and the other is in a different region.

Point in time recovery in CloudSQL will create a new instance, the instance setting will inherit with the source of the instance. Before you do the point in time recovery, please ensure that you already enabled binary logging. When you perform point in time recovery, you just need to fill in the binary log name and recovery position.

Azure database for MySQL takes data files backup and transaction logs. The schedule of backup itself is a combination of full and differential backup for servers with up to 4TB storage size, while snapshot backup happens for up to 16TB max storage server. 

The full backup runs once a week, while the differential backups occur twice a day. The default retention period of the backup is 7 days, but you can always configure the retention up to 35 days.

There are two types of restoration in Azure database for MySQL, which is :

• Point-in-time restore, available as a redundancy backup option, or you can create a new server in the same region as your original server, utilizing the full backup and transaction log for restoring the data.
• Geo-restore, available if you configure a geo-redundant in the storage option. It will allow you to restore your backup to different regions.

Please note that neither AWS, Google or Azure allow you to download your backups. 

Database Monitoring

RDS provides monitoring integration with CloudWatch, you can see some of metrics such as CPU Utilization, DB Connections, write IOPS & read IOPS, write throughput & read throughput, write and read latency. You can create an alarm to trigger the alert from CloudWatch, based on some metrics category and just define the threshold.

Similar to RDS, GCP CloudSQL also integrates with stackdriver, you can see metrics like : CPU Utilization, memory utilization, active connections, transactions/sec, ingress/egress bytes, write & read operations, replication lag.

Azure database for MySQL provides some metrics, eg; Active Connections, CPU percent, failed connection, IO percent, memory percent, replication lag, storage percent, storage used. You can also create alerts in Azure databases for MySQL, choose the metrics, and define the rules.

Conclusion

Based on 4 key areas; MySQL Version & Patches, Known Issues and Limitations, Backup and Restore, Database Monitoring, in my opinion Amazon RDS for MySQL is still the best database as a service for MySQL. It provides detailed versions and patches, very limited issues and limitations compared to others. It is a convenient way of running MySQL, with the caveat that the price for the service has gone up in recent years.