如何将本地MySQL db导入到RDS db实例?

I've created a RDS instance called realcardiodb (the engine is mysql) and I've exported my database from my localhost. File is saved locally called localhostrealcardio.sql

我创建了一个名为realcardiodb的RDS实例(引擎是mysql)，并从本地主机导出数据库。文件被本地保存为localhostrealcardio.sql

Most research says to use mysqldump to import data from a local system to a web server, but my system doesn't even recognize mysqldump.

大多数研究都说使用mysqldump将数据从本地系统导入到web服务器，但我的系统甚至不认识mysqldump。

C:\xampp\mysql>mysqldump
'mysqldump' is not recognized as an internal or external command, operable program or batch   file.

How do I resolve this error should I use mysqldump? (I definitely have mysql install on my system)

应该如何使用mysqldump解决这个错误?(我的系统肯定安装了mysql)

Is there a better utility I should use?

我应该使用更好的工具吗?

Any help is appreciated, especially if you have experience importing mysql to aws rds.

如果您有将mysql导入到aws rds的经验，我们将非常感激您的帮助。

Thanks! DK

谢谢!DK

Update 7/31/2012 So I got the error resolved. mysqldump is in the bin directory C:\xampp\mysql\bin>mysqldump AWS provides the folloinwg instructions for uploading a local database to RDS:

更新7/31/2012，所以我解决了错误。mysqldump位于bin目录C:\xampp\mysql\bin>mysqldump AWS为将本地数据库上传到RDS提供了folloinwg指令:

mysqldump acme | mysql --host=hostname --user=username --password acme

Can someone break this down for me?

有人能帮我打破这个吗?

1) Is the first 'acme' (after mysqldump command) the name of my local database or the exported sql file I saved locally?

1)第一个“acme”(在mysqldump命令之后)是本地数据库的名称，还是本地保存的导出sql文件?

2)Is the hostname the IP address, Public DNS, RDS Endpoint or neither?

2)主机名是IP地址、公共DNS、RDS端点还是两者都没有?

3)The username and password I assume is the RDS credentials and the second acme is the name of the database I created in RDS.

3)我假定的用户名和密码是RDS凭据，第二个acme是我在RDS中创建的数据库的名称。

Thanks!

谢谢!

4 个解决方案

#1

This is how I did it for a couple instances that had data in the MySQl tables.

这是我对MySQl表中有数据的几个实例所做的。

The steps to creating an RDS database instance: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.MySQL.html

创建RDS数据库实例的步骤:http://docs.aws.amazon.com/amazonrds/latest/userguide/chap_gettingstar.creatingconnecting.mysql.html

Note: Make sure the RDS instance has a security group configured that relates to the EC2 security group. http://docs.amazonwebservices.com/AmazonRDS/latest/UserGuide/USER_Workin...

注意:确保RDS实例配置了与EC2安全组相关的安全组。http://docs.amazonwebservices.com/AmazonRDS/latest/UserGuide/USER_Workin..。

Before we go forward, let me provide a list of what some of the following placeholders are:

在我们继续之前，让我提供一个以下占位符的列表:

host.address.for.rds.server = this will be what is referred to as the "end point" in your RDS description/settings page.
host.address.for.rds。服务器=这将被称为您的RDS描述/设置页面中的“结束点”。
rdsusername = the master user account which you created during RDS setup.
rdsusername =在RDS设置期间创建的主用户帐户。
rdsdatabase = a blank database which you created inside the server on your RDS instance.
rdsdatabase =在RDS实例的服务器中创建的空白数据库。
backupfile.sql = the sql dump file your made of your pre-existing installation's database.
backupfile。sql = sql转储文件，这是您的已存在安装的数据库。

Once you've created a fresh RDS database instance, and have configured its security settings, log into this server (from within an ssh session to your EC2 server) and then create an empty database inside the instance using basic SQL commands.

一旦您创建了一个新的RDS数据库实例，并配置了它的安全设置，登录到这个服务器(从ssh会话中登录到EC2服务器)，然后使用基本的SQL命令在实例中创建一个空数据库。

mysql -h host.address.for.rds.server -P 3306 -u rdsusername -p
(enter your password)
create database rdsdatabase;

Then quit out of the MySQL environment inside your RDS server.

然后退出RDS服务器中的MySQL环境。

\q

This tutorial assumes you already have a backup from your old database. If you don't, go create one now. After that, you’re ready to import that sql dump file into the empty database waiting on your RDS server.

本教程假设您已经有来自旧数据库的备份。如果没有，现在就创建一个。之后，您就可以将该sql转储文件导入等待RDS服务器的空数据库。

mysql -h host.address.for.rds.server -u rdsusername -p  rdsdatabase < backupfile.sql

It might take a few seconds to complete, depending on the size of the sql dump file. Your indication that it is finished is that the bash command prompt reappears.

根据sql转储文件的大小，可能需要几秒钟才能完成。您的指示已经完成，即bash命令提示符再次出现。

Note: the command “mysqlimport” is used when imported data directly into an existing table inside a database. It might seem like we’re “importing” data, but this is not what we’re actually doing in this situation. The database we are migrating to has no tables yet, and the sql dump file we’re using contains the sql commands to generate the tables it needs.

注意:当将数据直接导入数据库中的现有表时，将使用“mysqlimport”命令。看起来我们似乎是在“导入”数据，但在这种情况下，我们实际上并没有这么做。我们要迁移到的数据库还没有表，我们使用的sql转储文件包含sql命令来生成它需要的表。

Confirm the Transfer

确认转让

Now, if you didn't get any error messages, then your sql transfer probably worked. If you want, you can double check to see if it did by connecting to your RDS database server, looking up the database you created, and check to see if the tables are now present.

现在，如果您没有收到任何错误消息，那么您的sql传输可能会成功。如果需要，您可以通过连接到RDS数据库服务器、查找您创建的数据库并检查表是否存在来检查它是否存在。

mysql -h host.address.for.rds.server -P 3306 -u rdsusername -p
(enter your password)
use rdsdatabase;
show tables;

#2

I prefer using MySQL workbench. It's much more easier & user friendly than the command line way.

我更喜欢使用MySQL workbench。它比命令行更容易和用户友好。

It provides a simple GUI.

它提供了一个简单的GUI。

MySQL workbench or SQL Yog.

MySQL workbench或SQL Yog。

These are the steps that I did.

这是我所做的步骤。

1) Install MySQL Workbench.

1)安装MySQL工作台。

2) In AWS console, there must be a security group for your RDS instance. Add an inbound rule to that group for allowing connections from your machine. It's simple. Add your IP-address.

2)在AWS控制台，您的RDS实例必须有一个安全组。向该组添加入站规则，以允许从计算机进行连接。这很简单。添加你的ip地址。

3) Open MySQL workbench, Add a new connection.

3)打开MySQL工作台，添加一个新的连接。

4) Give the connection a name you prefer.

给这个连接一个你喜欢的名字。

5) Choose connection method- Standard TCP/IP

5)选择连接方式——标准的TCP/IP

6) Enter your RDS endpoint in the field of Hostname.

6)在主机名字段中输入RDS端点。

7) Port:3306

7)端口:3306

8) Username: master username (the one which which you created during the creation of your RDS instance)

8)用户名:主用户名(在创建RDS实例时创建的用户名)

9)Password: master password

9)密码:主密码

10) Click Test Connection to check your connection.

10)单击Test Connection检查您的连接。

11) If connection is successful, click OK.

11)如果连接成功，点击OK。

12) Open the connection.

12)打开连接。

13) you will see your database 'realcardiodb' there.

你会在那里看到你的数据库“realcardiodb”。

14) Now you can export your mysqldump file to this database. Go to-> Server. Click Data Import.

现在可以将mysqldump文件导出到此数据库。去- >服务器。单击数据导入。

15) You can check whether the data has been migrated by simply opening a blank SQL file & typing in basic SQL commands like use database, select * from table;

15)只需打开一个空白的SQL文件，输入基本的SQL命令，如use database, select * from table，就可以检查数据是否已迁移;

That's it. Viola.

就是这样。中提琴。

#3

If you have a backup.sql in your PC, No need to transfer to EC2. Just give below line on your terminal in your PC.

如果你有备份的话。PC中的sql，不需要传输到EC2。只要在你的电脑上输入以下行。

$ mysql -h rdsinstance-hostaddress-ending.rds.amazonaws.com -u rds_username -p  rds_database < /path/to/your/backup.sql

Enter password: paswd_mysql_user

输入密码:paswd_mysql_user

That's all.

这是所有。

Import backup directly from existing remote server

直接从现有远程服务器导入备份。

SSH connect to your remote server

SSH连接到远程服务器

Get the remote server mysql backup (backup/path/backupfile.sql)

获取远程服务器mysql备份(backup/path/backupfile.sql)

Import backup file to RDS mysql while you in remote server shell

在远程服务器shell中，将备份文件导入RDS mysql

mysql -h your-mysql-instance.region.rds.amazonaws.com -u db_username -p db_name < backup/path/backupfile.sql

Note:

注意:

I have tried all the above criteria to import my existing backup to new RDS database, including through EC2 as in AWS documentation. It was a 10GB backup. So I have tried tables by tables as well. It shows process completed but some data were missing for large tables. So I had to write a DB to DB data migration script.

我尝试了以上所有标准，将现有的备份导入到新的RDS数据库，包括通过AWS文档中的EC2。这是一个10GB的备份。所以我也按表格试过。它显示了已完成的过程，但是对于大型表，有些数据丢失了。所以我必须写一个DB到DB的数据迁移脚本。

#4

Here is the AWS RDS Mysql document to import customer data into RDS

这是AWS RDS Mysql文档，用于将客户数据导入RDS

http://aws.amazon.com/articles/2933

Create flat files containing the data to be loaded
创建包含要加载的数据的平面文件。
Stop any applications accessing the target DB Instance
停止任何访问目标DB实例的应用程序
Create a DB Snapshot
创建一个数据库快照
Disable Amazon RDS automated backups
禁用Amazon RDS自动备份。
Load the data using mysqlimport
使用mysqlimport加载数据
Enable automated backups again
再次启用自动备份

#1