Reputation: 26772
I have been given a VERY large mysql backup file. It is ~630 MB... I guess someone thought it was a good idea to store images in a database... Anyway, I need to restore the data somehow in MySQL. But i can't get it done because of the filesize.
At first i tried to do it with MySQL Workbench. But when i try to import the file it gives me the following error:
Could not allocate xxxxx bytes to read file C:\backup.sql
Then i tried to do it through command prompt. I entered the following in cmd
:
C:\> mysql -u user -pPassword database < C:\backups.sql
That eventually gives me the following warning:
ERROR 2006 (HY000) at line 68230: MySQL server has gone away
Guess that is also because of the big filesize?
I have no other ideas on how to restore the data. Is that still possible somehow?
Upvotes: 13
Views: 24562
Reputation: 272106
Increasing the wait_timeout
and/or interactive_timeout
should help. First check the current value:
C:\> mysql -hlocalhost -uroot -proot
mysql> SHOW VARIABLES LIKE 'wait_timeout';
If this is very low (e.g. 30 seconds) then increase it (e.g. 5 minutes):
mysql> SET SESSION wait_timeout = 300;
mysql> SET SESSION interactive_timeout = 300;
Then execute your SQL file:
mysql> \. database.sql
Upvotes: 12
Reputation:
I had a 3.5G dump file, I tried to import it with PhpMyAdmin and MySql workbench but without success. So I just used the console to run it
# mysql -u user_name -pYour_passwd your_db < your_dump.sql
and it works great
Upvotes: 3