Restoring MySQL Instance Best Practices

If you have hundreds of databases and want to restore all of them, these Best Practices are for you.

Individual database restore is much slower then a straight raw file restore of your MySQL data directory. When databases are restored individually (see Restoring a MySQL Database), each row must be selected and inserted and this takes a very long time. So we recommend to shut down your MySQL instance and restore your MySQL data directory using the file restore feature (see Restoring Files). Follow the instructions below.


Windows | Linux

Windows

1. Identify the MySQL data directory.

2. Stop the MySQL instance. The following screen-shot shows how to do it using the Windows MySQL Administrator.

Alternatively, if you are using Windows, you can stop the MySQL service just like any other service. Open a command prompt (click "Start", select "Run", type cmd, and click OK), type the following command and press <Enter>:

net stop MySQL

3. Move the contents of the data directory to another location as a backup.

4. Use CDP to perform a file restore of the MySQL data directory. Refer to Restoring Files.

5. Start the MySQL instance. The following screen-shot shows how to do it using the Windows MySQL Administrator:

Alternatively,you can start the MySQL service just like any other service. Open a command prompt (click "Start", select "Run", type cmd, and click OK), type the following command and press <Enter>:

net start MySQL

Linux

1. Identify the MySQL data directory. For this purpose you can use the show variables command as shown below.

2. Stop the MySQL instance using the following command:

/etc/init.d/mysql stop

3. Move the contents of the data directory to another location as a backup.

4. Use CDP to perform a file restore of the MySQL data directory. Refer to Restoring Files.

5. Start the MySQL instance using the following command:

/etc/init.d/mysql start