Backup and Recovery for Mysql

Soft

All soft can be found at %SYSCONTROL%/projects/mysql. The basic idea is to have the main scripts at ../mysql/backup and basic functionality and configuration at : ../mysql/lib and ../mysql/etc. Logging and status is kept under %SYSCONTROL%/local/logs/mysql/backup and %SYSCONTROL%/local/logs/mysql/backup_status.

The backup actions could be run locally or remotely from a central machine that will act as semaphore allowing actions to run if there are not blocking ones i.e. we try to avoid to do a snapshot at the same time we run a full.

Backup scripts are based on Percona software, please check at percona website for the right package for your mysql version, nowadays:

Calling a backup action

Important tags on your entitiy

These tags must be present:

- sc-db-datafile-location: datadir of mysql server
- sc-rman-arch : binary logs location
- sc-rman-fra: backups directory 
- sc-run-as: identity of the user running the command, usually mysql, it will be used to connect to the db.
- sc-tns-listener-name: socket for mysql

Usually actions are run locally, therefore a socket parameter in order to connect will be used.

Percona software is used to implement the backup solution.

Logging

Logs of scripts actions can be found at:

• On the mysql server itself, either at /tmp or /ORA/dbs01/syscontrol/local/logs/mysql/backup
• On the central server, i.e. dbsrvd242: /ORA/dbs01/syscontrol/local/logs/mysql/backup

Final result is kept at: ITCORE schema: mysql_sys

Calling actions

Logs can be controlled on the machine at /tmp and on the central server (at this time dbsrvd242): /ORA/dbs01/syscontrol/local/logs/mysql/backup.

A full:

/ORA/dbs01/syscontrol/projects/mysql/backup/backup_wrapper.sh -nopingdb -exec mysql_backup.pl -type full_final  level_EXEC_FULL_FINAL drupal

/ORA/dbs01/syscontrol/projects/mysql/backup/backup_wrapper.sh -nopingdb -exec mysql_backup.pl -type full_inc  level_EXEC_FULL_INC drupal

Incremental backups:

/ORA/dbs01/syscontrol/projects/mysql/backup/backup_wrapper.sh -nopingdb -exec mysql_backup.pl -type inc level_EXEC_INC drupal

Archivelog backup:

/ORA/dbs01/syscontrol/projects/mysql/backup/backup_wrapper.sh -nopingdb -exec mysql_archive.pl level_EXEC_ARCH drupal

Snapshot:

/ORA/dbs01/syscontrol/projects/mysql/backup/backup_wrapper.sh -nopingdb -exec mysql_snapshot.pl level_EXEC_SNAP drupal

Snapshots special

Check documentation at: https://twiki.cern.ch/twiki/bin/view/DESgroup/Private/SettingUpSnapshots .

For drupal03 this setup was followed:

dbnasg405> snap reserve drupal03 20

dbnasg405> vol options drupal03
nosnap=on, nosnapdir=off, minra=off, no_atime_update=on, nvfail=off,
ignore_inconsistent=off, snapmirrored=off, create_ucode=off,
convert_ucode=off, maxdirsize=73400, schedsnapname=ordinal,
fs_size_fixed=off, compression=off, guarantee=volume, svo_enable=off,
svo_checksum=off, svo_allow_rman=off, svo_reject_errors=off,
no_i2p=off, fractional_reserve=100, extent=off, try_first=volume_grow,
read_realloc=off, snapshot_clone_dependency=off, nbu_archival_snap=off

dbnasg405> snap autodelete drupal03 on
dbnasg405> snap autodelete drupal03 defer_delete scheduled
dbnasg405> snap autodelete drupal03 trigger  snap_reserve

dbnasg405> snap autodelete drupal03 show
snapshot autodelete settings for drupal03:
state                           : on
commitment                      : try
trigger                         : snap_reserve
target_free_space               : 20%
delete_order                    : oldest_first
defer_delete                    : scheduled
prefix                          : (not specified)
destroy_list                    : none

dbnasg405> snap list drupal03
Volume drupal03
working...

  %/used       %/total  date          name
----------  ----------  ------------  --------
  0% ( 0%)    0% ( 0%)  May 26 18:04  snapscript_26052011_200621_26

Use cases

Use case 1: full backups

A restore to a certain point in time needs to be performed, after checking for the right full to be used:

--shutdown the database
/etc/init.d/mysql stop

--First do a clean-up in the datadir, keep a copy of my.cnf

[mysql@dbsrvg3308 brtest]$ pwd
/ORA/dbs03/MYSQL/brtest
-- a copy of my.cnf is also kept in each backup (full or incremental)
cp my.cnf /tmp/my.cnf    
rm -rf *
[mysql@dbsrvg3308 2011-05-10_11-45-23]$ innobackupex-1.5.1 --copy-back /ORA/dbs02/MYSQL/backups/2011-05-10_15-34-29

-- Start mysqld skipping networking so no writting will be done:

[mysql@dbsrvg3308 2011-05-10_11-45-23]$mysqld_safe  --socket=/tmp/mysql_restore.sock --skip-networking &


-- At the location of production database check the position in the log file.
[mysql@dbsrvg3308 brtest]$ cat xtrabackup_binlog_pos_innodb
/ORA/dbs02/MYSQL/mysql-bin-brtest.000064        1418422

From above we now the position at the binarylog from where to start, earlier is not possible. We can also check till when we want to go, keep in mind the time intervals dont include the time indicated in the --stop-date tag, the stop just before:

-- PITR
--Just indicating from where to start

/usr/bin/mysqlbinlog --start-date="2011-05-10 13:40:01" --stop-date="2011-05-11 9:44:01" mysql-bin-brtest.000069 mysql-bin-brtest.000070 mysql-bin-brtest.000071 > /tmp/tttt

We have a look to /tmp/ttt to check where to stop if we dont want to apply all logs:

# at 405948
#110511  9:43:52 server id 1  end_log_pos 406021        Query   thread_id=35    exec_time=0     error_code=0
SET TIMESTAMP=1305099832/*!*/;
BEGIN
/*!*/;
# at 406021
#110511  9:43:52 server id 1  end_log_pos 406049        Intvar
SET INSERT_ID=6/*!*/;
# at 406049
#110511  9:43:52 server id 1  end_log_pos 406356        Query   thread_id=35    exec_time=0     error_code=0
SET TIMESTAMP=1305099832/*!*/;
INSERT INTO comment (nid, pid, uid, subject, hostname, created, changed, status, thread, name, mail, homepage, language) VALUES ('1', '0', '4', 'I am here', '137.138.76.30', '1305099832', '1305099831', '1', '04/', 'rgaspar', '', '', 'und')
/*!*/;
# at 406356
#110511  9:43:52 server id 1  end_log_pos 406585        Query   thread_id=35    exec_time=0     error_code=0
SET TIMESTAMP=1305099832/*!*/;
UPDATE node_comment_statistics SET cid='6', comment_count='4', last_comment_timestamp='1305099831', last_comment_name='', last_comment_uid='4'
WHERE  (nid = '1')
/*!*/;
# at 406585
#110511  9:43:52 server id 1  end_log_pos 406892        Query   thread_id=35    exec_time=0     error_code=0
SET TIMESTAMP=1305099832/*!*/;
INSERT INTO field_data_comment_body (entity_type, entity_id, revision_id, bundle, delta, language, comment_body_value, comment_body_format) VALUES ('comment', '6', '6', 'comment_node_article', '0', 'und', 'Iamhere  09:43', 'filtered_html')
/*!*/;
# at 406892
#110511  9:43:52 server id 1  end_log_pos 407203        Query   thread_id=35    exec_time=0     error_code=0
SET TIMESTAMP=1305099832/*!*/;
INSERT INTO field_revision_comment_body (entity_type, entity_id, revision_id, bundle, delta, language, comment_body_value, comment_body_format) VALUES ('comment', '6', '6', 'comment_node_article', '0', 'und', 'Iamhere  09:43', 'filtered_html')
/*!*/;
# at 407203
#110511  9:43:52 server id 1  end_log_pos 407356        Query   thread_id=35    exec_time=0     error_code=0
SET TIMESTAMP=1305099832/*!*/;
UPDATE search_dataset SET reindex='1305099831'


--We want to stop at 2011-05-10 9:44:01
mysqlbinlog --start-position="1418422" --stop-date="2011-05-10 9:44:01"  mysql-bin-brtest.000069 mysql-bin-brtest.000070 mysql-bin-brtest.000071  | mysql -u mysql -pXXXXX --socket=/tmp/mysql_restore.sock

After restore:

>Open mysql
mysqladmin -u mysql -pXXXXX shutdown  --socket=/tmp/mysql_restore.sock
/etc/init.d/mysql start

Use case 2: Incrementals

NOTE: The actual implementation presents limitation due to constraints in the percona software, that means that incrementals only take care of innodb databases, ISAM must be handled independently. Incrementals for the time being shouldnt be used!!! Same as before but we use incrementals.
Shutdown the database if not yet done:

--Shutdown mysql
mysqladmin -u mysql -pXXXXX shutdown

Imagine we have two incrementals to use to be applied to the full (ready to receive incrementals):

[mysql@dbsrvg3308 backups]$ pwd
/ORA/dbs02/MYSQL/backups
[mysql@dbsrvg3308 backups]$ ls -ltr
drwxr-xr-x 9 mysql ci 4096 May 11 11:39 2011-05-11_11-26-58
drwx------ 6 mysql ci 4096 May 11 15:55 2011-05-11_11-26-58_inc1
drwx------ 6 mysql ci 4096 May 11 15:55 2011-05-11_11-26-58_inc2

We apply the incrementals:

xtrabackup --prepare --apply-log-only --target-dir=/ORA/dbs02/MYSQL/backups/2011-05-11_11-26-58 --incremental-dir=/ORA/dbs02/MYSQL/backups/2011-05-11_11-26-58_inc1
xtrabackup --prepare --apply-log-only --target-dir=/ORA/dbs02/MYSQL/backups/2011-05-11_11-26-58 --incremental-dir=/ORA/dbs02/MYSQL/backups/2011-05-11_11-26-58_inc2

Keep a copy of the my.cnf file and do:

[mysql@dbsrvg3308 brtest]$ pwd
/ORA/dbs03/MYSQL/brtest     #datadir of our Mysql database
cp my.cnf /tmp/my.cnf
rm -rf *
[mysql@dbsrvg3308 2011-05-10_11-45-23]$ innobackupex-1.5.1 --copy-back /ORA/dbs02/MYSQL/backups/2011-05-11_11-26-58

Use case 3: Use of snapshots

Stop mysqld .
Check location of datafiles, controller and volume:

[mysql@dbsrvg3308 2011-05-15_11-24-32_inc1]$ mount
dbnasb302:/vol/mysql00 on /ORA/dbs00/MYSQL type nfs (rw,remount,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,timeo=600,addr=172.31.132.15)
dbnasb302:/vol/mysql03 on /ORA/dbs03/MYSQL type nfs (rw,remount,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,timeo=600,addr=172.31.132.15)
dbnasb301:/vol/mysql02 on /ORA/dbs02/MYSQL type nfs (rw,remount,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,timeo=600,addr=172.31.132.12)

dbnasb302> snap list mysql03
Volume mysql03
working...

  %/used       %/total  date          name
----------  ----------  ------------  --------
  4% ( 4%)    1% ( 1%)  May 13 10:31  clone_snapscript_13052.1
  8% ( 4%)    1% ( 1%)  May 13 10:24  snapscript_13052011_095530_108
  9% ( 1%)    1% ( 0%)  May 13 10:23  snapscript_13052011_095155_107
 13% ( 4%)    2% ( 1%)  May 12 18:09  snapscript_12052011_174039_103

Once selected do a restore:

dbnasb302> snap restore -t vol  -s snapscript_12052011_174039_103 mysql03

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

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

Are you sure you want to do this? yes

You have selected volume mysql03, snapshot snapscript_12052011_174039_103

Proceed with revert? yes
Thu May 12 19:31:55 CEST [dbnasb302: wafl.snaprestore.revert:notice]: Reverting volume mysql03 to a previous snapshot.
Volume mysql03: revert successful.

Start mysqld, it should perform a crash recovery, assure that binlogs are available. To help you, snapshot names are coded following this convention: snapscript_12052011_174039_103 i.e. snapscript_ddmmyyyy_hhmmss_binlognumber .

Use case 4: use of FlexClone

Check the right snapshot to use and create a clone ( writtable volume based on a snapshot):

vol clone create  [-s none | file | volume] [-f] -b  []
vol clone create snapscript_13052011_095155_107 -b mysql03 snapscript_13052011_095155_107

Start a mysql instance using that volume, we can use different option liks skip-networking, different socket or different port so there is no collision with the production one, i.e.:

-- Read-only copy
mysqld_safe --user=mysql --datadir=/net/dbnasb302/vol/snapscript_13052011_095155_107/brtest --socket=/tmp/mysql_restore.sock --read-only --port=5501
--Privilege user can always connect read-write
mysql -u mysql -pXXXX --socket=/tmp/mysql_restore.sock 
-- Shutdown
mysqladmin -u mysql -pXXXXX --socket=/tmp/mysql_restore.sock shutdown 

When done please destroy the clone:

dbnasXXX>vol offline volname
dbnasXXX>vol destroy volname

-- RubenGaspar - 13-May-2011