# Importing data into an Amazon RDS for MariaDB DB instance Importing data into an RDS for MariaDB DB instance You can use several different techniques to import data into an RDS for MariaDB DB instance. The best approach depends on a number of factors: + Source of the data + Amount of data + One-time import or ongoing + Amount of downtime If you are also migrating an application with the data, the amount of downtime is important to consider. The following table lists techniques to importing data into an RDS for MariaDB DB instance: **Note** Amazon RDS doesn't support `mariadb-backup` or importing from Amazon S3 for MariaDB. | Source | Amount of data | One time or ongoing | Application downtime | Technique | More information | | --- | --- | --- | --- | --- | --- | | Existing MariaDB database on premises or on Amazon EC2 | Any | Ongoing | Minimal | Configure replication with an existing MariaDB database as the replication source. You can configure replication into a MariaDB DB instance using MariaDB global transaction identifiers (GTIDs) when the external instance is MariaDB version 10.0.24 or higher, or using binary log coordinates for MariaDB instances on earlier versions than 10.0.24. MariaDB GTIDs are implemented differently than MySQL GTIDs, which aren't supported by Amazon RDS. | [Configuring binary log file position replication with an external source instance](MySQL.Procedural.Importing.External.ReplMariaDB.md) [Importing data to an Amazon RDS for MariaDB DB instance with reduced downtime](mariadb-importing-data-reduced-downtime.md) | | Any existing database | Any | One time or ongoing | Minimal | Use AWS Database Migration Service to migrate the database with minimal downtime and, for many database DB engines, continue ongoing replication. | [What is AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html) and [Using a MySQL-compatible database as a target for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.MySQL.html) in the *AWS Database Migration Service User Guide* | | Existing MariaDB DB instance | Any | One time or ongoing | Minimal | Create a read replica for ongoing replication. Promote the read replica for one-time creation of a new DB instance. | [Working with DB instance read replicas](USER_ReadRepl.md) | | Existing MariaDB database | Small | One time | Some | Copy the data directly to your MariaDB DB instance using a command-line utility. | [Importing data from an external MariaDB database to an Amazon RDS for MariaDB DB instance](mariadb-importing-data-external-database.md) | | Data not stored in an existing database | Medium | One time | Some | Create flat files and import them using MariaDB `LOAD DATA LOCAL INFILE` statements. | [Importing data from any source to an Amazon RDS for MariaDB DB instance](mariadb-importing-data-any-source.md) | **Note** The `mysql` system database contains authentication and authorization information required to log in to your DB instance and access your data. Dropping, altering, renaming, or truncating tables, data, or other contents of the `mysql` database in your DB instance can result in errors and might render the DB instance and your data inaccessible. If this occurs, you can restore the DB instance from a snapshot using the AWS CLI [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html) command. You can recover the DB instance using [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html) command. # Importing data considerations for MariaDB Importing data considerations The following content contains technical information related to loading data into MariaDB. This content is aimed at users who are familiar with the MariaDB server architecture. ## Binary logging Enabling binary logging reduces data load performance and requires up to four times additional disk space compared to disabled logging. The transaction size used to load the data directly affects system performance and disk space needs—larger transactions require more resources. ## Transaction size Transaction size influences the following aspects of MariaDB data loads: + Resource consumption + Disk space utilization + Resume process + Time to recover + Input format (flat files or SQL) This section describes how transaction size affects binary logging and makes the case for disabling binary logging during large data loads. You can enable and disable binary logging by setting the Amazon RDS automated backup retention period. Non-zero values enable binary logging, and zero disables it. For more information, see [Backup retention period](USER_WorkingWithAutomatedBackups.BackupRetention.md). This section also describes the impact of large transactions on InnoDB and why it's important to keep transaction sizes small. ### Small transactions For small transactions, binary logging doubles the number of disk writes required to load the data. This effect can severely degrade performance for other database sessions and increase the time required to load the data. The degradation experienced depends in part on the following factors: + Upload rate + Other database activity taking place during the load + Capacity of your Amazon RDS DB instance The binary logs also consume disk space roughly equal to the amount of data loaded until the logs are backed up and removed. Amazon RDS minimizes this by frequently backing up and removing binary logs. ### Large transactions For large transactions, binary logging triples IOPS and disk usage for the following reasons: + The binary log cache stores transaction data temporarily on disk. + This cache grows with the transaction size, which consumes disk space. + When the transaction (commit or rollback) completes, the system copies the cache to the binary log. This process creates three copies of the data: + The original data + The cache on disk + The final binary log entry Each write operation incurs additional IO, further impacting performance. Because of this, binary logging requires triple the disk space compared to disabled logging. For example, loading 10 GiB of data as a single transaction creates three copies: + 10 GiB for the table data + 10 GiB for the binary log cache + 10 GiB for the binary log file The total temporary disk space required is 30 GiB. Important disk space considerations: + The cache file persists until either the session ends or a new transaction creates another cache. + The binary log remains until it's backed up, potentially holding 20 GiB (cache and log) for an extended period. If you use `LOAD DATA LOCAL INFILE` to load the data, data recovery creates a fourth copy in case the database has to be recovered from a backup made before the load. During recovery, MariaDB extracts the data from the binary log into a flat file. MariaDB then runs `LOAD DATA LOCAL INFILE`. Building on the preceding example, this recovery requires a total temporary disk space of 40 GiB, or 10 GiB each for table, cache, log, and local file. Without at least 40 GiB of free disk space, recovery fails. ### Optimizing large data loads For large data loads, disable binary logging to reduce overhead and disk space requirements. You can disable binary logging by setting the backup retention period to 0. After loading completes, restore the backup retention period to the appropriate non-zero value. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md) and [Backup retention period](USER_ModifyInstance.Settings.md) in the settings table. **Note** If the DB instance is a source DB instance for read replicas, then you can't set the backup retention period to 0. Before loading the data, we recommend that you create a DB snapshot. For more information, see [Managing manual backups](USER_ManagingManualBackups.md). ## InnoDB The following information about undo logging and recovery options supports keeping InnoDB transactions small to optimize database performance. ### Understanding InnoDB undo logging Undo is a logging mechanism that enables transaction rollback and supports multi-version concurrency control (MVCC). For MariaDB 10.11 and lower versions, undo logs are stored in the InnoDB system tablespace (usually ibdata1) and are retained until the purge thread removes them. As a result, large data load transactions can cause the system tablespace to become quite large and consume disk space that you can't reclaim unless you recreate the database. For all MariaDB versions, the purge thread must wait to remove any undo logs until the oldest active transaction either commits or rolls back. If the database is processing other transactions during the load, their undo logs also accumulate and can't be removed, even if the transactions commit and no other transaction needs the undo logs for MVCC. In this situation, all transactions—including read-only transactions—slow down. This slowdown occurs because all transactions access all rows that any transaction—not just the load transaction—changes. In effect, transactions must scan through undo logs that long-running load transactions prevented from being purged during an undo log cleanup. This affects performance for any operation accessing modified rows. ### InnoDB transaction recovery options Although InnoDB optimizes commit operations, large transaction rollbacks are slow. For faster recovery, perform a point-in-time recovery or restore a DB snapshot. For more information, see [Point-in-time recovery](USER_PIT.md) and [Restoring to a DB instance](USER_RestoreFromSnapshot.md). ## Data import formats MariaDB supports two data import formats: flat files and SQL. Review the information about each format to determine the best option for your needs. ### Flat files For small transactions, load flat files with `LOAD DATA LOCAL INFILE`. This data import format can provide the following benefits over using SQL: + Less network traffic + Lower data transmission costs + Decreased database processing overhead + Faster processing `LOAD DATA LOCAL INFILE` loads the entire flat file as one transaction. Keep the size of the individual files small for the following advantages: + **Resume capability** – You can keep track of which files have been loaded. If a problem arises during the load, you can pick up where you left off. You might need to retransmit some data to Amazon RDS, but with small files, the amount retransmitted is minimal. + **Parallel data loading** – If you have sufficient IOPS and network bandwidth for a single file load, loading in parallel could save time. + **Load rate control** – If your data load has a negative impact on other processes, you can control the load rate by increasing the interval between files. Large transactions reduce the benefits of using `LOAD DATA LOCAL INFILE` to import data. When you can't break a large amount of data into smaller files, consider using SQL. ### SQL SQL has one main advantage over flat files: you can easily keep transaction sizes small. However, SQL can take significantly longer to load than flat files. Also, after a failure, it can be difficult to determine where to resume—you can't restart mariadb-dump files. If a failure occurs while loading mariadb-dump file, you must modify or replace the file before the load can resume. Or, alternatively, after you correct the cause of the failure, you can restore to the point in time before the load and resend the file. For more information, see [Point-in-time recovery](USER_PIT.md). ## Using Amazon RDS DB snapshots for database checkpoints If you load data over long durations—such as hours or days—without binary logging, use DB snapshots to provide periodic checkpoints for data safety. Each DB snapshot creates a consistent copy of your database instance that serves as a recovery point during system failures or data corruption events. Because DB snapshots are fast, frequent checkpointing has minimal impact on load performance. You can delete previous DB snapshots without impacting database durability or recovery capabilities. For more information about DB snapshots, see [Managing manual backups](USER_ManagingManualBackups.md). ## Reducing database load times The following items are additional tips to reduce load times: + Create all secondary indexes before loading data into MariaDB databases. Unlike other database systems, MariaDB rebuilds the entire table when adding or modifying secondary indexes. This process creates a new table with index changes, copies all data, and drops the original table. + Load data in primary key order. For InnoDB tables, this can reduce load times by 75%–80% and reduce data file size by 50%. + Disable foreign key constraints by setting `foreign_key_checks` to `0`. This is often required for flat files loaded with `LOAD DATA LOCAL INFILE`. For any load, disabling foreign key checks accelerates data loading. After loading completes, re-enable constraints by setting `foreign_key_checks` to `1` and verify the data. + Load data in parallel unless approaching a resource limit. To enable concurrent loading across multiple table segments, use partitioned tables when appropriate. + To reduce SQL execution overhead, combine multiple `INSERT` statements into single multi-value `INSERT` operations. `mariadb-dump` implements this optimization automatically. + Reduce InnoDB log IO operations by setting `innodb_flush_log_at_trx_commit` to `0`. After loading completes, restore `innodb_flush_log_at_trx_commit` to `1`. **Warning** Setting `innodb_flush_log_at_trx_commit` to `0` causes InnoDB to flush its logs every second instead of at each commit. This setting increases performance but can risk transaction loss during system failures. + If you are loading data into a DB instance that doesn't have read replicas, set `sync_binlog` to `0`. After loading completes, restore `sync_binlog parameter`to `1`. + Load data into a Single-AZ DB instance before converting the DB instance to a Multi-AZ deployment. If the DB instance already uses a Multi-AZ deployment, we don't recommend switching to a Single-AZ deployment for data loading. Doing so only provides marginal improvements # Importing data from an external MariaDB database to an Amazon RDS for MariaDB DB instance Importing data from an external database You can import data from an existing MariaDB database to an RDS for MariaDB DB instance. You do so by copying the database with [mysqldump](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html) or [mariadb-dump](https://mariadb.com/kb/en/mariadb-dump/), and piping the database directly into the RDS for MariaDB DB instance. The `mysqldump` or `mariadb-dump` command line utility is commonly used to make backups and transfer data from one MariaDB server to another. It's included with MariaDB client software. Starting with MariaDB 11.0.1, you must use `mariadb-dump` instead of `mysqldump`. A typical `mysqldump` command to move data from an external database to an Amazon RDS DB instance looks similar to the following example. Replace values with your own information. For MariaDB 11.0.1 and higher versions, replace `mysqldump` with `mariadb-dump`. ``` mysqldump -u local_user \ --databases database_name \ --single-transaction \ --compress \ --order-by-primary \ --routines=0 \ --triggers=0 \ --events=0 \ -plocal_password | mariadb -u RDS_user \ --port=port_number \ --host=host_name \ -pRDS_password ``` **Important** Make sure not to leave a space between the `-p` option and the entered password. As a security best practice, specify credentials other than the prompts shown in this example. Make sure that you're aware of the following recommendations and considerations: + Exclude the following schemas from the dump file: + `sys` + `performance_schema` + `information_schema` The `mysqldump` and `mariadb-dump` utility excludes these schemas by default. + If you need to migrate users and privileges, consider using a tool that generates the data control language (DCL) for recreating them, such as the [pt-show-grants](https://www.percona.com/doc/percona-toolkit/LATEST/pt-show-grants.html) utility. + To perform the import, make sure the user doing so has access to the DB instance. For more information, see [Controlling access with security groups](Overview.RDSSecurityGroups.md). The parameters used are as follows: + `-u local_user` – Use to specify a user name. In the first usage of this parameter, specify the name of a user account on the local MariaDB database that you identify with the `--databases` parameter. + `--databases database_name` – Use to specify the name of the database on the local MariaDB instance that you want to import into Amazon RDS. + `--single-transaction` – Use to ensure that all of the data loaded from the local database is consistent with a single point in time. If there are other processes changing the data while `mysqldump` or `mariadb-dump` is reading it, using this parameter helps maintain data integrity. + `--compress` – Use to reduce network bandwidth consumption by compressing the data from the local database before sending it to Amazon RDS. + `--order-by-primary` – Use to reduce load time by sorting each table's data by its primary key. + `--routines` – Use if routines such as stored procedures or functions exist in the database that you are copying. Set the parameter to `0`, which excludes the routines during the import process. Then later manually recreate the routines in the Amazon RDS database. + `--triggers` – Use if triggers exist in the database that you are copying. Set the parameter to `0`, which excludes the triggers during the import process. Then later manually recreate the triggers in the Amazon RDS database. + `--events` – Use if events exist in the database that you are copying. Set the parameter to `0`, which excludes the events during the import process. Then later manually recreate the events in the Amazon RDS database. + `-plocal_password` – Use to specify a password. In the first usage of this parameter, specify the password for the user account that you identify with the first `-u` parameter. + `-u RDS_user` – Use to specify a user name. In the second usage of this parameter, specify the name of a user account on the default database for the MariaDB DB instance that you identify with the `--host` parameter. + `--port port_number` – Use to specify the port for your MariaDB DB instance. By default, this is 3306 unless you changed the value when creating the DB instance. + `--host host_name` – Use to specify the Domain Name System (DNS) name from the Amazon RDS DB instance endpoint, for example, `myinstance.123456789012.us-east-1.rds.amazonaws.com`. You can find the endpoint value in the DB instance details in the Amazon RDS console. + `-pRDS_password` – Use to specify a password. In the second usage of this parameter, you specify the password for the user account identified by the second `-u` parameter. Make sure to create any stored procedures, triggers, functions, or events manually in your Amazon RDS database. If you have any of these objects in the database that you are copying, then exclude them when you run `mysqldump` or `mariadb-dump`. To do so, include the following parameters with your `mysqldump` or `mariadb-dump` command: + `--routines=0` + `--triggers=0` + `--events=0` **Example** The following example copies the `world` sample database on the local host to an RDS for MariaDB DB instance. Replace values with your own information. For Linux, macOS, or Unix: ``` sudo mariadb-dump -u local_user \ --databases world \ --single-transaction \ --compress \ --order-by-primary \ --routines=0 \ --triggers=0 \ --events=0 \ -plocal_password | mariadb -u rds_user \ --port=3306 \ --host=my_instance.123456789012.us-east-1.rds.amazonaws.com \ -pRDS_password ``` For Windows: Run the following command in a command prompt that has been opened by right-clicking **Command Prompt** on the Windows programs menu and choosing **Run as administrator**. Replace values with your own information. ``` mariadb-dump -u local_user ^ --databases world ^ --single-transaction ^ --compress ^ --order-by-primary ^ --routines=0 ^ --triggers=0 ^ --events=0 ^ -plocal_password | mariadb -u RDS_user ^ --port=3306 ^ --host=my_instance.123456789012.us-east-1.rds.amazonaws.com ^ -pRDS_password ``` **Note** As a security best practice, specify credentials other than the prompts shown in the example. # Importing data to an Amazon RDS for MariaDB DB instance with reduced downtime Importing data with reduced downtime In some cases, you might need to import data from an external MariaDB database that supports a live application to an RDS for MariaDB DB instance. Use the following procedure to minimize the impact on availability of applications. This procedure can also help if you are working with a very large database. Using this procedure, you can reduce the cost of the import by reducing the amount of data that is passed across the network to AWS. In this procedure, you transfer a copy of your database data to an Amazon EC2 instance and import the data into a new Amazon RDS database. You then use replication to bring the Amazon RDS database up-to-date with your live external instance, before redirecting your application to the Amazon RDS database. If the external instance is MariaDB 10.0.24 or higher and the target instance is RDS for MariaDB, configure MariaDB replication based on global transaction identifiers (GTIDs). Otherwise, configure replication based on binary log coordinates. We recommend GTID-based replication if your external database supports it because GTID-based replication is a more reliable method. For more information, see [Global transaction ID](http://mariadb.com/kb/en/mariadb/global-transaction-id/) in the MariaDB documentation. The following diagram shows importing an external MariaDB database into a MariaDB database on Amazon RDS. ![\[Workflow that shows importing an external MariaDB database into a MariaDB database on Amazon RDS.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/MigrateMariaDBToRDS_1.png) ## Task 1: Create a copy of your existing database Task 1: Create a copy of your existing database The first step in the process of migrating a large amount of data to an RDS for MariaDB database with minimal downtime is to create a copy of the source data. The following diagram shows creating a backup of the MariaDB database. ![\[Workflow that shows creating a backup of the MariaDB database.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/MigrateMariaDBToRDS_2.png) You can use the `mysqldump` or `mariadb-dump` utility to create a database backup in either SQL or delimited-text format. In MariaDB 10.5, the client is called [mariadb-dump](https://mariadb.com/kb/en/mariadb-dump/). Starting with MariaDB 11.0.1, you must use `mariadb-dump` instead of `mysqldump`. We recommend that you do a test run with each format in a non-production environment to see which method minimizes the amount of time that `mysqldump` or `mariadb-dump` runs. We also recommend that you weigh `mysqldump` or `mariadb-dump` performance against the benefit offered by using the delimited-text format for loading. A backup using delimited-text format creates a tab-separated text file for each table being dumped. To reduce the amount of time required to import your database, you can load these files in parallel using the `LOAD DATA LOCAL INFILE` command. For more information, see [Step 5: Load the data](mariadb-importing-data-any-source.md#mariadb-importing-data-any-source-load-data) in the Importing data from any source procedure. Before you start the backup operation, make sure to set the replication options on the MariaDB database that you are copying to Amazon RDS. The replication options include turning on binary logging and setting a unique server ID. Setting these options causes your server to start logging database transactions and prepares it to be a source replication instance later in this process. Make sure that you're aware of the following recommendations and considerations: + Use the `--single-transaction` option with `mysqldump` or `mariadb-dump` because it dumps a consistent state of the database. To ensure a valid dump file, don't run data definition language (DDL) statements while `mysqldump` or `mariadb-dump` is running. You can schedule a maintenance window for these operations. + Exclude the following schemas from the dump file: + `sys` + `performance_schema` + `information_schema` The `mysqldump` and `mariadb-dump` utilities exclude these schemas by default. + If you need to migrate users and privileges, consider using a tool that generates the data control language (DCL) for recreating them, such as the [pt-show-grants](https://www.percona.com/doc/percona-toolkit/LATEST/pt-show-grants.html) utility. ### To set replication options 1. Edit the `my.cnf` file. This file is usually located under `/etc`. ``` sudo vi /etc/my.cnf ``` Add the `log_bin` and `server_id` options to the `[mysqld]` section. The `log_bin` option provides a file name identifier for binary log files. The `server_id` option provides a unique identifier for the server in source-replica relationships. The following example shows the updated `[mariadb]` section of a `my.cnf` file: ``` [mariadb] log-bin server-id=1 log-basename=master1 binlog-format=mixed ``` For more information, see [Setting the Replication Source Configuration](https://mariadb.com/docs/server/ha-and-performance/standard-replication/setting-up-replication) in the MariaDB documentation. 1. For replication with a Multi-AZ DB cluster, enable `gtid_strict_mode`. For more information, see [ gtid\$1strict\$1mode ](https://mariadb.com/docs/server/ha-and-performance/standard-replication/gtid#gtid_strict_mode) in the MariaDB documentation. Enabling `gtid_strict_mode` isn't required for replication with a DB instance. 1. Restart the `mariadb` service. ``` sudo service mariadb restart ``` ### To create a backup copy of your existing database 1. Create a backup of your data using the `mysqldump` or `mariadb-dump` utility, specifying either SQL or delimited-text format. To improve performance and ensure data integrity, use the `--order-by-primary` and `--single-transaction` options for `mysqldump` and `mariadb-dump`. To avoid including the MySQL system database in the backup, don't use the `--all-databases` option with `mysqldump` or `mariadb-dump`. For more information, see [Creating a Data Snapshot Using mysqldump](https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication-howto-mysqldump.html) in the MySQL documentation. Use `chmod`, if necessary, to make sure that the directory where the backup file is being created is writeable. **Important** On Windows, run the command window as an administrator. + To produce SQL output, use the following command. For MariaDB 10.11 versions and lower, replace `mariadb-dump` with `mysqldump`. For Linux, macOS, or Unix: ``` sudo mariadb-dump \ --databases database_name \ --master-data=2 \ --single-transaction \ --order-by-primary \ -r backup.sql \ -u local_user \ -ppassword ``` **Note** As a security best practice, specify credentials other than the prompts shown in the example. For Windows: ``` mariadb-dump ^ --databases database_name ^ --master-data=2 ^ --single-transaction ^ --order-by-primary ^ -r backup.sql ^ -u local_user ^ -ppassword ``` **Note** As a security best practice, specify credentials other than the prompts shown in the example. + To produce delimited-text output, use the following command. For MariaDB 11.01 and higher versions, replace `mysqldump` with `mariadb-dump`. For Linux, macOS, or Unix: ``` sudo mysqldump \ --tab=target_directory \ --fields-terminated-by ',' \ --fields-enclosed-by '"' \ --lines-terminated-by 0x0d0a \ database_name \ --master-data=2 \ --single-transaction \ --order-by-primary \ -ppassword ``` For Windows: ``` mysqldump ^ --tab=target_directory ^ --fields-terminated-by "," ^ --fields-enclosed-by """ ^ --lines-terminated-by 0x0d0a ^ database_name ^ --master-data=2 ^ --single-transaction ^ --order-by-primary ^ -ppassword ``` **Note** As a security best practice, specify credentials other than the prompts shown in the example. Make sure to create any stored procedures, triggers, functions, or events manually in your Amazon RDS database. If you have any of these objects in the database that you are copying, then exclude them when you run `mysqldump` or `mariadb-dump`. To do so, include the following arguments with your `mysqldump` or `mariadb-dump` command: `--routines=0` `--triggers=0` `--events=0` When you run `mysqldump` and specify the delimited-text format, a `CHANGE MASTER TO` comment is returned. This comment contains the master log file name and position. If the external instance is a MariaDB 10.0.23 or lower version, note the values for `MASTER_LOG_FILE` and `MASTER_LOG_POS`. You need these values when setting up replication. The following output is returned for MariaDB versions. ``` -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin-changelog.000031', MASTER_LOG_POS=107; ``` 1. If the external instance you are using is MariaDB version 10.0.24 or higher, use GTID-based replication. Run `SHOW MASTER STATUS` on the external MariaDB instance to get the binary log file name and position, and then convert them to a GTID by running `BINLOG_GTID_POS` on the external MariaDB instance. ``` SELECT BINLOG_GTID_POS('binary_log_file_name', binary_log_file_position); ``` Note the GTID returned. You need the GTID to configure replication. 1. Compress the copied data to reduce the amount of network resources needed to copy your data to the Amazon RDS database. Note the size of the backup file. You need this information when determining how large an Amazon EC2 instance to create. When you are done, compress the backup file using GZIP or your preferred compression utility. + To compress SQL output, use the following command: ``` gzip backup.sql ``` + To compress delimited-text output, use the following command: ``` tar -zcvf backup.tar.gz target_directory ``` ## Task 2: Create an Amazon EC2 instance and copy the compressed database Task 2: Create an Amazon EC2 instance Copying your compressed database backup file to an Amazon EC2 instance takes fewer network resources than doing a direct copy of uncompressed data between database instances. After your data is in Amazon EC2, you can copy it from there directly to your MariaDB database. For you to save on the cost of network resources, your Amazon EC2 instance must be in the same AWS Region as your Amazon RDS DB instance. Having the Amazon EC2 instance in the same AWS Region as your Amazon RDS database also reduces network latency during the import. The following diagram shows copying the database backup to an Amazon EC2 instance. ![\[Workflow that shows copying the database backup to an Amazon EC2 instance.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/MigrateMariaDBToRDS_3.png) ### To create an Amazon EC2 instance and copy your data 1. In the AWS Region where you plan to create the Amazon RDS database, create a virtual private cloud (VPC), a VPC security group, and a VPC subnet. Ensure that the inbound rules for your VPC security group allow the IP addresses required for your application to connect to AWS. You can specify a range of IP addresses—for example, `203.0.113.0/24`—or another VPC security group. You can use the [Amazon VPC console](https://console.aws.amazon.com/vpc) to create and manage VPCs, subnets, and security groups. For more information, see [Getting started with Amazon VPC](https://docs.aws.amazon.com/vpc/latest/userguide/what-is-amazon-vpc.html#getting-started) in the *Amazon Virtual Private Cloud User Guide*. 1. Open the [Amazon EC2 console](https://console.aws.amazon.com/ec2) and choose the AWS Region to contain both your Amazon EC2 instance and your Amazon RDS database. Launch an Amazon EC2 instance using the VPC, subnet, and security group that you created in Step 1. Ensure that you select an instance type with enough storage for your database backup file when it is uncompressed. For details on Amazon EC2 instances, see [Getting started with Amazon EC2](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EC2_GetStarted.html) in the *Amazon Elastic Compute Cloud User Guide*. 1. To connect to your Amazon RDS database from your Amazon EC2 instance, edit your VPC security group. Add an inbound rule specifying the private IP address of your EC2 instance. You can find the private IP address on the **Details** tab of the **Instance** pane in the EC2 console window. To edit the VPC security group and add an inbound rule, choose **Security Groups** in the EC2 console navigation pane, choose your security group, and then add an inbound rule for MySQL or Aurora specifying the private IP address of your EC2 instance. To learn how to add an inbound rule to a VPC security group, see [Security group rules](https://docs.aws.amazon.com/vpc/latest/userguide/security-group-rules.html) in the *Amazon Virtual Private Cloud User Guide*. 1. Copy your compressed database backup file from your local system to your Amazon EC2 instance. Use `chmod`, if necessary, to make sure that you have write permission for the target directory of the Amazon EC2 instance. You can use `scp` or a Secure Shell (SSH) client to copy the file. The following command is an example `scp` command: ``` scp -r -i key pair.pem backup.sql.gz ec2-user@EC2 DNS:/target_directory/backup.sql.gz ``` **Important** When copying sensitive data, be sure to use a secure network transfer protocol. 1. Connect to your Amazon EC2 instance and install the latest updates and the MariaDB client tools using the following commands: ``` sudo yum update -y sudo yum install mariadb1011-client-utils -y ``` For more information, see [Connect to your instance](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EC2_GetStarted.html#ec2-connect-to-instance-linux) for Linux instances in the *Amazon Elastic Compute Cloud User Guide* and [MariaDB Connectors](https://mariadb.com/docs/connectors) in the MariaDB documentation. 1. While connected to your Amazon EC2 instance, decompress your database backup file. The following commands are examples. + To decompress SQL output, use the following command: ``` gzip backup.sql.gz -d ``` + To decompress delimited-text output, use the following command: ``` tar xzvf backup.tar.gz ``` ## Task 3: Create a MariaDB database and import data from your Amazon EC2 instance Task 3: Create a MariaDB database By creating an RDS for MariaDB DB instance in the same AWS Region as your Amazon EC2 instance, you can import the database backup file from Amazon EC2 faster than over the internet. The following diagram shows importing the backup from an Amazon EC2 instance into a MariaDB database. ![\[Workflow that shows importing the backup from the EC2 instance into the MariaDB database.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/MigrateMariaDBToRDS_4.png) ### To create a MariaDB database and import your data 1. Determine which DB instance class and what amount of storage space is required to support the expected workload for this Amazon RDS database. As part of this process, decide what is sufficient space and processing capacity for your data load procedures. Also, decide what is required to handle the production workload. You can estimate this based on the size and resources of the source MariaDB database. For more information, see [DB instance classes](Concepts.DBInstanceClass.md). 1. Create a DB instance in the AWS Region that contains your Amazon EC2 instance. Follow the instructions in [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md) and use the following guidelines: + Specify a DB engine version that is compatible with your source DB instance. + Specify the same virtual private cloud (VPC) and VPC security group as for your Amazon EC2 instance. This approach ensures that your Amazon EC2 instance and your Amazon RDS instance are visible to each other over the network. Make sure your DB instance is publicly accessible. To set up replication with your source database as described in a following section, your DB instance must be publicly accessible. + Don't configure multiple Availability Zones, backup retention, or read replicas until after you have imported the database backup. When that import is completed, you can configure Multi-AZ and backup retention for the production instance. 1. Review the default configuration options for the Amazon RDS database. If the default parameter group for the database doesn't have the configuration options that you want, find a different one that does or create a new parameter group. For more information about creating a parameter group, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md). 1. Connect to the new Amazon RDS database as the master user. Create the users required to support the administrators, applications, and services that need to access the DB instance. The hostname for the Amazon RDS database is the **Endpoint** value for this DB instance without the port number, for example, `mysampledb.123456789012.us-west-2.rds.amazonaws.com`. You can find the endpoint value in the database details in the Amazon RDS console. 1. Connect to your Amazon EC2 instance. For more information, see [Connect to your instance](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EC2_GetStarted.html#ec2-connect-to-instance-linux) for Linux instances in the *Amazon Elastic Compute Cloud User Guide*. 1. Connect to your Amazon RDS database as a remote host from your Amazon EC2 instance using the `mysql` command. The following command is an example: ``` mysql -h host_name -P 3306 -u db_master_user -p ``` The *host\$1name* is the Amazon RDS database endpoint. 1. At the `mysql` prompt, run the `source` command and pass it the name of your database dump file. This command loads the data into the Amazon RDS DB instance. + For SQL format, use the following command: ``` MariaDB [(none)]> source backup.sql; ``` + For delimited-text format, first create the database, if it isn't the default database that you created when setting up the Amazon RDS database. ``` MariaDB [(none)]> create database database_name; MariaDB [(none)]> use database_name; ``` Then create the tables. ``` MariaDB [(none)]> source table1.sql MariaDB [(none)]> source table2.sql etc... ``` Then import the data. ``` MariaDB [(none)]> LOAD DATA LOCAL INFILE 'table1.txt' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '0x0d0a'; MariaDB [(none)]> LOAD DATA LOCAL INFILE 'table2.txt' INTO TABLE table2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '0x0d0a'; etc... ``` To improve performance, you can perform these operations in parallel from multiple connections so that all of your tables are created and then loaded at the same time. **Note** If you used any data-formatting options with `mysqldump` or `mariadb-dump` when you initially dumped the table, make sure to use the same options with `LOAD DATA LOCAL INFILE` to ensure proper interpretation of the data file contents. 1. Run a simple `SELECT` query against one or two of the tables in the imported database to verify that the import was successful. If you no longer need the Amazon EC2 instance used in this procedure, terminate the EC2 instance to reduce your AWS resource usage. To terminate an EC2 instance, see [Terminate an instance](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/terminating-instances.html#terminating-instances-console) in the *Amazon Elastic Compute Cloud User Guide*. ## Task 4: Replicate data from your external database to your new Amazon RDS database Task 4: Replicate data from your external database Your source database was likely updated during the time that it took to copy and transfer the data to the MariaDB database. You can use replication to bring the copied database up-to-date with the source database. ![\[Workflow that shows replicating data from the external MariaDB database to the database on Amazon RDS.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/MigrateMariaDBToRDS_5.png) The permissions required to start replication on an Amazon RDS database are restricted and aren't available to your Amazon RDS master user. Because of this, use the appropriate Amazon RDS stored procedure: + [mysql.rds\$1set\$1external\$1master](mysql-stored-proc-replicating.md#mysql_rds_set_external_master) + [mysql.rds\$1set\$1external\$1master\$1gtid](mysql_rds_set_external_master_gtid.md) to configure replication and [mysql.rds\$1start\$1replication](mysql-stored-proc-replicating.md#mysql_rds_start_replication) to start replication ### To start replication In Task 1, [when you set replication options](#mariadb-importing-data-reduced-downtime-set-replication-options), you turned on binary logging and set a unique server ID for your source database. Now, you can set up your Amazon RDS database as a replica with your live database as the source replication instance. 1. In the Amazon RDS console, add the IP address of the server that hosts the source database to the VPC security group for the Amazon RDS database. For more information on configuring a VPC security group, see [Configure security group rules ](https://docs.aws.amazon.com/vpc/latest/userguide/working-with-security-group-rules.html) in the *Amazon Virtual Private Cloud User Guide*. You might also need to configure your local network to permit connections from the IP address of your Amazon RDS database so that it can communicate with your source instance. To find the IP address of the Amazon RDS database, use the `host` command: ``` host host_name ``` The *host\$1name* is the DNS name from the Amazon RDS database endpoint, for example `myinstance.123456789012.us-east-1.rds.amazonaws.com`. You can find the endpoint value in the DB instance details in the Amazon RDS console. 1. Using the client of your choice, connect to the source instance and create a user to be used for replication. This account is used solely for replication and must be restricted to your domain to improve security. The following command is an example: ``` CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED BY 'password'; ``` **Note** Specify credentials other than the prompts shown here as a security best practice. 1. For the source instance, grant `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges to your replication user. For example, to grant the `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges on all databases for the '`repl_user`' user for your domain, issue the following command: ``` GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com'; ``` 1. If you used SQL format to create your backup file and the external instance isn't MariaDB 10.0.24 or higher, look at the contents of that file by running the following command: ``` cat backup.sql ``` The file includes a `CHANGE MASTER TO` comment that contains the master log file name and position. This comment is included in the backup file when you use the `--master-data` option with `mysqldump`. Note the values for `MASTER_LOG_FILE` and `MASTER_LOG_POS`. ``` -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin-changelog.000031', MASTER_LOG_POS=107; ``` If you used delimited text format to create your backup file and the external instance isn't MariaDB 10.0.24 or higher, you should already have binary log coordinates from Step 1 in Task 1 [when you created a backup copy of your existing database](#mariadb-importing-data-reduced-downtime-create-backup). If the external instance is MariaDB 10.0.24 or higher, you should already have the GTID from which to start replication from Step 2 in Task 1 [when you created a backup copy of your existing database](#mariadb-importing-data-reduced-downtime-create-backup). 1. Make the Amazon RDS database the replica. If the external instance isn't MariaDB 10.0.24 or higher, connect to the Amazon RDS database as the master user and identify the source database as the source replication instance by using the [mysql.rds\$1set\$1external\$1master](mysql-stored-proc-replicating.md#mysql_rds_set_external_master) stored procedure. If you have a SQL format backup file, use the master log file name and master log position that you determined in Step 4. If you used delimited-text format, use the name and position that you determined when creating the backup files. The following command is an example: ``` CALL mysql.rds_set_external_master ('myserver.mydomain.com', 3306, 'repl_user', 'password', 'mysql-bin-changelog.000031', 107, 1); ``` **Note** Specify credentials other than the prompts shown here as a security best practice. If the external instance is MariaDB 10.0.24 or higher, connect to the Amazon RDS database as the master user and identify the source database as the source replication instance by using the [mysql.rds\$1set\$1external\$1master\$1gtid](mysql_rds_set_external_master_gtid.md) stored procedure. Use the GTID that you determined in Step 2 in Task 1 [when you created a backup copy of your existing database](#mariadb-importing-data-reduced-downtime-create-backup). The following command is an example: ``` CALL mysql.rds_set_external_master_gtid ('source_server_ip_address', 3306, 'ReplicationUser', 'password', 'GTID', 1); ``` The `source_server_ip_address` is the IP address of source replication instance. An EC2 private DNS address isn't currently supported. **Note** Specify credentials other than the prompts shown here as a security best practice. 1. On the Amazon RDS database, to start replication, run the following command that uses the [mysql.rds\$1start\$1replication](mysql-stored-proc-replicating.md#mysql_rds_start_replication) stored procedure: ``` CALL mysql.rds_start_replication; ``` 1. On the Amazon RDS database, to determine when the replica is up-to-date with the source replication instance, run the [SHOW REPLICA STATUS](https://dev.mysql.com/doc/refman/8.0/en/show-replica-status.html) command. The results of the `SHOW REPLICA STATUS` command include the `Seconds_Behind_Master` field. When the `Seconds_Behind_Master` field returns 0, then the replica is up-to-date with the source replication instance. For a MariaDB 10.5, 10.6, 10.11, 11.4, or 11.8 DB instance, use the [mysql.rds\$1replica\$1status](mysql_rds_replica_status.md) stored procedure instead of running the MySQL command. 1. After the Amazon RDS database is up-to-date, turn on automated backups so you can restore that database if needed. You can turn on or modify automated backups for your Amazon RDS database by using the [Amazon RDS console](https://console.aws.amazon.com/rds/). For more information, see [Introduction to backups](USER_WorkingWithAutomatedBackups.md). ## Task 5: Redirect your live application to your Amazon RDS instance Task 5: Redirect your live application After the MariaDB database is up-to-date with the source replication instance, you can now update your live application to use the Amazon RDS instance. ![\[Workflow that shows stopping replication and directing the live application to the database on Amazon RDS.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/MigrateMariaDBToRDS_6.png) ### To redirect your live application to your MariaDB database and stop replication 1. To add the VPC security group for the Amazon RDS database, add the IP address of the server that hosts the application. For more information on modifying a VPC security group, see [Configure security group rules](https://docs.aws.amazon.com/vpc/latest/userguide/working-with-security-group-rules.html) in the *Amazon Virtual Private Cloud User Guide*. 1. Verify that the `Seconds_Behind_Master` field in the [SHOW REPLICA STATUS](https://dev.mysql.com/doc/refman/8.0/en/show-replica-status.html) command results is 0, which indicates that the replica is up-to-date with the source replication instance. ``` SHOW REPLICA STATUS; ``` For a MariaDB 10.5, 10.6, 10.11, 11.4, or 11.8 DB instance, use the [mysql.rds\$1replica\$1status](mysql_rds_replica_status.md) procedure instead of running the MySQL command. 1. Close all connections to the source when their transactions complete. 1. Update your application to use the Amazon RDS database. This update typically involves changing the connection settings to identify the hostname and port of the Amazon RDS database, the user account and password to connect with, and the database to use. 1. Connect to the DB instance. 1. Stop replication for the Amazon RDS instance by running the following command that uses the [mysql.rds\$1stop\$1replication](mysql-stored-proc-replicating.md#mysql_rds_stop_replication) stored procedure: ``` CALL mysql.rds_stop_replication; ``` 1. Reset the replication configuration so this instance is no longer identified as a replica by running the following command that uses the [mysql.rds\$1reset\$1external\$1master](mysql-stored-proc-replicating.md#mysql_rds_reset_external_master) stored procedure on your Amazon RDS database: ``` CALL mysql.rds_reset_external_master; ``` 1. Turn on additional Amazon RDS features such as Multi-AZ support and read replicas. For more information, see [Configuring and managing a Multi-AZ deployment for Amazon RDS](Concepts.MultiAZ.md) and [Working with DB instance read replicas](USER_ReadRepl.md). # Importing data from any source to an Amazon RDS for MariaDB DB instance Importing data from any source With Amazon RDS, you can migrate existing MariaDB data from any source to an RDS for MariaDB DB instance. You can transfer data from on-premises databases, other cloud providers, or existing RDS for MariaDB DB instances to your target RDS for MariaDB DB instance. With this functionality, you can consolidate databases, implement disaster recovery solutions, or transition from self-managed databases. Common scenarios include moving from self-hosted MariaDB servers to fully managed Amazon RDS DB instances, consolidating multiple MariaDB databases into a single DB instance, or creating test environments with production data. The following sections provide step-by-step instructions for importing your MariaDB data using methods such as `mariadb-dump`, backup files, or replication. ## Step 1: Create flat files containing the data to be loaded Step 1: Create flat files Use a common format, such as comma-separated values (CSV), to store the data to be loaded. Each table must have its own file—you can't combine data for multiple tables in the same file. Give each file the same name as the table it corresponds to. The file extension can be anything you like. For example, if the table name is `sales`, the file name could be `sales.csv` or `sales.txt`. If possible, order the data by the primary key of the table being loaded. Doing this drastically improves load times and minimizes disk storage requirements. The speed and efficiency of this procedure depends on keeping the size of the files small. If the uncompressed size of any individual file is larger than 1 GiB, split it into multiple files and load each one separately. On Unix-like systems (including Linux), use the `split` command. For example, the following command splits the `sales.csv` file into multiple files of less than 1 GiB, splitting only at line breaks (-C 1024m). The names of the new files include ascending numerical suffixes. The following command produces files with names such as `sales.part_00` and `sales.part_01`. ``` split -C 1024m -d sales.csv sales.part_ ``` Similar utilities are available for other operating systems. You can store the flat files anywhere. However, when you load the data in [Step 5](#mariadb-importing-data-any-source-load-data), you must invoke the `mysql` shell from the same location where the files exist, or use the absolute path for the files when you run `LOAD DATA LOCAL INFILE`. ## Step 2: Stop any applications from accessing the target DB instance Step 2: Stop any applications Before starting a large load, stop all application activity from accessing the target DB instance that you plan to load to. We recommend this particularly if other sessions will be modifying the tables being loaded or tables that they reference. Doing this reduces the risk of constraint violations occurring during the load and improves load performance. It also makes it possible to restore the DB instance to the point just before the load without losing changes made by processes not involved in the load. Of course, this might not be possible or practical. If you can't stop applications from accessing the DB instance before the load, take steps to ensure the availability and integrity of your data. The specific steps required vary greatly depending upon specific use cases and site requirements. ## Step 3: Create a DB snapshot If you plan to load data into a new DB instance that contains no data, you can skip this step. Otherwise, we recommend that you create DB snapshots of the target Amazon RDS DB instance both before and after the data load. Amazon RDS DB snapshots are complete backups of your DB instance that you can use to restore your DB instance to a known state. When you initiate a DB snapshot, I/O operations to your DB instance are momentarily suspended while your database is backed up. Creating a DB snapshot immediately before the load makes it possible for you to restore the database to its state before the load, if you need to. A DB snapshot taken immediately after the load protects you from having to load the data again in case of a mishap. You can also use DB snapshots after the load to import data into new database instances. The following example runs the AWS CLI [create-db-snapshot](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-snapshot.html) command to create a DB snapshot of the `AcmeRDS` instance and give the DB snapshot the identifier `"preload"`. For Linux, macOS, or Unix: ``` aws rds create-db-snapshot \ --db-instance-identifier AcmeRDS \ --db-snapshot-identifier preload ``` For Windows: ``` aws rds create-db-snapshot ^ --db-instance-identifier AcmeRDS ^ --db-snapshot-identifier preload ``` You can also use the restore from DB snapshot functionality to create test DB instances for dry runs or to undo changes made during the load. Keep in mind that restoring a database from a DB snapshot creates a new DB instance that, like all DB instances, has a unique identifier and endpoint. To restore the DB instance without changing the endpoint, first delete the DB instance so that you can reuse the endpoint. For example, to create a DB instance for dry runs or other testing, you give the DB instance its own identifier. In the example, `AcmeRDS-2`" is the identifier. The example connects to the DB instance using the endpoint associated with `AcmeRDS-2`. For more information, see [restore-db-instance-from-db-snapshot](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html). For Linux, macOS, or Unix: ``` aws rds restore-db-instance-from-db-snapshot \ --db-instance-identifier AcmeRDS-2 \ --db-snapshot-identifier preload ``` For Windows: ``` aws rds restore-db-instance-from-db-snapshot ^ --db-instance-identifier AcmeRDS-2 ^ --db-snapshot-identifier preload ``` To reuse the existing endpoint, first delete the DB instance and then give the restored database the same identifier. For more information, see [delete-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/delete-db-instance.html). The following example also takes a final DB snapshot of the DB instance before deleting it. This is optional but recommended. For Linux, macOS, or Unix: ``` aws rds delete-db-instance \ --db-instance-identifier AcmeRDS \ --final-db-snapshot-identifier AcmeRDS-Final aws rds restore-db-instance-from-db-snapshot \ --db-instance-identifier AcmeRDS \ --db-snapshot-identifier preload ``` For Windows: ``` aws rds delete-db-instance ^ --db-instance-identifier AcmeRDS ^ --final-db-snapshot-identifier AcmeRDS-Final aws rds restore-db-instance-from-db-snapshot ^ --db-instance-identifier AcmeRDS ^ --db-snapshot-identifier preload ``` ## Step 4 (Optional): Turn off Amazon RDS automated backups Step 4 (Optional): Turn off automated backups **Warning** Don't turn off automated backups if you need to perform point-in-time recovery. Turning off automated backups is a performance optimization and isn't required for data loads. Turning off automated backups erases all existing backups. As a result, after you turn off automated backups, point-in-time recovery isn't possible. Manual DB snapshots aren't affected by turning off automated backups. All existing manual DB snapshots are still available for restore. Turning off automated backups reduces load time by about 25 percent and reduces the amount of storage space required during the load. If you plan to load data into a new DB instance that contains no data, turning off backups is an easy way to speed up the load and avoid using the additional storage needed for backups. However, in some cases you might plan to load into a DB instance that already contains data. If so, weigh the benefits of turning off backups against the impact of losing the ability to perform point-in-time-recovery. DB instances have automated backups turned on by default (with a one day retention period). To turn off automated backups, set the backup retention period to zero. After the load, you can turn backups back on by setting the backup retention period to a nonzero value. To turn on or turn off backups, Amazon RDS shuts the DB instance down and then restarts it to turn MariaDB logging on or off. Run the AWS CLI `modify-db-instance` command to set the backup retention to zero and apply the change immediately. Setting the retention period to zero requires a DB instance restart, so wait until the restart has completed before proceeding. For more information, see [modify-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html). For Linux, macOS, or Unix: ``` aws rds modify-db-instance \ --db-instance-identifier AcmeRDS \ --apply-immediately \ --backup-retention-period 0 ``` For Windows: ``` aws rds modify-db-instance ^ --db-instance-identifier AcmeRDS ^ --apply-immediately ^ --backup-retention-period 0 ``` You can check the status of your DB instance with the AWS CLI [describe-db-instances](https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-instances.html) command. The following example displays the DB instance status of the `AcmeRDS` DB instance: ``` aws rds describe-db-instances --db-instance-identifier AcmeRDS --query "*[].{DBInstanceStatus:DBInstanceStatus}" ``` When the DB instance status is `available`, you're ready to proceed to the next step. ## Step 5: Load the data To read rows from your flat files into the database tables, use the MariaDB `LOAD DATA LOCAL INFILE` statement. **Note** You must invoke the `mariadb` shell from the same location where your flat files exist, or use the absolute path for the files when you run `LOAD DATA LOCAL INFILE`. The following example shows how to load data from a file named `sales.txt` into a table named `Sales` in the database: ``` MariaDB [(none)]> LOAD DATA LOCAL INFILE 'sales.txt' INTO TABLE Sales FIELDS TERMINATED BY ' ' ENCLOSED BY '' ESCAPED BY '\\'; Query OK, 1 row affected (0.01 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 ``` For more information about the `LOAD DATA` statement, see [LOAD DATA INFILE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/load-data-into-tables-or-index/load-data-infile) in the MariaDB documentation. ## Step 6: Turn back on Amazon RDS automated backups Step 6: Turn back on automated backups If you turned off Amazon RDS automated backups in [Step 4](#mariadb-importing-data-any-source-turn-off-automated-backups), after the load is finished, turn automated backups on by setting the backup retention period back to its preload value. As noted in Step 4, Amazon RDS restarts the DB instance, so be prepared for a brief outage. The following example runs the AWS CLI [modify-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html) command to turn on automated backups for the `AcmeRDS` DB instance and set the retention period to one day: For Linux, macOS, or Unix: ``` aws rds modify-db-instance \ --db-instance-identifier AcmeRDS \ --backup-retention-period 1 \ --apply-immediately ``` For Windows: ``` aws rds modify-db-instance ^ --db-instance-identifier AcmeRDS ^ --backup-retention-period 1 ^ --apply-immediately ```