`
Donald_Draper
  • 浏览: 981023 次
社区版块
存档分类
最新评论

Mysql 备份工具XtraBackup增量备份

阅读更多
安装:http://donald-draper.iteye.com/admin/blogs/2314832
全量备份:http://donald-draper.iteye.com/admin/blogs/2315812
准备:
创建表
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
INSERT INTO `test`.`role` (`id`, `name`, `age`) VALUES ('1', 'jack', '23');
用到的命令顺序
增量备份:
innobackupex --user=donald --password=123456  /home/test/base --no-timestamp --parallel=4

innobackupex --user=donald --password=123456  --incremental /home/test/base1 --incremental-basedir=/home/test/base --no-timestamp --parallel=4

innobackupex --user=donald --password=123456  --incremental /home/test/base2 --incremental-basedir=/home/test/base1 --no-timestamp --parallel=4
备份恢复:
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base1/
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base2/
#Once you merge the base with all the increments, you can prepare it to roll back the uncommitted transactions,回滚未提交的事物
innobackupex --apply-log --use-memory=100M /home/test/base
准备恢复:
innobackupex --copy-back --rsync /home/test/base
下面是实验:
修改mysql数据目录权限:

[donald@test home]$ ls -al
total 16
drwxr-xr-x.  8 root   root    96 Aug  5 16:21 .
dr-xr-xr-x. 17 root   root  4096 Jul 26 14:32 ..
drwx------. 18 donald user  4096 Aug  5 11:38 donald=
drwxr-xr-x   5 mysql  mysql 4096 Aug  5 16:27 mysqldata
drwxr-xr-x   5 mysql  mysql  119 Jul 26 15:51 mysqldata1
drwxrwxrwx.  5 mysql  mysql 4096 Aug  5 16:19 mysqldata2
drwxrwxr-x.  2 mysql  mysql   64 Jul 26 16:05 mysqllog
drwxr-x---   4 donald user    30 Aug  5 15:00 test
[donald@test home]$ sudo chmod -R 777 mysqldata
[sudo] password for donald:
[donald@test home]$ ls -al
total 16
drwxr-xr-x.  8 root   root    96 Aug  5 16:21 .
dr-xr-xr-x. 17 root   root  4096 Jul 26 14:32 ..
drwx------. 18 donald user  4096 Aug  5 11:38 donald=
drwxrwxrwx   5 mysql  mysql 4096 Aug  5 16:27 mysqldata
drwxr-xr-x   5 mysql  mysql  119 Jul 26 15:51 mysqldata1
drwxrwxrwx.  5 mysql  mysql 4096 Aug  5 16:19 mysqldata2
drwxrwxr-x.  2 mysql  mysql   64 Jul 26 16:05 mysqllog
drwxr-x---   4 donald user    30 Aug  5 15:00 test

全量备份:
[donald@test test]$ innobackupex --user=donald --password=123456  /home/test/base --no-timestamp --parallel=4
....
xtrabackup: Creating suspend file '/home/test/base/xtrabackup_log_copied' with pid '15191'
xtrabackup: Transaction log of lsn (13194631350) to (13194631350) was copied.
160805 16:59:19  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/home/test/base'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 120
160805 16:59:19  innobackupex: Connection to database server closed
160805 16:59:19  innobackupex: completed OK!
[donald@test test]$ cd base
[donald@test base]$ ls
backup-my.cnf  ibdata1  mysql  performance_schema  test  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile
[donald@test base]$ cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 13194631350
last_lsn = 13194631350
compact = 0

增量备份1:

插入数据
INSERT INTO `test`.`role` (`id`, `name`, `age`) VALUES ('2', 'mark', '24');
[donald@test base]$ innobackupex --user=donald --password=123456  --incremental /home/test/base1 --incremental-basedir=/home/test/base --no-timestamp --parallel=4
...
xtrabackup: The latest check point (for incremental): '13194631350'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (13194631350)

xtrabackup: Creating suspend file '/home/test/base1/xtrabackup_log_copied' with pid '15639'
xtrabackup: Transaction log of lsn (13194631350) to (13194631350) was copied.
160805 17:03:55  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/home/test/base1'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 120
160805 17:03:55  innobackupex: Connection to database server closed
160805 17:03:55  innobackupex: completed OK!
[donald@test base]$ cd ..
[donald@test test]$ cd base1
[donald@test base1]$ ls
backup-my.cnf  ibdata1.meta  performance_schema  xtrabackup_binlog_info  xtrabackup_info
ibdata1.delta  mysql         test                xtrabackup_checkpoints  xtrabackup_logfile
[donald@test base1]$ cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 13194631350
to_lsn = 13194631350
last_lsn = 13194631350
compact = 0

增量备份2:
插入数据
INSERT INTO `test`.`role` (`id`, `name`, `age`) VALUES ('3', 'doanld', '25');
[donald@test test]$ innobackupex --user=donald --password=123456  --incremental /home/test/base2 --incremental-basedir=/home/test/base1 --no-timestamp --parallel=4
...
xtrabackup: The latest check point (for incremental): '13194634030'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (13194634030)

xtrabackup: Creating suspend file '/home/test/base2/xtrabackup_log_copied' with pid '15848'
xtrabackup: Transaction log of lsn (13194634030) to (13194634030) was copied.
160805 17:07:54  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/home/test/base2'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 624
160805 17:07:54  innobackupex: Connection to database server closed
160805 17:07:54  innobackupex: completed OK!
[donald@test test]$ cd base2
[donald@test base2]$ ls
backup-my.cnf  ibdata1.meta  performance_schema  xtrabackup_binlog_info  xtrabackup_info
ibdata1.delta  mysql         test                xtrabackup_checkpoints  xtrabackup_logfile
[donald@test base2]$ cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 13194631350
to_lsn = 13194634030
last_lsn = 13194634030
compact = 0

备份恢复:
[donald@test test]$ innobackupex --apply-log --redo-only --use-memory=100M /home/test/base

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
.....
[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 1371, file name mysql-bin.000019

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13194634923
160805 17:16:37  innobackupex: completed OK!

增量恢复1:
[donald@test Desktop]$ innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base1/
...
innobackupex: Copying '/home/test/base1/performance_schema/session_connect_attrs.frm' to '/home/test/base/performance_schema/session_connect_attrs.frm'
innobackupex: Copying '/home/test/base1/performance_schema/session_account_connect_attrs.frm' to '/home/test/base/performance_schema/session_account_connect_attrs.frm'
160805 17:44:29  innobackupex: completed OK!

增量恢复2:
[donald@test Desktop]$ innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base2/
...
innobackupex: Copying '/home/test/base2/performance_schema/events_statements_summary_by_digest.frm' to '/home/test/base/performance_schema/events_statements_summary_by_digest.frm'
innobackupex: Copying '/home/test/base2/performance_schema/session_connect_attrs.frm' to '/home/test/base/performance_schema/session_connect_attrs.frm'
innobackupex: Copying '/home/test/base2/performance_schema/session_account_connect_attrs.frm' to '/home/test/base/performance_schema/session_account_connect_attrs.frm'
160805 17:46:40  innobackupex: completed OK!

回滚事物
[donald@test Desktop]$ innobackupex --apply-log --use-memory=100M /home/test/base
...
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13194640304
160805 17:48:19  innobackupex: completed OK!

转移mysql数据文件:
在执行此步前删除role表中的数据
[donald@test Desktop]$ cd /home/
[donald@test home]$ ls
donald=  mysqldata  mysqldata1  mysqldata2  mysqllog  test
[donald@test home]$ sudo service msyql stop
[sudo] password for donald:
Redirecting to /bin/systemctl stop  msyql.service
Failed to stop msyql.service: Unit msyql.service not loaded.
[donald@test home]$ sudo service mysql stop
Shutting down MySQL.... SUCCESS!
[donald@test home]$ mv mysqldata mysqldata3
mv: cannot move ‘mysqldata’ to ‘mysqldata3’: Permission denied
[donald@test home]$ sudo mv mysqldata mysqldata3
[donald@test home]$ ls -al
total 16
drwxr-xr-x.  8 root   root    97 Aug  5 17:50 .
dr-xr-xr-x. 17 root   root  4096 Jul 26 14:32 ..
drwx------. 18 donald user  4096 Aug  5 11:38 donald=
drwxr-xr-x   5 mysql  mysql  119 Jul 26 15:51 mysqldata1
drwxrwxrwx.  5 mysql  mysql 4096 Aug  5 16:19 mysqldata2
drwxrwxrwx   5 mysql  mysql 4096 Aug  5 17:50 mysqldata3
drwxrwxr-x.  2 mysql  mysql   64 Jul 26 16:05 mysqllog
drwxr-x---   6 donald
增量全备恢复:
[donald@test home]$ sudo innobackupex --copy-back --rsync /home/test/base

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.
........
innobackupex: back to '/home/mysqldata'

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/home/test/base'
innobackupex: back to original InnoDB log directory '/home/mysqldata'
innobackupex: Copying '/home/test/base/ib_logfile1' to '/home/mysqldata/ib_logfile1'
innobackupex: Copying '/home/test/base/ib_logfile0' to '/home/mysqldata/ib_logfile0'
innobackupex: Finished copying back files.

160805 17:53:11  innobackupex: completed OK!

恢复mysql数据文件夹权限:
[donald@test home]$ ls -al
total 24
drwxr-xr-x.  9 root   root  4096 Aug  5 17:52 .
dr-xr-xr-x. 17 root   root  4096 Jul 26 14:32 ..
drwx------. 18 donald user  4096 Aug  5 11:38 donald=
drwxr-xr-x   5 root   root  4096 Aug  5 17:53 mysqldata
drwxr-xr-x   5 mysql  mysql  119 Jul 26 15:51 mysqldata1
drwxrwxrwx.  5 mysql  mysql 4096 Aug  5 16:19 mysqldata2
drwxrwxrwx   5 mysql  mysql 4096 Aug  5 17:50 mysqldata3
drwxrwxr-x.  2 mysql  mysql   64 Jul 26 16:05 mysqllog
drwxr-x---   6 donald user    54 Aug  5 17:07 test
[donald@test home]$ sudo chown -R mysql:mysql mysqldata
[donald@test home]$ ls -al
total 24
drwxr-xr-x.  9 root   root  4096 Aug  5 17:52 .
dr-xr-xr-x. 17 root   root  4096 Jul 26 14:32 ..
drwx------. 18 donald user  4096 Aug  5 11:38 donald=
drwxr-xr-x   5 mysql  mysql 4096 Aug  5 17:53 mysqldata
drwxr-xr-x   5 mysql  mysql  119 Jul 26 15:51 mysqldata1
drwxrwxrwx.  5 mysql  mysql 4096 Aug  5 16:19 mysqldata2
drwxrwxrwx   5 mysql  mysql 4096 Aug  5 17:50 mysqldata3
drwxrwxr-x.  2 mysql  mysql   64 Jul 26 16:05 mysqllog
drwxr-x---   6 donald user    54 Aug  5 17:07 test
[donald@test home]$ sudo service  mysql start
Starting MySQL.. SUCCESS!
查看数据库数据:
mysql> use test;
Database changed
mysql> select * from role;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | jack   |  23 |
|  2 | mark   |  24 |
|  3 | doanld |  25 |
+----+--------+-----+
3 rows in set
总结:
注意msyql文件夹权限
注意备份恢复的第一步 the procedure is very straight-forward using the --redo-only option, starting with the base backup:添加 --redo-only选项
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base
别忘了倒数第二步:
#Once you merge the base with all the increments, you can prepare it to roll back the uncommitted transactions,回滚未提交的事物
innobackupex --apply-log --use-memory=100M /home/test/base

分享到:
评论

相关推荐

    MySQL 备份工具Xtrabackup.docx

    总的来说,Xtrabackup作为一款强大的MySQL备份工具,以其高效的备份和恢复能力,以及对InnoDB引擎的优化,成为了许多企业和开发者的选择。通过合理配置和使用Xtrabackup,可以实现高效、安全的数据库备份,确保在...

    实时增量备份 、 XtraBackup 备份 、 MySQL 主从同步 、 总结和答疑

    MySQL 实时增量备份和 XtraBackup 备份技术详解 MySQL 是目前最流行的开源关系数据库管理系统之一,对于 MySQL 的备份和恢复机制至关重要。本文将详细介绍 MySQL 实时增量备份和 XtraBackup 备份技术,并结合实际...

    基于xtrabackup的MySQL数据库备份及还原Shell脚本.zip

    `xtrabackup`是Percona公司开发的一款高效、无锁的MySQL和MariaDB热备份工具,它允许在不影响数据库服务的情况下进行全备和增量备份。本教程将详细介绍如何使用`xtrabackup`结合Shell脚本来实现MySQL数据库的备份与...

    percona-xtrabackup mysql备份工具

    总的来说,Percona XtraBackup是MySQL备份解决方案的有力选择,特别是在需要高效、安全、无中断备份的业务环境中。通过熟练掌握和使用这款工具,可以有效地保护数据,提高系统的可用性和可靠性。

    mysql全备份及增量备份及还原脚本

    Percona XtraBackup是一个开源的热备份工具,可以在不锁定数据库的情况下进行增量备份。首先,你需要进行一次全备份,然后定期执行增量备份。以下是使用XtraBackup的示例: 1. 全备份: ```bash innobackupex --...

    Mysql 备份恢复与xtrabackup备份

    Xtrabackup是一个开源的物理备份工具,它能够创建数据库的增量或完整备份,并且在备份过程中不影响数据库的正常服务。它的优点在于能够进行在线备份,减少业务中断时间,同时支持快速恢复。 - **增量备份** 只备份...

    xtrabackup备份恢复shell脚本

    Xtrabackup是一款开源的、针对InnoDB存储引擎的热备份工具,它能够在不锁定数据库的情况下进行完整备份。本文将详细探讨如何利用Xtrabackup结合Shell脚本来实现高效、灵活的备份与恢复操作。 一、Xtrabackup简介 ...

    MySQL备份学习之xtrabackup软件

    Xtrabackup是一款高效、开源的MySQL热备份工具,特别适用于InnoDB和XtraDB存储引擎。在这个主题中,我们将深入探讨Xtrabackup软件在MySQL备份中的应用及其重要特性。 首先,Xtrabackup由Percona公司开发,它允许在...

    物理备份,xtrabackup包 适用于MySQL5.7及以前

    Xtrabackup是Percona公司开发的一款高效、免费的MySQL物理备份工具,尤其适用于MySQL 5.7及之前的版本。它以其对InnoDB存储引擎的出色支持和在备份过程中对数据库服务几乎零影响的特点,成为了数据库管理员的首选。 ...

    mysql全库备份增量备份脚本

    ### xtrabackup增量备份 `xtrabackup`是由Percona开发的一个开源工具,特别适用于InnoDB存储引擎的MySQL数据库,它提供了高效且无锁的热备份功能。全量备份后,xtrabackup可以进行增量备份,仅备份自上次备份以来...

    mysql热备份工具文档

    这里我们关注的是“mysql热备份工具文档”,它主要涉及的是Percona Xtrabackup这款高效且非侵入式的MySQL热备份工具。Xtrabackup能够在线备份InnoDB和XtraDB存储引擎的数据库,甚至在数据库服务正在运行时也能完成,...

    XtraBackup(MySQL备份) v1.6 for Win

    XtraBackup是一个用来备份 MySQL 的 InnoDB 数据库的开源工具。例如执行增量备份的命令是: # ./xtrabackup --backup --target-dir=/backup/delta --incremental-basedir=/backup/base

    linux-实现mysql增量备份和还原

    Xtrabackup是一款开源的、用于MySQL和Percona Server的热备份工具,它支持全量备份和增量备份,是实现这一目标的理想选择。 首先,我们要理解全量备份和增量备份的概念。全量备份是指备份所有数据库或数据文件,这...

    linux mysql自动备份工具

    本篇文章将详细讲解如何在Linux系统中设置一个自动的MySQL备份工具,以便实现数据的自动备份,降低因系统故障或人为错误导致的数据丢失风险。 首先,我们需要了解备份MySQL数据库的基本原理。MySQL提供了多种备份...

    MySQL DBA percona-xtrabackup备份文档

    Percona XtraBackup是一款广泛使用的MySQL备份工具,它由Percona公司开发,是MySQL备份解决方案中的佼佼者。它支持全库备份、增量备份和在线(热)备份,能为DBA(数据库管理员)提供强大的备份和恢复功能。 首先,...

    Mysql数据库备份恢复测试报告-xtrabackup.docx

    本测试中并未详细描述增量备份的过程,但提到全量及增量备份集均可用于恢复,说明增量备份也在恢复过程中起到了补充作用。 3. **恢复验证**:恢复后,通过检查数据库中的数据更新时间(如`mysql.innodb_table_stats...

    第三十七章:Mysql全量备份及增量备份1

    Xtrabackup是一个开源的热备份工具,它可以对InnoDB和XtraDB存储引擎的MySQL数据库进行无锁备份。相比传统的冷备份,Xtrabackup能在不中断服务的情况下完成全量和增量备份,提高了备份效率,降低了业务影响。使用...

    mysql数据库备份和恢复工具

    三、MySQL备份工具 1. mysqldump:MySQL官方提供的命令行工具,可以执行全量和增量备份。例如,执行全量备份的命令为: ``` mysqldump -u username -p password database_name > backup.sql ``` 2. Percona ...

    基于xtrabackup8工具增量物理备份Mysql8数据自动化工具

    原文链接:...2、星期二、星期三、星期五、星期六Mysql增量备份 3、根据指定日期实现Mysql数据物理恢复

Global site tag (gtag.js) - Google Analytics