MySQL–Restoring large database on windows.

Recently while restoring database from a large backup file I encountered some common MySQL issues and a quick search on google helped me in resolving them.

So for reference I am posting the  steps for resolving these issues on windows :

1.  For starting restore, issue the following command from command prompt:

mysql –u root –p > [sql file path]

After this you will be prompted for root account’s password. On entering root account’s password server will start to restore the database from the backup. However if mysql server’s configuration has not been adjusted to handle large backups then you will soon see a similar message:

Error 2006: MySQL server has gone away.

2. Locate ‘my.ini’ file on your system. On Windows XP the default location is:

‘<Drive Letter>:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server <version>’

For Windows 7 the default location is:

‘<Drive Letter>:\ProgramData\MySQL\MySQL Server <version>’

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

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

max_allowed_packet = <Size>M

wait_timeout = <time in seconds>

       Save the file and restart MySQL server by starting the stopped service.

Now restart database restoration as described in step 1.

If the error still persists then keep on changing the values as described in step 4.

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/

Advertisements
Posted in MySql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

In Archive
%d bloggers like this: