mysql备份还原工具percona-xtrabackup

时间:2022-08-31 17:12:46

今天主要讲全部还原。

1、安装percona-xtrabackup报错处理
mysql备份还原工具percona-xtrabackup
[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@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、报错处理
按提示在 my.cnf的[mysqld]下加入
[mysqld] 
innodb_force_recovery = 6  (设置低于6,还是会有问题)
正常启动,但是为只读模式,只可以查询而已。
mysql备份还原工具percona-xtrabackup
mysql备份还原工具percona-xtrabackup
mysql备份还原工具percona-xtrabackup
可以正常启动了,查看日志
[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;