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` (
  `ID` int(7) NOT NULL AUTO_INCREMENT,
  `Name` char(20) NOT NULL DEFAULT '‘,
   PRIMARY KEY (`ID`) ) ENGINE=InnoDB;
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 //
 DROP PROCEDURE IF EXISTS Voju5Insert//
 CREATE PROCEDURE Voju5Insert (p1 INT)
 BEGIN
     SET @x = 0;
     REPEAT
         INSERT INTO voju5 SELECT NULL, '1thousand';
         SET @x = @x + 1;
     UNTIL @x > p1 END REPEAT;
 END
 //
 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

Recovery

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/ \
  apply-log

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 \
  apply-incremental-backup

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 \
  backup-dir-to-image

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

Objective:

– 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 \
  list-image

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 \
  extract
mysqlbackup --backup-image=/home/mysql/voju5/backup/full_backup.mbi \
  --src-entry=datadir/v5_4 --dst-entry=/home/mysql/voju5/reco/v5_4 \
  extract

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

Advertisement