MySQL Enterprise Backup: PITR Partial Online Recovery

Here’s a look at using MySQL Enterprise Backup in a specific example:

Consider a Backup Policy

– Full Backup of the environment.
– Complemental Incremental backups & online BinLogs.
And the Restore:
– Logical Restore.
– Online, Zero impact.
– Partial, single database, group of tables.

The Backup

A working environment, with 4 databases, of which 2 will require restoration.
Full backup with MySQL Enterprise Backup:
mysqlbackup --user=root --socket=/tmp/mysql.sock \
  --backup-dir=/home/mysql/voju5/backup/ \
  --with-timestamp backup

Test preparation

Create 4 different databases, where the structure & content is the same.
create database v5_1; use v5_1; create table `voju5` (
  `Name` char(20) NOT NULL DEFAULT '‘,
create database v5_2; use v5_2; create table `voju5` (..);
create database v5_3; use v5_3; create table `voju5` (..);
create database v5_4; use v5_4; create table `voju5` (..);
Insert some rows in each of the “voju5” tables, within each database (using a homemade procedure):
 delimiter //
     SET @x = 0;
         INSERT INTO voju5 SELECT NULL, '1thousand';
         SET @x = @x + 1;
     UNTIL @x > p1 END REPEAT;
 delimiter ;
call Voju5Insert (1000);

The Incremental Backup

Then execute an incremental backup to safeguard the newly created databases & objects and inserted rows.

mysqlbackup --user=root --socket=/tmp/mysql.sock \
  --incremental-backup-dir=/home/mysql/voju5/backup_inc \
  --with-timestamp --incremental \
  --incremental_base=history:last_backup backup


2 Options for Logical Recovery.

We can:
1. Convert the existing backup-dir to a single file backup and then extract it, or
2. Go via the collection of tablespaces, locking and discarding as we go.
Restore method also depends on whether:
1. Tables have had rows deleted or modified (here we can use transportable tablespaces)
2. If the object has been deleted, then we need to recreate it, from a mysqldump extracted from a restored environment.
– In another separate environment or using a specific my.cnf.

Preparing backups

Bring the full backup up to date with all InnoDB data:
mysqlbackup \
  --backup-dir=/home/mysql/voju5/backup/2013-07-25_01-44-43/ \

Update the Full backup with the Incremental backup:

mysqlbackup --backup-dir=/home/mysql/voju5/backup/2013-07-25_01-44-43/ \
  --incremental-backup-dir=/home/mysql/voju5/backup_inc/2013-07-25_17-20-18 \
And then get a single consolidated image file to work from:
mysqlbackup --backup-image=/home/mysql/voju5/backup/full_backup.mbi \
  --backup-dir=/home/mysql/voju5/backup/2013-07-25_01-44-43 \

Recovery: The Test

Now let’s insert some rows, to reflect changes in the binlogs:
use v5_2
call Voju5Insert (1000);
select count(*) from voju5;

| count(*) |
|     2002 |
And generate something to have to recover from:
delete from voju5 where id < 10;
select count(*) from voju5;
| count(*) |
|     1993 |

The Recovery Scenario

– Restore the whole table with the logs applied, before the error.
– Online, without having to stop or impede access to the other users.
So when did the error happen then? Let’s view the General_log:
vi ol63uek01.log
 /delete from voju5
 130729 13:16:29     2 Query     delete from voju5 where id < 10

Backup status

What’s the backup history:
use mysql
select backup_id, start_time, end_time, binlog_pos, binlog_file, \
  backup_type, backup_format, backup_destination, exit_state 
  from backup_history;
| backup_id         | start_time          | end_time            | binlog_pos | binlog_file      | backup_type | backup_format | backup_destination                               | exit_state |
| 13747094837220932 | 2013-07-25 01:44:43 | 2013-07-25 01:45:26 |     829045 | mysql-bin.000008 | FULL        | DIRECTORY     | /home/mysql/voju5/backup/2013-07-25_01-44-43     | SUCCESS    |
| 13747656186636058 | 2013-07-25 17:20:18 | 2013-07-25 17:20:46 |    1654141 | mysql-bin.000008 | INCREMENTAL | DIRECTORY     | /home/mysql/voju5/backup_inc/2013-07-25_17-20-18 | SUCCESS    |
Position  =  1654141  binlog =  mysql-bin.000008

List contents & Extract

Time to list single-file image contents:
mysqlbackup --backup-image=/home/mysql/voju5/backup/full_backup.mbi \
Now we know where the 2 databases are, v5_2 & v5_4, extract them:
mysqlbackup --backup-image=/home/mysql/voju5/backup/full_backup.mbi \
  --src-entry=datadir/v5_2 --dst-entry=/home/mysql/voju5/reco/v5_2 \
mysqlbackup --backup-image=/home/mysql/voju5/backup/full_backup.mbi \
  --src-entry=datadir/v5_4 --dst-entry=/home/mysql/voju5/reco/v5_4 \

Next steps

We also know which binlog to start reading from, and from what position, in order to gather all changes after the Incremental backup and just before the “delete” occurred.

Also, as there is more than 1 binlog, we pass the list on a single line:

mysqlbinlog --start-position=1654141 \
  --stop-datetime="2013-07-29 13:16:28" /binlogs/mysql-bin.000008 \
  /binlogs/mysql-bin.000009 --base64-output=decode-rows --verbose \
  --database=v5_2 > recover_1654141_20130729131628.sql

With this file, recover_1654141_20130729131628.sql, we can see the sql commands COMMENTED OUT, i.e. any execution of this file will not restore anything.


Transportable tablespaces.

Now discard the original tables, and replace with the recovered ones from the Full+INC image result set:

Generate the ‘lock’ and ‘discard’ sql syntax:

select 'use v5_2' db, concat_ws(' ','lock tables',table_name,'write;') 'lock', concat_ws(' ','alter table',table_name,'discard tablespace;') 'discard' from tables where table_schema = 'v5_2';
select 'use v5_4' db, concat_ws(' ','lock tables',table_name,'write;') 'lock', concat_ws(' ','alter table',table_name,'discard tablespace;') 'discard' from tables where table_schema = 'v5_4';


use v5_2; lock tables voju5 write; alter table voju5 discard tablespace;

use v5_4; lock tables voju5 write; alter table voju5 discard tablespace;

Copy the recovered .ibd files from the single file backup image:

cd /opt/mysql/mysql/data/v5_2

cp ~/voju5/reco/v5_2/*.ibd .

Import the tablespaces:

select concat_ws(‘ ‘,’alter table’,table_name,’import tablespace;’) ‘import’ from tables where table_schema = ‘v5_2’;

alter table voju5 import tablespace;


Unlock the tables (execute only once for all tables):

unlock tables;


Time to apply the data extracted from the binlogs, after the incremental backup:

mysqlbinlog --start-position=1654141 \
  --stop-datetime="2013-07-29 13:16:28" \
  /binlogs/mysql-bin.000008 /binlogs/mysql-bin.000009 \
  --verbose --database=v5_2 | mysql –uroot 


Confirm that we have restored the table with all its rows:

mysql> select count(*) from v5_2.voju5;
| count(*) |
|     2002 |


Further details

Just some further details of the procedure and impact of restoring, at a space requirement level:

/opt/mysql/mysql/data                  546996 Kb
/home/mysql/voju5                     1461780 Kb
- 1 full backup (uncompressed)         538984 Kb
- 1 incremental backup                 383412 Kb
- 1 Full+INC single file image         538450 Kb
- Recovered db's (v5_2+v5_4)              328 Kb
- recover sql w/ comments script          576 Kb
- meta & datadir dir's                      8 Kb

One thought on “MySQL Enterprise Backup: PITR Partial Online Recovery

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s