CODEWITHSUNDEEP
amazon-rds
chrisl-921fb74d
chrisl-921fb74d

Reputation: 23100

Importing database for amazon rds: ERROR 2006 (HY000) at line 667: MySQL server has gone away

While importing a database to my amazon rds instance i've been issued the following error:

ERROR 2006 (HY000) at line 667: MySQL server has gone away

I went ahead and tried changing the interative_timeout setting to a larger number. However, it'll only let me set that for a session and amazon doesn't allow it to be set for global sessions.

How do i import a larger database into my amazon's rds instance?

Upvotes: 4

Views: 2042

Answers (3)

Justin Fortier
Justin Fortier

Reputation: 488

Here's how I did it, mind you my databases weren't very large (largest one was 1.5G).

First dump your existing database(s):

mysqldump [database_name] --master-data=2 --single-transaction --order-by-primary -uroot -p | gzip > /mnt/dumps/[database_name].sql.gz

You can then transfer this file to an Amazon EC2 instance that has permission to access your RDS instance using something like scp. Once the file is located on your Amazon EC2 instance you should extract it using:

gzip [database_name].sql.gz -d
#you should now have a file named [database_name].sql in your directory.
mysql -uroot -p -h=[rds-instance]
source [database_name].sql

It should then start importing. This information is located in their documentation.

Upvotes: 0

Rory Savage
Rory Savage

Reputation: 1

You can simply change your RDS DB sizing settings by using the parameter group settings. Most MSQL settings are in there. It will require a restart of the instance however. The setting you want is max_allowed_packet and you need to not only set it with the client, but on the server itself.

Upvotes: 0

Matt Healy
Matt Healy

Reputation: 18531

The documentation gives instructions on how to import large datasets. Typically, the best method is to create flat files and import them in to your RDS instance.

I recently completed a migration of a database over 120GB in size from a physical server to RDS. I dumped each table in to a flat CSV file, then split the larger files in to multiple 1GB size parts. I then imported each table in to RDS.

Upvotes: 0

Related Questions