数据库的备份与还原系列——全备份+两增量的备份与还原

场景：周日全备份，周一，周二增量备份，周三出现问题，数分钟后发现；

数据库的安装：yum install mariadb-server -ysystemctl start mariadbyum install percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm 在10.2的高版本上默认即时innodb_file_per_table；这里的版本是5.5，暂时没开启，需要手动写到配置文件中。[root@mysql ~]$ vim /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0innodb_file_per_tablelog_bin

[root@mysql ~]$mysql < hellodb_InnoDB.sql 全备份：[root@mysql ~]$mkdir -pv /backups[root@mysql ~]$ll /backupstotal 0[root@mysql ~]$innobackupex /backups/周一数据修改后；晚上对数据进行增量备份；MariaDB [(none)]> use hellodb;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [hellodb]> insert students(name,age,gender) values('a','28','F');Query OK, 1 row affected (0.01 sec)[root@mysql ~]$mkdir /backups/{inc1,inc2} -pvmkdir: created directory '/backups/inc1'mkdir: created directory '/backups/inc2'[root@mysql ~]$innobackupex --incremental /backups/inc1/ --incremental-basedir=/backups/2018-02-25_15-21-53/    --incremental /backups/inc1/是指这是增量备份，以及备份的位置；    --incremental-basedir=/backups/2018-02-25_15-21-53/是指基于哪个全备份或者增量备份的增量备份；[root@mysql ~]$ll /backups/total 0drwxr-x--- 6 root root 187 Feb 25 15:21 2018-02-25_15-21-53drwxr-xr-x 3 root root  33 Feb 25 15:31 inc1drwxr-xr-x 2 root root   6 Feb 25 15:25 inc2[root@mysql ~]$ll /backups/inc1/total 0drwxr-x--- 6 root root 213 Feb 25 15:31 2018-02-25_15-31-17周二的时候修改数据，并进行增量备份；MariaDB [hellodb]> insert students(name,age,gender) values('b','28','F');Query OK, 1 row affected (0.01 sec)[root@mysql ~]$innobackupex --incremental /backups/inc2/ --incremental-basedir=/backups/inc1/2018-02-25_15-31-17/[root@mysql ~]$du -sh /backups/23M    /backups/[root@mysql ~]$scp -pr /backups/ 192.168.27.17:/app/[root@mysql17 ~]$ls /app/backups/2018-02-25_15-21-53  inc1  inc2[root@mysql17 ~]$

数据库的还原与数据整理操作：

备份还原：    还原的过程中MySQL服务是不启动的；数据预整理：    [root@mysql17 ~]$innobackupex --apply-log --redo-only /app/backups/2018-02-25_15-21-53/        --redo-only表示有相应的增量备份；将第一次增量备份的数据合并到完全备份中；[root@mysql17 ~]$innobackupex --apply-log --redo-only /app/backups/2018-02-25_15-21-53/ --incremental-dir=/app/backups/inc1/2018-02-25_15-31-17/将第二次增量备份的数据合并到完全备份中；[root@mysql17 ~]$innobackupex --apply-log --redo-only /app/backups/2018-02-25_15-21-53/ --incremental-dir=/app/backups/inc2/2018-02-25_15-34-00/如果数据目录非空，那么就可以删除他；[root@mysql17 ~]$ls /var/lib/mysql/将整理后的数据复制到数据目录中；[root@mysql17 ~]$innobackupex --copy-back /app/backups/2018-02-25_15-21-53/[root@mysql17 ~]$ll /var/lib/mysql/total 18444drwxr-x--- 2 root root      146 Feb 25 02:48 hellodb-rw-r----- 1 root root 18874368 Feb 25 02:48 ibdata1drwxr-x--- 2 root root     4096 Feb 25 02:48 mysqldrwxr-x--- 2 root root     4096 Feb 25 02:48 performance_schemadrwxr-x--- 2 root root       20 Feb 25 02:48 test-rw-r----- 1 root root      481 Feb 25 02:48 xtrabackup_info[root@mysql17 ~]$[root@mysql17 ~]$chown -R mysql.mysql /var/lib/mysql/[root@mysql17 ~]$ll /var/lib/mysql/total 18444drwxr-x--- 2 mysql mysql      146 Feb 25 02:48 hellodb-rw-r----- 1 mysql mysql 18874368 Feb 25 02:48 ibdata1drwxr-x--- 2 mysql mysql     4096 Feb 25 02:48 mysqldrwxr-x--- 2 mysql mysql     4096 Feb 25 02:48 performance_schemadrwxr-x--- 2 mysql mysql       20 Feb 25 02:48 test-rw-r----- 1 mysql mysql      481 Feb 25 02:48 xtrabackup_info[root@mysql17 ~]$

启动数据库，核对数据库数据：

[root@mysql17 ~]$systemctl start mariadb[root@mysql17 ~]$mysqlWelcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 2Server version: 5.5.56-MariaDB MariaDB ServerCopyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> select * from hellodb.students;+-------+---------------+-----+--------+---------+-----------+| StuID | Name          | Age | Gender | ClassID | TeacherID |+-------+---------------+-----+--------+---------+-----------+|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 ||     2 | Shi Potian    |  22 | M      |       1 |         7 ||     3 | Xie Yanke     |  53 | M      |       2 |        16 ||     4 | Ding Dian     |  32 | M      |       4 |         4 ||     5 | Yu Yutong     |  26 | M      |       3 |         1 ||     6 | Shi Qing      |  46 | M      |       5 |      NULL ||     7 | Xi Ren        |  19 | F      |       3 |      NULL ||     8 | Lin Daiyu     |  17 | F      |       7 |      NULL ||     9 | Ren Yingying  |  20 | F      |       6 |      NULL ||    10 | Yue Lingshan  |  19 | F      |       3 |      NULL ||    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL ||    12 | Wen Qingqing  |  19 | F      |       1 |      NULL ||    13 | Tian Boguang  |  33 | M      |       2 |      NULL ||    14 | Lu Wushuang   |  17 | F      |       3 |      NULL ||    15 | Duan Yu       |  19 | M      |       4 |      NULL ||    16 | Xu Zhu        |  21 | M      |       1 |      NULL ||    17 | Lin Chong     |  25 | M      |       4 |      NULL ||    18 | Hua Rong      |  23 | M      |       7 |      NULL ||    19 | Xue Baochai   |  18 | F      |       6 |      NULL ||    20 | Diao Chan     |  19 | F      |       7 |      NULL ||    21 | Huang Yueying |  22 | F      |       6 |      NULL ||    22 | Xiao Qiao     |  20 | F      |       1 |      NULL ||    23 | Ma Chao       |  23 | M      |       4 |      NULL ||    24 | Xu Xian       |  27 | M      |    NULL |      NULL ||    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL ||    26 | a             |  28 | F      |    NULL |      NULL ||    27 | b             |  28 | F      |    NULL |      NULL |+-------+---------------+-----+--------+---------+-----------+27 rows in set (0.01 sec)