# Migration options for large MySQL and MariaDB databases Migration options You can choose from an extensive range of options to migrate from on-premises MySQL or MariaDB databases to Amazon Relational Database Service (Amazon RDS) or Amazon Aurora MySQL-Compatible Edition databases instances. Choosing the right migration approach and tool is essential for a successful migration, and in this guide, you evaluate the options based on your usability, data size, and downtime requirements. The following are the common migration tools and approaches that are available to migrate multi-terabyte self-managed MySQL databases efficiently to Amazon RDS, Aurora, or Amazon Elastic Compute Cloud (Amazon EC2) database instances: + [Percona XtraBackup](percona-xtrabackup.md) (Physical) + [MyDumper](mydumper.md) (Logical) + [mysqldump and mysqlpump](mysqldump-and-mysqlpump.md) (Logical) + [Split backup](split-backup.md) (Physical, logical, or both) The following are the common migration tools and approaches that are available to migrate multi-terabyte MySQL-compatible (such as MariaDB) databases efficiently to Amazon RDS, Aurora, or Amazon EC2 database instances: + [MyDumper](mydumper.md) (Logical) + [mysqldump and mysqlpump](mysqldump-and-mysqlpump.md) (Logical) + [Split backup](split-backup.md) (Physical, logical, or both) For each migration tool, there are several approaches you can use to transfer the large database backup file to the AWS Cloud. Options are provided for each tool, and you can also use Amazon S3 File Gateway. For more information, see [Using Amazon S3 File Gateway to transfer backup files](amazon-s3-file-gateway.md) in this guide. # Percona XtraBackup **Important** Percona XtraBackup is not supported for MariaDB versions 10.3 or later and is only partially supported for versions 10.1 and 10.2. [Percona XtraBackup](https://docs.percona.com/percona-xtrabackup/8.0/index.html) is a common open-source warm backup software for MySQL and MariaDB that makes non-blocking backups for InnoDB and XtraDB storage engines. It works with MySQL or MariaDB servers. For more information about the tool and some of its features and benefits, see [About Percona XtraBackup](https://docs.percona.com/percona-xtrabackup/8.0/about-xtrabackup.html) in the Percona XtraBackup documentation. This tool uses the physical migration approach. It directly copies the MySQL or MariaDB data directory and the files within it. For large databases, such as those larger than 100 GB, this can provide a significantly better restoration time than some other tools. You create a backup of the on-premises source database, migrate the backup files to the cloud, and then restore the backup on the new, target database instance. The following diagram shows the high-level steps involved in migrating a database by using an Percona XtraBackup backup file. Depending on the size of the backup file, there are two options available for transferring the backup to an Amazon Simple Storage Service (Amazon S3) bucket in the AWS Cloud. ![\[Diagram of migrating a Percona XtraBackup file and restoring it on an AWS DB instance.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/migration-large-mysql-mariadb-databases/images/percona-xtrabackup-migration-aws.png) The following are the steps for using Percona XtraBackup to migrate a database to the AWS Cloud: 1. Install Percona XtraBackup on the on-premises server. If you’re using Amazon Aurora MySQL version 2 or Amazon RDS, see [Installing Percona XtraBackup 2.4](https://docs.percona.com/percona-xtrabackup/2.4/installation.html). If you’re using Amazon Aurora MySQL version 3, see Installing [Percona XtraBackup 8.0](https://docs.percona.com/percona-xtrabackup/8.0/installation.html) in the Percona XtraBackup documentation. 1. Create a full backup of the source MySQL or MariaDB database. For instructions for Percona XtraBackup 2.4, see [Full backup](https://docs.percona.com/percona-xtrabackup/2.4/backup_scenarios/full_backup.html). For instructions for Percona XtraBackup 8.0, see [Create a full backup](https://docs.percona.com/percona-xtrabackup/8.0/create-full-backup.html). 1. Transfer the backup files over the internet by using an approved service or tool in your organization, such as the following: + [AWS Site-to-Site VPN](https://docs.aws.amazon.com/vpn/latest/s2svpn/VPC_VPN.html) + [AWS Client VPN](https://docs.aws.amazon.com/vpn/latest/clientvpn-user/client-vpn-user-what-is.html) + [AWS Direct Connect](https://docs.aws.amazon.com/directconnect/latest/UserGuide/Welcome.html) + [Amazon S3 File Gateway](https://docs.aws.amazon.com/filegateway/latest/files3/what-is-file-s3.html) (For more information, see [Using Amazon S3 File Gateway to transfer backup files](amazon-s3-file-gateway.md) in this guide.) + [AWS Command Line Interface (AWS CLI)](https://aws.amazon.com/getting-started/hands-on/backup-to-s3-cli/) 1. From the Amazon S3 bucket, restore the backup files to the target database instance. For instructions, see the following: + For Aurora MySQL-Compatible Edition, see [Migrating data from MySQL by using an Amazon S3 bucket](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.ExtMySQL.html#AuroraMySQL.Migrating.ExtMySQL.S3.Restore) in the Amazon RDS documentation. + For Amazon RDS for MySQL or for Amazon EC2, see [Importing data into a MySQL DB instance](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.Other.html). + For Amazon RDS for MariaDB or for Amazon EC2, see [Importing data into a MariaDB DB instance](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MariaDB.Procedural.Importing.html). 1. (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](https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterbaseconfig.html) in the MySQL documentation + For Amazon Aurora, see the following: + [Synchronizing the Amazon Aurora MySQL DB cluster with the MySQL database using replication](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.ExtMySQL.html#AuroraMySQL.Migrating.ExtMySQL.S3.RepSync) in the Aurora documentation + [Using binlog replication in Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.html) in the Aurora documentation + For Amazon RDS, see the following: + [Working with MySQL replication](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_MySQL.Replication.html) in the Amazon RDS documentation + [Working with MariaDB replication](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_MariaDB.Replication.html) in the Amazon RDS documentation + For Amazon EC2, see the following: + [Setting Up Binary Log File Position Based Replication](https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication-howto.html) in the MySQL documentation + [Setting Up Replicas](https://dev.mysql.com/doc/refman/8.0/en/replication-setup-replicas.html) in the MySQL documentation + [Setting Up Replication](https://mariadb.com/kb/en/setting-up-replication/) in the MariaDB documentation ## Advantages + Because Percona XtraBackup uses a physical migration approach, the restore process is typically faster than tools that use a logical migration approach. This is because the performance is limited by the disk or network throughput rather than the compute resources necessary for data processing. + Because the restore process is a direct copy of the files from the S3 bucket to the target database instance, Percona XtraBackup files typically restore faster than backup files created with other tools. + Percona XtraBackup is adaptable. For example, it supports multiple threads to help you copy files faster and supports compression to reduce the size of the backup. ## Limitations + Offline backup is not possible because Percona XtraBackup must have access to the source database server. + Percona XtraBackup can be used only on systems with identical system architectures. For example, it is not possible to restore a backup of a source database running on Intel for Windows Server to an ARM for Linux target server. + Percona XtraBackup isn't supported for MariaDB version 10.3 or later, and it is only partially supported for MariaDB version 10.2 and version 10.1. For more information, see [Percona XtraBackup Overview: Compatibility with MariaDB](https://mariadb.com/kb/en/percona-xtrabackup-overview/#compatibility-with-mariadb) in the MariaDB knowledge base. + You cannot use Percona XtraBackup to restore a source MariaDB database to a target MySQL database instance, such as Amazon RDS for MySQL or Aurora MySQL-Compatible. + The total volume of data and number of objects you can store in an S3 bucket are unlimited, however, the maximum file size is 5 TB. If your backup file exceeds 5 TB, you can split it up into multiple, smaller files. + When the `innodb_file_per_table` setting is off, Percona XtraBackup doesn't support partial backups that use `--tables`, `--tables-exclude`, `--tables-file`, `--databases`, `--databases-exclude`, or `--databases-file`. For more information for Percona XtraBackup version 2.4, see [Partial backups](https://docs.percona.com/percona-xtrabackup/2.4/innobackupex/partial_backups_innobackupex.html). For more information for Percona XtraBackup version 8.0, see [Create a partial backup](https://docs.percona.com/percona-xtrabackup/8.0/create-partial-backup.html). ## Best practices + To improve the performance of the backup process, do the following: + Copy multiple files in parallel by using [--parallel=](https://docs.percona.com/percona-xtrabackup/2.4/xtrabackup_bin/xbk_option_reference.html#-parallel) + Compress multiple files in parallel by using [--compress-threads=](https://docs.percona.com/percona-xtrabackup/2.4/xtrabackup_bin/xbk_option_reference.html#-compress-threads) + Increase memory by using [--use-memory=](https://docs.percona.com/percona-xtrabackup/2.4/xtrabackup_bin/xbk_option_reference.html#-use-memory) + Encrypt multiple files in parallel by using [--encrypt-threads=](https://docs.percona.com/percona-xtrabackup/2.4/xtrabackup_bin/xbk_option_reference.html#-encrypt-threads) + Ensure that there is sufficient space on the source server to take the database backup files. + Generate the database backup with the Percona xbstream (.xbstream) format file. For more information, see [The xbstream binary overview](https://docs.percona.com/percona-xtrabackup/8.0/xbstream-binary-overview.html) in the Percona XtraBackup documentation. # MyDumper [MyDumper](https://github.com/mydumper/mydumper#what-is-mydumper) (GitHub) is an open-source, logical migration tool that consists of two utilities: + MyDumper exports a consistent backup of MySQL databases. It supports backing up the database by using multiple parallel threads, up to one thread per available CPU core. + myloader reads the backup files created by MyDumper, connects to the target database instance, and then restores the database. The following diagram shows the high-level steps involved in migrating a database by using a MyDumper backup file. This architecture diagram includes three options for migrating the backup file from the on-premises data center to an EC2 instance in the AWS Cloud. ![\[Diagram of migrating a MyDumper backup file and using myloader to restore it on the AWS DB instance.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/migration-large-mysql-mariadb-databases/images/mydumper-myloader-migration-aws.png) The following are the steps for using MyDumper to migrate a database to the AWS Cloud: 1. Install MyDumper and myloader. For instructions, see [How to install mydumper/myloader](https://github.com/mydumper/mydumper#how-to-install-mydumpermyloader) (GitHub). 1. Use MyDumper to create a backup of the source MySQL or MariaDB database. For instructions, see [How to use MyDumper](https://github.com/mydumper/mydumper#how-to-use-mydumper). 1. 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](https://docs.aws.amazon.com/fsx/latest/WindowsGuide/using-file-shares.html) or [Amazon Elastic File System (Amazon EFS)](https://docs.aws.amazon.com/efs/latest/ug/efs-onpremises.html) file system to the on-premises server that runs your database instance. You can use AWS Direct Connect or Site-to-Site 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 the 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](https://docs.aws.amazon.com/AmazonS3/latest/userguide/transfer-acceleration.html) to transfer the file more quickly. Use the [s3fs-fuse](https://github.com/s3fs-fuse/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](https://docs.aws.amazon.com/datasync/latest/userguide/what-is-datasync.html) to move the backup file to an Amazon S3 bucket. Use the [s3fs-fuse](https://github.com/s3fs-fuse/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](amazon-s3-file-gateway.md) in this guide. 1. Use myloader to restore the backup on the target database instance. For instructions, see [myloader usage](https://github.com/mydumper/mydumper_docs/blob/0e5cd71a5549c8a5de0105adf4d5f95953eadb67/myloader_usage.rst) (GitHub). 1. (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](https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterbaseconfig.html) in the MySQL documentation + For Amazon Aurora, see the following: + [Synchronizing the Amazon Aurora MySQL DB cluster with the MySQL database using replication](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.ExtMySQL.html#AuroraMySQL.Migrating.ExtMySQL.S3.RepSync) in the Aurora documentation + [Using binlog replication in Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.html) in the Aurora documentation + For Amazon RDS, see the following: + [Working with MySQL replication](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_MySQL.Replication.html) in the Amazon RDS documentation + [Working with MariaDB replication](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_MariaDB.Replication.html) in the Amazon RDS documentation + For Amazon EC2, see the following: + [Setting Up Binary Log File Position Based Replication](https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication-howto.html) in the MySQL documentation + [Setting Up Replicas](https://dev.mysql.com/doc/refman/8.0/en/replication-setup-replicas.html) in the MySQL documentation + [Setting Up Replication](https://mariadb.com/kb/en/setting-up-replication/) in the MariaDB documentation ## Advantages + MyDumper supports parallelism by using multi-threading, which improves the speed of backup and restore operations. + MyDumper avoids expensive character set conversion routines, which helps ensure the code is highly efficient. + MyDumper simplifies the data view and parsing by using dumping separate files for tables and metadata. + MyDumper maintains snapshots across all threads and provides accurate positions of primary and secondary logs. + You can use Perl Compatible Regular Expressions (PCRE) to specify whether to include or exclude tables or databases. ## Limitations + You might choose a different tool if your data transformation processes require intermediate dump files in flat format instead of SQL format. + myloader doesn't import database user accounts automatically. If you are restoring the backup to Amazon RDS or Aurora, recreate the users with the required permissions. For more information, see [Master user account privileges](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.MasterAccounts.html) in the Amazon RDS documentation. If you are restoring the backup to an Amazon EC2 database instance, you can manually export the source database user accounts and import them into the EC2 instance. ## Best practices + Configure MyDumper to divide each table into segments, such as 10,000 rows in each segment, and write each segment in a separate file. This makes it possible to import the data in parallel later. + If you are using the InnoDB engine, use the `--trx-consistency-only` option to minimize locking. + Using MyDumper to export the database can become read-intensive, and the process can impact overall performance of the production database. If you have a replica database instance, run the export process from the replica. Before you run the export from the replica, stop the replication SQL thread. This helps the export process run more quickly. + Don't export the database during peak business hours. Avoiding peak hours can stabilize the performance of your primary production database during the database export. + Amazon RDS for MySQL doesn't support the `keyring_aws` plugin. For more information, see [Known issues and limitations](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.KnownIssuesAndLimitations.html#MySQL.Concepts.Limits.KeyRing). To migrate the on-premises encrypted tables to the Amazon RDS instance, in the backup scripts, you need to remove `ENCRYPTION` or `DEFAULT ENCRYPTION` from the `CREATE TABLE` syntax. For encryption at rest, you can use an AWS Key Management Service (AWS KMS) key. For more information, see [Encrypting Amazon RDS resources](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.Encryption.html). # mysqldump and mysqlpump [mysqldump](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html) and [mysqlpump](https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html) 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 version 5.7.8. mysqlpump was removed in MySQL version 8.4. The following diagram shows the high-level steps involved in migrating a database by using a mysqldump or mysqlpump backup file. ![\[Diagram of migrating a mysqldump or mysqlpump backup file and restoring it on an AWS DB instance.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/migration-large-mysql-mariadb-databases/images/mysqldump-mysqlpump-migration-aws.png) 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](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install-linux-quick.html) in the MySQL documentation. This installs both mysqldump and mysqlpump. 1. Using mysqldump or mysqlpump, create a backup of the source, on-premises database. For instructions, see [mysqldump](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html) and [mysqlpump](https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html) in the MySQL documentation, or see [Making Backups with mysqldump](https://mariadb.com/kb/en/making-backups-with-mysqldump/) in the MariaDB documentation. For more information about invoking MySQL programs and specifying options, see [Using MySQL programs](https://dev.mysql.com/doc/refman/8.0/en/programs-using.html). 1. 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](https://docs.aws.amazon.com/fsx/latest/WindowsGuide/using-file-shares.html) or [Amazon Elastic File System (Amazon EFS)](https://docs.aws.amazon.com/efs/latest/ug/efs-onpremises.html) file system to the on-premises server that runs your database instance. You can use AWS Direct Connect or Site-to-Site 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 the 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](https://docs.aws.amazon.com/AmazonS3/latest/userguide/transfer-acceleration.html) to transfer the file more quickly. Use the [s3fs-fuse](https://github.com/s3fs-fuse/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](https://docs.aws.amazon.com/datasync/latest/userguide/what-is-datasync.html) to move the backup file to an Amazon S3 bucket. Use the [s3fs-fuse](https://github.com/s3fs-fuse/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](amazon-s3-file-gateway.md) in this guide. 1. Use the native restore method to restore the backup on the target database. For instructions, see [Reloading SQL-Format Backups](https://dev.mysql.com/doc/refman/8.0/en/reloading-sql-format-dumps.html) in the MySQL documentation, or see [Restoring Data from Dump Files](https://mariadb.com/kb/en/restoring-data-from-dump-files/) in the MariaDB documentation. 1. (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](https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterbaseconfig.html) in the MySQL documentation + For Amazon Aurora, see the following: + [Synchronizing the Amazon Aurora MySQL DB cluster with the MySQL database using replication](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.ExtMySQL.html#AuroraMySQL.Migrating.ExtMySQL.S3.RepSync) in the Aurora documentation + [Using binlog replication in Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.html) in the Aurora documentation + For Amazon RDS, see the following: + [Working with MySQL replication](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_MySQL.Replication.html) in the Amazon RDS documentation + [Working with MariaDB replication](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_MariaDB.Replication.html) in the Amazon RDS documentation + For Amazon EC2, see the following: + [Setting Up Binary Log File Position Based Replication](https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication-howto.html) in the MySQL documentation + [Setting Up Replicas](https://dev.mysql.com/doc/refman/8.0/en/replication-setup-replicas.html) in the MySQL documentation + [Setting Up Replication](https://mariadb.com/kb/en/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 or for versions 8.4 and later. + 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](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html) to create and restore the backup. # Split backup A *split backup* strategy is when you migrate a large database server by dividing the backup into multiple parts. You might use different approaches to migrate each part of the backup. This can be the best option for the following use cases: + **Large database server but small individual databases** – This is a good approach when the size of the total database server is multiple TBs but the size of each individual, independent user database is less than 1 TB. To reduce the overall migration period, you can migrate individual database separately and in parallel. Let's use an example of an on-premises, 2 TB database server. This server consists of four databases that are each 0.5 TB. You can take backups of each individual database separately. When restoring the backup, you can either restore all databases on an instance in parallel, or if the databases are independent, you can restore each backup on a separate instance. It's a best practice to restore independent databases on separate instances, instead of restoring them on the same instance. For more information, see Best practices in this guide. + **Large database server but small individual database tables** – This is a good approach when the size of the total database server is multiple TBs but the size of each independent database table is less than 1 TB. To reduce the overall migration period, you can migrate independent tables individually. Let's use an example of a single user database that is 1 TB, and it is the only database in an on-premises database server. There are 10 tables in the database, and each is 100 GB. You can take backups of each individual table separately. When restoring the backup, you can restore all tables on an instance in parallel. + **A database contains both transactional and non-transactional workload tables** – Similar to the previous use case, you can use a split backup approach when you have both transactional and non-transactional workload tables in the same database. Let's use an example of a 2 TB database that consists of 0.5 TB of critical workload tables used for online transaction processing (OLTP) and a single 1.5 TB table used for archiving old data. You can take the backup of all database objects except the archive table as a single-transaction and consistent backup. Then, you take another, separate backup of the archive table only. For the archive table backup, you can also consider taking multiple, parallel backups by using conditions to split the number of rows in the backup file. The following is an example: ``` mysqldump -p your_db1 --tables your_table1 --where="column1 between 1 and 1000000 " > your_table1_part1.sql mysqldump -p your_db1 --tables your_table1 --where="column1 between 1000001 and 2000000 " > your_table1_part2.sql mysqldump -p your_db1 --tables your_table1 --where="column1 > 2000000 " > your_table1_part3.sql ``` When restoring the backup files, you can restore transactional workload backup and the archive table backup in parallel. + **Compute resource limitations** – If you have limited compute resources in the on-premises server, such as CPU, memory, or disk I/O, this can affect stability and performance when taking the backup. Instead of taking a complete backup, you can divide it into parts. For example, an on-premises production server might be heavily loaded with workloads and have limited CPU resources. If you take a single-run backup of a multi-terabyte database on this server, it can consume additional CPU resources and adversely affect the production server. Instead of taking the complete database backup, divide the backup into multiple parts, such as 2–3 tables each.