Recovering MySQL From FlashArray Volume Snapshots
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 volume snapshot is created on a system using the default file system layout or the default layout is preserved but in a different location.
- The data , transaction and binary log file system locations are all separate.
- The files for a specific database/schema are on a separate volume.
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.
Select the mount point and click on the Remove button and then the OK button.
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.
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.
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.
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.
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.
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.
The new drive letter or path should be displayed in the dialog. Select OK when complete to mount the volume.
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:
- Restore data volume from the latest snapshot.
- Restore binary log volume from the latest snapshot.
- Start MySQL database server.
- 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 T0 with subsequent log volume snapshots at times T1, T2 with 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 T2 included 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.