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!
No comments:
Post a Comment