innobackupex全量备份+增量备份手记

公众号【架构师也是人】

墨衍会员 · AI 创作全网分发码龄18年

关注

原创最新推荐文章于 2026-06-27 10:19:20 发布 1k 阅读 AI 写同款· GEO 优化 ›

mysql使用innobackupex备份详解阅读详情

全量备份

查库

[root@localhost ~]# service mysqld5612 status
[root@localhost ~]# mysql -uroot -proot
mysql> select * from t1.test1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from t5.test1;
+------+------+
| id   | name |
+------+------+
|    3 | c    |
|    2 | b    |
|    1 | a    |
+------+------+
3 rows in set (0.00 sec)

备份

[root@localhost ~]# mkdir /backups/mysql/ex
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password="root" /backups/mysql/ex/$(date +%Y%m%d)/ --no-timestamp --parallel=2
..........
.....
innobackupex: Backup created in directory '/backups/mysql/ex/20151026'
innobackupex: MySQL binlog position: filename 'mysql-bin.000053', position 120
151026 10:10:24  innobackupex: Connection to database server closed
151026 10:10:24  innobackupex: completed OK!

删库

[root@localhost ~]# mysql -uroot -proot
.....
mysql> delete from t1.test1;
Query OK, 3 rows affected (0.33 sec)

mysql> drop table t1.test1;
Query OK, 0 rows affected (0.12 sec)

mysql> select * from t1.test1;
ERROR 1146 (42S02): Table 't1.test1' doesn't exist
mysql> exit
Bye

生成恢复文件

[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --use-memory=512m --apply-log /backups/mysql/ex/20151026/

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 177043625
151026 10:13:18  innobackupex: completed OK!
[root@localhost ~]# service mysqld5612 stop
Shutting down MySQL........ SUCCESS!

恢复

[root@localhost ~]# rm -rf /home/data/mysql/data/*
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /backups/mysql/ex/20151026/
....
innobackupex: Finished copying back files.

151026 10:15:10  innobackupex: completed OK!

[root@localhost ~]# chown -R mysql:mysql /home/data/mysql/data/
[root@localhost ~]# service mysqld5612 start
Starting MySQL.................................. SUCCESS! 
[root@localhost ~]# mysql -uroot -proot
...
mysql> select * from t1.test1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.06 sec)

mysql> exit
Bye

增量备份

备份

命令丢失待补充

151026 12:48:05  innobackupex: completed OK!

生成恢复文件

[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=backup --password="backup" /backups/mysql/ex/innobackupex_full_20151026_2/ --incremental-dir=/backups/mysql/ex/innobackex_incre_2_2/ --apply-log
....
151026 12:48:37  innobackupex: completed OK!

恢复

[root@localhost ~]# innobackupex --apply-log /backups/mysql/ex/innobackupex_full_20151026_2/
...
151026 12:54:01  innobackupex: completed OK!

修改文件权限

[root@localhost ~]# ll /home/data/mysql/data/
total 5242932
drwxr-xr-x. 2 root root       4096 Oct 26 12:53 business_db
-rw-r--r--. 1 root root 1073741824 Oct 26 12:53 IBdata1
...
-rw-r--r--. 1 root root        693 Oct 26 12:53 xtrabackup_info
[root@localhost ~]# chown -R mysql:mysql /home/data/mysql/data/
[root@localhost ~]# ll /home/data/mysql/data/
total 5242932
drwxr-xr-x. 2 mysql mysql       4096 Oct 26 12:53 business_db
-rw-r--r--. 1 mysql mysql 1073741824 Oct 26 12:53 IBdata1
...
[root@localhost ~]# service mysqld5612 start
Starting MySQL...........