MySQL备份和恢复（三）Xtrabackup实现热备：完全备份+增量备份
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 ...
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
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1376
52
The relationship between mysql user and database
Apr 08, 2025 pm 07:15 PM
In MySQL database, the relationship between the user and the database is defined by permissions and tables. The user has a username and password to access the database. Permissions are granted through the GRANT command, while the table is created by the CREATE TABLE command. To establish a relationship between a user and a database, you need to create a database, create a user, and then grant permissions.
RDS MySQL integration with Redshift zero ETL
Apr 08, 2025 pm 07:06 PM
Data Integration Simplification: AmazonRDSMySQL and Redshift's zero ETL integration Efficient data integration is at the heart of a data-driven organization. Traditional ETL (extract, convert, load) processes are complex and time-consuming, especially when integrating databases (such as AmazonRDSMySQL) with data warehouses (such as Redshift). However, AWS provides zero ETL integration solutions that have completely changed this situation, providing a simplified, near-real-time solution for data migration from RDSMySQL to Redshift. This article will dive into RDSMySQL zero ETL integration with Redshift, explaining how it works and the advantages it brings to data engineers and developers.
MySQL: The Ease of Data Management for Beginners
Apr 09, 2025 am 12:07 AM
MySQL is suitable for beginners because it is simple to install, powerful and easy to manage data. 1. Simple installation and configuration, suitable for a variety of operating systems. 2. Support basic operations such as creating databases and tables, inserting, querying, updating and deleting data. 3. Provide advanced functions such as JOIN operations and subqueries. 4. Performance can be improved through indexing, query optimization and table partitioning. 5. Support backup, recovery and security measures to ensure data security and consistency.
How to fill in mysql username and password
Apr 08, 2025 pm 07:09 PM
To fill in the MySQL username and password: 1. Determine the username and password; 2. Connect to the database; 3. Use the username and password to execute queries and commands.
Query optimization in MySQL is essential for improving database performance, especially when dealing with large data sets
Apr 08, 2025 pm 07:12 PM
1. Use the correct index to speed up data retrieval by reducing the amount of data scanned select*frommployeeswherelast_name='smith'; if you look up a column of a table multiple times, create an index for that column. If you or your app needs data from multiple columns according to the criteria, create a composite index 2. Avoid select * only those required columns, if you select all unwanted columns, this will only consume more server memory and cause the server to slow down at high load or frequency times For example, your table contains columns such as created_at and updated_at and timestamps, and then avoid selecting * because they do not require inefficient query se
Can I retrieve the database password in Navicat?
Apr 08, 2025 pm 09:51 PM
Navicat itself does not store the database password, and can only retrieve the encrypted password. Solution: 1. Check the password manager; 2. Check Navicat's "Remember Password" function; 3. Reset the database password; 4. Contact the database administrator.
How to view mysql
Apr 08, 2025 pm 07:21 PM
View the MySQL database with the following command: Connect to the server: mysql -u Username -p Password Run SHOW DATABASES; Command to get all existing databases Select database: USE database name; View table: SHOW TABLES; View table structure: DESCRIBE table name; View data: SELECT * FROM table name;
Understand ACID properties: The pillars of a reliable database
Apr 08, 2025 pm 06:33 PM
Detailed explanation of database ACID attributes ACID attributes are a set of rules to ensure the reliability and consistency of database transactions. They define how database systems handle transactions, and ensure data integrity and accuracy even in case of system crashes, power interruptions, or multiple users concurrent access. ACID Attribute Overview Atomicity: A transaction is regarded as an indivisible unit. Any part fails, the entire transaction is rolled back, and the database does not retain any changes. For example, if a bank transfer is deducted from one account but not increased to another, the entire operation is revoked. begintransaction; updateaccountssetbalance=balance-100wh
