What is MySQL?

MySQL serves as the primary relational data store for many popular websites, applications, and commercial products. With more than 25 years of community-backed development and support, MySQL is a reliable, stable, and secure structured query language (SQL) database management system.

The MySQL database is suitable for a wide variety of use cases, including mission-critical apps and dynamic websites. MySQL is also a popular SQL engine for modern applications, due to its simplicity and reliability. Application developers gain an engine with a simple client-server interface and faster onboarding, while organizations save costs on training.

AWS supports MySQL in a variety of ways, including a fully managed database service, Amazon Relational Database Service (RDS) for MySQL. Amazon Aurora MySQL-Compatible Edition is also built using MySQL, and Amazon RDS supports the popular MySQL fork project, MariaDB.

You can also host MySQL software on Amazon EC2 and self-manage the database, or browse the 3rd party MySQL offerings on AWS Marketplace.

The first version of MySQL Server was released in 1995 by the Swedish company MySQL AB, founded by David Axmark, Allan Larsson, and Michael Widenius. MySQL takes its name from Widenius’ daughter, named My. The MySQL project was released as open source in 2000, under the GNU General Public License (GPL).

By 2001, MySQL had reached more than 2 million active installations; by 2004, the software was being downloaded more than 30,000 times a day. MySQL was acquired by Sun Microsystems in 2008 and, when Oracle acquired Sun in 2009, it also took ownership of MySQL. 

MySQL database has found favor with web developers due to its ease-of-use and productivity features, including triggers, stored procedures, and updatable views. MySQL includes utilities like mysqldump, a backup program; mysqladmin, an administrative client; and MySQL Workbench, a GUI for management and migration work.

Over time, MySQL has boosted its performance capabilities with features including B-tree disk tables with index compression, thread-based memory allocation, and optimized nested-loop joins. Row-level locking and consistent reads in the storage engine give MySQL additional performance benefits for multi-user concurrency. MySQL follows and promotes design practices for faster performance by providing core functionality with a minimal feature set.

MySQL’s InnoDB transactional storage engine adheres to the ACID model, with additional capabilities that improve data protection, including point-in-time recovery and autocommit. InnoDB also offers data integrity through support for foreign key constraints, preventing data inconsistencies across data tables.

MySQL includes hardened and flexible security features, including host-based verification and encryption of password traffic. InnoDB offers additional security benefits, with data-at-rest tablespace encryption using a two-tier encryption key architecture.

MySQL is available under an open source license (the GNU General Public License), allowing you to freely use and modify the source code when you install MySQL. Managed versions of MySQL, like Amazon RDS for MySQL, carry no additional licensing costs for the MySQL engine, unlike proprietary SQL engines.

MySQL’s large, global community of contributors and enthusiasts brings many additional and long-tail benefits to using the database system. For example, the MySQL community stays on top of security issues and bug fixes, contributing to the overall resilience of the software. MySQL user groups, events, forums, and mailing lists provide a built-in network for education and support.

MySQL is suitable for any application requiring a transactional SQL engine, including mission-critical apps and heavily trafficked websites. MySQL adheres to ACID principles and includes extensions to ANSI/ISO Standard SQL, as well as support for XML and JSON.

MySQL also supports high-availability database clustering and can handle petabyte-sized databases. Popular applications that use the MySQL database include TYPO3, MODx, Joomla, WordPress, phpBB, MyBB, and Drupal. 

MySQL is one of the most popular transactional engines for ecommerce platforms. MySQL is particularly useful for managing all the data for customers, transactions, and product catalogs.

In ecommerce solutions, MySQL is often used in conjunction with other, non-relational databases, including document and key-value stores for syncing order data, and storing non-product data. 

MySQL is integral to countless applications running on the LAMP open source software stack (LAMP stands for Linux, Apache, MySQL, and PHP/Python/Perl). LAMP is a ubiquitous web services solution stack and is widely considered the platform of choice for dynamic websites and high-performance web applications.

MariaDB is a popular fork of MySQL from MariaDB, which was founded by the original developers of MySQL who intended it to remain free and open source under the GNU GPL. While MariaDB is maintained separately from MySQL, it retains a high degree of MySQL compatibility, including library binary equivalency, exact matching with application programming interfaces (APIs), and various management tools. AWS supports MariaDB with the fully managed database service, Amazon RDS for MariaDB.

Percona Server is another popular fork of MySQL from Percona. Percona Server includes XtraDB, Percona's fork of the InnoDB Storage Engine.

MySQL leads with SQL language support. MySQL also supports most leading programming languages and protocols, enabling users with: Python, Java, Perl, Node.js, .NET, PHP, Ruby, Erlang, C/C++, Tcl, ODBC, and Objective-C.

MySQL and PostgreSQL are both popular open source relational databases. MySQL's popularity is because it has been perceived as easy to use and fast, whereas PostgreSQL has been perceived as feature-rich and more comparable to commercially licensed versions of databases like Oracle. However, the current major versions of MySQL and PostgreSQL both offer many robust features and capabilities in support of query performance, reliability, security, and more. You can learn more about PostgreSQL and try Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL using the AWS Free Tier.

The most practical native approach to the MySQL relational database management system migration is to perform an SQL dump and then conduct replication activities to ensure synchronized databases. MySQL’s mysqldump, a built-in backup utility, serves as a foundation for MySQL server database migration. Mysqldump dumps the entire database with a dump file and provides a set of SQL statements to regenerate the database. This method is useful for a database size under 10GB, when you have a fast and stable internet connection, and data migration time is not critical.

By transferring Percona XtraBackup files and backups into Amazon S3 first, you can migrate database servers in a 1-to-1 fashion from a source database to a new Amazon RDS for MySQL or Aurora DB cluster. Using this data migration conversion process to load data can be significantly faster than using mysqldump.

The built-in INFORMATION_SCHEMA Database can be used as a MySQL metadata explorer. Using this MySQL metadata explorer in combination with AWS DMS Schema Conversion, you can determine whether your source database schemas match your destination database schemas and data types and identify where to remediate any gaps.

AWS Database Migration Service (DMS) discovers, assesses, converts, and migrates MySQL database workloads to AWS, decreasing manual editing and effort. The AWS DMS migration wizard enables you to select both the source and target infrastructure, utilizing automated replication with Change Data Capture (CDC), and creates reports with automated assessment and target object recommendations. There is no need to convert schemas or object definitions in MySQL to MySQL migrations. Leverage the AWS Application Discovery Service to discover any dependencies in your MySQL server databases before you migrate data. 

If you are performing a simple lift-and-shift to migrate data to EC2 from your on-premises MySQL server, you can migrate your entire server to EC2 using the AWS Application Migration Service. You can apply this technique for single databases or multiple database objects.