Amazonrds 简明教程

Amazon RDS - MySQL DB Export Import

Amazon RDS MySQL 提供了将数据导入到数据库和从数据库导出数据的简便方法。在成功连接到 MySQL 数据库后，我们可以使用 CLI 工具运行导入和导出命令，以获取来自其他来源的数据，并将其放置在 RDS 数据库中或导出。以下是决定将数据导入 Amazon RDS - MySQL 数据库时要考虑的一些场景。

Amazon RDS MySQL provides easy ways of importing data into the DB and exporting data from the DB. After we are able to successfully connect to the MySQL 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. Below are the scenarios to consider when deciding on the approach to the import the data into the Amazon RDS - MySQL database.

From an Existing MySQL database

现有的 MySQL DB 可能会出现在本地或另一个 EC2 实例中。我们所做操作的示意图如下所示。

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

on premise

Creating a backup from On-Premise DB

作为第一步，我们使用以下命令创建本地数据库的备份。

As a first step we create a backup of the on-premise database using the below command.

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

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

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

Storing the backup file in S3.

将上面创建的备份文件上传到与目标 RDS MySQL DB 数据库所在同一地区的预定 Amazon S3 存储桶。你可以按照以下链接：@ {s0}了解如何上传。

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

Import data from Amazon S3 to RDS- MySQL database

你可以使用以下 Amazon CLI 命令将数据从 S3 导入到 MySQL DB 中。

You can use the following Amazon CLI command to import the data from S3 to MySQL 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-MySQL Instance

在某些情况下，你可能希望将现有 RDS MYSQL DB 中的数据导入到另一个 RDS MYSQL DB 中。例如，创建灾难恢复数据库或仅为业务报告创建数据库等。在这种情况下，我们创建与其源数据库副本一致的只读副本，然后将该只读副本升级为新的数据库实例。当我们想要复制数据时，可以使用它们防止直接从原始源数据库中进行大读取操作。

There may be scenarios when you want data from an existing RDS MYSQL DB to be taken into another RDS MYSQL DB. 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 - Mysql DB 导入数据到一个新 RDS - Mysql DB 的最终需求。我们使用以下命令完成将只读副本升级到一个数据库实例的过程。

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 – Mysql 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 - MySQL，我们必须使用称为 Amazon DMS 的 Amazon 数据迁移服务。它使用模式转换工具将现有数据库转换成 MYSQL 平台。下图说明了整个过程。它还基于上一节中描述的类似于复制的原则。

In order to import data from any other database to Amazon RDS – MySQL, 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.

amazon dms

Exporting Data from MySQL

从 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.

  1. Start the instance of MySQL running external to Amazon RDS.

  2. Designate the MySQL DB instance to be the replication source.

  3. Use mysqldump to transfer the database from the Amazon RDS instance to the instance external to Amazon RDS.

以下是 mysqldum 命令的代码

Below is the code for the mysqldum command

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