Home > Storage > PowerFlex > White Papers > Deploying MySQL Database on Dell PowerFlex with NVMe over TCP > Example of a partial recovery in MySQL
This section shows how to restore MySQL database to a specific point in time.
To simulate a partial data recovery of the MySQL database, some tables are dropped from the MySQL database. PowerFlex snapshots taken previously are used to restore partial data, that is, to restore data to a specific point in time (PIT). Before starting the test, a snapshot is taken as described in the section Manual PowerFlex snapshots.
Perform the following steps to restore MySQL database to a specific point in time:
$ mysql -u root -p
$ mysql> CHECK TABLE customer,district,history,item,new_order,order_line,orders,stock,warehouse EXTENDED;
$ mysql> SELECT * FROM orders ORDER BY o_id DESC LIMIT 10;
The figure shows that the last “Order ID 4007” was updated is at 04:34:46.
$ mysql> SHOW BINARY LOGS;
+---------------+------------+-----------+
| Log_name | File_size | Encrypted |
+---------------+------------+-----------+
| binlog.000001 | 1073840972 | No |
| binlog.000002 | 1078326718 | No |
.
.
.
.
| binlog.000020 | 2582 | No |
| binlog.000021 | 2791 | No |
+---------------+------------+-----------+
The database is staged, logs are flushed, and a snapshot is taken, as described in Database recovery using PowerFlex snapshots.
INSERT INTO orders (o_id,o_w_id,o_d_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local, o_entry_d) VALUES (4008,408,408,408,NULL,408,408,NOW());
INSERT INTO orders (o_id,o_w_id,o_d_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local, o_entry_d) VALUES (4009,409,409,409,NULL,409,409,NOW());
INSERT INTO orders (o_id,o_w_id,o_d_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local, o_entry_d) VALUES (4010,410,410,410,NULL,410,410,NOW());
INSERT INTO orders (o_id,o_w_id,o_d_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local, o_entry_d) VALUES (4011,411,411,411,NULL,411,411,NOW());
INSERT INTO orders (o_id,o_w_id,o_d_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local, o_entry_d) VALUES (4012,412,412,412,NULL,412,412,NOW());
INSERT INTO orders (o_id,o_w_id,o_d_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local, o_entry_d) VALUES (4013,413,413,413,NULL,413,413,NOW());
INSERT INTO orders (o_id,o_w_id,o_d_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local, o_entry_d) VALUES (4014,414,414,414,NULL,414,414,NOW());
$ mysql> SELECT * FROM orders ORDER BY o_id DESC LIMIT 10;
The figure shows that the last “Order ID 4014” update is at 04:42:00.
$ mysql> SHOW BINARY LOGS;
+---------------+------------+-----------+
| Log_name | File_size | Encrypted |
+---------------+------------+-----------+
| binlog.000001 | 1073840972 | No |
| binlog.000002 | 1078326718 | No |
.
.
.
.
| binlog.000020 | 2582 | No |
| binlog.000021 | 2791 | No |
| binlog.000022 | 3091 | No |
+---------------+------------+-----------+
The binlog file binlog.000022 has the been updated with the new records that are inserted to the database.
For test purposes, one table "orders" was dropped by running the following command:
$ mysql> DROP TABLES orders;
Query OK, 0 rows affected (3.49 sec)
$ mysql> CHECK TABLE customer,district,history,item,new_order,order_line,orders,stock,warehouse EXTENDED;
At this stage, the database is corrupt, and the table is missing. To retrieve the data to a specific PIT, a snapshot taken during Step 6 is used.
$ systemctl stop mysql.service
$ umount /MySQL_Data
Note: For restoring the database only, data, redo log, and undo log volumes are restored as current binary logs are used for restoring the data to a specific PIT.
To restore the data to specific PIT, only the binlog.000022 binlog file is used as it contains the data that is needed for restoring the data to a specific time, that is,”04:42:00.”
$ $ mysqlbinlog --base64-output=decode-rows -vv binlog.000022 |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
#240620 4:42:00 server id 1 end_log_pos 2507 CRC32 0x92ed7954 Write_rows: table id 372 flags: STMT_END_F
### INSERT INTO `Snap01_20GB_N03`.`orders`
### SET
### @1=4014 /* INT meta=0 nullable=0 is_null=0 */
### @2=414 /* INT meta=0 nullable=0 is_null=0 */
### @3=414 /* INT meta=0 nullable=0 is_null=0 */
### @4=414 /* INT meta=0 nullable=1 is_null=0 */
.
.
.
.
# at 2615
#240620 4:42:40 server id 1 end_log_pos 2768 CRC32 0x024608fe Query thread_id=11 exec_time=5 error_code=0 Xid = 70178990
use `Snap01_20GB_N03`/*!*/;
SET TIMESTAMP=1718872960/*!*/;
DROP TABLE `orders` /* generated by server */
/*!*/;
# at 2768
#240620 4:48:14 server id 1 end_log_pos 2791 CRC32 0x993aefcb Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;$
Also, note that at 240620 4:48:14 there is an entry that the table was dropped.
Note: In this test, the last update for restoring data is in the binlog.000022 file, therefore, restoring other binlog files is not required.
$ mysqlbinlog -vv binlog.000022 --stop-datetime="24-06-20 4:42:10" > snap-bin22.sql
$ systemctl start mysql.service
$ mysql -u root -p < snap-bin22.sql
$ systemctl start mysql.service
$ CHECK TABLE customer,district,history,item,new_order,order_line,orders,stock,warehouse EXTENDED;systemctl start mysql.service
or
$ mysqlcheck -c -u root -p --all-databases