# 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:
    

```bash
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:

```bash
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:
    

```bash
<Drive>:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server <version>
```

For **Windows 7** the default location is:

```bash
<Drive>:\ProgramData\MySQL\MySQL Server <version>
```

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

```bash
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/