Question: I have some directory backups available to me created using the following command line:
mysqlbackup –defaults-file=/myfolderpath/.backup-my.cnf –backup-dir=$BKUP_DIR backup-and-apply-log
What is the procedure to restore the backup for a point in time recovery ?
Answer: Here is a template , and it makes some assumptions – modify based on your requirements
You’ll notice in the mysqlbackup command the usage of the copy-back switch used to restore files from a directory backup to their original locations within the MySQL server.
mysql> show global variables like ‘datadir’;
+—————+———————-+
| Variable_name | Value |
+—————+———————-+
| datadir | /mypath/mysql/data/ |
+—————+———————-+
1 row in set (0.00 sec)
mysql> show global variables like ‘innodb_log_files_in_group’;
+—————————+——-+
| Variable_name | Value |
+—————————+——-+
| innodb_log_files_in_group | 2 |
+—————————+——-+
1 row in set (0.00 sec)
mysql> show global variables like ‘innodb_log_file_size’;
+———————-+———+
| Variable_name | Value |
+———————-+———+
| innodb_log_file_size | 5242880 |
+———————-+———+
1 row in set (0.00 sec)
mysql> show global variables like ‘innodb_data_file_path’;
+———————–+————————+
| Variable_name | Value |
+———————–+————————+
| innodb_data_file_path | ibdata1:10M:autoextend |
+———————–+————————+
1 row in set (0.00 sec)
——————————
/etc/init.d/mysql stop
——————————
mysqlbackup –backup-dir=/mypath/mysql/BKUP/2021-08-11_18-30-01/ –datadir=/mypath/mysql/data/ –innodb_log_files_in_group=2 –innodb_log_file_size=5242880 –innodb_data_file_path=ibdata1:10M:autoextend copy-back
/etc/init.d/mysql start
Login to database and verify the tables
Author: Jack Vamvas
Leave a Reply