使用xtrabackup备份mysql数据库
⦁ 下载软件包
https://www.percona.com/downloads/XtraBackup/
选择软件版本和操作系统,下载rpm包
Percona-XtraBackup-2.4.5-re41c0be-el6-x86_64-bundle
⦁ 上传rpm包到服务器,解压安装
tar -xvf Percona-XtraBackup-2.4.5-re41c0be-el6-x86_64-bundle.tar
[root@india-dba2-test xtrabackup]# ls -l
total 127720
-rw-rw-r--. 1 root root 8277540 Nov 25 18:28 percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm
-rw-r--r--. 1 root root 65392640 Feb 15 08:49 Percona-XtraBackup-2.4.5-re41c0be-el6-x86_64-bundle.tar
-rw-rw-r--. 1 root root 41917404 Nov 25 18:28 percona-xtrabackup-24-debuginfo-2.4.5-1.el6.x86_64.rpm
-rw-rw-r--. 1 root root 15193040 Nov 25 18:28 percona-xtrabackup-test-24-2.4.5-1.el6.x86_64.rpm
根据文档,只需要安装percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm即可
[root@india-dba2-test xtrabackup]# rpm -ivh percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm
warning: percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
libev.so.4()(64bit) is needed by percona-xtrabackup-24-2.4.5-1.el6.x86_64
libz.so.1(ZLIB_1.2.0)(64bit) is needed by percona-xtrabackup-24-2.4.5-1.el6.x86_64
1)缺少libev.so
根据网上的资料http://www.cnblogs.com/kerrycode/p/5308719.html
到http://rpmfind.net/linux/rpm2html/search.php?query=libev&submit=Search+…&system=red&arch=
搜索libev 根据对应操作系统下载rpm包安装
[root@india-dba2-test xtrabackup]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
warning: libev-4.15-1.el6.rf.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY
Preparing... ########################################### [100%]
1:libev ##########
################################# [100%]
2)缺少libz.so.1(ZLIB_1.2.0)(64bit)
经测试,到http://rpmfind.net/linux/rpm2html/search.php?query=zlib&submit=Search+…&system=&arch=下载最新的zlib-1.2.3-29.el6.x86_64.rpm强制安装即可
[root@india-dba2-test xtrabackup]# rpm -ivh zlib-1.2.3-29.el6.x86_64.rpm --nodeps --force
Preparing... ########################################### [100%]
1:zlib ########################################### [100%]
再安装xtrabackup软件成功
root@india-dba2-test xtrabackup]# rpm -ivh percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm
warning: percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ########################################### [100%]
1:percona-xtrabackup-24 ########################################### [100%]
3、使用xtrabackup进行备份
建立一个备份目录 /home/mysql_backup
然后备份到该目录
全库备份命令:
innobackupex --user=root --password=BBK-admin123! /home/mysql_backup/
备份指定的数据库,多个数据库使用—databases参数,用空格分开,其他参数可用
innobackupex –help查看帮助
innobackupex --databases=“exam_test exam_test2” --user=root --password=BBK-admin123! /home/mysql_backup/
4、还原
删除exam_test 中的某几个表
全库还原步骤
Service mysqld stop
删除datadir里的所有文件,测试时为/var/lib/mysql
Cd /var/lib/mysql
Rm –rf *
依次执行
innobackupex --user=root --password=BBK-admin123! --apply-log /home/mysql_backup/2017-02-15_09-33-32/
innobackupex --user=root --password=BBK-admin123! --copy-back /home/mysql_backup/2017-02-15_09-33-32/
Cd /var/lib/mysql
Chown –R mysql:mysql *
Service mysqld start
验证数据是否还原
Mysql如果要开启部分数据库还原,需要设置参数