首页 > 文章 > xtrabackup备份mysql

xtrabackup备份mysql

xtrabackup的安装
安装依赖关系
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
下载软件包,并安装软件
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
创建备份帐号
grant all on *.* to [email protected]‘localhost‘ identified by ‘123456‘;
FLUSH PRIVILEGES;
创建备份目录
mkdir /data/mysqlbackup -p
备份
先进行第一次全备
innobackupex --defaults-file=/etc/my.cnf --user=smile --password=123456 --socket=/data/mysql/mysql.sock --no-timestamp /data/mysqlbackup/xfull
第一次增备
innobackupex --user=smile --password=123456 --incremental --no-timestamp --incremental-basedir=/data/mysqlbackup/xfull /data/mysqlbackup/xinc1
第二次增备
innobackupex --user=smile --password=123456 --incremental --no-timestamp --incremental-basedir=/data/mysqlbackup/xinc1 /data/mysqlbackup/xinc2
参数说明:

--incremental:开启增量备份功能

--incremental-basedir:上一次备份的路径

恢复准备
恢复前准备
  恢复数据前的准备(合并xtabackup_log_file和备份的物理文件)
将redo进行重做,已提交的写到数据文件,未提交的使用undo回滚,模拟CSR的过程
 第一步:先应用全备日志(--apply-log,在全备中apply-log时,只应用redo,不应用undo)
innobackupex --apply-log --redo-only /data/mysqlbackup/xfull/
第二步:合并增量到全备中xinc1到full中,并且apply-log,只应用redo,不应用undo
innobackupex --apply-log --redo-only --incremental-dir=/data/mysqlbackup/xinc1 /data/mysqlbackup/xfull/
第三步:合并增量到全备中xinc2到full中,redo和undo都应用
innobackupex --apply-log --incremental-dir=/data/mysqlbackup/xinc2 /data/mysqlbackup/xfull/
第四步:整体full执行apply-log,redo和undo都应用
innobackupex --apply-log /data/mysqlbackup/xfull/
使用innobackupex命令进行恢复
innobackupex --copy-back /data/mysqlbackup/xfull/
授权:chown -R mysql:mysql /data/mysql

 

启动数据库
systemctl start mysqld

 

如果启动时出现如下两种报错:

错误1: 

2019-06-10T11:42:10.068237+08:00 0 [ERROR] InnoDB: Unable to open undo tablespace ‘.//undo001‘.
2019-06-10T11:42:10.068267+08:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2019-06-10T11:42:10.668794+08:00 0 [ERROR] Plugin ‘InnoDB‘ init function returned error.
2019-06-10T11:42:10.668841+08:00 0 [ERROR] Plugin ‘InnoDB‘ registration as a STORAGE ENGINE failed.
2019-06-10T11:42:10.668848+08:00 0 [ERROR] Failed to initialize builtin plugins.
2019-06-10T11:42:10.668862+08:00 0 [ERROR] Aborting
2019-06-10T11:42:10.033459+08:00 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.26-log) starting as process

错误2:

2019-06-10T11:42:10.068237+08:00 0 [ERROR] InnoDB: Unable to open undo tablespace ‘.//undo001‘.
2019-06-10T11:42:10.068267+08:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2019-06-10T11:42:10.668794+08:00 0 [ERROR] Plugin ‘InnoDB‘ init function returned error.
2019-06-10T11:42:10.668841+08:00 0 [ERROR] Plugin ‘InnoDB‘ registration as a STORAGE ENGINE failed.
2019-06-10T11:42:10.668848+08:00 0 [ERROR] Failed to initialize builtin plugins.
2019-06-10T11:42:10.668862+08:00 0 [ERROR] Aborting

 

到数据目录下删除redo undo日志 
rm -f ib_*
rm -f ibdata1 ibtmp1
如果不想这样可以在my.cnf中加入如下配置,但加入需要重新初始化:
innodb_max_undo_log_size = 128M
innodb_undo_log_truncate = ON
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_purge_rseg_truncate_frequency = 10
时间:2019-06-13 00:17:14阅读(91)
联系我们 - 留言反馈
© 2017 版权所有 鲁ICP备17052893号