Amazonrds 简明教程

Amazon RDS - MariaDB Data Import

Amazon RDS MariaDB 提供了将数据导入到数据库和从数据库导出数据的简便方法。在我们成功连接到 MariaDB 数据库后，我们可以使用 CLI 工具运行导入和导出命令，以将数据从其他源导入到 RDS 数据库或从 RDS 数据库导出数据。

Amazon RDS MariaDB provides easy ways of importing data into the DB and exporting data from the DB. After we are able to successfully connect to the MariaDB database we can use CLI tools to run the import and export commands to get the data from other sources in and out of the RDS database.

以下是决定将数据导入到 Amazon RDS-MariaDB 数据库的方法时要考虑的场景。

Below are the scenarios to consider when deciding on the approach to the import the data into the Amazon RDS- MariaDB database.

From an Existing MariaDB database

现有的 MariaDB 可以出现在本地或另一个 EC2 实例中。我们所做的内容可以用下图进行表示。

An existing MariaDB can be present on premise or in another EC2 instance. Diagrammatically what we do is shown below.

Creating a backup from On-Premise DB

第一步，我们使用以下命令创建本地数据库的备份。MariaDB 是 MySQL 的克隆，因此它可以使用几乎与 MySQL 相同的所有命令。

As a first step we create a backup of the on-premise database using the below command. MariaDB being a clone of MySQL, can use nearly all the same commands as MySQL.

# mysqldump -u user -p[user_password] [database_name] > backupfile.sql

会创建一个名为 backupfile.sql 的文件，其中包含表结构以及要使用的数据。

A file with name backupfile.sql is cerated which contains the table structure along with the data to be used.

Storing the backup file in S3.

将上述创建的备份文件上传到目标 RDS MariaDB 数据库所在区域中预先确定的 Amazon S3 存储桶中。您可以按照 this link 中的链接了解如何上传。

Upload the backup file created above to a pre-decided Amazon S3 bucket in the same region where the target RDS MariaDB database is present. You can follow link: this link to learn about how to upload.

Import data from Amazon S3 to RDS- MariaDB database

您可以使用以下 Amazon CLI 命令将数据从 S3 导入到 MariaDB 数据库。

You can use the following Amazon CLI command to import the data from S3 to MariaDB DB.

aws rds restore-db-instance-from-s3 \
--allocated-storage 125 \
--db-instance-identifier tddbidentifier \
--db-instance-class db.m4.small \
--engine mysql \
--master-user-name masterawsuser \
--master-user-password masteruserpassword \
--s3-bucket-name tpbucket \
--s3-ingestion-role-arn arn:aws:iam::account-number:role/rolename \
--s3-prefix bucketprefix \
--source-engine mysql \
--source-engine-version 5.6.27

From Another RDS- MariaDB Instance

在某些情况下，您可能希望将现有 RDS MariaDB 数据库中的数据导入到另一个 RDS MariaDB 中。例如，创建灾难恢复数据库或者仅为业务报告创建数据库。在这种情况下，我们会创建是从属副本（它们是源数据库的副本），然后将该从属副本升级为新的数据库实例。当我们要复制数据时，使用它们可以防止从原始源数据库直接进行繁重的读取操作。

There may be scenarios when you want data from an existing RDS MariaDB DB to be taken into another RDS MariaDB. For example, to cerate a Disaster recovery DB or create a DB only for business reporting etc. In such scenario, we create read replicas which are a copy of their source DB and then promote that read replica to a new DB instance. They are used to prevent direct heavy read from the original source DB when we want to copy the data.

create a read-replica

aws rds create-db-instance-read-replica \
    --db-instance-identifier myreadreplica \
    --source-db-instance-identifier mydbinstance

Promote a Read replica to DB Instance

现在我们有了副本，我们可以将其升级为独立的数据库实例。这将满足我们从一个 RDS – MariaDB 数据库导入数据到另一个数据库的需求。以下命令用于完成将从属副本升级为数据库实例。

Now as we have the replica, we can promote it to a standalone DB instance. This will serve our end need of importing data from o RDS – MariaDB DB to a new one. The following command is used to complete the promotion of a read replica to a db instance.

aws rds create-db-instance-read-replica \
    --db-instance-identifier readreplica_name \
    --region target_region_name
    --db-subnet-group-name subnet_name
    --source-db-instance-identifier arn:aws:rds:region_name:11323467889012:db:mysql_instance1

From Any Database

要将数据从任何其他数据库导入到 Amazon RDS – MariaDB，我们必须使用名为 Amazon DMS（又称 Amazon 数据迁移服务）的服务。它使用模式转换工具将现有数据库转换为 MYSQL 平台。下图说明了整个过程。此外，它还可以基于前一节中所述的类似复制原理运行。

In order to import data from any other database to Amazon RDS – MariaDB, we have to use the amazon Data Migration Service also called Amazon DMS. It uses Schema conversion tool to translate the existing data base to a the MYSQL platform. The below diagram explains the overall process. Also it works on the similar principle of replication as described in the previous section.

Exporting Data from MariaDB

从 Amazon RDS Mysql DB 导出数据是一个直接的过程，它基于我们上面看到的相同的复制原则。以下是执行导出过程的步骤。

Exporting of data from Amazon RDS Mysql DB is a straight forwards process where it works on the same replication principle we have seen above. Below are the steps to carry out the export process.

Start the instance of MariaDB running external to Amazon RDS.
Designate the MariaDB DB instance to be the replication source.
Use mysqldump to transfer the database from the Amazon RDS instance to the instance external to Amazon RDS.

以下是用于传输数据的 mysqldump 命令的代码。

Below is the code for mysqldump command to transfer the data

mysqldump -h RDS instance endpoint \
    -u user \
    -p password \
    --port=3306 \
    --single-transaction \
    --routines \
    --triggers \
    --databases  database database2 \
    --compress  \
    --compact | mysql \
        -h MariaDB host \
        -u master user \
        -p password \
        --port 3306