Restore single database from full Xtrabackup backup


Percona Xtrabackup does not have any direct way to import single database. I have found a script to restore single database written by Phil Buescher. I have made few changes in the script. There are few perquisite to run the script. You can download the script from here.


You need to use mysql-utility mysqlfrm to get the structure of the table from backup. But mysqlfrm works only for innodb tables.

Prerequisite:
    1. Table should be innodb engine.
    2. innodb_file_per_table option should be enabled on the source server
       before taking the backup.
    3. If you have any view inside your backup directory, script will fail. It will
       print the name of the file where it failed. You need to go inside backup directory and delete all the files related to those views.

For restoring DB we need to have all the following backups. I am referring to the directory structure created by the script I mentioned in my earlier blog post.
 
1. Copy the full weekly database.
2. Copy the daily database upto which day you want to restore.
3. Inside daily database directory, you will find incr directory.
4. Inside incr directory, you will have multiple backups, you can keep the
folders upto what time you want to restore your backup.


In the following exerice, Eg: I want to restore the backup upto
2018-02-07, 2:00 PM for database testdb.

For this first, we need to find out the latest full backup before the date, I
want to restore. Here we have ful backup available full/2018-02-06_23-13-41.
Lets copy it into a folder.

        cp -R full/2018-02-06_23-13-41 /tmp/restore/.

Now we need to get the daily backup for 2018-02-07. Lets check if we have
daily backup for 2018-02-07.
       
        ls -lth daily/

we have backup for 2018-02-07_00-00-12. Lets copy it to restore folder.
   
        cp -R daily/2018-02-07_00-00-12 /tmp/restore/.

Now we need to check the incr directory inside daily backup.
       
        ls -lth /tmp/restore/2018-02-07_00-00-12/incr/

we see there are multiple backups inside incr directory. we have 2 backups after
2:00 PM, we should delete those as we do not need those.
       
        rm -rf /tmp/restore/2018-02-07_00-00-12/incr/2018-02-07_20-00-12
        rm -rf /tmp/restore/2018-02-07_00-00-12/incr/2018-02-07_16-00-01

All the backup we have taken is in compressed form. We need to uncompress it.

        innobackupex --decompress /tmp/restore/2018-02-06_23-13-41
        innobackupex --decompress /tmp/restore/2018-02-07_00-00-12
        innobackupex --decompress /tmp/restore/2018-02-07_00-00-12/incr/2018-02-07_12-00-12
        innobackupex --decompress /tmp/restore/2018-02-07_00-00-12/incr/2018-02-07_08-00-01
        innobackupex --decompress /tmp/restore/2018-02-07_00-00-12/incr/2018-02-07_04-00-01

Now we need to prepare the database backup to restore. For this we need to
apply logs on backup. We will start from the base directory (2018-02-06_23-13-41)
and then we will apply all other incremental backups on base backup. All backups
will be applied with redo-only option expcept the last backup.
   
    innobackupex --apply-log --redo-only /tmp/restore/2018-02-06_23-13-41
    innobackupex --apply-log --redo-only /tmp/restore/2018-02-06_23-13-41 --incremental-dir=/tmp/restore/2018-02-07_00-00-12
    innobackupex --apply-log --redo-only /tmp/restore/2018-02-06_23-13-41 --incremental-dir=/tmp/restore/2018-02-07_00-00-12/incr/2018-02-07_12-00-12
    innobackupex --apply-log --redo-only /tmp/restore/2018-02-06_23-13-41 --incremental-dir=/tmp/restore/2018-02-07_00-00-12/incr/2018-02-07_08-00-01
    innobackupex --apply-log /tmp/restore/2018-02-06_23-13-41 --incremental-dir=/tmp/restore/2018-02-07_00-00-12/incr/2018-02-07_04-00-01

Now the database backup is prepared in base directory (/tmp/restore/2018-02-06_23-13-41).

To restore the single database, you need to run the script we downloade above with 2 parameters.

    restoreSingleDB.sh <db_name_to_restore_to> <db_backup_directory>

I used this script as below

    restoreSingleDB.sh    testdb /tmp/restore/2018-02-06_23-13-41/testdb

Note: Script failed 3 times because there were 3 views in db when backup was taken. I have to delete all the files with the view names manually from /tmp/restore/2018-02-06_23-13-41/eta_curriculum directory.

Comments

Post a Comment

Popular posts from this blog

MySQL 5.7 cluster with Galera on RedHat 7.4

Galera Cluster is a master-master replication, this means it keeps same data on all the nodes. For this purpose it transfers data continuously between nodes (IST or SST). There are 2 methods with which you can transfer the data. Using Percona XtraBackup or rsync. We are going to use rsync.                         yum install rsync We need to setup SELinux in permissive mode for required ports so that nodes can communicate. For this purpose we are going to use semanage . We can install this by given command.        yum install policycoreutils-python Also you firewall should not stop communication between the nodes, you can use telnet utility to check if nodes can communicate with each other on specific ports. If its not already installed, you can install it.               ...
Read more

MySQL default Password

Recently, I was setting up MySQL Galera cluster on redhat 7.4. I installed MySQL 5.7 and Galera cluster on the system according to the instructions provided in galera support page ( Galera Cluster setup ). I have made a blog post for installation and setup here . But when I started the mysql, I was not able to login as I did not setup any password for mysql root account. When I tried logging in without password, I got access denied. After looking for the answers on internet, I found out that MySQL creates a temporary password and write it in log files. It is clrearly mentioned in mysql documentation here . All I needed to do to find this password is to run the following command.      grep 'temporary password' /var/log/mysqld.log Problem solved !
Read more