Percona XtraBackup is the world’s only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. With Percona XtraBackup, you can achieve the following benefits: Backups that complete
Percona XtraBackup is the world’s only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. With Percona XtraBackup, you can achieve the following benefits:
Backups that complete quickly and reliably
Uninterrupted transaction processing during backups
Savings on disk space and network bandwidth
Automatic backup verification
Higher uptime due to faster restore time
以上摘自官方文档对Xtrabackup的描述。
Xtrabackup是Percona公司的开源mysql热备软件，软件自身支持完全备份和增量备份，功能强大，使用简单，且备份结束会自动检查备份的可用性。对于InnoDB和XtraDB可以实现无阻塞的备份。
Xtrabackup会在备份目录下自动生成以当前日期和时间为名的目录，目录下包含当前备份所有数据文件和丰富的当前备份时的状态信息。
实例演示：
xtrabackup的安装，安装包可从官网下载http://www.percona.com/software/percona-xtrabackup，官方提供了二进制和源码包，根据自己需要下载
/* 下载官方的rpm包安装，xtrabackup要依赖perl-DBD-mysql包，不想手动解决依赖关系可以yum localinstall安装 */
[root@console ~]# yum localinstall --nogpgcheck percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm -y
/* xtrabackup安装后生成的文件不多 */
[root@console ~]# rpm -ql percona-xtrabackup
/usr/bin/innobackupex /* 备份时用的命令,会根据mysql版本自动调用xtrabackup_{55,56} */
/usr/bin/innobackupex-1.5.1
/usr/bin/xbcrypt /* 提供备份过程加密支持 */
/usr/bin/xbstream /* 支持流式备份 */
/usr/bin/xtrabackup
/usr/bin/xtrabackup_55 / * 这个和下面那个才是备份过程实际调用的备份程序 */
/usr/bin/xtrabackup_56
/usr/share/doc/percona-xtrabackup-2.1.4
/usr/share/doc/percona-xtrabackup-2.1.4/COPYING /* 文档只有一个软件授权文件，没有man文档，不过--help给出的帮助信息也比较丰富 */备份过程：
/* 在库db1中准备一张表tb1，做效果比较用 */
MariaDB [(none)]> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> USE db1
Database changed
MariaDB [db1]> CREATE TABLE `tb1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(30) NOT NULL, `Gender` enum('F','M','O') NOT NULL, PRIMARY KEY (`id`) );
Query OK, 0 rows affected (0.22 sec)
MariaDB [db1]> DESC tb1;
+--------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(30) | NO | | NULL | |
| Gender | enum('F','M','O') | NO | | NULL | |
+--------+-------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
MariaDB [db1]> INSERT INTO tb1 (Name,Gender) VALUES ('Tom','M'),('Jerry','F');
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [db1]> SELECT * FROM tb1;
+----+-------+--------+
| id | Name | Gender |
+----+-------+--------+
| 1 | Tom | M |
| 2 | Jerry | F |
+----+-------+--------+
2 rows in set (0.00 sec)
/* 创建一个用于备份的最小权限的用户 */
MariaDB [(none)]> CREATE USER 'bakuser'@'localhost' IDENTIFIED BY 'backupass';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bakuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'bakuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)现在先做一次完全备份：
/* 全备只需指定用于备份的用户名、密码和备份路径即可，最后出现innobackupex: completed OK! 则代表备份成功 */ [root@node1 ~]# innobackupex --user=bakuser --password=backupass /innobackup/ innobackupex: Backup created in directory '/innobackup/2014-07-31_09-27-36' innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 1270 140731 09:27:39 innobackupex: Connection to database server closed 140731 09:27:40 innobackupex: completed OK! /* 看一下备份都生成了哪些文件,除了数据库文件外还多了一些文件，注意由于我用root登陆系统，备份后的文件属主属组都是root，恢复后要改为运行mysqld进程的用户，不然mysql起不来 */ [root@node1 ~]# cd /innobackup/2014-07-31_09-27-36/ [root@node1 2014-07-31_09-27-36]# ll total 18476 -rw-r--r--. 1 root root 260 Jul 31 09:27 backup-my.cnf /* 备份命令用到的配置选项信息 */ drwx------. 2 root root 4096 Jul 31 09:27 db1 drwx------. 2 root root 4096 Jul 31 09:27 hellodb -rw-r-----. 1 root root 18874368 Jul 31 09:27 ibdata1 drwx------. 2 root root 4096 Jul 31 09:27 mydb drwxr-xr-x. 2 root root 4096 Jul 31 09:27 mysql drwxr-xr-x. 2 root root 4096 Jul 31 09:27 performance_schema drwxr-xr-x. 2 root root 4096 Jul 31 09:27 test -rw-r--r--. 1 root root 13 Jul 31 09:27 xtrabackup_binary /* 记录备份过程实际用的备份程序 */ -rw-r--r--. 1 root root 24 Jul 31 09:27 xtrabackup_binlog_info /* 记录备份时的二进制日志文件和当前的位置 */ -rw-r-----. 1 root root 89 Jul 31 09:27 xtrabackup_checkpoints /* 记录备份类型、 状态（是否prepared)、是否压缩、备份的LSN(Log Sequence Number日志序列号）范围等信息 */ -rw-r-----. 1 root root 2560 Jul 31 09:27 xtrabackup_logfile /* xtrabackup自己的日志文件，新版本中不直接可见 */ [root@node1 2014-07-31_09-27-36]# cat backup-my.cnf # This MySQL options file was generated by innobackupex. # The MySQL server [mysqld] innodb_data_file_path=ibdata1:10M:autoextend innodb_log_files_in_group=2 innodb_log_file_size=5242880 innodb_fast_checksum=0 innodb_page_size=16384 innodb_log_block_size=512 [root@node1 2014-07-31_09-27-36]# cat xtrabackup_binary xtrabackup_55[root@node1 2014-07-31_09-27-36]# [root@node1 2014-07-31_09-27-36]# cat xtrabackup_binlog_info mysql-bin.000001 1270 [root@node1 2014-07-31_09-27-36]# cat xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 1660869 last_lsn = 1660869 compact = 0 [root@node1 2014-07-31_09-27-36]# file xtrabackup_logfile xtrabackup_logfile: data
全备完成后，对数据库做一些修改后再做一次增备：
/* 第一次增量备份,--incremental 备份类型为增量， --incremental-basedir 指定这次增备是相对哪一个备份做的增量,这里是相对全备 */
[root@node1 innobackup]# innobackupex --user=bakuser --password=backupass --incremental /innobackup/ --incremental-basedir=/innobackup/2014-07-31_09-27-36/
innobackupex: Backup created in directory '/innobackup/2014-07-31_11-51-19'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 1525
140731 11:51:48 innobackupex: Connection to database server closed
140731 11:51:48 innobackupex: completed OK!
/* 做些修改，做第二次增备 */
MariaDB [db1]> DELETE FROM tb1 WHERE Name='Tom';
Query OK, 1 row affected (0.09 sec)
MariaDB [db1]> UPDATE tb1 SET Gender='F' WHERE Name='Dongfang Bubai';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [db1]> SELECT * FROM tb1;
+----+----------------+--------+
| id | Name | Gender |
+----+----------------+--------+
| 2 | Jerry | F |
| 3 | Bob | M |
| 4 | Dongfang Bubai | F |
+----+----------------+--------+
3 rows in set (0.00 sec)
/* incremental-basedir 相对第一次增量的增量 */
[root@node1 innobackup]# innobackupex --user=bakuser --password=backupass --incremental /innobackup/ --incremental-basedir=/innobackup/2014-07-31_11-51-19/
innobackupex: Backup created in directory '/innobackup/2014-07-31_11-59-29'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 1922
140731 11:59:33 innobackupex: Connection to database server closed
140731 11:59:33 innobackupex: completed OK!
/* 第二次增备完成后，再做一些数据修改操作，以模拟实际生产环境数据库突然崩溃场景 */
MariaDB [db1]> INSERT INTO tb1 (Name,Gender) VALUES ('Kangshifu Guamian','O');
Query OK, 1 row affected (0.04 sec)
/* 将二进制日志备份出来一份，生产环境中二进制日志切记不要和数据文件放在一起，
最好不要在同一分区甚至同一物理磁盘, 以免一旦玉石俱焚，悔之晚矣 */
[root@node1 innobackup]# mysql -e 'SHOW MASTER STATUS;'
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 2168 | | |
+------------------+----------+--------------+------------------+
[root@node1 innobackup]# cp /mydata/data/
aria_log.00000001 hellodb/ ib_logfile1 mysql-bin.000001 node1.bob.org.pid
aria_log_control ibdata1 mydb/ mysql-bin.index performance_schema/
db1/ ib_logfile0 mysql/ node1.bob.org.err test/
[root@node1 innobackup]# cp /mydata/data/mysql-bin.000001 /innobackup/
/* 人工让数据库彻底崩盘 */
[root@node1 innobackup]# service mysqld stop
Shutting down MySQL.. [ OK ]
[root@node1 innobackup]# rm -rf /mydata/data/*恢复：
xtrabackup的恢复比较特殊，恢复前要先执行prepare过程，prepare主要完成把已提交的事务数据同步到数据文件，未完成的事务则回滚，这是对于只有一个完全备份来说。当还需要结合其他的增备时，则在些过程只需要将已提交的事务同步，未提交的事务则不用回滚，因为在本次备份时未提交的事务可能会在下一备份中已经提交，以避免这种无谓的劳动。
/* 注意 backup_type 和 to_lsn的变化 */ [root@node1 innobackup]# cat 2014-07-31_09-27-36/xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 1660869 last_lsn = 1660869 compact = 0 /* Prepare全备,--apply-log 指定要prepare, --redo-only 只处理已提交事务，未提交的事务则不回滚 backup_type由full-backuped --> full-prepared */ [root@node1 innobackup]# innobackupex --apply-log --redo-only /innobackup/2014-07-31_09-27-36/ ... 140731 15:05:53 innobackupex: completed OK! [root@node1 innobackup]# cat 2014-07-31_09-27-36/xtrabackup_checkpoints backup_type = full-prepared from_lsn = 0 to_lsn = 1660869 last_lsn = 1660869 compact = 0 /* prepare 增备1， --incremental-dir 增备的路径 */ [root@node1 innobackup]# innobackupex --apply-log --redo-only /innobackup/2014-07-31_09-27-36/ --incremental-dir /innobackup/2014-07-31_11-51-19/ /* 再来看full backup 的 to_lsn 已经 --> 增备1 的 to_lsn了，此时增量1也就退出舞台了 */ [root@node1 innobackup]# cat 2014-07-31_11-51-19/xtrabackup_checkpoints backup_type = incremental from_lsn = 1660869 to_lsn = 1661993 last_lsn = 1661993 compact = 0 [root@node1 innobackup]# cat 2014-07-31_09-27-36/xtrabackup_checkpoints backup_type = full-prepared from_lsn = 0 to_lsn = 1661993 last_lsn = 1661993 compact = 0 /* prepare 增备2，增量1已经prepare上去了，所以基准还是全备，--incremental-dir 指向增量2 */ [root@node1 innobackup]# innobackupex --apply-log /innobackup/2014-07-31_09-27-36/ --incremental-dir=/innobackup/2014-07-31_11-59-29/ /* 完事后再瞅一眼，全备的 to_lsn 已经 --> 增量2 的了 */ [root@node1 innobackup]# cat 2014-07-31_09-27-36/xtrabackup_checkpoints backup_type = full-prepared from_lsn = 0 to_lsn = 1664255 last_lsn = 1664255 compact = 0 [root@node1 innobackup]# cat 2014-07-31_11-59-29/xtrabackup_checkpoints backup_type = incremental from_lsn = 1661993 to_lsn = 1664255 last_lsn = 1664255 compact = 0
至此，恢复的预准备工作已完成，两个增备也完成了历史使命，恢复只要用全备的一个就可以了：
/* 恢复过程灰常简单，恢复过程也不需要启动mysql, 但是备份过程是一定要启动mysql的，因为要以备份用户身份连到Mysql上的嘛 */ [root@node1 innobackup]# innobackupex --copy-back /innobackup/2014-07-31_09-27-36/ /* 现在还不能启动mysql, 对了，属主属组还没改 */ [root@node1 innobackup]# ll /mydata/data/ total 18456 drwxr-xr-x. 2 root root 4096 Jul 31 15:54 db1 drwxr-xr-x. 2 root root 4096 Jul 31 15:54 hellodb -rw-r--r--. 1 root root 18874368 Jul 31 15:54 ibdata1 drwxr-xr-x. 2 root root 4096 Jul 31 15:54 mydb drwxr-xr-x. 2 root root 4096 Jul 31 15:54 mysql drwxr-xr-x. 2 root root 4096 Jul 31 15:54 performance_schema drwxr-xr-x. 2 root root 4096 Jul 31 15:54 test [root@node1 innobackup]# chown -R mysql.mysql /mydata/data/* /* 现在就可以启动了 。。 */ [root@node1 innobackup]# service mysqld start Starting MySQL..... [ OK ] /* 正常启动，数据都在 */ MariaDB [db1]> SELECT * FROM tb1; +----+----------------+--------+ | id | Name | Gender | +----+----------------+--------+ | 2 | Jerry | F | | 3 | Bob | M | | 4 | Dongfang Bubai | F | +----+----------------+--------+ 3 rows in set (0.00 sec)
再加上最后的二进制日志恢复，整个恢复过程就全搞颠了
[root@node1 innobackup]# cat /innobackup/2014-07-31_11-59-29/xtrabackup_binlog_info mysql-bin.000001 1922 [root@node1 innobackup]# mysqlbinlog --start-position=1922 mysql-bin.000001 > 11.sql [root@node1 innobackup]# mysql < 11.sql MariaDB [(none)]> SELECT * FROM db1.tb1; +----+-------------------+--------+ | id | Name | Gender | +----+-------------------+--------+ | 2 | Jerry | F | | 3 | Bob | M | | 4 | Dongfang Bubai | F | | 5 | Kangshifu Guamian | O | +----+-------------------+--------+ 4 rows in set (0.01 sec) /* 二进制日志恢复，kangshifu guamian finnaly O 出锅了 */
To be continued ...
图文详解mysql架构原理May 17, 2022 pm 05:54 PM本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了关于架构原理的相关内容，MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层，下面一起来看一下，希望对大家有帮助。
mysql怎么去掉第一个字符May 19, 2022 am 10:21 AM方法：1、利用right函数，语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”；2、利用substring函数，语法为“select substring(指定字段,2)..”。
mysql的msi与zip版本有什么区别May 16, 2022 pm 04:33 PMmysql的msi与zip版本的区别：1、zip包含的安装程序是一种主动安装，而msi包含的是被installer所用的安装文件以提交请求的方式安装；2、zip是一种数据压缩和文档存储的文件格式，msi是微软格式的安装包。
mysql怎么替换换行符Apr 18, 2022 pm 03:14 PM在mysql中，可以利用char()和REPLACE()函数来替换换行符；REPLACE()函数可以用新字符串替换列中的换行符，而换行符可使用“char(13)”来表示，语法为“replace(字段名,char(13),'新字符串') ”。
mysql怎么将varchar转换为int类型May 12, 2022 pm 04:51 PM转换方法：1、利用cast函数，语法“select * from 表名 order by cast(字段名 as SIGNED)”；2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。
MySQL复制技术之异步复制和半同步复制Apr 25, 2022 pm 07:21 PM本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了关于MySQL复制技术的相关问题，包括了异步复制、半同步复制等等内容，下面一起来看一下，希望对大家有帮助。
mysql怎么判断是否是数字类型May 16, 2022 am 10:09 AM在mysql中，可以利用REGEXP运算符判断数据是否是数字类型，语法为“String REGEXP '[^0-9.]'”；该运算符是正则表达式的缩写，若数据字符中含有数字时，返回的结果是true，反之返回的结果是false。
带你把MySQL索引吃透了Apr 22, 2022 am 11:48 AM本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了mysql高级篇的一些问题，包括了索引是什么、索引底层实现等等问题，下面一起来看一下，希望对大家有帮助。
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
SublimeText3 English version
Recommended: Win version, supports code prompts!
Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.
Zend Studio 13.0.1
Powerful PHP integrated development environment
DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software
mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
