Home > Storage > PowerFlex > White Papers > Deploying MySQL Database on Dell PowerFlex with NVMe over TCP > Database recovery using PowerFlex snapshots
This section describes the procedure to take application-consistent PowerFlex database snapshots manually. These snapshots offer flexibility and control for creating on-demand copies. The database snapshots can help to:
Perform the following steps to prepare the MySQL database for taking a consistent snapshot:
$ FLUSH LOGS;
$ FLUSH TABLES WITH READ LOCK;
$ LOCK INSTANCE FOR BACKUP;
Note: To flush the binary logs and stage the database, run the $ FLUSH BINARY LOGS; command.
$ UNLOCK INSTANCE;
$ UNLOCK TABLES;
A consistent snapshot of MySQL database volumes is created.
Database recovery encompasses restoring the specified snapshot and applying binary logs to restore the database to its desired state. This method ensures data integrity and minimizes downtime if there is database corruption.
The following steps describe the complete database recovery of MySQL database volumes using PowerFlex snapshot:
To restore the full database, only the data, redo logs, and undo logs snapshot are used to overwrite the data on the respective volumes.
$ systemctl stop mysql.service
$ umount /<directory name for data>
$ umount /<directory name for redologs>
$ umount /<directory name for undologs>
Figure 14. PowerFlex volume overwrite
Note: When the content is overwritten, the data cannot be retrieved. Therefore, run this command with caution.
$ mount /dev/nvme1n1p1 /MySQL_Data
$ mount /dev/nvme1n3p1 /RedoLogs
$ mount /dev/nvme1n4p1 /UndoLogs
$ lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINTS
sda 8:0 0 160G 0 disk
├─sda1 8:1 0 512M 0 part /boot/efi
├─sda2 8:2 0 10G 0 part /
├─sda3 8:3 0 86.7G 0 part /home
└─sda4 8:4 0 62.8G 0 part [SWAP]
sr0 11:0 1 1024M 0 rom
nvme1n1 259:3 0 2T 0 disk
└─nvme1n1p1 259:9 0 2T 0 part /MySQL_Data
nvme1n2 259:14 0 1T 0 disk
└─nvme1n2p1 259:16 0 1024G 0 part /Binary_Logs
nvme1n3 259:27 0 256G 0 disk
└─nvme1n3p1 259:29 0 256G 0 part /RedoLogs
nvme1n4 259:36 0 256G 0 disk
└─nvme1n4p1 259:38 0 256G 0 part /UndoLogs
$ systemctl start mysql.service;
$ /Binary_Logs/mysqlnode03_binarylogs # ls -lrth
total 7.3G
-rw-r----- 1 mysql mysql 1.1G Jun 20 03:20 binlog.0000xx
.
.
.
-rw-r----- 1 mysql mysql 2.6K Jun 20 04:41 binlog.000020
-rw-r----- 1 mysql mysql 2.8K Jun 20 04:48 binlog.000021
To restore the full database, binlog files are used for restoring the data.
Note: If there are multiple binlog files, convert each binlog file must be converted to an .sql file and restore them sequentially.
$ mysqlbinlog -vv binlog.0000xx > Snap-N03_bin0xx.sql
$ mysql -u root -p < Snap-bin0xx.sql
The database restore takes time based on the amount of data that must be restored. Do not cancel the restoration operation as it automatically exits when the file restoration is completed.
$ mysqlcheck --databases "Database Name" -u root -p
If table names are available, you can run the following command:
$ CHECKSUM TABLE customer, district,history,item,new_order,order_line,orders,stock,warehouse extended;
The full database is restored.
This section describes the steps to restore the database to a specific point in time (PIT) using the PowerFlex snapshots.
To restore the database to a specific time only, the data, redo logs, and undo logs snapshot are used to overwrite the data on the source volume.
$ systemctl stop mysql.service
$ umount /<directory name for data>
$ umount /<directory name for redologs>
$ umount /<directory name for undologs>
Figure 15. PowerFlex volume overwrite
Note: When the content is overwritten, the data cannot be retrieved. Therefore, run this command with caution. Mount the NVMe volumes to the respective MySQL data, binary, redo, and undo logs folders. The mounted folders are created on the database server with the required rights.
$ mount /dev/nvme1n1p1 /MySQL_Data
$ mount /dev/nvme1n2p1 /Binary_Logs
$ mount /dev/nvme1n3p1 /RedoLogs
$ mount /dev/nvme1n4p1 /UndoLogs
$ lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINTS
sda 8:0 0 160G 0 disk
├─sda1 8:1 0 512M 0 part /boot/efi
├─sda2 8:2 0 10G 0 part /
├─sda3 8:3 0 86.7G 0 part /home
└─sda4 8:4 0 62.8G 0 part [SWAP]
sr0 11:0 1 1024M 0 rom
nvme1n1 259:3 0 2T 0 disk
└─nvme1n1p1 259:9 0 2T 0 part /MySQL_Data
nvme1n2 259:14 0 1T 0 disk
└─nvme1n2p1 259:16 0 1024G 0 part /Binary_Logs
nvme1n3 259:27 0 256G 0 disk
└─nvme1n3p1 259:29 0 256G 0 part /RedoLogs
nvme1n4 259:36 0 256G 0 disk
└─nvme1n4p1 259:38 0 256G 0 part /UndoLogs
$ systemctl start mysql.service;
$ /Binary_Logs/mysqlnode03_binarylogs # ls -lrth
total 7.3G
-rw-r----- 1 mysql mysql 1.1G Jun 20 03:20 binlog.0000xx
.
.
.
-rw-r----- 1 mysql mysql 2.6K Jun 20 04:41 binlog.000020
-rw-r----- 1 mysql mysql 2.8K Jun 20 04:48 binlog.000021
$ mysqlbinlog --base64-output=decode-rows -vv binlog.000021 |less
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240606 4:02:32 server id 1 end_log_pos 127 CRC32 0x287f3c17 Start: binlog v 4, server v 8.4.0 created 240606 4:02:32 at startup
ROLLBACK/*!*/;
# at 127
#240606 4:02:32 server id 1 end_log_pos 158 CRC32 0xe037d0a5 Previous-GTIDs
# [empty]
# at 158
Note: If there are multiple binlog files to be restored, then all the binlog files up until the required specific recovery time must be converted to .sql format and restored sequentially before restoring the last .sql file.
$ mysqlbinlog -vv binlog.0000xx --stop-datetime="YY-MM-DD HH:MM:SS" > Snap-N03_bin0xx.sql
The date time parameter must specify the point in time of recovery.
$ mysql -u root -p < Snap-N03_bin0xx.sql
The database restore takes time based on the amount of data that must be restored. Do not cancel the restoration operation as it automatically exits when the file restoration is completed.
$ mysqlcheck --databases "Database Name" -u root -p
If table names are available, you can run the following command:
$ CHECKSUM TABLE customer, district,history,item,new_order,order_line,orders,stock,warehouse extended;
The database is restored to a specific point in time.
For more information about MySQL data recovery, see MySQL Point-in-Time (Incremental) Recovery.