innobackupex备份数据库

innobackupex是xtrabackup的一个链接，是用C编译的工具，在后续的版本中会删除；
[root@web ~]# ll /usr/bin/innobackupex
lrwxrwxrwx 1 root root 10 Dec  9 17:10 /usr/bin/innobackupex -> xtrabackup

innobackupex的选项跟xtrabackup的差不多通用的
完整备份
[root@web ~]# innobackupex --user=root --password=root --default-file=/etc/my.cnf --port=3306 --databases=magedu /backup/magedu/
201221 15:14:40  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
201221 15:14:40  version_check Connected to MySQL server
........................................................
201225 16:29:46 [00] Writing /innobackup/2020-12-25_16-29-30/backup-my.cnf
201225 16:29:46 [00]        ...done
201225 16:29:46 [00] Writing /innobackup/2020-12-25_16-29-30/xtrabackup_info
201225 16:29:46 [00]        ...done
xtrabackup: Transaction log of lsn (456396767) to (456396776) was copied.
201225 16:29:46 completed OK!
查看innobackupex自动生成的以日期命名的目录
[root@web ~]# ll /innobackup/2020-12-25_16-29-30/
total 77964

查看整个库的所有表的行数； mysql> select a.TABLE_NAME,a.TABLE_ROWS from TABLES as a where TABLE_SCHEMA = 'ultrax' order by TABLE_ROWS desc; +-----------------------------------+------------+ | TABLE_NAME                        | TABLE_ROWS | +-----------------------------------+------------+ | pre_common_district               |      45051 | | pre_common_setting                |        406 | | pre_common_block_style            |        103 | | pre_common_syscache               |        102 | ..................... | pre_common_member_verify_info     |          0 | | pre_ucenter_pm_members            |          0 | | pre_common_taskvar                |          0 | | pre_common_mailqueue              |          0 | +-----------------------------------+------------+ 297 rows in set (0.04 sec)

删除数据
[root@web ~]# service mysqld stop
Shutting down MySQL..... SUCCESS!
[root@web ~]# rm -rf /data/*
[root@web ~]# ll /data/
total 0

准备恢复
[root@web ~]# innobackupex --apply-log /innobackup/2020-12-25_16-29-30/
innobackupex version 2.4.21 based on MySQL server 5.7.32 Linux (x86_64) (revision id: 5988af5)
xtrabackup: cd to /innobackup/2020-12-25_16-29-30/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 456397352
201225 16:43:16 completed OK!


恢复数据;[root@web ~]# innobackupex --copy-back /innobackup/2020-12-25_16-29-30/
201225 16:45:06 [01] Copying ./my6897451/wp_usermeta.frm to /data/my6897451/wp_usermeta.frm
201225 16:45:06 [01]        ...done
201225 16:45:06 [01] Copying ./my6897451/wp_postmeta.frm to /data/my6897451/wp_postmeta.frm
201225 16:45:06 [01]        ...done
201225 16:45:06 completed OK!

更改数据文件属主属组
[root@web data]# chown -R mysql.mysql /data
启动mysqld
[root@web data]# service mysqld start
Starting MySQL.... SUCCESS!
检查数据是否存在
mysql> use magedu;
Database changed
mysql> show tables;
+------------------+
| Tables_in_magedu |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+------------+-------+
| id | name       | sex   |
+----+------------+-------+
|  1 | zhouxiuna  | femal |
|  2 | canglaoshi | femal |
+----+------------+-------+
2 rows in set (0.00 sec)
查整个库的表的行数与删除数据前比较
mysql> select table_name,table_rows from information_schema.tables where table_schema='ultrax' order by tables.table_rows desc;
+-----------------------------------+------------+
| table_name                        | table_rows |
+-----------------------------------+------------+
| pre_common_district               |      45051 |
| pre_common_setting                |        406 |
| pre_common_block_style            |        103 |
| pre_common_syscache               |        102 |
| pre_common_smiley                 |         85 |
| pre_common_admincp_perm           |         67 |
........................................................
| pre_forum_collectionfollow        |          0 |
| pre_forum_polloption_image        |          0 |
| pre_common_block_favorite         |          0 |
+-----------------------------------+------------+
297 rows in set (0.06 sec)

第一次基于完整备份进行增量备份[root@web ~]# innobackupex --incremental /backup/incremental --user=root --password=root --incremental-basedir=/innobackup/2020-12-25_16-29-30/201225 17:12:30 Backup created in directory '/backup/incremental/2020-12-25_17-12-14/'MySQL binlog position: filename 'mysql-bin.000015', position '21800'201225 17:12:30 [00] Writing /backup/incremental/2020-12-25_17-12-14/backup-my.cnf201225 17:12:30 [00]        ...done201225 17:12:30 [00] Writing /backup/incremental/2020-12-25_17-12-14/xtrabackup_info201225 17:12:30 [00]        ...donextrabackup: Transaction log of lsn (456397371) to (456397380) was copied.201225 17:12:30 completed OK
查看备份生成的目录
[root@web ~]# ls /backup/incremental/2020-12-25_17-12-14/backup-my.cnf  db.opt  ib_buffer_pool  ibdata1.meta  my6897451  performance_schema  ultrax     xtrabackup_binlog_info  xtrabackup_info     zabbixdata           girls   ibdata1.delta   magedu        mysql      sys                 wordpress  xtrabackup_checkpoints  xtrabackup_logfile

插入数据
mysql> use magedu;Database changedmysql> select * from student;
+----+------------+-------+
| id | name       | sex   |
+----+------------+-------+
|  1 | zhouxiuna  | femal |
|  2 | canglaoshi | femal |
+----+------------+-------+
2 rows in set (0.02 sec)
mysql> insert into student (name,sex) values ('yangmi','femal'),('ajiao','femal');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

二次增量备份
[root@web ~]# innobackupex --default-file=/etc/my.cnf --user=root --password=root --incremental /backup/incremental/ --incremental-basedir=/backup/incremental/2020-12-25_17-12-14/
MySQL binlog position: filename 'mysql-bin.000015', position '22098'
201225 17:19:34 [00] Writing /backup/incremental/2020-12-25_17-19-15/backup-my.cnf
201225 17:19:34 [00]        ...done
201225 17:19:34 [00] Writing /backup/incremental/2020-12-25_17-19-15/xtrabackup_info
201225 17:19:34 [00]        ...done
xtrabackup: Transaction log of lsn (456399442) to (456399451) was copied.
201225 17:19:34 completed OK!

模拟删除数据
[root@web ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!

[root@web ~]# mv /data/ /tmp/mysqlbacku2020-12-25/

[root@web ~]# ll /data
total 0

准备恢复数据
先prepare完整备份的
[root@web ~]# innobackupex --apply-log --redo-only /innobackup/2020-12-25_16-29-30/

后prepare第一次增量备份的
[root@web ~]# innobackupex --apply-log --redo-only /innobackup/2020-12-25_16-29-30/ --incremental-dir=/backup/incremental/2020-12-25_17-12-14/

201225 17:27:40 [00] Copying /backup/incremental/2020-12-25_17-12-14//xtrabackup_info to ./xtrabackup_info
201225 17:27:40 [00]        ...done
201225 17:27:40 completed OK!

prepare第二次增量备份的
[root@web ~]# innobackupex --apply-log /innobackup/2020-12-25_16-29-30/ --incremental-dir=/backup/incremental/2020-12-25_17-19-15/

InnoDB: Shutdown completed; log sequence number 456397380
201225 17:31:21 completed OK!

再次对完整备份做--apply-log对未提交的事务做会滚
[root@web ~]# innobackupex --apply-log /innobackup/2020-12-25_16-29-30/

***注意的是***当执行完增量备份恢复后就无法基于前者做增量了，最好是执行完恢复操作后再次进行全备，并在此基础上进行增量。否则在最后对完整备份进行--apply-log时，会报以下的错误
“xtrabackup: error: The transaction log file is corrupted.
xtrabackup: error: The log was not applied to the intended LSN!”

恢复
[root@web ~]# innobackupex --default-file=/etc/my.cnf --copy-back --rsync /innobackup/2020-12-25_16-29-30/

201225 17:39:29 [01] Copying ./my6897451/wp_usermeta.frm to /data/my6897451/wp_usermeta.frm
201225 17:39:29 [01]        ...done
201225 17:39:29 [01] Copying ./my6897451/wp_postmeta.frm to /data/my6897451/wp_postmeta.frm
201225 17:39:29 [01]        ...done
201225 17:39:29 completed OK!

[root@web ~]# service mysqld start
Starting MySQL.Logging to '/data/web.err'.
....... SUCCESS!

mysql> use magedu;

Database changed

mysql> select * from student;
+----+------------+-------+
| id | name       | sex   |
+----+------------+-------+
|  1 | zhouxiuna  | femal |
|  2 | canglaoshi | femal |
|  3 | yangmi     | femal |
|  4 | ajiao      | femal |
+----+------------+-------+
4 rows in set (0.00 sec)