mysqldump and mysqlpump

mysqldump and mysqlpump are native database backup tools for MySQL. MariaDB supports mysqldump but doesn't support mysqlpump. Both of these tools create logical backups and are part of the MySQL client programs. mysqldump supports single-threaded processing. mysqlpump supports parallel processing of databases and objects within databases, to speed up the dump process. It was introduced in MySQL server version 5.7.8.

The following diagram shows the high-level steps involved in migrating a database by using a mysqldump or mysqlpump backup file.

The following are the steps for using mysqldump or mysqlpump to migrate a database to the AWS Cloud:

1. Install MySQL Shell on the on-premises server. For instructions, see Installing MySQL Shell in the MySQL documentation. This installs both mysqldump and mysqlpump.

2. Using mysqldump or mysqlpump, create a backup of the source, on-premises database. For instructions, see mysqldump and mysqlpump in the MySQL documentation, or see Making Backups with mysqldump in the MariaDB documentation. For more information about invoking MySQL programs and specifying options, see Using MySQL programs.

3. Move the backup file to an EC2 instance in the AWS Cloud by using one of the following approaches:

   Approach 3A – Mount an Amazon FSx or Amazon Elastic File System (Amazon EFS) file system to the on-premises server that runs your database instance. You can use AWS Direct Connect or AWS VPN to establish the connection. You can directly back up the database to the mounted file share, or you can perform the backup in two steps by backing up the database to a local file system and then uploading it to the mounted FSx or EFS volume. Next, mount the Amazon FSx or Amazon EFS file system, which is also mounted on the on-premises server, on an EC2 instance.

   Approach 3B – Use AWS CLI, AWS SDK, or Amazon S3 REST API to directly move the backup file from the on-premises server to an S3 bucket. If the target S3 bucket is in an AWS Region that is far away from the data center, you can use Amazon S3 Transfer Acceleration to transfer the file more quickly. Use the s3fs-fuse file system to mount the S3 bucket on the EC2 instance.

   Approach 3C – Install the AWS DataSync agent at the on-premises data center, and then use AWS DataSync to move the backup file to an Amazon S3 bucket. Use the s3fs-fuse file system to mount the S3 bucket on the EC2 instance.

   Note

   You can also use Amazon S3 File Gateway to transfer the large database backup files to an S3 bucket in the AWS Cloud. For more information, see Using Amazon S3 File Gateway to transfer backup files in this guide.

4. Use the native restore method to restore the backup on the target database. For instructions, see Reloading SQL-Format Backups in the MySQL documentation, or see Restoring Data from Dump Files in the MariaDB documentation.

5. (Optional) You can set up replication between the source database and the target database instance. You can use binary log (binlog) replication to reduce downtime. For more information, see the following:

   • Setting the replication source configuration in the MySQL documentation

   • For Amazon Aurora, see the following:

     • Synchronizing the Amazon Aurora MySQL DB cluster with the MySQL database using replication in the Aurora documentation

     • Using binlog replication in Amazon Aurora in the Aurora documentation

   • For Amazon RDS, see the following:

     • Working with MySQL replication in the Amazon RDS documentation

     • Working with MariaDB replication in the Amazon RDS documentation

   • For Amazon EC2, see the following:

     • Setting Up Binary Log File Position Based Replication in the MySQL documentation

     • Setting Up Replicas in the MySQL documentation

     • Setting Up Replication in the MariaDB documentation

Advantages

• mysqldump and mysqlpump are included in the MySQL Server installation

• The backup files generated by these tools are in a more readable format.

• Before restoring the backup file, you can modify the resultant .sql file by using a standard text editor.

• You can back up a specific table, database, or even a particular data selection.

• mysqldump and mysqlpump are machine-architecture independent.

Limitations

• mysqldump is a single-threaded backup process. Performance for taking a backup is good for small databases, but it can become inefficient when the backup size is larger than 10 GB.

• Backup files in logical format are voluminous, especially when saved as text, and often slow to create and restore.

• Data restoration can be slow because reapplying SQL statements in the target DB instance involves intensive disk I/O and CPU processing for insertion, index creation, and referential integrity constraints enforcement.

• The mysqlpump utility is not supported for MySQL versions earlier than 5.7.8.

• By default, mysqlpump does not take a backup of the system databases, such as performance_schema or sys. To backup part of the system database, explicitly name it in the command line.

• mysqldump does not backup InnoDB CREATE TABLESPACE statements.

Note: Backups of CREATE TABLESPACE statements and system databases are useful only when you are restoring MySQL or MariaDB database backups to an EC2 instance. These backups are not used for Amazon RDS or Aurora.

Best practices

• When you're restoring the database backup, disable the key checks, such as FOREIGN_KEY_CHECKS, at the session level in the target database . This increases the restoration speed.

• Make sure the database user has sufficient privileges to create and restore the backup.