Skip to main content
Pure Technical Services

Creating Volume Snapshots for MySQL on FlashArray

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

FlashArray volume snapshots are a very fast way to create recovery points for MySQL instances. MySQL provides for a number of possible volume and file system architectural layouts each of which will affect the process of creating a storage snapshot on FlashArray. 

Regardless of volume and file system architectural layout there are two methods of creating a MySQL Database volume snapshot:

  • Application Consistent  - An application consistent volume snapshot is typically performed by coordinating with the application and operating system to ensure everything is in a state that recovery is assured and possible to a point in time. 
  • Crash Consistent - A crash consistent volume snapshot ignores the presence or possibility of an application. No effort is made to ensure that the application is in a specific state and recovery to a point in time cannot be guaranteed. 

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. 

Matching MySQL Directories to FlashArray Volumes  

Before being able to create a MySQL storage snapshot the MySQL directories and any corresponding FlashArray volumes need to be identified. 

Finding MySQL Directories

To do this a command needs to be run as an administrative user in MySQL workbench or the MySQL command line utility:

SHOW VARIABLES WHERE Variable_Name LIKE "%dir" OR Variable_Name LIKE "%directory%" OR Variable_Name LIKE "log_bin%";

The response to this command provides a list of all of the directories on which the MySQL instance will have the various data files.  

Output for MySQL deployed on Linux/Unix

Using the default layout with a FlashArray volume the output should have the basedir and datadir populated but some others will either have no entry or contain "./" which indicates the files will be stored in the datadir.

mysql> SHOW VARIABLES WHERE Variable_Name LIKE "%dir" OR Variable_Name LIKE "%directory%" OR Variable_Name LIKE "log_bin%";
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| basedir                         | /usr/                          |
| character_sets_dir              | /usr/share/mysql-8.0/charsets/ |
| datadir                         | /var/lib/mysql/                |
| innodb_data_home_dir            |                                |
| innodb_doublewrite_dir          |                                |
| innodb_log_group_home_dir       | ./                             |
| innodb_temp_tablespaces_dir     | ./#innodb_temp/                |
| innodb_tmpdir                   |                                |
| innodb_undo_directory           | ./                             |
| lc_messages_dir                 | /usr/share/mysql-8.0/          |
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/binlog          |
| log_bin_index                   | /var/lib/mysql/binlog.index    |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| plugin_dir                      | /usr/lib64/mysql/plugin/       |
| slave_load_tmpdir               | /tmp                           |
| tmpdir                          | /tmp                           |
+---------------------------------+--------------------------------+
18 rows in set (0.00 sec)

If using Separate data/binary and transaction log directories the output will have specific values for directories other than basedir and datadir.

mysql> SHOW VARIABLES WHERE Variable_Name LIKE "%dir" OR Variable_Name LIKE "%directory%" OR Variable_Name LIKE "log_bin%";
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| basedir                         | /mysql/base/                          |
| character_sets_dir              | /mysql/base/share/mysql-8.0/charsets/ |
| datadir                         | /mysql/base/data/                     |
| innodb_data_home_dir            | /mysql/base/data                      |
| innodb_doublewrite_dir          |                                       |
| innodb_log_group_home_dir       | /mysql/log                            |
| innodb_temp_tablespaces_dir     | ./#innodb_temp/                       |
| innodb_tmpdir                   |                                       |
| innodb_undo_directory           | /mysql/undo                           |
| lc_messages_dir                 | /mysql/base/share/mysql-8.0/          |
| log_bin                         | OFF                                   |
| log_bin_basename                |                                       |
| log_bin_index                   |                                       |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
| plugin_dir                      | /mysql/base/lib64/mysql/plugin/       |
| slave_load_tmpdir               | /tmp                                  |
| tmpdir                          | /tmp                                  |
+---------------------------------+---------------------------------------+
18 rows in set (0.00 sec)

Output for MySQL deployed on Microsoft Windows

This is an example of a Separate data/binary and transaction log directories deployment. 

mysql> SHOW VARIABLES WHERE Variable_Name LIKE "%dir" OR Variable_Name LIKE "%directory%" OR Variable_Name LIKE "log_bin%";
+---------------------------------+-------------------------------------------------+
| Variable_name                   | Value                                           |
+---------------------------------+-------------------------------------------------+
| basedir                         | C:\MySQL\Base\                                  |
| character_sets_dir              | C:\MySQL\Base\share\charsets\                   |
| datadir                         | C:\MySQL\Base\Data\                             |
| innodb_data_home_dir            | C:\MySQL\Base\Data                              |
| innodb_doublewrite_dir          |                                                 |
| innodb_log_group_home_dir       | C:\MySQL\Log\Data                               |
| innodb_temp_tablespaces_dir     | .\#innodb_temp\                                 |
| innodb_tmpdir                   |                                                 |
| innodb_undo_directory           | C:\MySQL\Undo\Data                              |
| lc_messages_dir                 | C:\MySQL\Base\share\                            |
| log_bin                         | ON                                              |
| log_bin_basename                | C:\MySQL\Base\Data\MYSQL-WINDOWS-bin            |
| log_bin_index                   | C:\MySQL\Base\Data\MYSQL-WINDOWS-bin.index      |
| log_bin_trust_function_creators | OFF                                             |
| log_bin_use_v1_row_events       | OFF                                             |
| plugin_dir                      | C:\MySQL\Base\lib\plugin\                       |
| slave_load_tmpdir               | C:\Windows\SERVIC~1\NETWOR~1\AppData\Local\Temp |
| tmpdir                          | C:\Windows\SERVIC~1\NETWOR~1\AppData\Local\Temp |
+---------------------------------+-------------------------------------------------+
18 rows in set (0.01 sec)

Matching MySQL Directories to Mounted File System(s)

Once the directory locations are known they can be inspected to identify the FlashArray volume(s) which each corresponds to. 

For Linux/Unix systems the "df " command is useful to get an overview of what file systems are mounted and the volumes they correspond to. 

For Microsoft Windows systems the diskpart and wmic utilities will be used to identify file system(s) and corresponding volume(s). 

If using a file system layout which separates database/schema files then the location of each database/schema will not be shown with this output. Instead, datadir directory should be checked to see where the symbolic links direct towards. 

Linux/Unix 

When using the "df -h" command all mounted file systems and the corresponding volumes are listed.  One the volumes for MySQL have been identified take note of the /dev/mapper/<volume> path as this will be used to identify the volume serial number. 

Using the default layout with a FlashArray volume there should be a volume mounted to "/var/lib/mysql". This file system contains all of the databases/schemas, transaction logs, and binary logs.

[root@DB-01 ~]# df -h
Filesystem                                      Size  Used Avail Use% Mounted on
devtmpfs                                        252G     0  252G   0% /dev
tmpfs                                           252G     0  252G   0% /dev/shm
tmpfs                                           252G   11M  252G   1% /run
tmpfs                                           252G     0  252G   0% /sys/fs/cgroup
/dev/mapper/rhel-root                           410G  5.5G  405G   2% /
/dev/mapper/rhel-home                            37G  293M   37G   1% /home
/dev/mapper/3624a93708488b6dac70f42a20001edcd2 1014M  325M  690M  32% /boot
/dev/mapper/3624a93708488b6dac70f42a20001edcd1  599M  6.9M  592M   2% /boot/efi
tmpfs                                            51G     0   51G   0% /run/user/0
/dev/mapper/3624a9370668f1ab9b15f4bc400011883   2.0T   25G  2.0T   2% /var/lib/mysql

Where Separate data/binary and transaction log directories have been used the output of "df -h" should have multiple volumes, each with its own file system, which corresponds to the entries identified when looking for the MySQL directories. 

[root@DB-01 ~]# df -h
Filesystem                                      Size  Used Avail Use% Mounted on
devtmpfs                                        252G     0  252G   0% /dev
tmpfs                                           252G     0  252G   0% /dev/shm
tmpfs                                           252G   11M  252G   1% /run
tmpfs                                           252G     0  252G   0% /sys/fs/cgroup
/dev/mapper/rhel-root                           410G   38G  373G  10% /
/dev/dm-9                                      1014M  256M  759M  26% /boot
/dev/mapper/rhel-home                            37G  293M   37G   1% /home
/dev/mapper/3624a93708488b6dac70f42a20001edce1  599M  6.9M  592M   2% /boot/efi
tmpfs                                            51G     0   51G   0% /run/user/0
/dev/mapper/3624a9370668f1ab9b15f4bc400011886   2.0T   34G  2.0T   2% /mysql/base
/dev/mapper/3624a9370668f1ab9b15f4bc40001189e   512G  3.7G  509G   1% /mysql/undo
/dev/mapper/3624a9370668f1ab9b15f4bc400011894   1.0T   40G  985G   4% /mysql/log
/dev/mapper/3624a9370668f1ab9b15f4bc4000118a2   768G   36G  733G   5% /mysql/warehouse01
/dev/mapper/3624a9370668f1ab9b15f4bc4000118a3   768G  5.4G  763G   1% /mysql/warehouse02
/dev/mapper/3624a9370668f1ab9b15f4bc4000118a4   768G  5.4G  763G   1% /mysql/warehouse03
/dev/mapper/3624a9370668f1ab9b15f4bc4000118a5   768G  5.4G  763G   1% /mysql/warehouse04

If separating database and schema files using symbolic links then the datadir needs to be inspected to see where the link directs to. Symbolic links can be identified below where the entry is of the following form: localfolder -> remote folder. Take note of the remote folder and check it against the entries provided by df -h. 

[root@DB-01 ~]# ll -h /mysql/base/data/
total 18G
-rw-r-----. 1 mysql mysql   56 Feb 23 06:10  auto.cnf
-rw-------. 1 mysql mysql 1.7K Feb 23 06:10  ca-key.pem
-rw-r--r--. 1 mysql mysql 1.1K Feb 23 06:10  ca.pem
-rw-r--r--. 1 mysql mysql 1.1K Feb 23 06:10  client-cert.pem
-rw-------. 1 mysql mysql 1.7K Feb 23 06:10  client-key.pem
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:27  DB-06-bin.000001
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:28  DB-06-bin.000002
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:28  DB-06-bin.000003
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:28  DB-06-bin.000004
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:28  DB-06-bin.000005
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:28  DB-06-bin.000006
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:29  DB-06-bin.000007
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:29  DB-06-bin.000008
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:29  DB-06-bin.000009
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:29  DB-06-bin.000010
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:29  DB-06-bin.000011
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:29  DB-06-bin.000012
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:30  DB-06-bin.000013
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:30  DB-06-bin.000014
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:30  DB-06-bin.000015
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:30  DB-06-bin.000016
-rw-r-----  1 mysql mysql 1.1G Mar  1 02:30  DB-06-bin.000017
-rw-r-----  1 mysql mysql  72M Mar  1 02:30  DB-06-bin.000018
-rw-r-----  1 mysql mysql  342 Mar  1 02:30  DB-06-bin.index
-rw-r-----  1 mysql mysql 3.6K Mar  1 02:26  ib_buffer_pool
-rw-r-----. 1 mysql mysql  12M Mar  1 02:30  ibdata1
-rw-r-----  1 mysql mysql  12M Mar  1 02:26  ibtmp1
drwxr-x---. 2 mysql mysql  187 Mar  1 02:26 '#innodb_temp'
drwxr-x---. 2 mysql mysql  143 Feb 23 06:10  mysql
-rw-r-----. 1 mysql mysql  25M Mar  1 02:30  mysql.ibd
drwxr-x---. 2 mysql mysql 8.0K Feb 23 06:10  performance_schema
-rw-------. 1 mysql mysql 1.7K Feb 23 06:10  private_key.pem
-rw-r--r--. 1 mysql mysql  452 Feb 23 06:10  public_key.pem
-rw-r--r--. 1 mysql mysql 1.1K Feb 23 06:10  server-cert.pem
-rw-------. 1 mysql mysql 1.7K Feb 23 06:10  server-key.pem
drwxr-x---. 2 mysql mysql   28 Feb 23 06:10  sys
lrwxrwxrwx. 1 root  root    18 Feb 23 07:03  warehouse01 -> /mysql/warehouse01
lrwxrwxrwx. 1 root  root    18 Feb 23 07:04  warehouse02 -> /mysql/warehouse02
lrwxrwxrwx. 1 root  root    18 Feb 23 07:04  warehouse03 -> /mysql/warehouse03
lrwxrwxrwx. 1 root  root    18 Feb 23 07:04  warehouse04 -> /mysql/warehouse04

Microsoft Windows 

Open a PowerShell or Command Prompt and enter the diskpart utility:

diskpart

Once in the diskpart utility use the following to list the volumes:

list volume
DISKPART> list volume

  Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
  Volume 0     D                       DVD-ROM         0 B  No Media
  Volume 1     C                NTFS   Partition    255 GB  Healthy    Boot
  Volume 2         Recovery     NTFS   Partition    499 MB  Healthy    Hidden
  Volume 3                      FAT32  Partition     99 MB  Healthy    System
  Volume 4         Warehouse01  NTFS   Partition    767 GB  Healthy
    C:\MySQL\Warehouse01\
  Volume 5         Warehouse02  NTFS   Partition    767 GB  Healthy
    C:\MySQL\Warehouse02\
  Volume 6         Warehouse03  NTFS   Partition    767 GB  Healthy
    C:\MySQL\Warehouse03\
  Volume 7         Warehouse04  NTFS   Partition    767 GB  Healthy
    C:\MySQL\Warehouse04\
  Volume 8         MySQL_Base   NTFS   Partition   2047 GB  Healthy
    C:\MySQL\Base\
  Volume 9         MySQL_BinLo  NTFS   Partition   1247 GB  Healthy
    C:\MySQL\BinLog\
  Volume 10        MySQL_Log    NTFS   Partition   1023 GB  Healthy
    C:\MySQL\Log\
  Volume 11        MySQL_Undo   NTFS   Partition    511 GB  Healthy
    C:\MySQL\Undo\

Take note of the Volume ### and Ltr. Using this information the volumes can then be matched to a serial number using wmic.

Match the File System(s) to FlashArray Volume(s)

Linux/Unix

The operating system will be queried for the device serial number of the MySQL data volume(s) and then matched to a block storage device on FlashArray. 

This example will use the default layout with a FlashArray volume thus only one volume needs to be identified. The process is the same for any additional volumes used in a different layout.

Using the "df" command we query the mount point/directory on which the volume is mounted. 

 df -h | grep /var/lib/mysql
[root@DB-01 ~]# df -h | grep /var/lib/mysql
/dev/mapper/3624a9370668f1ab9b15f4bc400011883   2.0T   71G  2.0T   4% /var/lib/mysql

Using the device name (/dev/mapper/<volume identifier>) udev is queried for the DM_SERIAL property.  

udevadm info --query=all --name=<device name> | grep DM_SERIAL
[root@DB-01 ~]# udevadm info --query=all --name /dev/mapper/3624a9370668f1ab9b15f4bc400011883 | grep DM_SERIAL
E: DM_SERIAL=3624a9370668f1ab9b15f4bc400011883

Using the serial number returned for the device, it is possible to match it up to the block storage volume on a FlashArray. Note that the block volume serial number will be all of the characters after “3624a9370”. 

Microsoft Windows 

Execute the following command in PowerShell or Command Prompt to query all devices for the Name , SerialNumber and Size:

wmic diskdrive get name,size,SerialNumber
PS C:\Users\DBTest> wmic diskdrive get name,size,SerialNumber
Name                SerialNumber                      Size
\\.\PHYSICALDRIVE8  6000c2918160c0d53dc1237b7e3e3d19  1099506078720
\\.\PHYSICALDRIVE2  6000c2994e8572da6bfb90202edbed11  824633671680
\\.\PHYSICALDRIVE7  6000c29ef37e9a94dcf21be84f608af2  137436203520
\\.\PHYSICALDRIVE1  6000c29047b4551b50408c9b57e99e5a  824633671680
\\.\PHYSICALDRIVE4  6000c294abd48d0593e4e3016ac12e04  824633671680
\\.\PHYSICALDRIVE6  6000c29b57204022741f5058a00c11d3  1340029716480
\\.\PHYSICALDRIVE0  6000c29de05233d12724ad984a6d8c46  274872407040
\\.\PHYSICALDRIVE9  6000c290012fd2a5252f07966e6bfce1  549753039360
\\.\PHYSICALDRIVE3  6000c2962cfb818def338b473e4af34c  824633671680
\\.\PHYSICALDRIVE5  6000c29a62d616cb61296af759197574  2199020382720

The \\.\PHYSICALDRIVE<Number> corresponds to the Volume number.

Once the serial numbers have been identified

To see all of the volumes and their serial numbers using the FlashArray CLI execute the following:

purevol list
pureuser@flasharray> purevol list
Name                    Size   Source  Created                  Serial
DB-01-MySQL-Base        2T     -       2021-02-19 00:53:54 PST  668F1AB9B15F4BC400011883
DB-01-MySQL-Log         1T     -       2021-02-19 00:54:45 PST  668F1AB9B15F4BC40001188E
DB-01-MySQL-Undo        512G   -       2021-02-19 00:55:18 PST  668F1AB9B15F4BC40001189C
Warehouse01             768G   -       2021-02-23 06:20:17 PST  668F1AB9B15F4BC4000118A4
Warehouse02             768G   -       2021-02-23 06:20:17 PST  668F1AB9B15F4BC4000118A5
Warehouse03             768G   -       2021-02-23 06:20:17 PST  668F1AB9B15F4BC4000118A3
Warehouse04             768G   -       2021-02-23 06:20:17 PST  668F1AB9B15F4BC4000118A2

Once the volume(s) have been identified then a storage snapshot can be created for it. 

Creating Storage Snapshots for MySQL 

Depending on the file system layout used for MySQL the approach to creating a storage storage could differ based on how the solution has been deployed. 

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

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".

clipboard_ec285cd5b947e13fad1521792559ef1af.png

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

clipboard_ebd83213e52106513fd9f38852c90461a.png

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

clipboard_e882f4eba0a2209b4386e53a99cbf0744.png

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. 

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

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. 

clipboard_e36a2292b4dff468c963d42b32f5697c7.png

Give the Snapshot a name and then select Create. 

clipboard_ed8e37c0d54adce2fb73aa7356e8470f8.png

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. 

clipboard_e6f8da137203070fe5aaca1685a3ba34f.png

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)

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. 

clipboard_e36a2292b4dff468c963d42b32f5697c7.png

Give the Snapshot a name and then select Create. 

clipboard_ed8e37c0d54adce2fb73aa7356e8470f8.png

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. 

clipboard_e6f8da137203070fe5aaca1685a3ba34f.png

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