Skip to main content
Pure Technical Services

Recovering MySQL From FlashArray Volume Snapshots

Currently viewing public documentation. Please login to access the full scope of documentation.

This guide can be used to recover from a snapshot for a MySQL instance. Recovery can be done for the original system a snapshot was taken from or for copying the MySQL instance to a new location. 

This knowledge article covers the creation of volume snapshots for the entire MySQL Instance.

To move an individual database/schema using volume snapshots see Moving Individual Databases Between Instances Using Volume Snapshots.

The concepts and instructions explained here can be applied to both MySQL and MariaDB. 

The method for recovering a MySQL system from storage snapshots will vary in the following scenarios:

The recovery method for application consistency is the same as crash consistency. The difference between the two approaches is how consistency is guaranteed. 

The MySQL option file should attempt to be as similar to the variables used when the storage snapshot was taken. The most important variable to ensure remains the same is innodb_page_size. If the storage snapshot was taken without this property being set then the default value is 16K. In the event that innodb_page_size is different or has changed then it must be set back to the originating value. 

If innodb_log_files_in_group or innodb_log_file_size has changed since the snapshot was taken then post volume recovery and pre startup the log files should be deleted. Log files are recreated on startup if they are not found.  

Recovery Process

Step 1. Stop the MySQL Instance

Microsoft Windows - In a PowerShell terminal type the following command (for those running MySQL 8 the service name is mysql80 , for mysql 5.7 it is mysql57):

net stop mysql80

Linux - In a terminal execute the following command:

sudo systemctl stop mysqld 

Step 2. Unmount the Volume 

Repeat this for each volume used for the MySQL instance. 

Microsoft Windows - Press the Win + R keys to open Run, type diskmgmt.msc into Run, and click/tap on OK to open Disk Management. Right click or press and hold on the drive (ex: "MySQL_Base") you want to unmount, and click/tap on Change Drive Letter and Paths.

Capture.JPG

Select the mount point and click on the Remove button and then the OK button.

clipboard_eea4bc19e54f59c3ef102c160b77f3d14.png

Linux - In a terminal execute the umount command:

umount /var/lib/mysql 

Step 3. Recover the Volume Snapshot 

Repeat this for each volume used for the MySQL instance. 

In the FlashArray Graphical user interface navigate to the Snapshots view by selecting Protection and then Snapshots.

clipboard_ebf209fa75600471d681be8bf88bcb311.png

Find the storage snapshot taken for the MySQL instance. In this example the snapshot is named DB-07-MySQL-Base.MySQL-AppConsistency. Once the snapshot has been identified select the three eclipses to bring up the context menu. 

clipboard_eb4b8d04ec7340534c2dd6ca161b8065d.png

There are 2 options for recovery:

Restore to parent volume

The original volume from which the snapshot was created will be overwritten with the snapshot. This can be performed by selecting Restore.

A prompt will be displayed to confirm the operation. Once Restore has been selected then the operation will proceed. 

clipboard_eba3491d2d25236dfc75082da20980208.png

Copy to a new or existing volume

A new volume can be created from the storage snapshot or another existing volume can be overwritten with the storage snapshot. This can be performed by selecting Copy...

The new volume needs to be given a name, if a volume with the same name already exists Overwrite needs to be selected. Once Copy has been selected the operation will proceed. 

clipboard_e905edc47a8c58d89db534492956055e7.png

The original volume has a snapshot created before being overwritten to ensure that recovery to the original volume can be performed. 

If copying to a new volume then the volume needs to be attached to a host. 

Step 4. Mount the volume 

Repeat this for each volume used for the MySQL instance. 

Assuming the volume is already connected to the host and has been discovered proceed by mounting the volume.

If a new volume has been created there could be a need to rescan for new devices. To discover new devices do the following:

Microsoft Windows 

In Disk Management select Action and then Rescan Disks

clipboard_e664b5e855d6c255ac9679e58c90263f0.png

Linux 

To scan for new devices use the rescan-scsi-bus.sh script with the -a qualifier. 

rescan-scsi-bus.sh

Microsoft Windows - Press the Win + R keys to open Run, type diskmgmt.msc into Run, and click/tap on OK to open Disk Management. Right click or press and hold on the drive (ex: "MySQL_Base") you want to mount, and click/tap on Change Drive Letter and Paths.

In Change Drive Letters and Paths for <Volume> select the Add... button. Choose either a drive letter or an empty NTFS folder to mount to volume to and select OK when done. 

clipboard_eae87673dbcc68eabf9b0815acb36d999.png

The new drive letter or path should be displayed in the dialog. Select OK when complete to mount the volume. 

clipboard_e624af21a7493b0c70d80a9f4456c4887.png

Linux - In a terminal execute the mount command for the device:

mount /dev/mapper/<device> /var/lib/mysql 

If there is an entry in fstab and the original volume was overwritten with a snapshot then the mount command for the directory can be used without the device:

mount /var/lib/mysql 

Step 5. Start the MySQL Instance 

Microsoft Windows - Run the following command in a PowerShell terminal (for those running MySQL 8 the service name is mysql80 , for mysql 5.7 it is mysql57):

net start MySQL80

Linux - Run the following command in a terminal:

systemctl start mysqld

Recovery for Data, Transaction, and Binary Logs on Separate Volumes

To recover a full database after a catastrophic failure, perform the following procedure:

  1. Restore data volume from the latest snapshot.
  2. Restore binary log volume from the latest snapshot.
  3. Start MySQL database server.
  4. Recover to a point-in-time using binary logs.

The incremental point-in-time recovery is dependent on the binary log and data volume snapshots. If the data volume snapshot was taken at time Twith subsequent log volume snapshots at times T1, Twith failure occurring at time T3, the database recovery is possible up to the last successfully committed and recorded transaction in the binary log at time T3.

When the database server is started after the failure, the new binary log is created. The binary logs recovered from snapshots will incrementally restore data to the most recent time available. The binary log recovery is initiated with mysqlibinlog command. For example, the log volume recovered from the snapshot created at Tincluded binlog.00001 closed at time T1, binlog.00002 closed time T2. If binlog.00003 is available when a catastrophic failure occurred at T3, it should be also included in the database recovery; to begin the incremental recovery, execute the following:

shell> mysqlbinlog binlog.00001 binlog.00002 binlog.00003 | mysql -u <user> -p

For additional details in incremental binary log recovery, refer to MySQL documentation https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.html.

Recovery for Databases/schema on a Separate Volume

Each database that resides on its own volume may need to have the soft link recreated after recovery. See Layout for separating database/schema files for more information on how this is done.

The recovery of a single database, if deployed on its own volume, is not possible. The entire instance needs to be recovered as there could be issues with regard to table space resolution.