mysql备份还原工具percona-xtrabackup

今天主要讲全部还原。

1、安装percona-xtrabackup报错处理

参考： http://blog.csdn.net/evils798/article/details/51433073

           https://my.oschina.net/u/1433006/blog/528262

[root@www-1 ~]# wget  ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm 

[root@www-1 ~]# wget  http://mirror.centos.org/centos/6/os/x86_64/Packages/numactl-2.0.9-2.el6.x86_64.rpm

[root@test1 ~]# wget https://www.percona.com/downloads/XtraBackup/XtraBackup-2.1.5/RPM/rhel6/x86_64/percona-xtrabackup-2.1.5-680.rhel6.x86_64.rpm

[root@www-1 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm 

[root@www-1 ~]# rpm -ivh numactl-2.0.9-2.el6.x86_64.rpm

Preparing...                ########################################### [100%]

   1:numactl                ########################################### [100%]

[root@www-1 ~]# rpm -ivh percona-xtrabackup-24-2.4.1-1.el6.x86_64.rpm

warning: percona-xtrabackup-24-2.4.1-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

Preparing...                ########################################### [100%]

   1:percona-xtrabackup-24  ########################################### [100%]

 

注意：percona-xtrabackup-24版本MySQL5.7以上

          percona-xtrabackup-2.1.5适合MySQL5.6

 

2、实战演练

192.168.1.242线上主库备份数据，拷贝到测试环境192.168.3.86使用

a、将全备的数据拷贝至3,86

b、3.86安装MySQL5.6.15，并正常启动

c、安装备份工具percona-xtrabackup-2.1.5版本

d、删除所有数据库，导入数据，重启数据库

 

3、报错处理

MySQL出现Error: page 1 log sequence number解决： http://www.chengyongxu.com/blog/mysql%E5%87%BA%E7%8E%B0error-page-1-log-sequence-number%E8%A7%A3%E5%86%B3/

按提示在 my.cnf的[mysqld]下加入

[mysqld] 

innodb_force_recovery = 6  （设置低于6，还是会有问题）

正常启动，但是为只读模式，只可以查询而已。

可以正常启动了，查看日志

[root@test1 ~]# tail -f /var/log/mysqld.log   （当缓存中没有数据，需要从磁盘获取）

2017-07-19 11:13:33 10824 [ERROR] InnoDB: Failed to find tablespace for table '"pms"."accountkpisheet"' in the cache. Attempting to load the tablespace with space id 10.

2017-07-19 11:13:48 10824 [ERROR] InnoDB: Failed to find tablespace for table '"pms"."adjustwishproductsschedule"' in the cache. Attempting to load the tablespace with space id 16.

2017-07-19 11:13:50 10824 [ERROR] InnoDB: Failed to find tablespace for table '"pms"."aliexpressaccount"' in the cache. Attempting to load the tablespace with space id 3451350.

2017-07-19 11:13:50 10824 [ERROR] InnoDB: Failed to find tablespace for table '"pms"."tr_aliaccount_alicategory"' in the cache. Attempting to load the tablespace with space id 389786.

2017-07-19 11:13:51 10824 [ERROR] InnoDB: Failed to find tablespace for table '"pms"."aliexpresscategory"' in the cache. Attempting to load the tablespace with space id 617645.

 

4、还原步骤

[root@kaifa_87 home]# innobackupex -user=root -password='!QAZxsw2' --apply-log /home/2017-08-06/2017-08-06_03-33-34/

[root@kaifa_87 home]# rm -fr /home/mysql/*

[root@kaifa_87 home]# innobackupex --copy-back /home/2017-08-06/2017-08-06_03-33-34/

[root@kaifa_87 home]# chown -R mysql.mysql /home/mysql/

[root@kaifa_87 home]# kill -9 32217 31619

[root@kaifa_87 home]# ps aux |grep mysql/

root 2586 0.0 0.0 103264 916 pts/0 S+ 15:10 0:00 grep mysql/

[root@kaifa_87 home]# /etc/init.d/mysqld restart

ERROR! MySQL server PID file could not be found!

Starting MySQL.............. SUCCESS

 

5、设置权限

[root@kaifa_87 home]# mysql -uroot -p'123456'

mysql> grant select,insert,update,delete on *.* to 'root'@'192.168.%.%' Identified by "!QAZxsw2";

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

客户端连接测试：

[root@localhost classes]# mysql -h192.168.3.87 -uroot -p'123456'

Welcome to the MySQL monitor. Commands end with ; or \g.

Server version: 5.6.15-log Source distribution

mysql> show databases;