Creating Storage Snapshots for MySQL

Depending on theOptimizing SQL Server Operations and Scale Technical Solution Design the approach to creating a storage storage could differ based on how the solution has been deployed.

Note:

It is important to identify how recovery will be performed as this will impact how a storage snapshot should be taken.

Note:

If only a single volume is used for MySQL persistent storage then the storage snapshot can be created for that single volume without any additional steps.

If multiple volumes are used it is strongly advised that the storage snapshot is created for a protection group containing all of the relevant MySQL volumes.

Protection Groups offer additional protection functionality such as offload to NFS,AWS S3, or Azure Blob storage. If additional snapshot functionality such as offloading and/or scheduling is required then the storage snapshot should be created as a apart of a Protection Group.

Creating a Protection Group for MySQL volumes

To create a protection group, in the FlashArray GUI navigate to the Protection section and select "Protection groups". To create a Protection group on the local system select the "+" in the top right hand corner of "Source Protection Groups".

When the "Create Protection Group" prompt is shown give it a name.

Once the Protection Group has been created navigate towards it and Add either the MySQL Hosts, Host Groups, or Volumes.

Application Consistency

To ensure database and snapshot consistency, MySQL database tables must be flushed from the table cache and locked for modifications by executing the flush tables with read lock SQL statement. This action halts database operations. Furthermore, if binary logs are located on a different volume than database files and are also protected, forward transaction recovery is possible by starting the new binary log. Below is the procedure for a MySQL application-consistent snapshot:

1a. Flush and lock tables

Execute the following command to flush the logs and lock the tables using the MySQL command line client or in a workbench window:


flush tables with read lock;

1b. (Optional) Flush binary log

If the file system layout separates data and binary log files it may be desirable to flush the binary log.

Note:

A snapshot of the binary log volume is important to perform point in time (incremental) recovery.

To flush the binary log execute the following in the MySQL command line:


flush logs;

1c. (Optional) Obtain the Master Log Name

Note:

This step is important for scenarios where point in time recovery is required.

Execute the following in the MySQL command line to obtain the Master Log name:


show master status;


mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000031 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Take note of the File and Position fields.

2. Take a volume snapshot of the MySQL volume(s)

If only a single volume is being used then navigate to the volume in the FlashArray GUI (Storage -> Volumes -> <Volume>) and select the "+" or 3 vertical eclipses for the Volume Snapshots section and select Create.

Give the Snapshot a name and then select Create.

If using a Protection Group then in the Protection Group management view (Protection -> Protection Groups - > Source Protection Groups -> <Protection Group Name>) and select the "+" or 3 vertical eclipses for the Protection group Snapshots section and select Create.

Give the Protection Group Snapshot a name if required and apply Retention or Replication Properties. The storage snapshot will then be created.

3. Unlock the MySQL tables

Execute the following command to unlock the tables using the MySQL command line client or in a workbench window:


flush tables with read lock;

Crash Consistency

The crash-consistent FlashArray volume snapshots do not require any interaction with MySQL database and can be executed through the Command Line Interface (CLI), Rest API, and FlashArray Graphical User Interface. Crash consistent storage snapshots can also be automated using a Protection Group and schedule.

1. Take a volume snapshot of the MySQL volume(s)

Give the Snapshot a name and then select Create.

Give the Protection Group Snapshot a name if required and apply Retention or Replication Properties. The storage snapshot will then be created.