Advanced Restore for MySQL

Browse Operations

For instructions about Browse operations, see Browse and Restore Data: Advanced. For information about the Browse and Restore feature, see Browse and Restore: Overview.

Restoring and Recovering an Entire Server

Whenever the MySQL databases are corrupt or lost, you can restore and recover the entire MySQL server.

We recommend that you do not drop MySQL database (system database) before performing a restore operation. The MySQL database should be running when you perform the restore operation.

Note

If binary logging is disabled on a MySQL server, selecting the Log check box and Recover check box is optional.
If you are restoring from the MySQL Enterprise backed up data, you will not get the option to select the Data check box and the Log check box.
During a restore operation, data directory of the destination MySQL server is overwritten. The data is not staged at any location.

To the Same Instance on the Source Client

By default, the data is restored to the same location from where it was backed up, and the existing database files are overwritten. This restore leaves the database in an online state.

From the CommCell Browser, navigate to Client Computers | <Client> | MySQL.
Right-click <Instance> on the source client.
Click All Tasks, and then click Restore MySql Server.

The MySql Restore dialog box appears.
From the Destination Server list, select the name of the instance where you want to restore.
Verify that the Destination Server State of the database is On-Line. If necessary, click Refresh to update the status.
Select the Data check box and the Log check box.
Select the Recover check box.
In the Staging Location box, enter the staging location where the logs will be restored.
Click OK.

To a Different Instance on the Source Client

From the CommCell Browser, navigate to Client Computers | <Client> | MySQL.
Right-click <Instance> on the source client.
Click All Tasks, and then click Restore MySql Server.

The MySql Restore dialog box appears.
From the Destination Server list, select the name of the instance where you want to restore.
Verify that the Destination Server State of the database is On-Line. If necessary, click Refresh to update the status.
Select the Data check box and the Log check box.
Select the Recover check box.
In the Staging Location box, enter the staging location where the logs will be restored.
Click OK.

To a Different Destination Server (Cross-Machine Restore)

You can restore a server to the same instance on a different client (also called a cross-machine restore). When a server is restored to another client, the restored data inherits the permissions from the parent directory.

Note

If you are restoring the MySQL system database while performing a cross machine restore, ensure that both the source and the destination servers have MySQL instance created in the CommCell Console using the same SA username and password.
If SA user information of the source server is not available at the destination server, first create the same SA username and password on the destination server, and then create an instance on the destination server from the CommCell Console before starting the restore operation.

From the CommCell Browser, navigate to Client Computers | <Client> | MySQL.
Right-click <Instance> on the source client.
Click All Tasks, and then click Restore MySql Server.

The MySql Restore dialog box appears.
From the Destination Server list, select the name of the destination client computer where you want to restore.
Verify that the Destination Server State of the database is On-Line. If necessary, click Refresh to update the status.
Select the Data check box and the Log check box.
Select the Recover check box.
In the Staging Location box, enter the staging location where the logs will be stored.
Click OK.

To a Point-in-Time

The point-in-time database restore is useful in the following scenarios:

If any unwanted transaction occurs in the database, you can revert the database to a state just before the transaction.
If a database fails, you can restore to the state just before the point of failure.

Note

When you perform a point-in-time restore for a database, the next scheduled backup for that database converts to a full backup automatically.

From the CommCell Browser, navigate to Client Computers | <Client> | MySQL.
Right-click <Instance>, click All Tasks, and then click Restore MySql Server.

The MySql Restore dialog box appears.
From the Destination Server list, select the name of the instance where you want to restore.
Verify that the status of the destination server is On-Line. If necessary, click Refresh to update the status.
Select the Data check box and the Log check box.
Select the Recover check box.
In the Staging Location box, enter the location where the server will be restored.
Select Point-in-time, and then set the restore date, time, and time zone to the appropriate values.
Click OK.

Restoring and Recovering Individual Databases

In addition to restoring the MySQL Server, you can restore specific databases that were lost due to an error or corruption. By default, the selected databases are restored to the original location from the latest backup.

From the CommCell Browser, navigate to Client Computers | <Client> | MySQL.
Right-click <Instance>, click All Tasks, and then click Browse and Restore.

The Restore Options dialog box appears.
Click View Content.
Select one or more databases, and then click Recover All Selected.

The MySql Restore dialog box appears.
From the Destination Server list, select the name of the client computer that contains the instance where you want to restore the database.
Verify that the status of the database is On-Line. If necessary, click Refresh to update the status.
Select the Data check box and the Log check box.
Select the Recover check box.
In the Staging Location box, enter the location where the database will be restored.
Click OK.

To a Point-in-Time

Restore a database to a point-in-time if you want to revert the database to a point before a transaction, or if you need to restore a database to the point prior to a failure.

When you perform a point-in-time restore, the next scheduled backup of the database automatically converts to a full backup.

From the CommCell Browser, navigate to Client Computers | <Client> | MySQL.
Right-click <Instance>, click All Tasks, and then click Browse and Restore.

The Restore Options dialog box appears.
Click View Content.
Select one or more databases, and then click Recover All Selected.

The MySql Restore dialog box appears.
From the Destination Server list, select the name of the client computer that contains the instance where you want to restore the database.
Verify that the status of the database is On-Line. If necessary, click Refresh to update the status.
Select the Data check box and the Log check box.
Select the Recover check box.
In the Staging Location box, enter the location where the database will be restored.
Select Point-in-time, and then set the restore date, time, and time zone to the appropriate values.

Make sure that the point-in-time is later than the browse time that is set in the Browse and Restore Options dialog box.
Click OK.

Restoring Transaction Logs

By default, both data and logs are restored. You can restore only the logs whenever it is necessary.

From the CommCell Browser, navigate to Client Computers | <Client> | MySQL.
Right-click <Instance> on the source client, click All Tasks, and then click Restore MySql Server.

The MySql Restore dialog box appears.
From the Destination Server list, select the name of the instance where you want to restore.
Verify that the Destination Server State of the database is On-Line. If necessary, click Refresh to update the status.
Clear the Data check box.
Select the Log check box.
Select the Recover check box.
In the Staging Location box, enter the location where the logs will be restored.
Click OK.

Recurring Restores of Logs

When you perform log restores, you can choose to include the recurring logs for the restore. This is useful when you want to replicate data from one server to another during failover cases.

From the CommCell Browser, navigate to Client Computers | <Client> | MySQL.
Right-click <Instance> on the source client, click All Tasks, and then click Restore MySql Server.

The MySql Restore dialog box appears.
From the Destination Server list, select the name of the instance where you want to restore.
Verify that the Destination Server State of the database is On-Line. If necessary, click Refresh to update the status.
Clear the Data check box.
Select the Log check box.
Select the Recurring Restore check box.
Clear the Recover check box to replicate offline data from one server to another server.
In the Destination Folder box, enter the location where the logs will be restored.
Click OK.

Restoring Data and Transaction Logs without Recovery

By default, the databases and logs are recovered along with the restore. However, if the MySQL Server is offline, you can restore the data and the logs to a destination folder. Later, you can recover the MySQL Server.

From the CommCell Browser, navigate to Client Computers | <Client> | MySQL.
Right-click <Instance>, click All Tasks, and then click Browse and Restore.

The Restore Options dialog box appears.
Click View Content.
Select one or more databases, and then click Recover All Selected.

The MySql Restore dialog box appears.
From the Destination Server list, select the name of the instance where you want to restore.
Select the Data check box and the Log check box.
Clear the Recover check box.
In the Destination Folder box, enter the location where the database and the transaction logs will be restored.
Click OK.

Note

An incremental backup operation gets converted to a full backup operation if you run the incremental backup after running a restore operation without selecting the Recover check box.

Applying the Logs and Database Dumps Manually to the MySQL Server

While performing a restore operation, you can apply the logs and database dumps to the MySQL Server in the following cases:

If the MySQL Server is offline, clear the Recover check box, and then perform the restore operation. After the MySQL Server is online, you can apply the logs and database dumps to the MySQL Server.
If you want to replicate offline data from one server to another server, clear the Recover check box, and then perform the restore operation. Later you can apply the logs and database dumps to the MySQL Server.

Before You Begin

Stage database dumps and logs to a destination folder. For detailed information, see the "Restoring Data and Transaction Logs without Recovery" section on this page.

Procedure

Use the following steps to apply database dumps to the MySQL Server:
1. Run the following command to drop the database dump in the MySQL Server:
```
drop database <database name>
```
  Example:
```
drop database mydatabase2  
```
2. Create a new destination database:
```
create database <database name>
```
  Example:
```
create database mynewdb
```
3. To apply the database dump to the MySQL Server, run the following command :
```
mysql -u <username> -p <database name> <dumpfile>
```
  Example:
```
mysql -u root -p mynewdb <dumpfile>
 Password:*****
```
To restore database to a particular point in time, you can use the following steps to apply logs to the MySQL Server:
1. Identify the master log file number and master log position from the database dump file saved at the staging location:
  
  Example: CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000104', MASTER_LOG_POS=107
2. To apply the master log file to the MySQL Server, run the following commands:
```
mysqlbinlog <master logfile> --stop-datetime=<PIT> -d <databasename> --result-file=<result file name> --start-position=<master log position>
```
```
mysql -u <username> -p -D <database name> -e "source <result file name>"
```
  Example:
```
mysqlbinlog mysql-bin.000104 --stop-datetime="2015-10-6 15:36:0" -d mydatabase2 --result-file=new.sql --start-position=107
```
```
mysql -u root -p -D mydatabase2 -e "source new.sql"
```
3. After applying the master log file, you can apply a log file with the next sequential log file number.
  
  For example, if the master log file number is mysql-bin.000104, the next log file that you can apply should have mysql-bin.000105 as its log file number. Then apply the second log file with log file number mysql-bin.000106, and so on.
  
  Run the following commands for each log file that you want to apply to the MySQL Server:
```
mysqlbinlog <logfile> --stop-datetime=<PIT> -d <databasename> --result-file=<result file name>
```
```
mysql -u <username> -p -D <database name> -e "source <result file name>"
```
  Example for applying log file number mysql-bin.000105:
```
mysqlbinlog mysql-bin.000105 --stop-datetime="2015-10-6 15:36:0" -d mydatabase2 --result-file=new.sql
```
```
mysql -u root -p -D mydatabase2 -e "source new.sql"
```
  Example for applying log file number mysql-bin.000106:
```
mysqlbinlog mysql-bin.000106 --stop-datetime="2015-10-6 15:36:0" -d mydatabase2 --result-file=new.sql
```
```
mysql -u root -p -D mydatabase2 -e "source new.sql"
```
  Similarly, you can run these commands sequentially for each log file that you want to apply to the MySQL Server.
To apply the database dump to the MySQL Server, create the database and run the following command:
```
mysql -u <username> -p <password> < dumpfile
```
Example:
```
mysql -u root -p <mydatabase2
 Password:*****
```