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

最新推荐文章于 2025-03-17 20:40:50 发布

原创最新推荐文章于 2025-03-17 20:40:50 发布 · 992 阅读

0 ·

CC 4.0 BY-SA版权

Mysql 专栏收录该内容

36 篇文章

订阅专栏

本文介绍MySQL数据库的全量备份与增量备份过程，并演示了如何进行数据恢复操作。包含具体的备份命令、恢复步骤及权限调整等关键环节。

全量备份

查库

[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...........