使用innobackupex、xtrabackup恢复Mysql数据库

安装xtrabackup的数据库

# yum install https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-3.noarch.rpm

开始安装xtrabackup
安装依赖包

#  yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

然后

找到xtrabackup相关的包# yum list | grep xtrabackup据，
yum安装

yum -y install percona-xtrabackup.x86_64

恢复mysql需先停止mysqld服务

# systemctl stop mysqld

将备份的数据库备份filename拷贝到本机的/home/mysql/目录

# cd /home/mysql/

# tar vizxf filename.tar.gz

安装相关依赖，否则会报错

# yum -y install perl-Digest-MD5

# yum -y install rsync

执行：

# innobackupex --defaults-file=./backup-my.cnf --apply-log ./ --ibbackup xtrabackup_56

系统显示 innobackupex: completed OK!，则数据恢复成功。

执行 ls

查看需要恢复的数据库有b2b、cgwy、cgwy_22 ,然后把这些需要恢复的数据库目录复制到/var/lib/mysql目录

# rsync -avz b2b cgwy cgwy_22 ib* /var/lib/mysql/

然后再修改b2b cgwy cgwy_22的用户与组为mysql

# chown -R mysql:mysql b2b
# chown -R mysql:mysql cgwy
# chown -R mysql:mysql cgwy_22

启动mysql，并查看数据库的表里内容

# systemctl start mysqld

# mysql -uroot -p

输入密码

# mysql> show databases;

系统显示入选

选定数据库

# mysql> use b2b;

查看所有表

# mysql> show tables;

查看表中内容

# mysql> select * from table_name;

若无误，至此，大功告成！

方法二
安装xtrabackup及拷贝mysql备份到home/mysql目录，解压mysql备份，然后删除备份压缩文件

# cd /home/mysql/
# tar zxvf hins1300235_data_20161103031518.tar.gz
# rm -rf hins1300235_data_20161103031518.tar.gz

切换本机mysql安装目录 /var/lib/mysql

# cd  /var/lib/

备份mysql及删除

# cp -a mysql/ mysql.bak

# cd mysql 
# rm -rf * 

恢复文件分两步
第1步是apply-log，为了加快速度，一般建议设置–use-memory，这个步骤完成之后，目录/mysql下的备份文件已经准备就绪。因为我们的文件比较少,因此我们不需要设置–user-memory产数文件 ；第2步是copy-back，即把备份文件拷贝至原数据目录下：

# cd mysql
# innobackupex --defaults-file=/etc/my.cnf --user=root  --apply-log /home/mysql --ibbackup xtrabackup_56
# innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /home/mysql --ibbackup xtrabackup_56

修改权限 启动服务

# cd ..
# chown mysql:mysql /var/lib/mysql/ -R
# systemctl start mysqld

登陆查看，同方法一
如果登陆，输入密码 提示：

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

则需要重置user密码：

停止服务：

# systemctl stop mysqld
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

此时会莫名其妙的停顿在下面界面

[root@localhost ~]# 161106 07:03:25 mysqld_safe Logging to '/var/log/mysqld.log'.
161106 07:03:25 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

按ctrl+z中断即可
在输入mysql,回车 登陆

[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql>use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user from user;
+----------------+------------+
| host           | user       |
+----------------+------------+
| %              | aurora     |
| %              | b2b        |
| 127.0.0.1      | root       |
+----------------+------------+
3 rows in set (0.01 sec)
mysql>UPDATE user SET Password = password ( '123456' ) WHERE User = 'b2b' ;
mysql> flush privileges ; 
Query OK, 0 rows affected (0.01 sec) 
mysql> quit

可见user有b2b 和 root ，其中b2b可以在任何主机访问，root只限本地登陆，
所以，上面我重置了b2b密码为：123456

重新登陆：

# mysql -ub2b -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| b2b                |
| cgwy               |
| cgwy_22            |
| mysql              |
| performance_schema |
| report             |
+--------------------+
7 rows in set (0.01 sec)

一切搞定！