xtrabackup全量+增量备份手记

本文详细介绍了使用XtraBackup工具进行MySQL全量备份与增量备份的过程,并演示了如何通过备份文件进行数据恢复。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

全量

[root@localhost ~]# rm -rf /backups/mysql/20151026/
[root@localhost ~]# ll /backups/mysql/
total 0
[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --user=root --password="root" --port=3306 --backup --target-dir=/backups/mysql/$(date +%Y%m%d)/
...
sent 2148828291 bytes  received 301 bytes  138634102.71 bytes/sec
total size is 2148565014  speedup is 1.00

[root@localhost 20151026]# ll /home/data/mysql/data/
total 5255264
-rw-rw----. 1 root  root  1073741824 Oct 26 04:28 IBdata1
-rw-rw----. 1 root  root  1073741824 Oct 26 04:28 IBdata2

-rw-r--r--. 1 root  root          22 Oct 26 04:28 xtrabackup_binlog_pos_innodb
[root@localhost 20151026]# chown -R mysql:mysql /home/data/mysql/data/
[root@localhost 20151026]# ll /home/data/mysql/data/
total 5255264
-rw-rw----. 1 mysql mysql 1073741824 Oct 26 04:28 IBdata1
-rw-rw----. 1 mysql mysql 1073741824 Oct 26 04:28 IBdata2
-rw-r--r--. 1 mysql mysql         22 Oct 26 04:28 xtrabackup_binlog_pos_innodb
[root@localhost 20151026]# service mysqld5612 start
Starting MySQL........................... SUCCESS! 

[root@localhost 20151026]# mysql -uroot -proot
....
mysql> select * from t5.test1;
+------+------+
| id   | name |
+------+------+
|    3 | c    |
|    2 | b    |
|    1 | a    |
+------+------+
3 rows in set (0.05 sec)

增量

[root@localhost ~]# mysql -uroot -proot -e "CREATE USER 'backup'@'%' IDENTIFIED BY 'backup'";
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -ubackup -pbackup

[root@localhost ~]# mysql -uroot -proot -e "GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT,CREATE TABLESPACE,SUPER ON *.* TO 'backup'@'%'";
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -ubackup -pbackup
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.13-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| business_db        |
| mysql              |
| performance_schema |
| t1                 |
| t5                 |
| test               |
+--------------------+
7 rows in set (0.12 sec)

备份

[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --user=backup --password="backup" --port=3306 --backup --target-dir=/backups/mysql/full_incre_$(date +%Y%m%d)
...
xtrabackup: Transaction log of lsn (177047752) to (177047752) was copied.

恢复

[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --backup --user=backup --password="backup" --target-dir=/backups/mysql/incre_20151027/ --incremental-basedir=/backups/mysql/incre_20151026/
....
xtrabackup: Transaction log of lsn (177051860) to (177051860) was copied.

[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=backup --password="backup" --apply-log-only --target-dir=/backups/mysql/full_incre_20151026/

[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=backup --password="backup" --apply-log-only --target-dir=/backups/mysql/full_incre_20151026/ --incremental-dir=/backups/mysql/incre_20151027/

[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=backup --password="backup" --target-dir=/backups/mysql/full_incre_20151026/

[root@localhost ~]# service mysqld5612 stop
Shutting down MySQL...... SUCCESS! 
[root@localhost ~]# rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' /backups/mysql/full_incre_20151026/ /home/data/mysql/data/

[root@localhost ~]# chown -R mysql:mysql /home/data/mysql/data
[root@localhost ~]# service mysql start
mysql: unrecognized service
[root@localhost ~]# service mysqld5612 start
Starting MySQL................................. SUCCESS! 
[root@localhost ~]# 

========================mysql操作=======随后整理==================

[root@localhost ~]# mysql -uroot -proot

mysql> use t1;
Database changed
mysql> create table test2 select 1 as id,'aaa' as name;
Query OK, 1 row affected (0.46 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test2;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.03 sec)

mysql> create table test3 select 1 as id,'bbb' as name;
Query OK, 1 row affected (0.25 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show tables;
+--------------+
| Tables_in_t1 |
+--------------+
| test1        |
| test2        |
| test3        |
+--------------+
3 rows in set (0.14 sec)

mysql> select * from test2;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)

mysql> select * from test3;
+----+------+
| id | name |
+----+------+
|  1 | bbb  |
+----+------+
1 row in set (0.04 sec)

mysql> delete from test2;delete from test3;
Query OK, 1 row affected (0.15 sec)

Query OK, 1 row affected (0.01 sec)

mysql> select * from test3;
Empty set (0.00 sec)

mysql> select * from test2;
Empty set (0.00 sec)

mysql> exit


[root@localhost ~]# mysql -uroot -proot

mysql> select * from t1.test1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.06 sec)

mysql> select * from t1.test2;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.07 sec)

mysql> select * from t1.test3;
+----+------+
| id | name |
+----+------+
|  1 | bbb  |
+----+------+
1 row in set (0.10 sec)
### Xtrabackup全量备份增量备份的区别及用法 #### 全量备份 (Full Backup) 全量备份是指对整个数据库的数据文件进行一次完整的复制操作。这种备份方式会捕获所有的数据页,即使某些页面未被修改也会包含在内。以下是创建全量备份的具体方法: 通过运行`xtrabackup`命令可以完成全量备份的操作[^3]。例如,以下是一个典型的全量备份命令: ```bash xtrabackup --backup --target-dir=/path/to/full_backup_dir/ ``` 此命令会在指定的目标目录下保存所有必要的数据文件。 #### 增量备份 (Incremental Backup) 增量备份则是基于上一次的全量增量备份来记录自上次备份以来发生的变化部分。这种方式的优点在于它仅存储发生变化的部分,因此通常占用更少的空间并能更快地完成备份过程。为了实现这一点,XtraBackup利用了InnoDB的日志机制来追踪这些变化[^5]。 要执行增量备份,需提供一个参数指向最近的一次基础备份(无论是另一个增量还是最初的全备)。下面展示了一个如何设置增量备份的例子: ```bash xtrabackup --backup --target-dir=/path/to/incr_backup_dir/ \ --incremental-basedir=/path/to/full_or_previous_incr_backup_dir/ ``` 这里的关键选项是`--incremental-basedir`,它指定了作为本次增量的基础备份位置[^1]。 #### 还原流程 对于全量备份来说,其恢复相对简单直接;而对于多层嵌套的增量备份,则需要依次应用每一级增量到初始的完全副本之上才能最终得到最新的状态版本。这可以通过重复调用准备阶段的相关指令达成目标。 #### 工具对比考量因素 当评估像MySQL Enterprise Backup这样的商业解决方案或者开源替代品如MariaDB Backup时,应该考虑到诸如性能表现、资源消耗以及特定功能需求等方面差异[^4]。然而无论如何挑选具体产品方案,都务必重视验证测试环节以确认所选策略能够满足实际业务连续性的要求[^2]。 ```bash # Example of preparing a full backup before applying incrementals. xtrabackup --prepare --apply-log-only --target-dir=/path/to/full_backup/ # Then apply each incremental on top sequentially. xtrabackup --prepare --apply-log-only --target-dir=/path/to/full_backup/ --incremental-dir=/path/to/incremental1/ xtrabackup --prepare --target-dir=/path/to/full_backup/ --incremental-dir=/path/to/incrementalN/ ``` 上述脚本展示了怎样逐步处理一系列增量更新至原始基线直至最新状况的过程。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值