mysql innodb 增量备份_MySQL 的 InnoDB 数据库的增量备份方案（1） – XtraBackup | 酷客365...

原因：

原来每天都做数据备份，一般都是用的全量备份，现在数据库太大，全量非常影响性能，所以在网上找了一些办法，就找到了  XtraBackup 这个工具

前提：

MySQL数据库，InnoDB 引擎

使用：

增量备份：

[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/ #全备数据

[root@master ~]# mysql -uroot -p　　#在master上创建student库并创建testtb表插入若干数据

Enter password:

mysql> create database student;

Query OK, 1 row affected (0.03 sec)

mysql> use student;

Database changed

mysql> create table testtb(id int);

Query OK, 0 rows affected (0.07 sec)

mysql> insert into testtb values(1),(10),(99);

Query OK, 3 rows affected (0.04 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from testtb;

+------+

| id |

+------+

| 1 |

| 10 |

| 99 |

+------+

3 rows in set (0.00 sec)

mysql> quit;

Bye

#使用innobackupex进行增量备份

[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 --incremental /backups/ --incremental-basedir=/backups/2018-07-30_11-01-37/

......

180730 13:51:50 Executing UNLOCK TABLES

180730 13:51:50 All tables unlocked

180730 13:51:50 Backup created in directory '/backups/2018-07-30_13-51-47/'

MySQL binlog position: filename 'mysql-bin.000005', position '664'

180730 13:51:50 [00] Writing /backups/2018-07-30_13-51-47/backup-my.cnf

180730 13:51:50 [00] ...done

180730 13:51:50 [00] Writing /backups/2018-07-30_13-51-47/xtrabackup_info

180730 13:51:50 [00] ...done

xtrabackup: Transaction log of lsn (3158741) to (3158741) was copied.

180730 13:51:50 completed OK!

[root@master backups]# ll　　#查看备份数据

total 0

drwxr-x--- 7 root root 232 Jul 30 11:01 2018-07-30_11-01-37　　#全量备份数据目录

drwxr-x--- 8 root root 273 Jul 30 13:51 2018-07-30_13-51-47　　#增量备份数据目录

[root@master 2018-07-30_11-01-37]# cat xtrabackup_checkpoints #查看全量备份的xtrabackup_checkpoints

backup_type = full-backuped　　#备份类型为全量备份

from_lsn = 0　　#lsn从0开始

to_lsn = 3127097　　#lsn到3127097结束

last_lsn = 3127097

compact = 0

recover_binlog_info = 0

[root@master 2018-07-30_13-51-47]# cat xtrabackup_checkpoints 　　#查看增量备份的xtrabackup_checkpoints

backup_type = incremental　　#备份类型为增量备份

from_lsn = 3127097　　#lsn从3127097开始

to_lsn = 3158741　　 #lsn到啊3158741结束

last_lsn = 3158741

compact = 0

recover_binlog_info = 0

增量恢复：

(1)模拟mysql故障，删除数据目录所有数据

[root@master ~]# /etc/init.d/mysqld stop　　#模拟mysql故障，停止mysql

Shutting down MySQL.. SUCCESS!

[root@master ~]# rm -rf /usr/local/mysql/data/*　　#删除数据目录中的所有数据

(2)合并全备数据目录，确保数据的一致性

[root@master ~]# innobackupex --apply-log --redo-only /backups/2018-07-30_11-01-37/

180730 14:05:27 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.

At the end of a successful apply-log run innobackupex

prints "completed OK!".

innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

xtrabackup: cd to /backups/2018-07-30_11-01-37/

......

......

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 3127106

InnoDB: Number of pools: 1

180730 14:05:29 completed OK!

(3)将增量备份数据合并到全备数据目录当中

[root@master ~]# innobackupex --apply-log --redo-only /backups/2018-07-30_11-01-37/ --incremental-dir=/backups/2018-07-30_13-51-47/

180730 14:06:42 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.

At the end of a successful apply-log run innobackupex

prints "completed OK!".

......

......

180730 14:06:44 [00] ...done

180730 14:06:44 completed OK!

[root@master ~]# cat /backups/2018-07-30_11-01-37/xtrabackup_checkpoints

backup_type = log-applied　　#查看到数据备份类型是增加

from_lsn = 0　　#lsn从0开始

to_lsn = 3158741　　#lsn结束号为最新的lsn

last_lsn = 3158741

compact = 0

recover_binlog_info = 0

(4)恢复数据

[root@master ~]# innobackupex --copy-back /backups/2018-07-30_11-01-37/

180730 14:07:51 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.

At the end of a successful copy-back run innobackupex

prints "completed OK!".

.......

.......

180730 14:08:17 [01] ...done

180730 14:08:17 completed OK!

[root@master ~]# ll /usr/local/mysql/data/

total 77844

-rw-r----- 1 root root 79691776 Jul 30 14:08 ibdata1

drwxr-x--- 2 root root 20 Jul 30 14:08 kim

drwxr-x--- 2 root root 4096 Jul 30 14:08 mysql

drwxr-x--- 2 root root 4096 Jul 30 14:08 performance_schema

drwxr-x--- 2 root root 20 Jul 30 14:08 repppp

drwxr-x--- 2 root root 56 Jul 30 14:08 student

drwxr-x--- 2 root root 4096 Jul 30 14:08 wordpress

-rw-r----- 1 root root 21 Jul 30 14:08 xtrabackup_binlog_pos_innodb

-rw-r----- 1 root root 554 Jul 30 14:08 xtrabackup_info

[root@master ~]# chown -R mysql.mysql /usr/local/mysql/data　　#更改数据的属主属组

[root@master ~]# /etc/init.d/mysqld start　　#启动mysql

Starting MySQL.Logging to '/usr/local/mysql/data/master.err'.

.. SUCCESS!

[root@master ~]# mysql -uroot -p -e "show databases;"　　#查看数据是否恢复

Enter password:

+--------------------+

| Database |

+--------------------+

| information_schema |

| kim |

| mysql |

| performance_schema |

| repppp |

| student |

| wordpress |

+--------------------+