MariaDB [(none)]> select * from testdb.students;+----+------------+------+--------+| id | name | age| gender |+----+------------+------+--------+|1 | zhangsan | 15 | f||2 | lisi | 15 | m||3 | wanger | 25 | m||4 | liuwu| 24 | f||5 | wangermazi | 28 | f|+----+------------+------+--------+5 rows in set (0.00 sec)

[root@jenkins ~]# innobackupex --user=lxk --host=localhost --password=lxkpass /tmp180916 11:56:18 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!".......中间省略......180916 11:56:22 Executing UNLOCK TABLES180916 11:56:22 All tables unlocked180916 11:56:22 Backup created in directory '/tmp/2018-09-16_11-56-18'180916 11:56:22 [00] Writing backup-my.cnf180916 11:56:22 [00]...done180916 11:56:22 [00] Writing xtrabackup_info180916 11:56:22 [00]...donextrabackup: Transaction log of lsn (1602080) to (1602080) was copied.180916 11:56:23 completed OK![root@jenkins ~]# cat /tmp/2018-09-16_11-56-18/xtrabackup_checkpoints backup_type = full-backuped #备份类型:全量备份from_lsn = 0#起始lsnto_lsn = 1602080#结束lsnlast_lsn = 1602080#总共多少个lsncompact = 0recover_binlog_info = 0[root@jenkins ~]# cat /tmp/2018-09-16_11-56-18/xtrabackup_info uuid = 7a05430c-b964-11e8-889e-000c29080758name = tool_name = innobackupex#备份工具名称tool_command = --user=lxk --host=localhost --password=... /tmp#备份时使用的命令tool_version = 2.3.6#工具版本ibbackup_version = 2.3.6server_version = 5.5.60-MariaDBstart_time = 2018-09-16 11:56:18#备份开始时间end_time = 2018-09-16 11:56:22#备份结束时间lock_time = 0binlog_pos = innodb_from_lsn = 0innodb_to_lsn = 1602080partial = Nincremental = Nformat = filecompact = N compressed = N#是否启用压缩encrypted = N #是否加密

[root@jenkins ~]# innobackupex --apply-log /tmp/2018-09-16_11-56-18/180916 12:06:16 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!".......中间省略......xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 1602582180916 12:06:19 completed OK! #此处显示completed OK即表示完成

停止mysql服务删库

[root@jenkins ~]# rm -rf /var/lib/mysql/*

通过全量备份恢复数据

[root@jenkins ~]# innobackupex --copy-back /tmp/2018-09-16_11-56-18/180916 12:11:19 innobackupex: Starting the copy-back operationIMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints "completed OK!".innobackupex version 2.3.6 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )180916 12:11:19 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0180916 12:11:19 [01]...done.....中间省略.....180916 12:11:20 [01]...done180916 12:11:20 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info180916 12:11:20 [01]...done180916 12:11:20 [01] Copying ./test/db.opt to /var/lib/mysql/test/db.opt180916 12:11:20 [01]...done180916 12:11:20 completed OK! #显示completed OK即为完成

修改恢复后文件的属主,属组为mysql

[root@jenkins ~]# ls /var/lib/mysql -ltotal 28692-rw-r----- 1 root root 18874368 Sep 16 12:11 ibdata1-rw-r----- 1 root root5242880 Sep 16 12:11 ib_logfile0-rw-r----- 1 root root5242880 Sep 16 12:11 ib_logfile1drwx------ 2 root root 4096 Sep 16 12:11 mysqldrwx------ 2 root root 4096 Sep 16 12:11 performance_schemadrwx------ 2 root root 4096 Sep 16 12:11 testdrwx------ 2 root root 4096 Sep 16 12:11 testdb-rw-r----- 1 root root437 Sep 16 12:11 xtrabackup_info[root@jenkins ~]# chown -R mysql.mysql /var/lib/mysql/*[root@jenkins ~]# ll /var/lib/mysql/total 28692-rw-r----- 1 mysql mysql 18874368 Sep 16 12:11 ibdata1-rw-r----- 1 mysql mysql5242880 Sep 16 12:11 ib_logfile0-rw-r----- 1 mysql mysql5242880 Sep 16 12:11 ib_logfile1drwx------ 2 mysql mysql 4096 Sep 16 12:11 mysqldrwx------ 2 mysql mysql 4096 Sep 16 12:11 performance_schemadrwx------ 2 mysql mysql 4096 Sep 16 12:11 testdrwx------ 2 mysql mysql 4096 Sep 16 12:11 testdb-rw-r----- 1 mysql mysql437 Sep 16 12:11 xtrabackup_info

启动MySQL并查看

[root@jenkins ~]# systemctl start mariadb[root@jenkins ~]# mysqlWelcome to the MariaDB monitor.Commands end with ; or \g.Your MariaDB connection id is 2Server version: 5.5.60-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> select * from testdb.students;+----+------------+------+--------+| id | name | age| gender |+----+------------+------+--------+|1 | zhangsan | 15 | f||2 | lisi | 15 | m||3 | wanger | 25 | m||4 | liuwu| 24 | f||5 | wangermazi | 28 | f|+----+------------+------+--------+5 rows in set (0.00 sec)

全量备份:

[root@jenkins ~]# innobackupex --user=lxk --host=localhost --password=lxkpass /tmp/180916 12:17:01 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!"......中间省略.....180916 12:17:03 Executing UNLOCK TABLES180916 12:17:03 All tables unlocked180916 12:17:03 Backup created in directory '/tmp//2018-09-16_12-17-01'180916 12:17:03 [00] Writing backup-my.cnf180916 12:17:03 [00]...done180916 12:17:03 [00] Writing xtrabackup_info180916 12:17:03 [00]...donextrabackup: Transaction log of lsn (1602592) to (1602592) was copied.180916 12:17:03 completed OK!

修改数据库,进行第一次增量备份

MariaDB [testdb]> insert into students values (6,'xiaoming',20,'f');Query OK, 1 row affected (0.00 sec)MariaDB [testdb]> select * from students;+----+------------+------+--------+| id | name | age| gender |+----+------------+------+--------+|1 | zhangsan | 15 | f||2 | lisi | 15 | m||3 | wanger | 25 | m||4 | liuwu| 24 | f||5 | wangermazi | 28 | f||6 | xiaoming | 20 | f|+----+------------+------+--------+6 rows in set (0.00 sec)

[root@jenkins ~]# innobackupex --incremental /tmp --incremental-basedir=/tmp/2018-09-16_12-17-01/180916 12:23:28 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!"......中间省略.....180916 12:23:30 [00]...donextrabackup: Transaction log of lsn (1602735) to (1602735) was copied.180916 12:23:30 completed OK!

再添加一条数据,进行第二次增量备份:

MariaDB [testdb]> insert into students values (8,'daming',20,'m');Query OK, 1 row affected (0.00 sec)

[root@jenkins ~]# innobackupex --incremental /tmp --incremental-basedir=/tmp/2018-09-16_12-23-28/180916 12:29:08 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!"......中间省略.....180916 12:29:10 [00] Writing xtrabackup_info180916 12:29:10 [00]...donextrabackup: Transaction log of lsn (1603615) to (1603615) was copied.180916 12:29:10 completed OK!

准备(prepare)数据:需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。基于所有的备份将未提交的事务进行“回滚”

(1) 准备全量备份文件

[root@jenkins tmp]# innobackupex --apply-log --redo-only 2018-09-16_12-17-01180916 12:34:06 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!"......中间省略.....InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 1602592180916 12:34:06 completed OK!

(2) 准备第一次增量备份文件:

注: --incremental-dir所指的目录必须为绝对路径

[root@jenkins 2018-09-16_12-17-01]# innobackupex --apply-log --redo-only ./ --incremental-dir=/tmp/2018-09-16_12-23-28180916 12:38:17 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!"......中间省略.....180916 12:38:18 [00] Copying /tmp/2018-09-16_12-23-28/xtrabackup_info to ./xtrabackup_info180916 12:38:18 [00]...done180916 12:38:18 completed OK!

(3) 准备第二次增量备份文件:

[root@jenkins 2018-09-16_12-17-01]# innobackupex --apply-log --redo-only ./ --incremental-dir=/tmp/2018-09-16_12-29-08/180916 12:42:56 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!"......中间省略.....180916 12:42:57 [01]...done180916 12:42:57 [00] Copying /tmp/2018-09-16_12-29-08//xtrabackup_info to ./xtrabackup_info180916 12:42:57 [00]...done180916 12:42:57 completed OK!

(4) 执行回滚操作

[root@jenkins tmp]# innobackupex --apply-log /tmp/2018-09-16_12-17-01180916 12:46:15 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!"......中间省略.....xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 1604128180916 12:46:18 completed OK!

(5) 关闭MySQL并删除/var/lib/mysql/下所有文件

(6) 恢复数据：

[root@jenkins tmp]# innobackupex --copy-back 2018-09-16_12-17-01/180916 12:48:39 innobackupex: Starting the copy-back operationIMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints "completed OK!"......中间省略.....180916 12:48:40 [01] Copying ./test/db.opt to /var/lib/mysql/test/db.opt180916 12:48:40 [01]...done180916 12:48:40 completed OK!

(6) 修改/var/lib/mysql/下文件的属主、属组并启动数据库并查看

[root@jenkins mysql]# chown -R mysql.mysql /var/lib/mysql/*[root@jenkins mysql]# lltotal 28692-rw-r----- 1 mysql mysql 18874368 Sep 16 12:48 ibdata1-rw-r----- 1 mysql mysql5242880 Sep 16 12:48 ib_logfile0-rw-r----- 1 mysql mysql5242880 Sep 16 12:48 ib_logfile1drwx------ 2 mysql mysql 4096 Sep 16 12:48 mysqldrwx------ 2 mysql mysql 4096 Sep 16 12:48 performance_schemadrwx------ 2 mysql mysql 4096 Sep 16 12:48 testdrwx------ 2 mysql mysql 4096 Sep 16 12:48 testdb-rw-r----- 1 mysql mysql462 Sep 16 12:48 xtrabackup_info[root@jenkins mysql]# systemctl start mariadb[root@jenkins mysql]# mysqlWelcome to the MariaDB monitor.Commands end with ; or \g.Your MariaDB connection id is 2Server version: 5.5.60-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> select * from testdb.students;+----+------------+------+--------+| id | name | age| gender |+----+------------+------+--------+|1 | zhangsan | 15 | f||2 | lisi | 15 | m||3 | wanger | 25 | m||4 | liuwu| 24 | f||5 | wangermazi | 28 | f||6 | xiaoming | 20 | f||8 | daming | 20 | m|+----+------------+------+--------+7 rows in set (0.00 sec)