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.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
I had the same problem:
- Go to your RDS Dashboard and click Parameter Groups.
- 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.
- Increase the ‘max_allowed_packet‘ on ‘LargeImport’ to accommodate your import size (Valid values are 1024-1073741824).
- Increase the ‘wait_timeout‘ parameter to accommodate your import size. (Valid values are 1-31536000 seconds).
- Save your changes.
- Click Instances in the left column and select your instance.
- Click Instance Actions and choose Modify.
- Change the Parameter Group to your new ‘LargeImport’ group and click Continue.
- Click ‘Modify DB Instance’.
- 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.
Method 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.
Method 3
According to the MySQL docs, this often has to do with timeouts. While it is hard to diagnose this, I could think of a possible workaround: fire up an EC2 instance in the same region as your RDS server (or use an existing one that you already have – a micro one or a small one will do), upload your big SQL dump there for example using SFTP, install the MySQL client libraries and upload your file from there. This will ensure a faster import speed between your mysql client and your RDS instance.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0