Recently while trying to restore database from a large database backup file I encountered few issues with MySQL.
So for reference I am posting the solution steps for resolving these issues on windows :
- Initiate the restoration process from command prompt as follows:
mysql –u root –p > [sql file path]
After this you will be prompted for root account’s password. After entering root account’s password server will begin the databse restoration process from the backup. However if mysql server’s configuration has not been adjusted to handle large backups then the restoration process will end with the following message:
Error 2006: MySQL server has gone away.
-
Stop MySQL service. Generally MySQL is installed as a service on windows. So open services window (use ‘services.msc‘ command on windows run dialog) and stop the required service.
-
Locate my.ini file on your system. On Windows XP the default location is:
<Drive>:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server <version>
For Windows 7 the default location is:
<Drive>:\ProgramData\MySQL\MySQL Server <version>
- Open my.ini file and add the following:
max_allowed_packet = <Size>M
wait_timeout = <time in seconds>
Use a split down value for
Following are some links for reference:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
http://bogdan.org.ua/2008/12/25/how-to-fix-mysql-server-has-gone-away-error-2006.html
http://wpmu.org/how-to-backup-and-import-a-very-large-wordpress-mysql-database/