Skip to main content
Pure1 Support Portal

Protecting MySQL Database With FlashArray Snapshots

Problem

As the size of the database grows and the continuous application availability remains critical, the need for quick database recovery is crucial. Traditional backup and restore methods might be cumbersome and time-consuming, not meeting application uptime requirements and recovery time objective (RTO). Pure Storage FlashArray™ is capable of taking volume snapshots, which are immutable point-in-time volume images. FlashArray snapshots may be easily utilized as a rapid means of database recovery. 

This article provides guidance on how to protect and recover MySQL database with FlashArray volume snapshots.

The methods and examples described apply to MySQL 8 Community Edition (GPL) with InnoDB database engine.

FlashArray snapshots are NOT a substitute for backup. Always have a proper data protection strategy.

Thoroughly test the procedures described here before implementing in production.

Solution

MySQL supports physical as well as logical types of backup and recovery. Physical backup is simply a copy of directories and files where data stored in the database is located. On the other hand, logical backup is a set of SQL statements required to restore the data. A database volume snapshot is one of the physical backup methods. Generally, a database can be backed up online while the server is running (hot backup) and when the server is stopped (cold backup). Additionally, warm backup is possible with the server running but with data locked for modifications. Besides the different backup types (logical, physical) and methods (hot, warm, cold), storage and database administrators may also utilize crash consistent and application consistent volume snapshots. A crash-consistent snapshot is executed without any consideration for the open files and pending transactions, whereas an application-consistent snapshot is made on a volume where the application and operating system transactions are fully committed to the persistent storage. 

MySQL provides numerous configuration options including data files and binary log location. A binary log is responsible for recording information about data modifications and it can be located on a different volume than database files. Depending on the binary log location, different procedures may be deployed to properly capture and restore the database with FlashArray snapshots. 

Crash Consistent Snapshot

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. Moreover, snapshots can be easily managed by using protection groups. A protection group defines a set of volumes, hosts, or host groups (called members) that are protected together through snapshots across the member volumes. Protection group snapshots also offer snapshot scheduling and retention policies for all member volumes. For details regarding protection groups, see the FlashArray documentation.

The following is the process for creating a volume snapshot.

Graphical User Interface

Select:

Storage ➤ Volumes ➤ <Volume> ➤ Volume Snapshots ➤ '+' (plus sign) ➤ Create Snapshot ➤ Create.

See Figure 1.

fig1.png

Figure 1 

Command Line Interface

The command to create a volume snapshot is purevol snap with optional snapshot name suffix with the following syntax: 

purevol snap [--suffix SUFFIX_NAME] VOL...

where:

--suffix: specify suffix name for the volume snapshot

For instance: 

purevol snap m20mysql05

If no other snapshots exist, this command creates a snapshot of the m20mysql05 volume with the name m20mysql05.1 . The suffix for the snapshot's name can be specified with --suffix option, otherwise, a unique monotonically increasing number is assigned.

Crash Consistent Snapshot: Recovery

To recover the database using a crash-consistent snapshot, perform the following steps:

  1. MySQL database host: stop database server. 
  2. MySQL database host: unmount database volume.
  3. FlashArray: overwrite the volume unmounted in step 2 with previously taken volume snapshot - see Recovering FlashArray Volume From Snapshot section for details.
  4. MySQL database host: mount database volume.
  5. MySQL database host: start database server.

Recovering FlashArray Volume From Snapshot

Graphical User Interface

FlashArray provides an option to create (copy) a new volume or to restore (overwrite) an existing volume from a snapshot. The protection group snapshots are also listed under Volume Snapshots. The restore process is identical to the individual (outside protection group) volume snapshots.

Select:

Storage ➤ Volumes ➤ <Volume> ➤ Volume Snapshots '⋮' (Vertical Ellipses) ➤ Copy or Restore.

See Figure 2.

fig2a.png

Figure 2

Command Line Interface

FlashArray provides an option to create (copy) a new volume from a snapshot or to restore (overwrite) an existing volume from the snapshot. The following syntax is for the purevol command: 

purevol copy [--overwrite] SOURCE TARGET

where:

--overwrite: overwrite an existing volume

For instance:

purevol copy --overwrite m20mysql05.1 m20mysql05

This command restores the m20mysql05 volume from the m20mysql05.1 snapshot.

Application Consistent Snapshot: Single Volume For Database and Binary Logs

To ensure database and snapshot consistency, MySQL database tables must be flushed from the table cache and locked for modifications by executing 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:

  1. MySQL database: Flush and lock tables - mysql > flush tables with read lock;
  2. FlashArray: take a volume snapshot of the data and log volumes - purevol snap <volume_name>.
  3. MySQL database: unlock tables - mysql > unlock tables; 

The screenshot of MySQL Workbench is shown in Figure 3 when flush tables with read lock and unlock tables commands were executed.

fig2.png

Figure 3

Application Consistent Snapshot Recovery With Single Volume For Database and Binary Logs

Recovering the database from an application-consistent snapshot is the same as the recovery from a crash-consistent snapshot. If the binary logs are backed up separately, incremental point-in-time-recovery is also achievable. 

Application Consistent Snapshot With Separate Volumes For Binary Logs And Database Files 

MySQL datadir and log-bin parameters define the location of data and binary logs. By separating logs from data, more flexible recovery options are possible. As the log volume snapshot frequency is increased the recovery point objective (RPO) improves. Additionally, a volume with database logs may be synchronously or asynchronously replicated to another FlashArray. It is also possible to copy (offload) log volume snapshots to another media with Snap To NFS and CloudSnap for long term retention and recovery. For more information regarding those options, please see Snap To NFS And CloudSnap

The following steps are required for MySQL database protection with FlashArray snapshots (see also Appendix A):

On busy database flush tables with read lock command may require some time.

  1. MySQL database: flush and lock tables - mysql > flush tables with read lock; 
  2. MySQL database: flush binary log - mysql > flush logs;
  3. MySQL database: obtain the name of the binary log files of the master - mysql > show master status; 
  4. FlashArray: create volume snapshot of  data and log volumes - purevol snap <volume_name>. 
  5. MySQL database: unlock tables - mysql > unlock tables; 
  6. At regular intervals execute flush logs command. 
  7. At regular intervals create log volume snapshot. 
  8. Optionally, offload or replicate snapshots to different media.

Application Consistent Snapshot Recovery: Separate Volumes For Binary Logs And Database Files

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.

Summary

FlashArray volume snapshot functionality is included with every FlashArray model. By providing nearly instantaneous snapshot creation as well as volume copy and recovery from snapshots, FlashArray becomes an easy choice for MySQL database deployments. Protection Groups are also available for snapshot management including scheduling and retention policy. Furthermore, asynchronous and synchronous (ActiveCluster) replication is ready for use by customers requiring low recovery time and point objectives. For more information about FlashArray and Pure Storage products please visit https://www.purestorage.com/products.html.

Appendix A

Sample commands shown in this article and their output.                

1. Flush And Lock MySQL Tables 
mysql> flush tables with read lock;
Query OK, 0 rows affected (51.01 sec)
2. Flush MySQL Binary Logs 
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
3. Obtain And Note Master Log Name
mysql> show master status;
+---------------+----------+--------------+------------------+-----------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                             |
+---------------+----------+--------------+------------------+-----------------------------------------------+
| binlog.000009 |      155 |              |                  | 6e3690c0-fb5a-11e9-bc3c-005056b51d19:1-480923 |
+---------------+----------+--------------+------------------+-----------------------------------------------+
4. Create FlashArray Snapshot 
pureuser@sn1-m20-c08-20> purevol snap m20mysql100
Name           Size  Source       Created                  Serial
m20mysql100.1  1T    m20mysql100  2019-12-05 10:18:07 PST  81D514EF2D554D9D00014BF5
5. Unlock MySQL Tables
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
6. Restore FlashArray Volume From Snapshot 
pureuser@sn1-m20-c08-20> purevol copy --overwrite m20mysql100.1 m20mysql100
Name         Size  Source       Created                  Serial                  
m20mysql100  1T    m20mysql100  2019-12-05 10:18:07 PST  81D514EF2D554D9D00014BF4
7. Start Incremental MySQL Database Recovery From Binary Logs 
shell> mysqlbinlog binlog.00001 binlog.00002 binlog.00003 | mysql -u <user> -p

 

© 2019 Pure Storage, the Pure P Logo, and the marks on the Pure Trademark List at https://www.purestorage.com/legal/pr...duserinfo.html are trademarks of Pure Storage, Inc. Other names are trademarks of their respective owners. 

THIS DOCUMENTATION IS PROVIDED "AS IS" AND ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT, ARE DISCLAIMED, EXCEPT TO THE EXTENT THAT SUCH DISCLAIMERS ARE HELD TO BE LEGALLY INVALID. PURE STORAGE SHALL NOT BE LIABLE FOR INCIDENTAL OR CONSEQUENTIAL DAMAGES IN CONNECTION WITH THE FURNISHING, PERFORMANCE, OR USE OF THIS DOCUMENTATION. THE INFORMATION CONTAINED IN THIS DOCUMENTATION IS SUBJECT TO CHANGE WITHOUT NOTICE.