#!/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
Friday 11 April 2014
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
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]#
.....
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)
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
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
........
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)
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.
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
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> 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
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
+------+-------+
| 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
....................
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]#
[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
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
[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
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]#
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!
.......
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!
.......................
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]#
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.
...........................
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.
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)
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!
Subscribe to:
Posts (Atom)