Percona Xtrabackup/innobackupex Backup and Restore process

This is a very simple example of how to use Percona Xtrabackup/innobackupex

This MariaDB just has the World Database in it as an example data.
This all could be scripted but for now it is for demo purposes.

Create a Full backup:

MariaDB [(none)]> create database Start_Of_Demo; -- Just here for the demo
Query OK, 1 row affected (0.00 sec)

[root@Fedora64 src]# innobackupex  --no-lock --parallel=4  --user=root  --extra-lsndir=/usr/local/src/incremental_last_checkpoint/  --no-timestamp /usr/local/src/fullbackup/

xtrabackup: Transaction log of lsn (1597964) to (1597964) was copied.

innobackupex: Backup created in directory '/usr/local/src/fullbackup'
130609 15:41:39  innobackupex: Connection to database server closed
130609 15:41:39  innobackupex: completed OK!

[root@Fedora64 src]# ls -al fullbackup/
total 18472
drwxr-xr-x. 6 root root     4096 Jun  9 15:41 .
drwxr-xr-x. 6 root root     4096 Jun  9 15:49 ..
-rw-r--r--. 1 root root      260 Jun  9 15:41 backup-my.cnf
-rw-r-----. 1 root root 18874368 Jun  9 15:41 ibdata1
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 mysql
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 performance_schema
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 Start_Of_Demo
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 world
-rw-r--r--. 1 root root       13 Jun  9 15:41 xtrabackup_binary
-rw-r-----. 1 root root       89 Jun  9 15:41 xtrabackup_checkpoints
-rw-r-----. 1 root root     2560 Jun  9 15:41 xtrabackup_logfile



Create an incremental backup:

MariaDB [(none)]> create database incremental_1;  -- Just here for the demo

Query OK, 1 row affected (0.00 sec)

[root@Fedora64 src]#innobackupex  --incremental --no-lock --parallel=4  --no-timestamp --user=root  --incremental-basedir=/usr/local/src/incremental_last_checkpoint/ --extra-lsndir=/usr/local/src/incremental_last_checkpoint/ /usr/local/src/incremental/

xtrabackup: Transaction log of lsn (1597964) to (1597964) was copied.

innobackupex: Backup created in directory '/usr/local/src/incremental'
130609 15:47:20  innobackupex: Connection to database server closed
130609 15:47:20  innobackupex: completed OK!

[root@Fedora64 src]# ls -al incremental
total 64
drwxr-xr-x. 7 root root  4096 Jun  9 15:47 .
drwxr-xr-x. 6 root root  4096 Jun  9 15:49 ..
-rw-r--r--. 1 root root   260 Jun  9 15:47 backup-my.cnf
-rw-r-----. 1 root root 16384 Jun  9 15:47 ibdata1.delta
-rw-r-----. 1 root root    44 Jun  9 15:47 ibdata1.meta
drwxr-xr-x. 2 root root  4096 Jun  9 15:47 incremental_1
drwxr-xr-x. 2 root root  4096 Jun  9 15:47 mysql
drwxr-xr-x. 2 root root  4096 Jun  9 15:47 performance_schema
drwxr-xr-x. 2 root root  4096 Jun  9 15:47 Start_Of_Demo
drwxr-xr-x. 2 root root  4096 Jun  9 15:47 world
-rw-r--r--. 1 root root    13 Jun  9 15:47 xtrabackup_binary
-rw-r-----. 1 root root    93 Jun  9 15:47 xtrabackup_checkpoints
-rw-r-----. 1 root root  2560 Jun  9 15:47 xtrabackup_logfile


Create another incremental backup:

MariaDB [(none)]> create database incremental_2;-- Just here for the demo

Query OK, 1 row affected (0.00 sec)

[root@Fedora64 src]# innobackupex  --incremental --no-lock --parallel=4  --no-timestamp --user=root  --incremental-basedir=/usr/local/src/incremental_last_checkpoint/ --extra-lsndir=/usr/local/src/incremental_last_checkpoint/ /usr/local/src/incremental_2/

xtrabackup: Transaction log of lsn (1597964) to (1597964) was copied.

innobackupex: Backup created in directory '/usr/local/src/incremental_2'
130609 15:49:49  innobackupex: Connection to database server closed
130609 15:49:49  innobackupex: completed OK!
[root@Fedora64 src]# ls -al incremental_2
total 68
drwxr-xr-x. 8 root root  4096 Jun  9 15:49 .
drwxr-xr-x. 6 root root  4096 Jun  9 15:49 ..
-rw-r--r--. 1 root root   260 Jun  9 15:49 backup-my.cnf
-rw-r-----. 1 root root 16384 Jun  9 15:49 ibdata1.delta
-rw-r-----. 1 root root    44 Jun  9 15:49 ibdata1.meta
drwxr-xr-x. 2 root root  4096 Jun  9 15:49 incremental_1
drwxr-xr-x. 2 root root  4096 Jun  9 15:49 incremental_2
drwxr-xr-x. 2 root root  4096 Jun  9 15:49 mysql
drwxr-xr-x. 2 root root  4096 Jun  9 15:49 performance_schema
drwxr-xr-x. 2 root root  4096 Jun  9 15:49 Start_Of_Demo
drwxr-xr-x. 2 root root  4096 Jun  9 15:49 world
-rw-r--r--. 1 root root    13 Jun  9 15:49 xtrabackup_binary
-rw-r-----. 1 root root    93 Jun  9 15:49 xtrabackup_checkpoints
-rw-r-----. 1 root root  2560 Jun  9 15:49 xtrabackup_logfile


Now you must keep this in mind.  

• You database of course has to be shutdown. 
• If you are doing a restore it is likely it crashed anyway
• The Data directory must be empty.

Make sure the server is off then clear our the data directory.

[root@Fedora64 src]#  ps -ef | grep mysql

root      4538  1940  0 15:54 pts/2    00:00:00 grep --color=auto mysql




[root@Fedora64 src]# ls -al /var/lib/mysql/

total 28724

drwxr-xr-x.  8 mysql mysql     4096 Jun  9 15:53 .

drwxr-xr-x. 43 root  root      4096 Jun  8 19:41 ..

-rw-rw----.  1 mysql mysql    16384 Jun  9 15:53 aria_log.00000001

-rw-rw----.  1 mysql mysql       52 Jun  9 15:53 aria_log_control

-rw-r--r--.  1 mysql mysql 18874368 Jun  9 15:53 ibdata1

-rw-rw----.  1 mysql mysql  5242880 Jun  9 15:53 ib_logfile0

-rw-rw----.  1 mysql mysql  5242880 Jun  9 15:17 ib_logfile1

drwx------.  2 mysql mysql     4096 Jun  9 15:43 incremental_1

drwx------.  2 mysql mysql     4096 Jun  9 15:48 incremental_2

drwxr-xr-x.  2 mysql mysql     4096 Jun  9 15:16 mysql

drwxr-xr-x.  2 mysql mysql     4096 Jun  9 15:16 performance_schema

drwx------.  2 mysql mysql     4096 Jun  9 15:40 Start_Of_Demo

drwxr-xr-x.  2 mysql mysql     4096 Jun  9 15:16 world




[root@Fedora64 src]# rm -Rf /var/lib/mysql/*

Now you must keep this in mind. When you create your backups and following incremental backups, you will have to restore the full backup first and then apply all incremental backups. So DO NOT think that you can do a full backup an later just restore from the last incremental.  Always keep in mind how many incremental backups you can afford to keep before another full backup is required. 

To restore just the full backup:

innobackupex --copy-back  /usr/local/src/fullbackup/




innobackupex: Starting to copy InnoDB log files

innobackupex: in '/usr/local/src/fullbackup'

innobackupex: back to original InnoDB log directory '/var/lib/mysql'

innobackupex: Finished copying back files.




130609 15:54:57  innobackupex: completed OK!




[root@Fedora64 src]# ls -al /var/lib/mysql/

total 18456

drwxr-xr-x.  6 mysql mysql     4096 Jun  9 15:54 .

drwxr-xr-x. 43 root  root      4096 Jun  8 19:41 ..

-rw-r--r--.  1 root  root  18874368 Jun  9 15:54 ibdata1

drwxr-xr-x.  2 root  root      4096 Jun  9 15:54 mysql

drwxr-xr-x.  2 root  root      4096 Jun  9 15:54 performance_schema

drwxr-xr-x.  2 root  root      4096 Jun  9 15:54 Start_Of_Demo

drwxr-xr-x.  2 root  root      4096 Jun  9 15:54 world







[root@Fedora64 mysql]# mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 1

Server version: 5.5.31-MariaDB MariaDB Server

That does the full backup but I made incremental backups after that. So it will have to be shutdown and the data directory cleaned out. Why ? You have to apply the incremental backups to the full then restore it.  It is done like the following example shows: 

innobackupex --apply-log --redo-only /usr/local/src/fullbackup/

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

130609 15:57:59  InnoDB: Starting shutdown...

130609 15:58:00  InnoDB: Shutdown completed; log sequence number 1597964

130609 15:58:00  innobackupex: completed OK!

Now let us apply the first incremental directory. You can see in the example below that the incremental_1 directory is now applied in the fullbackup directory. This was not the case earlier. 

innobackupex --apply-log --redo-only /usr/local/src/fullbackup/ --incremental-dir=/usr/local/src/incremental/

130609 15:58:42  innobackupex: completed OK!

[root@Fedora64 src]# ls -al fullbackup/

total 20520

drwxr-xr-x. 7 root root     4096 Jun  9 15:58 .

drwxr-xr-x. 6 root root     4096 Jun  9 15:49 ..

-rw-r--r--. 1 root root      260 Jun  9 15:41 backup-my.cnf

-rw-r-----. 1 root root 18874368 Jun  9 15:58 ibdata1

drwxr-xr-x. 2 root root     4096 Jun  9 15:58 incremental_1

drwxr-xr-x. 2 root root     4096 Jun  9 15:41 mysql

drwxr-xr-x. 2 root root     4096 Jun  9 15:41 performance_schema

drwxr-xr-x. 2 root root     4096 Jun  9 15:41 Start_Of_Demo

drwxr-xr-x. 2 root root     4096 Jun  9 15:41 world

-rw-r--r--. 1 root root       13 Jun  9 15:41 xtrabackup_binary

-rw-r-----. 1 root root       89 Jun  9 15:58 xtrabackup_checkpoints

-rw-r-----. 1 root root  2097152 Jun  9 15:58 xtrabackup_logfile

Now let us apply the second incremental directory. You can see in the example below that the incremental_2 directory is now applied in the fullbackup directory. This was not the case earlier. 

innobackupex --apply-log /usr/local/src/fullbackup/ --incremental-dir=/usr/local/src/incremental_2/

innobackupex: Copying '/usr/local/src/incremental_2/Start_Of_Demo/db.opt' to '/usr/local/src/fullbackup/Start_Of_Demo/db.opt'

130609 16:00:09  innobackupex: completed OK!

[root@Fedora64 src]# ls -al fullbackup/

total 20524

drwxr-xr-x. 8 root root     4096 Jun  9 16:00 .

drwxr-xr-x. 6 root root     4096 Jun  9 15:49 ..

-rw-r--r--. 1 root root      260 Jun  9 15:41 backup-my.cnf

-rw-r-----. 1 root root 18874368 Jun  9 16:00 ibdata1

drwxr-xr-x. 2 root root     4096 Jun  9 15:58 incremental_1

drwxr-xr-x. 2 root root     4096 Jun  9 16:00 incremental_2

drwxr-xr-x. 2 root root     4096 Jun  9 15:41 mysql

drwxr-xr-x. 2 root root     4096 Jun  9 15:41 performance_schema

drwxr-xr-x. 2 root root     4096 Jun  9 15:41 Start_Of_Demo

drwxr-xr-x. 2 root root     4096 Jun  9 15:41 world

-rw-r--r--. 1 root root       13 Jun  9 15:41 xtrabackup_binary

-rw-r-----. 1 root root       89 Jun  9 16:00 xtrabackup_checkpoints

-rw-r-----. 1 root root  2097152 Jun  9 15:58 xtrabackup_logfile

Now let us apply the full backup directory. You can see in the example below that the incremental_2 directory is now applied in the fullbackup directory. This was not the case earlier. 

[root@Fedora64 src]# rm -Rf /var/lib/mysql/*

[root@Fedora64 src]# innobackupex --copy-back  /usr/local/src/fullbackup/

[root@Fedora64 src]# chown -R mysql:mysql /var/lib/mysql

Everything is now restored and available:

[root@Fedora64 mysql]# mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 1

Server version: 5.5.31-MariaDB MariaDB Server




Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.




Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.




MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| Start_Of_Demo      |

| incremental_1      |

| incremental_2      |

| mysql              |

| performance_schema |

| world              |

+--------------------+

Links available for reference. 

• http://dev.mysql.com/doc/index-other.html
• http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/creating_a_backup_ibk.html
• http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/preparing_a_backup_ibk.html
• http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/incremental_backups_innobackupex.html
• http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/partial_backups_innobackupex.html
• http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/restoring_a_backup_ibk.html