Friday, 11 April 2014

Percona full and incremental backup script

#!/bin/sh
INNOBACKUPEX=innobackupex
INNOBACKUPEXFULL=/usr/bin/$INNOBACKUPEX
USEROPTIONS="--user=mysqlbackup --password=****** --socket=/tmp/mysql.sock"
TMPFILE="/tmp/innobackupex-runner.$$.tmp"
MYCNF=/etc/my.cnf
MYSQL=/usr/local/mysql/bin/mysql
MYSQLADMIN=/usr/local/mysql/bin/mysqladmin
BACKUPDIR=/data/storage/
FULLBACKUPDIR=$BACKUPDIR/full
INCRBACKUPDIR=$BACKUPDIR/incr
FULLBACKUPLIFE=86400 #full backup interval Time:seconds
KEEP=7 # keep how many full backup files
# Grab start time
STARTED_AT=`date +%s`
#############################################################################
# Display error message and exit
#############################################################################
error()
{
    echo "$1" 1>&2
    exit 1
}
# Check options before proceeding
if [ ! -x $INNOBACKUPEXFULL ]; then
  error "$INNOBACKUPEXFULL does not exist."
fi
if [ ! -d $BACKUPDIR ]; then
  mkdir $BACKUPDIR
  #error "Backup destination folder: $BACKUPDIR does not exist."
fi
if [ -z "`$MYSQLADMIN $USEROPTIONS status | grep 'Uptime'`" ] ; then
 error "HALTED: MySQL does not running."
fi
if ! `echo 'exit' | $MYSQL -s $USEROPTIONS` ; then
 error "User name or password is wrong."
fi
# Some info output
echo "----------------------------"
echo
echo "$0: MySQL backup script"
echo "started: `date`"
echo
# Create full and incr backup directories if they not exist.
mkdir -p $FULLBACKUPDIR
mkdir -p $INCRBACKUPDIR
# Find latest full backup
LATEST_FULL=`find $FULLBACKUPDIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`
# Get latest backup last modification time
LATEST_FULL_CREATED_AT=`stat -c %Y $FULLBACKUPDIR/$LATEST_FULL`
# Run an incremental backup if latest full is still valid. Otherwise, run a new full one.
if [ "$LATEST_FULL" -a `expr $LATEST_FULL_CREATED_AT + $FULLBACKUPLIFE + 5` -ge $STARTED_AT ] ; then
  # Create incremental backups dir if not exists.
  TMPINCRDIR=$INCRBACKUPDIR/$LATEST_FULL
  mkdir -p $TMPINCRDIR
  # Find latest incremental backup.
  LATEST_INCR=`find $TMPINCRDIR -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1`
  # If this is the first incremental, use the full as base. Otherwise, use the latest incremental as base.
  if [ ! $LATEST_INCR ] ; then
    INCRBASEDIR=$FULLBACKUPDIR/$LATEST_FULL
  else
    INCRBASEDIR=$LATEST_INCR
  fi
  echo "Running new incremental backup using $INCRBASEDIR as base."
  $INNOBACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS --incremental $TMPINCRDIR --incremental-basedir $INCRBASEDIR > $TMPFILE 2>&1
else
  echo "Running new full backup."
  $INNOBACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS $FULLBACKUPDIR > $TMPFILE 2>&1
fi
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
 echo "$INNOBACKUPEX failed:"; echo
 echo "---------- ERROR OUTPUT from $INNOBACKUPEX ----------"
 cat $TMPFILE
 rm -f $TMPFILE
 exit 1
fi
THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPFILE`
rm -f $TMPFILE
echo "Databases backed up successfully to: $THISBACKUP"
echo
# Cleanup
echo "Cleanup. Keeping only $KEEP full backups and its incrementals."
AGE=$(($FULLBACKUPLIFE * $KEEP / 60))
find $FULLBACKUPDIR -maxdepth 1 -type d -mmin +$AGE -execdir echo "removing: "$FULLBACKUPDIR/{} \; -execdir rm -rf $FULLBACKUPDIR/{} \; -execdir echo "removing: "$INCRBACKUPDIR/{} \; -execdir rm -rf $INCRBACKUPDIR/{} \;
echo
echo "completed: `date`"
exit 0

Monday, 7 April 2014

Percona innobackupEx step by step


Percona innobackupEx step by step

Backup from 192.168.1.193 and restore to 192.168.1.144

Step 1: prepare data on 192.168.1.193

[root@CENTOS4 local]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.6.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use dbatest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t;
Empty set (0.00 sec)
mysql> insert into t select 1,'aaa';
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> exit

Step2: Do a fullbackup

[root@CENTOS4 local]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123123 --socket=/tmp/mysql.sock /usr/local/mysqlbackup
.....
innobackupex: Backup created in directory '/usr/local/mysqlbackup/2014-04-07_17-11-10'
innobackupex: MySQL binlog position: filename 'CENTOS4-bin.000001', position 3769
140407 17:11:20  innobackupex: Connection to database server closed
140407 17:11:20  innobackupex: completed OK!
[root@CENTOS4 local]#



Step3: prepare data for diff backup point 1

 [root@CENTOS4 local]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.6.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use dbatest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t;
+------+-------+
| idx  | cname |
+------+-------+
|    1 | aaa   |
+------+-------+
1 row in set (0.00 sec)
mysql> insert into t select 2,'bbb';
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from t;
+------+-------+
| idx  | cname |
+------+-------+
|    1 | aaa   |
|    2 | bbb   |
+------+-------+
2 rows in set (0.00 sec)
mysql> exit
Bye
[root@CENTOS4 local]# ls -la /usr/local/mysqlbackup
total 12
drwxr-xr-x.  3 root root 4096 Apr  7 17:11 .
drwxr-xr-x. 20 root root 4096 Apr  7 17:11 ..
drwxr-xr-x.  7 root root 4096 Apr  7 17:11 2014-04-07_17-11-10

 

Step4: incremental backup at point 1

[root@CENTOS4 local]# innobackupex --incremental /usr/local/mysqlbackup --incremental-basedir=/usr/local/mysqlbackup/2014-04-07_17-11-10 --user=root --password=123123 --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock
........
innobackupex: Backup created in directory '/usr/local/mysqlbackup/2014-04-07_17-17-04'
innobackupex: MySQL binlog position: filename 'CENTOS4-bin.000001', position 3993
140407 17:17:12  innobackupex: Connection to database server closed
140407 17:17:12  innobackupex: completed OK!
[root@CENTOS4 local]# ls -lah /usr/local/mysqlbackup
total 16K
drwxr-xr-x.  4 root root 4.0K Apr  7 17:17 .
drwxr-xr-x. 20 root root 4.0K Apr  7 17:11 ..
drwxr-xr-x.  7 root root 4.0K Apr  7 17:11 2014-04-07_17-11-10
drwxr-xr-x.  7 root root 4.0K Apr  7 17:17 2014-04-07_17-17-04

 

Step 5: Prepare data for incremental backup point 2

[root@CENTOS4 local]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.6.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use dbatest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t;
+------+-------+
| idx  | cname |
+------+-------+
|    1 | aaa   |
|    2 | bbb   |
+------+-------+
2 rows in set (0.00 sec)
mysql> insert into t select 3,'ccc';
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from t;
+------+-------+
| idx  | cname |
+------+-------+
|    1 | aaa   |
|    2 | bbb   |
|    3 | ccc   |
+------+-------+
3 rows in set (0.00 sec)
mysql> exit
Bye

 

Step 6: incremental backup at point 2

[root@CENTOS4 local]# innobackupex --incremental /usr/local/mysqlbackup --incremental-basedir=/usr/local/mysqlbackup/2014-04-07_17-17-04 --user=root --password=123123 --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock
....................
innobackupex: Backup created in directory '/usr/local/mysqlbackup/2014-04-07_17-23-35'
innobackupex: MySQL binlog position: filename 'CENTOS4-bin.000001', position 4217
140407 17:23:44  innobackupex: Connection to database server closed
140407 17:23:44  innobackupex: completed OK!
[root@CENTOS4 local]# ls -lah /usr/local/mysqlbackup
total 20K
drwxr-xr-x.  5 root root 4.0K Apr  7 17:23 .
drwxr-xr-x. 20 root root 4.0K Apr  7 17:11 ..
drwxr-xr-x.  7 root root 4.0K Apr  7 17:11 2014-04-07_17-11-10
drwxr-xr-x.  7 root root 4.0K Apr  7 17:17 2014-04-07_17-17-04
drwxr-xr-x.  7 root root 4.0K Apr  7 17:23 2014-04-07_17-23-35

 

Step 7: zip and copy backup data to 192.168.1.144

[root@CENTOS4 local]# tar cf mysqlbackup.tar mysqlbackup
[root@CENTOS4 local]# gzip mysqlbackup.tar
[root@CENTOS4 local]# scp mysqlbackup.tar.gz root@192.168.1.144:/usr/local/
root@192.168.1.144 s password:
mysqlbackup.tar.gz                                                                                                                             100%  677KB 677.2KB/s   00:00
[root@CENTOS4 local]#

 

Step 8: unzip backup file on 192.168.1.144

[root@CENTOS2 ~]# ifconfig
eth0      Link encap:Ethernet  HWaddr 00:15:5D:01:C2:13
          inet addr:192.168.1.144  Bcast:192.168.1.255  Mask:255.255.255.0
[root@CENTOS2 ~]# cd /usr/local/
[root@CENTOS2 local]# ls -lah
total 740M
drwxr-xr-x. 17 root  root  4.0K Apr  7 17:27 .
drwxr-xr-x. 13 root  root  4.0K Aug 28  2013 ..
-rw-r--r--.  1 root  root  678K Apr  7 17:27 mysqlbackup.tar.gz
drwxr-xr-x.  2 root  root  4.0K Sep 10  2013 sbin
drwxr-xr-x.  6 root  root  4.0K Sep 16  2013 share
drwxr-xr-x.  2 root  root  4.0K Sep 23  2011 src
drwxr-xr-x.  3 root  root  4.0K Mar 28 16:37 usr
 [root@CENTOS2 local]# tar -zxvf mysqlbackup.tar.gz
...........................
[root@CENTOS2 local]# cd mysqlbackup
[root@CENTOS2 mysqlbackup]# ls -lah
total 20K
drwxr-xr-x.  5 root root 4.0K Apr  7 17:23 .
drwxr-xr-x. 18 root root 4.0K Apr  7 17:30 ..
drwxr-xr-x.  7 root root 4.0K Apr  7 17:11 2014-04-07_17-11-10
drwxr-xr-x.  7 root root 4.0K Apr  7 17:17 2014-04-07_17-17-04
drwxr-xr-x.  7 root root 4.0K Apr  7 17:23 2014-04-07_17-23-35

 

Step9: stop MySQL service and rename the MySQL data folder

root@CENTOS2 mysqlbackup]# service mysql stop;
Shutting down MySQL.. SUCCESS!
[root@CENTOS2 mysqlbackup]# cd /usr/local/mysql
[root@CENTOS2 mysql]# mv data data_bak1
[root@CENTOS2 mysql]# mkdir data

 

Step 10: apply full backup from /usr/local/mysqlbackup/2014-04-07_17-11-10

 [root@CENTOS2 mysql]# innobackupex --apply-log --redo-only /usr/local/mysqlbackup/2014-04-07_17-11-10  --user=root --password=123123
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".
............
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1694474
140407 17:34:30  innobackupex: completed OK!
[root@CENTOS2 mysql]#



 

Step 11: Applyincremental backup to point 1

 [root@CENTOS2 mysql]# innobackupex --apply-log --redo-only /usr/local/mysqlbackup/2014-04-07_17-11-10  --incremental-dir=/usr/local/mysqlbackup/2014-04-07_17-17-04 --user=root --password=123123
.......
innobackupex: Copying '/usr/local/mysqlbackup/2014-04-07_17-17-04/mysql/db.MYD' to '/usr/local/mysqlbackup/2014-04-07_17-11-10/mysql/db.MYD'
140407 17:35:29  innobackupex: completed OK!

Step 12: Apply incremental backup to point 2

[root@CENTOS2 mysql]# innobackupex --apply-log --redo-only /usr/local/mysqlbackup/2014-04-07_17-11-10  --incremental-dir=/usr/local/mysqlbackup/2014-04-07_17-23-35 --user=root --password=123123
.......................
innobackupex: Copying '/usr/local/mysqlbackup/2014-04-07_17-23-35/mysql/db.MYD' to '/usr/local/mysqlbackup/2014-04-07_17-11-10/mysql/db.MYD'
140407 17:36:51  innobackupex: completed OK!

 

Step 13:  apply full backup from /usr/local/mysqlbackup/2014-04-07_17-11-10

[root@CENTOS2 mysql]# innobackupex --apply-log /usr/local/mysqlbackup/2014-04-07_17-11-10 --user=root --password=123123
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!"
..................................
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1696800
140407 17:37:56  innobackupex: completed OK!
[root@CENTOS2 mysql]#

 

Step 14: Copy backup full backup from /usr/local/mysqlbackup/2014-04-07_17-11-10

[root@CENTOS2 mysql]# innobackupex --copy-back --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock /usr/local/mysqlbackup/2014-04-07_17-11-10
...........................
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/usr/local/mysqlbackup/2014-04-07_17-11-10'
innobackupex: back to original InnoDB log directory '/usr/local/mysql/data'
innobackupex: Copying '/usr/local/mysqlbackup/2014-04-07_17-11-10/ib_logfile1' to '/usr/local/mysql/data'
innobackupex: Copying '/usr/local/mysqlbackup/2014-04-07_17-11-10/ib_logfile0' to '/usr/local/mysql/data'
innobackupex: Finished copying back files.
140407 17:38:32  innobackupex: completed OK!

 

Step 15:  grants rights for mysql to data folder

[root@CENTOS2 mysql]# chown -R mysql .

[root@CENTOS2 mysql]# chgrp -R mysql .

[root@CENTOS2 mysql]# service mysql start

Starting MySQL... SUCCESS!

 

Step 16: Check data from new restored database

[root@CENTOS2 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbatest            |
| dbatest_1          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)
mysql> select * from dbatest.t;
+------+-------+
| idx  | cname |
+------+-------+
|    1 | aaa   |
|    2 | bbb   |
|    3 | ccc   |
+------+-------+
3 rows in set (0.00 sec)
MySQL>

Step 17: Data restored successfully!