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

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

[root@serv01 opt]# yum install percona-xtrabackup-2.0.2-461.rhel6.x86_.rpm -yInstalled: percona-xtrabackup.x86_ 0:2.0.2-461.rhel6 Dependency Installed: mysql.x86_ 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 Oyand 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 7312130910 21:50:03 innobackupex: completed OK!

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

[root@serv01 databackup]# lltotal 4drwxr-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 Oyand Percona Inc 2009-2012. All Rights Reserved.……xtrabackup: starting shutdown with innodb_fast_shutdown = 1130910 21:51:52 InnoDB: Starting shutdown...130910 21:51:56 InnoDB: Shutdown completed; log sequence number 2098188130910 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 Oyand Percona Inc 2009-2012. All Rights Reserved.……innobackupex: Starting to copy InnoDB system tablespaceinnobackupex: 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 filesinnobackupex: 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 18468drwxr-xr-x. 2 root root 4096 Sep 10 22:02 gamedrwxr-xr-x. 2 root root 4096 Sep 10 22:02 hello-rw-r-----. 1 root root 18874368 Sep 10 21:51 ibdata1drwxr-xr-x. 2 root root 4096 Sep 10 22:02 larrydbdrwxr-xr-x. 2 root root 4096 Sep 10 22:02 mntdrwxr-xr-x. 2 root root 4096 Sep 10 22:02 mysql-rw-rw----. 1 mysql mysql 0 Sep 10 22:02 mysql-bin.indexdrwxr-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.errdrwxr-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 mysqlroot 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.pidmysql 26 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=3306root 3022 1490 0 19:39 pts/0 00:00:00 mysql -uroot -px xxxxroot 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 startStarting MySQL.. SUCCESS! [root@serv01 databackup]# mysql -uroot -p123456 Server version: 5.5.29-log Source distributionmysql> 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/

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