xtrabackup在线备份及还原

dbcloud 2021-09-01 原文

xtrabackup下载
https://www.percona.com/downloads/XtraBackup/LATEST/
xtrabackup文档
https://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/innobackupex_script.html

安装依赖
yum -y install libaio perl-Time-HiRes perl-DBD-MySQL perl-IO-Socket-SSL

Error: Package: percona-xtrabackup-24-2.4.5-1.el6.x86_64 (/percona-xtrabackup-24-2.4.5-1.el6.x86_64)
Requires: libev.so.4()(64bit)

解决缺少libev包问题
wget http://mirrors.neusoft.edu.cn/epel/6/x86_64/libev-4.03-3.el6.x86_64.rpm
rpm -ivh libev-4.03-3.el6.x86_64.rpm

安装xtrabackup
rpm -ivh /data/percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm

数据库用户授权
mysql> CREATE USER \’bkpuser\’@\’localhost\’ IDENTIFIED BY \’s3cret\’;
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,Process ON *.* TO \’bkpuser\’@\’localhost\’;
mysql> FLUSH PRIVILEGES;

完整备份

innobackupex –defaults-file=/etc/my.cnf –host=localhost –user=bkpuser –password=s3cret /data/dbbak/innobackupex/ –no-timestamp

应用日志
innobackupex –apply-log /data/dbbak/innobackupex/ –use-memory=4G

停止数据库
/etc/init.d/mysql.server stop

数据目录重命名
mv /data/mysql /data/mysql_bak

还原数据库
innobackupex –copy-back /data/dbbak/innobackupex/

修改宿主目录权限
chown mysql:mysql /data/mysql -R

启动数据库
/etc/init.d/mysql.server start