Creating Volume Snapshots for MySQL on FlashArray
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".
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.
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.
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)
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.