一、简介Xtrabackup是由percona提供的Mysql数据库备份工具,据官方介绍,这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。其主要特点是备份过程快速、可靠,不会
一、简介
Xtrabackup是由percona提供的Mysql数据库备份工具,据官方介绍,这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。其主要特点是备份过程快速、可靠,不会打断正在执行的事务,能够基于压缩等功能节约磁盘空间和流量,自动实现备份检测及其还原速度快。
二、安装及备份、恢复实现
安装:其最新版的软件可从 Http://www.percona.com/software/percona-xtrabackup/ 获得。本文基于Centos6.x的系统,因此,直接下载相应版本的rpm包安装即可,这里不再演示其过程。
yum -y install percona-toolkit-2.2.4-1.noarch.rpm percona-xtrabackup-2.1.8-733.rhel6.x86_64.rpm
完全备份及删除数据目录实现恢复:
mysql>set session_sql_log_bin=0; #导入数据时让其不记录二进制日志
mysql>source /root/hellodb.sql #导入数据
mysql>set session_sql_log_bin=1; #开启二进制日志
[root@centos6]#innobackupex --user=root /mybackups/ #全量备份
[root@centos6]#service mysqld stop #停止数据库,删除数据目录
[root@centos6]#rm -rf /mydata/data;
;
;
DELIMITER ;
# at 4
#161122 19:31:08 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.32-MariaDB-log created 161122 19:31:08
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
fCw0WA8BAAAA8QAAAPUAAAABAAQANS41LjMyLU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAaiCAGCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAA+1uD6g==
';
# at 245
#161122 19:31:49 server id 1 end_log_pos 355 Querythread_id=9exec_time=0error_code=0
use `mydbs`;
SET TIMESTAMP=1479814309;
SET @@session.pseudo_thread_id=9;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1;
SET @@session.sql_mode=0;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8;
SET @@session.lc_time_names=0;
SET @@session.collation_database=DEFAULT;
create table students(id int,name varchar(20))
;
# at 355
#161122 19:32:26 server id 1 end_log_pos 424 Querythread_id=9exec_time=0error_code=0
SET TIMESTAMP=1479814346;
BEGIN
;
# at 424
#161122 19:32:26 server id 1 end_log_pos 537 Querythread_id=9exec_time=0error_code=0
SET TIMESTAMP=1479814346;
insert into students values (1,'tom'),(2,'jerry')
;
# at 537
#161122 19:32:26 server id 1 end_log_pos 564 Xid = 155
COMMIT;
DELIMITER ;
# End of log file
ROLLBACK ;
;
;
[root@centos6 binlogs]# mysqlbinlog --start-position=245 master-bin.000007>/root/incr.sql
[root@centos6 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.32-MariaDB-log MariaDB Server
Copyright (c) 2000, 2013, oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>set sql_log_bin=0;
MariaDB [hellodb]>source /root/incr.sql
MariaDB [hellodb]>set sql_log_bin=1;
MariaDB [hellodb]>use mydbs;
MariaDB [mydbs]> show tables;
+-----------------+
| Tables_in_mydbs |
+-----------------+
| students |
| t1 |
+-----------------+
2 rows in set (0.00 sec)
MariaDB [mydbs]> select * from studnets;
ERROR 1146 (42S02): Table 'mydbs.studnets' doesn't exist
MariaDB [mydbs]> select * from students;
+------+-------+
| id | name |
+------+-------+
| 1 | tom |
| 2 | jerry |
+------+-------+
2 rows in set (0.00 sec)
MariaDB [mydbs]> use hellodb;
Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| tbl |
| teachers |
| toc |
+-------------------+
8 rows in set (0.00 sec)
MariaDB [hellodb]>
经过查看数据和删除数据目录前进行对比,如数据一致则代表数据恢复成功,xtrabackup+二进制日志可实现数据的完全备份、增量备份、完全备份恢复和增量数据恢复,同时在使用此款备份工具时,不会影响客户的正常访问,达到提高用户体验。
--结束END--
本文标题: percona-xtrabackup完全及增量备份与恢复
本文链接: https://lsjlt.com/news/37490.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
2024-10-23
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0