Aim

Test our set-up in two situations:

• Using a full backup + binary logs
• Using snapshots

In both tests we try to get till latest transactions.

Using Full backups

In this case we simulate a clean shutdown:

/ORA/dbs01/oracle/product/crs/bin/crs_stop drupal_mysql

-- Check it
ps -efl | grep mysqld

We go for the chosen restore. As this is a test, we do a copy of actual contents:

--Restore desired backup, first do a clean-up in actual datadir

[mysql@dbvrtg046 DRUPAL]$ pwd
/ORA/dbs03/DRUPAL
[mysql@dbvrtg046]$ mv mysql mysql_toto
[mysql@dbvrtg046]$ mkdir mysql

[mysql@dbvrtg046]$ innobackupex-1.5.1 --copy-back /ORA/dbs02/DRUPAL/backups/2011-05-30_13-46-05

Now we need to replay logs till last transaction. The last redo log is not of our interest, it has been generated at start-up. So we check:

[mysql@dbvrtg046 2011-05-30_13-46-05]$ pwd
/ORA/dbs02/DRUPAL/backups/2011-05-30_13-46-05
[mysql@dbvrtg046 2011-05-30_13-46-05]$ cat xtrabackup_binlog_pos_innodb
/ORA/dbs02/DRUPAL/mysql/binlog.000034   6033652

mysqlbinlog --start-position="6033652" binlog.000034 binlog.000035 binlog.000036 binlog.000037 | mysql -u mysql -pXXXXXX --socket=/tmp/mysql_restore.sock

Let's stop and start the database:

--Shutdown
mysqladmin -u mysql -pXXXXX shutdown  --socket=/tmp/mysql_restore.sock

--Start application
/ORA/dbs01/oracle/product/crs/bin/crs_start drupal_mysql

From snapshots

Let's get a snapshot, in a general situation we would go for one of our old snapshots, but this is a test

-- SnAP:
[mysql@dbvrtg046 backup]$ pwd
/ORA/dbs01/syscontrol/projects/mysql/backup
[mysql@dbvrtg046 backup]$ ./mysql_snapshot.pl -snap dbnasg405:drupal03

Simulate an 'clean' stop:

 
--Stop application
/ORA/dbs01/oracle/product/crs/bin/crs_stop drupal_mysql

Chose the right snapshot:

dbnasg405> snap list drupal03
Volume drupal03
working...

  %/used       %/total  date          name
----------  ----------  ------------  --------
  0% ( 0%)    0% ( 0%)  May 30 13:59  snapscript_30052011_160124_40 <====== The last one. 40 is the binary logs from which (inclusive) we need to replay logs!!!
  8% ( 8%)    0% ( 0%)  May 26 18:40  snapscript_26052011_204242_27
  8% ( 0%)    0% ( 0%)  May 26 18:04  snapscript_26052011_200621_26

Do the restore:

dbnasg405> snap restore -t vol -s snapscript_30052011_160124_40 drupal03

WARNING! This will revert the volume to a previous snapshot.
All modifications to the volume after the snapshot will be
irrevocably lost.

Volume drupal03 will be made restricted briefly before coming back online.

Are you sure you want to do this? yes
mys
You have selected volume drupal03, snapshot snapscript_30052011_160124_40

Proceed with revert? yes
Mon May 30 14:10:21 GMT [dbnasg405: wafl.snaprestore.revert:notice]: Reverting volume drupal03 to a previous snapshot.
Volume drupal03: revert successful.

Replay latest logs and restart in production:

[mysql@dbvrtg046 mysql]$ mysqlbinlog binlog.000040 | mysql -u mysql -pXXXXX --socket=/tmp/mysql_restore.sock
[mysql@dbvrtg046 mysql]$ mysqladmin -u mysql -pTut1_Frut1 shutdown  --socket=/tmp/mysql_restore.sock
[mysql@dbvrtg046 mysql]$ /ORA/dbs01/oracle/product/crs/bin/crs_start drupal_mysql

-- RubenGaspar - 30-May-2011