The use of percona-xtrabackup MySQL backup and recovery software

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

One reason to use percona-xtrabackup


At the front, we talked about MySQL for cold, hot standby, mysqldump, hot copy, ensure data consistency. Because the MySQL cold standby, mysqldump, MySQL thermal copy can not achieve incremental backup, in the actual environment incremental backup is used, percona-xtrabackup is to realize incremental backup and health, therefore we need to use percona-xtrabackup.

The use of this article on the percona-xtrabackup software, an article on the percona-xtrabackup to achieve incremental backup and recovery.


Two what is the percona-xtrabackup


Percona XtraBackup is an open-source hot backup utility for MySQL -based servers that doesn't lock your database during the backup.

It can back up data from InnoDB, XtraDB,and MyISAM tableson MySQL 5.1 [1], 5.5 and5.6 servers, as well as Percona Server with XtraDB.For a high-level overview of many of its advanced features, including a featurecomparison, please see AboutPercona Xtrabackup.

Whether it is a 24x7 highly loaded server or alow-transaction-volume environment, Percona XtraBackup isdesigned to make backups a seamless procedure without disrupting theperformance of the server in a production environment.Commercial support contracts areavailable.

Percona XtraBackup is a combination of the xtrabackup C program,and the innobackupex Perl script. The xtrabackupprogramcopies and manipulates InnoDB and XtraDB datafiles, and the Perl script enables enhanced functionality,such as interacting with a running MySQL server and backing up MyISAM tables.



Three software and document acquisition


Software Acquisition



Document acquisition




Four software is used to explain

Note: the percona-xtrabackup version of 2.0.2, operating system version of RHEL 6.1 Server, MySQL version 5.1


The first step, prepare documents and copies of documents

[root@larrywen ule-mysql]# ll percona-xtrabackup-2.0.2-461.rhel6.x86_64.rpm 
-rwxr-xr-x. 1 root root 3786628 Sep 10 13:45 percona-xtrabackup-2.0.2-461.rhel6.x86_64.rpm

[root@larrywen ule-mysql]# scp percona-xtrabackup-2.0.2-461.rhel6.x86_64.rpm 192.168.1.11:/opt

The second step, the software needs to rely on the MySQL client, so the use of the yum installation. Attention, here to install only the MySQL client, and their use of source MySQL installation does not conflict

[root@serv01 opt]# yum install percona-xtrabackup-2.0.2-461.rhel6.x86_64.rpm -y
Installed:
  percona-xtrabackup.x86_64 0:2.0.2-461.rhel6                                                                                         

Dependency Installed:
  mysql.x86_64 0:5.1.52-1.el6_0.1

The third step, initialize backup

[root@serv01 databackup]# innobackupex --user=root --password=123456 /databackup/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 2009-2012.  All Rights Reserved.

...
innobackupex: Backup created in directory '/databackup/2013-09-10_21-49-44'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 7312
130910 21:50:03  innobackupex: completed OK!

The fourth step, the backup file is not available, we need to do a unified examination

[root@serv01 databackup]# ll
total 4
drwxr-xr-x. 9 root root 4096 Sep 10 21:50 2013-09-10_21-49-44

#Do the unified examination
[root@serv01 databackup]# innobackupex --apply-log /databackup/2013-09-10_21-49-44/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 2009-2012.  All Rights Reserved.
...
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
130910 21:51:52  InnoDB: Starting shutdown...
130910 21:51:56  InnoDB: Shutdown completed; log sequence number 2098188
130910 21:51:56  innobackupex: completed OK!

The fifth step, the simulation data loss

[root@serv01 databackup]# rm -rf /usr/local/mysql/data/*
[root@serv01 databackup]# ll /usr/local/mysql/data/
total 0

The sixth step, data recovery

[root@serv01 databackup]# innobackupex --copy-back /databackup/2013-09-10_21-49-44/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 2009-2012.  All Rights Reserved.
...
innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/databackup/2013-09-10_21-49-44'
innobackupex: back to original InnoDB data directory '/usr/local/mysql/data'
innobackupex: Copying file '/databackup/2013-09-10_21-49-44/ibdata1'

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/databackup/2013-09-10_21-49-44'
innobackupex: back to original InnoDB log directory '/usr/local/mysql/data'
innobackupex: Finished copying back files.

130910 22:02:29  innobackupex: completed OK!

The seventh step, restart the MySQL service discovery, error, pkill out, and then start all normal

[root@serv01 databackup]# /etc/init.d/mysqld restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL. ERROR! The server quit without updating PID file (/usr/local/mysql/data/serv01.host.com.pid).

#See recovery data directory, the owner and the group is not a MySQL user, we change the owner and owning group
[root@serv01 databackup]# ll /usr/local/mysql/data/
total 18468
drwxr-xr-x. 2 root  root      4096 Sep 10 22:02 game
drwxr-xr-x. 2 root  root      4096 Sep 10 22:02 hello
-rw-r-----. 1 root  root  18874368 Sep 10 21:51 ibdata1
drwxr-xr-x. 2 root  root      4096 Sep 10 22:02 larrydb
drwxr-xr-x. 2 root  root      4096 Sep 10 22:02 mnt
drwxr-xr-x. 2 root  root      4096 Sep 10 22:02 mysql
-rw-rw----. 1 mysql mysql        0 Sep 10 22:02 mysql-bin.index
drwxr-xr-x. 2 root  root      4096 Sep 10 22:02 performance_schema
-rw-r-----. 1 mysql root       771 Sep 10 22:02 serv01.host.com.err
drwxr-xr-x. 2 root  root      4096 Sep 10 22:02 test
-rw-r--r--. 1 root  root        24 Sep 10 22:02 xtrabackup_binlog_pos_innodb

[root@serv01 databackup]# chown mysql.mysql /usr/local/mysql/data/ -R

#Start again, still failed, we kill the process, start MySQL again, normal
[root@serv01 databackup]# /etc/init.d/mysqld restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL.. ERROR! The server quit without updating PID file (/usr/local/mysql/data/serv01.host.com.pid).

[root@serv01 databackup]# ps -ef | grep mysql
root      2386     1  0 18:36 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/serv01.host.com.pid
mysql     2664  2386  0 18:36 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/serv01.host.com.err --pid-file=/usr/local/mysql/data/serv01.host.com.pid --socket=/tmp/mysql.sock --port=3306
root      3022  1490  0 19:39 pts/0    00:00:00 mysql -uroot -px xxxx
root      5021  3040  0 22:05 pts/2    00:00:00 grep mysql
[root@serv01 databackup]# pkill -9 mysql
[root@serv01 databackup]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS! 

[root@serv01 databackup]# mysql -uroot -p123456 
Server version: 5.5.29-log Source distribution
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| game               |
| hello              |
| larrydb            |
| mnt                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
8 rows in set (0.00 sec)

Five reference




  My mailbox: 
  Sina micro Bo: @jutdb         
  Micro message public platform: JustOracle (micro signal: justoracle)
  Database technology exchange group: 336882565 (when the additive group verification of From CSDN XXX)
  All is well
  December 1, 2013
  By Larry Wen


katoon Sina CSDN
@The Wentasy blog post is only for reference, welcome everybody to come. If there are mistakes, hope that criticism. The original blog post for reprint please indicate the source, thank you): [CSDN blog]
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Ann at December 10, 2013 - 9:43 AM