MySQL利用xtrabackup进行增量备份的详细过程

本篇内容主要讲解“MySQL利用xtrabackup进行增量备份的详细过程”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL利用xtrabackup进行增量备份的详细过程”吧!
mysql -uroot –password=”” -e”CREATE USER ‘backup’@’192.168.%’ IDENTIFIED BY ‘123456’”;
mysql -uroot –password=”” -e”GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE, SUPERON *.* TO ‘backup’@’192.168.%'”;备份命令：
xtrabackup –defaults-file=/etc/my.cnf–user=backup –password=”123456″ –port=3306 –backup–target-dir=/data/backups/mysql/full_incre_$(date +%Y%m%d)
PS: 2>/tmp/fullbackup.log 将备份信息放入文件中。备份过程信息如下：
xtrabackup –defaults-file=/etc/my.cnf–backup –user=backup –password=”123456″ –port=3306–target-dir=/data/backups/mysql/incre_20150320/–incremental-basedir=/data/backups/mysql/full_incre_201503192>/tmp/incre_backup_1.log
查看备份日志信息：
建立一张表t_incre_2：
备份命令：
xtrabackup –defaults-file=/etc/my.cnf–backup –user=backup –password=”123456″ –po免费主机域名rt=3306–target-dir=/data/backups/mysql/incre_20150321/–incremental-basedir=/data/backups/mysql/incre_20150320 2>/tmp/incre_backup_2.log
备份信息记录如下：
备份命令如下：
xtrabackup –defaults-file=/etc/my.cnf–prepare –user=backup –password=”123456″ –apply-log-only–target-dir=/data/backups/mysql/full_incre_20150319 2>/tmp/full_restore.log
备份信息记录如下：
恢复命令：
xtrabackup –defaults-file=/etc/my.cnf–prepare –user=backup –password=”123456″ –apply-log-only–target-dir=/data/backups/mysql/full_incre_20150319–incremental-dir=/data/backups/mysql/incre_20150免费主机域名320 2>/tmp/restore_incre_1.log
恢复过程：
恢复命令：
xtrabackup –defaults-file=/etc/my.cnf–prepare –user=backup –password=”123456″ –apply-log-only –target-dir=/data/backups/mysql/full_incre_20150319–incremental-dir=/data/backups/mysql/incre_201503212>/tmp/restore_incre_2.log
恢复过程：
恢复命令：
xtrabackup –defaults-file=/etc/my.cnf–prepare –user=backup –password=”123456″–target-dir=/data/backups/mysql/full_incre_20150319 2>/tmp/restore_end_3.log
停止mysql服务：
service mysqld5612 stop
开始rsync数据文件：
cd /data/backups/mysql/full_incre_20150319
rsync -rvt –exclude’xtrabackup_checkpoints’ –exclude ‘xtrabackup_logfile’ ./ /home/data/mysql/data
授予mysql访问权限：
chown -R mysql:mysql /home/data/mysql/data
启动mysql服务：
service mysqld5612 start
登录mysql，看到以前在备份之后删除的t4以及t_incre_2表的数据已经通过2次增量备份恢复过来了，如下所示：
命令如下：
innobackupex –defaults-file=/etc/my.cnf–user=backup –password=”123456″ –host=192.168.52.129/data/backups/mysql/innobackupex_full_20150319/ –no-timestamp 2>/tmp/innobackupex_full.log
备份过程信息：
先录入增量数据
再进行增量备份，命令如下：
innobackupex –incremental/data/backups/mysql/innobackex_incre_1–incremental-basedir=/data/backups/mysql/innobackupex_full_20150319/–user=backup–password=”123456″ –host=192.168.52.129 –no-timestamp2>/tmp/innobackex_incre_1.log
备份过程信息如下：
先录入增量数据录入
开始进行第二次增量备份，备份命令：
innobackupex –incremental/data/backups/mysql/innobackex_incre_2 –incremental-basedir=/data/backups/mysql/innobackex_incre_1/–user=backup–password=”123456″ –host=192.168.52.129 –no-timestamp2>/tmp/innobackex_incre_2.log
备份过程信息如下：
备份命令如下：
innobackupex –user=backup–password=”123456″ –host=192.168.52.129 –apply-log/data/backups/mysql/innobackupex_full_20150319/2>/tmp/innobackex_restore_full.log
备份过程信息如下：
备份命令：
innobackupex –defaults-file=/etc/my.cnf–user=backup –password=”123456″ –host=192.168.52.129/data/backups/mysql/innobackupex_full_20150319/–incremental-dir=/data/backups/mysql/innobackex_incre_1 –apply-log2>/tmp/innobackex_restore_incre_1.log
备份过程信息如下：
备份命令如下：
innobackupex –defaults-file=/etc/my.cnf–user=backup –password=”123456″ –host=192.168.52.129/data/backups/mysql/innobackupex_full_20150319/–incremental-dir=/data/backups/mysql/innobackex_incre_2 –apply-log2>/tmp/innobackex_restore_incre_2.log
备份过程信息如下：
停止数据库
service mysqld5612 stop
清空数据目录下所有文件
mkdir -p /tmp/mysqldatabak/
mv/home/data/mysql/data/* /tmp/mysqldatabak/
将恢复好的数据按照配置文件的需求拷贝到相应目录
innobackupex –defaults-file=/etc/my.cnf–user=backup –password=”123456″ –host=192.168.52.129–defaults-file=/etc/my.cnf –copy-back/data/backups/mysql/innobackupex_full_20150319/ 2>/tmp/innobackex_incre_restore_end.log
赋予mysql账号权限
chown -R mysql:mysql /home/data/mysql/data
启动mysql服务
service mysqld5612 start
PS：–copy-back过程如下：
登录mysql界面，查看表incre1和表incre2，数据已经恢复，如下所示：
到此，相信大家对“MySQL利用xtrabackup进行增量备份的详细过程”有了更深的了解，不妨来实际操作一番吧！这里是云技术网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

相关推荐: MySQL Memory存储引擎的优势及性能测试

本篇内容主要讲解“MySQL Memory存储引擎的优势及性能测试”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL Memory存储引擎的优势及性能测试”吧!测试脚本：　　测试代码：　　总结　　.Net C…