Restore MySQL Database from mysqlbackup

In todays world, One of the most common activity is to perform database restore using old backup or performing PIT recovery for database.

In this blog , We will show you how to perform restore and recovery of the MySQL database using backup taken via mysqlbackup.

In order to restore Mysql database from MySQL backup , We will follow below steps:

1) Shut down MySQL Database Server/Service

Check the status of the mysql service using command : systemctl status mysql

Shut down mysql service using command : systemctl stop mysql

2) Delete all existing file of MySQL Server

Delete all files inside the server's data directory. Also delete all files inside the directories specified by the --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory options for restore, if the directories are different from the data directory.

3) Restore full Backup

mysqlbackup --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --datadir=/var/lib/mysql \
--backup-image=/dbbackup/testdb/2020-06-26/my_full_2020-06-26-0400.mbi --backup-dir=/dbbackup/testdb/restore_tmp copy-back-and-apply-log

Below are the logs for Full DB Backup restore:

3) Restore Incremental backup:

Restore the series of backups to the server, except for the last incremental backup in the series (which covers the targeted point in time for recovery).

mysqlbackup --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --datadir=/var/lib/mysql \
--backup-image=/dbbackup/testdb/2020-06-26/my_inc_2020-06-26-1430.mbi --backup-dir=/dbbackup/testdb/restore_tmp/inc_2020-06-26-1430 --incremental \

copy-back-and-apply-log

Below are the logs for Incremental DB Backup Restore:

5) When finished ,note the binary log position to which you have restored the server.The information is available from the backup_variables.txt file in the restored data directory of the server:

root@mysqlhost01:/var/lib/mysql# cat backup_variables.txt|grep binlog_position
binlog_position=binlog.000264:776776395
root@mysqlhost01:/var/lib/MySQL#

6) In order to perform PIT recovery ,We will restore closest Incremental backup to a directory so that we can read the binlog file to perform PIT recovery.

Extract last incremental backup in sequence for PIT recovery:

mysqlbackup --backup-dir=/dbbackup/testdb/restore_tmp/inc_2020-06-26-1830 --backup-image=/dbbackup/testdb/2020-06-26/my_inc_2020-06-26-1830.mbi image-to-backup-dir

Below are the logs for Final Incremental DB Backup restore:

7) Next step is to find the binlog_position in binlog which is close to the PIT recovery time.

Suppose,We want to Perform point in time recovery till 26 June 2020 16:32:56.

With binlog file which have the data/changes near to the target recovery time , Save output of the binlog to text file:
mysqlbinlog binlog.000264 > binlog_000264.sql

Cross-Check the time for last restore lsn(lsn from Step 5):

root@mysqlhost01:/dbbackup/testdb/restore_tmp/inc_2020-06-26-1830/datadir# cat binlog_000264.sql|grep 776776395
#200626 14:41:36 server id 3727631716 end_log_pos 776776395 Xid = 34691643
# at 776776395
root@mysqlhost01:/dbbackup/testdb/restore_tmp/inc_2020-06-26-1830/datadir#

Search for the log position around time 26 June 2020 16:32:56:

root@mysqlhost01:/dbbackup/testdb/restore_tmp/inc_2020-06-26-1830/datadir# cat binlog_000264.sql|grep "200626 16:32:56"
#200626 16:32:56 server id 3727631716 end_log_pos 842673253 GTID last_committed=824360 sequence_number=824361 rbr_only=yes original_committed_timestamp=1593160376336117 immediate_commit_timestamp=1593160376311145 transaction_length=730
#200626 16:32:56 server id 3727631716 end_log_pos 842673349 Query thread_id=15 exec_time=0 error_code=0
#200626 16:32:56 server id 3727631716 end_log_pos 842673454 Table_map: `mysql_innodb_cluster_metadata`.`routers` mapped to number 91
#200626 16:32:56 server id 3727631716 end_log_pos 842673874 Update_rows: table id 91 flags: STMT_END_F
#200626 16:32:56 server id 3727631716 end_log_pos 842673901 Xid = 35397625
#200626 16:32:56 server id 3727631716 end_log_pos 842673983 GTID last_committed=824361 sequence_number=824362 rbr_only=yes original_committed_timestamp=1593160376625073 immediate_commit_timestamp=1593160376600657 transaction_length=730
#200626 16:32:56 server id 3727631716 end_log_pos 842674079 Query thread_id=15 exec_time=0 error_code=0
#200626 16:32:56 server id 3727631716 end_log_pos 842674184 Table_map: `mysql_innodb_cluster_metadata`.`routers` mapped to number 91
#200626 16:32:56 server id 3727631716 end_log_pos 842674604 Update_rows: table id 91 flags: STMT_END_F
#200626 16:32:56 server id 3727631716 end_log_pos 842674631 Xid = 35397651

root@mysqlhost01:/dbbackup/testdb/restore_tmp/inc_2020-06-26-1830/datadir#

Change permission of the newly restored directory:

root@mysqlhost01:/var/lib# chown -R mysql:mysql /var/lib/mysql
Make Sure gtid_mode=ON and enforce_gtid_consistency=ON added in the my.cnf.
NOTE : If restoring to same server change backup-auto.cnf to auto.cnf and backup-mysqld-auto.cnf to mysqld-auto.cnf

Start mysql: systemctl start MySQL

8) Recover database to point in time:

mysqlbinlog --start-position="776776395" --stop-position="842674631" binlog.000264 | mysql -uroot -p

you can learn more about MySql and InnoDB from below books:

MySQL Replication Simplified: Easy step-by-step examples to establish, troubleshoot and monitor replication

Introducing InnoDB Cluster: Learning the MySQL High Availability Stack

MySQL Cookbook: Solutions for Database Developers and Administrators

Effective MySQL Backup and Recovery

=========================================================================

You can also , Visit our Blogs for related topic on Backup & Restore:

1)Backup MSSQL Sever Database to a mapped drive

2)Improve & Optimizing Performance of the MySQL Restore with mysqldump

3)Restore MySQL InnoDB Cluster from mysqlbackup(MySql Enterprise Backup)

4)Restoring a Failed member in InnoDB Cluster