MySQL备份与恢复之percona-xtrabackup软件的使用

妖狐艹你老母 2022-09-18 14:46 198阅读 0赞

## **一 使用percona-xtrabackup的原因** ##

在前面，我们讲到MySQL冷备、热备、mysqldump、热拷贝、保证数据的一致性。因为mysql冷备、mysqldump、mysql热拷贝均不能实现增量备份，在实际环境中增量备份是使用较多的，percona-xtrabackup就是为实现增量备份而生，因此我们需要使用percona-xtrabackup。

本文讲解percona-xtrabackup软件的使用，下一篇文章讲解[percona-xtrabackup实现增量备份及恢复][percona-xtrabackup]。

## **二 什么是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\]][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 **xtrabackup**programcopies 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.

## **三 软件及文档获取** ##

软件获取

[http://www.percona.com/software/percona-xtrabackup/downloads][http_www.percona.com_software_percona-xtrabackup_downloads]

[http://download.csdn.net/detail/wentasy/6638171][http_download.csdn.net_detail_wentasy_6638171]

文档获取

[http://www.percona.com/doc/percona-xtrabackup/2.1/][http_www.percona.com_doc_percona-xtrabackup_2.1]

[http://download.csdn.net/detail/wentasy/6638029][http_download.csdn.net_detail_wentasy_6638029]

## **四 软件使用讲解** ##

** **

注：本文采用的percona-xtrabackup版本为2.0.2，操作系统版本为RHEL 6.1 Server，MySQL版本为5.1

第一步，准备文件并拷贝文件

>     [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

第二步，该软件需要依赖MySQL客户端，所以使用yum安装。注意，此处安装的只是MySQL的客户端，和本身使用源码安装的MySQL不冲突

>     [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

第三步，初始化备份

>     [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!

第四步，这样的备份文件无法使用，我们需要做统一检查

>     [root@serv01 databackup]# ll
>     total 4
>     drwxr-xr-x. 9 root root 4096 Sep 10 21:50 2013-09-10_21-49-44
>     
>     #做统一检查
>     [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!

第五步，模拟数据丢失

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

第六步，恢复数据

>     [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!

第七步，重启mysql服务，发现报错，pkill掉，然后启动一切正常

>     [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).
>     
>     #查看恢复的数据目录，拥有者和所属组不是mysql用户，我们更改拥有者和所属组
>     [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
>     
>     #再次启动，仍然失败，我们杀掉进程，再次启动mysql，正常
>     [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)

## **五 参考资料** ##

[http://www.percona.com/doc/percona-xtrabackup/2.1/][http_www.percona.com_doc_percona-xtrabackup_2.1]

>     我的邮箱：wgbno27@163.com   新浪微博：@jutdb         
>       微信公众平台：JustOracle（微信号：justoracle）
>       数据库技术交流群：336882565（加群时验证 From CSDN XXX）
>       All is well
>       2013年12月1日
>       By Larry Wen

<table style="border-bottom:#48c5db 5px double; border-left:#48c5db 5px double; padding-bottom:5px; background-color:black; padding-left:5px; border-spacing:0; width:100%; padding-right:30px; height:90px; border-top:#48c5db 5px double; border-right:#48c5db 5px double; padding-top:5px"> 
 <tbody> 
  <tr> 
   <td><img alt="katoon" src="https://img-my.csdn.net/uploads/201301/06/1357456262_5409.jpg" style="height:80px"></td> 
   <td style="text-align:right"><a title="新浪微博求粉 ^_^" href="http://weibo.com/wentasy" rel="nofollow"><img title="新浪微博求粉 ^_^" alt="Sina" src="https://img-my.csdn.net/uploads/201210/26/1351182273_5476.png.thumb.jpg" style="height:40px"></a>&nbsp;<a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow"><img title="我的CSDN博客——HelloWorld" alt="CSDN" src="https://img-my.csdn.net/uploads/201210/26/1351182268_8254.png.thumb.jpg" style="height:40px"></a></td> 
  </tr> 
  <tr> 
   <td style="text-align:right; font-family:microsoft yahei; color:white; font-size:12px"> <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow">@Wentasy</a> 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]</td> 
  </tr> 
 </tbody> 
</table>

[percona-xtrabackup]: http://blog.csdn.net/justdb/article/details/17054667
[InnoDB]: http://www.percona.com/doc/percona-xtrabackup/2.1/glossary.html#term-innodb
[XtraDB]: http://www.percona.com/doc/percona-xtrabackup/2.1/glossary.html#term-xtradb
[MyISAM]: http://www.percona.com/doc/percona-xtrabackup/2.1/glossary.html#term-myisam
[1]: http://www.percona.com/doc/percona-xtrabackup/2.1/#n-1
[AboutPercona Xtrabackup]: http://www.percona.com/doc/percona-xtrabackup/2.1/intro.html
[Commercial support contracts areavailable]: http://www.percona.com/mysql-support/
[http_www.percona.com_software_percona-xtrabackup_downloads]: http://www.percona.com/software/percona-xtrabackup/downloads
[http_download.csdn.net_detail_wentasy_6638171]: http://download.csdn.net/detail/wentasy/6638171
[http_www.percona.com_doc_percona-xtrabackup_2.1]: http://www.percona.com/doc/percona-xtrabackup/2.1/
[http_download.csdn.net_detail_wentasy_6638029]: http://download.csdn.net/detail/wentasy/6638029