Uploading large mysql database on AWS RDS using putty - 'MySQL server has gone away'

Question

Im using Amazon Web services and I've setup EC2 and RDS and installed apache, php, mysql, phpmyadmin.

I can import small sql files (I also setup worpdress and imported a sql db for that -fine) but when I try to import a 200mg sql file using putty I get

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

After doing some research it seemed increasing max_allowed_packet, wait_timeout and net_read_timeout in DB Parameter Groups would help but it didnt (I did reboot after making changes, I also set those to their maximum allowed values using RightScale dashboard).

Is there anything else I should be aware of that might be causing this?

I also tried bigdump.php with no luck.

I presume loads of people upload large mysql databases using aws so this is kinda driving me crazy.

Answer 1

I had the same problem:

1. Go to your RDS Dashboard and click Parameter Groups.
2. Click Create DB Parameter Group, name it something like 'LargeImport', (making sure the DB Parameter Group Family you select matches your instance version) and edit the parameters.
3. Increase the 'max_allowed_packet' on 'LargeImport' to accommodate your import size (Valid values are 1024-1073741824).
4. Increase the 'wait_timeout' parameter to accommodate your import size. (Valid values are 1-31536000 seconds).
5. Save your changes.
6. Click Instances in the left column and select your instance.
7. Click Instance Actions and choose Modify.
8. Change the Parameter Group to your new 'LargeImport' group and click Continue.
9. Click 'Modify DB Instance'.
10. Once the change has completed, click Instance Actions again and reboot your instance.

Once your instance has rebooted, you should be able to do larger sql imports.

Once you've completed your import, switch your instance parameter group back to the default parameter group and reboot it again.

Hope this helps.

Answer 2

The accepted answer didn't work for me, but changing the max_allowed_packet to something large (i.e. 32000000 =~ 32MB) and rebooting did.