MySQL–Restoring large database on windows

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 :

1. 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.

1. 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.

2. 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>

1. Open my.ini file and add the following:

max_allowed_packet = <Size>M
wait_timeout = <time in seconds>

Use a split down value for , generally using a value half of the database size would be good to start with. Save the file and restart MySQL server by starting the stopped service. Now restart database restoration process as described in step 1. If the error still persists then reduce the max_allowed_packet size further in step 4 and continue back from step 1.

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/