Database backup and Restore series--full backup + two incremental backup and restore

Scenario: Sunday full Backup, Monday, Tuesday incremental backup, Wednesday problems, found after a few minutes;

Installation of the database: Yum install mariadb-server-ysystemctl start Mariadbyum install percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm On the 10.2 high version of the default instant innodb_file_per_table, the version here is 5.5, temporarily not open, you need to manually write to the configuration file. [Email protected] ~]$ 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

[[email protected] ~] $mysql  < hellodb_InnoDB.sql  full backup:[[email protected]  ~] $mkdir  -pv /backups[[email protected] ~] $ll  /backupstotal 0[[email  Protected] ~] $innobackupex  /backups/Monday data modified; Nightly incremental backup of data; 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 ', ' I ', ' F '); query ok, 1 row affected  (0.01&NBSP;SEC) [[email protected] ~] $mkdir  / backups/{inc1,inc2} -pvmkdir: created directory  '/backups/inc1 ' mkdir: created  directory  '/backups/inc2 ' [[email protected] ~] $innobackupex  --incremental /backups/inc1/ --incremental-basedir=/backups/2018-02-25_15-21-53/    --incremental / BACKUPS/INC1/refers to the incremental backup, and the location of the backup;     --incremental-basedir=/backups/2018-02-25_15-21-53/ is an incremental backup based on which full backup or incremental backup; [[email protected] ~] $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[[email protected] ~] $ll  /backups/inc1/total  0drwxr-x--- 6 root root 213 feb 25 15:31 2018-02-25_15-31-17 change the data in Tuesday, and make incremental backups; mariadb [hellodb]> insert students (Name,age,gender)  values (' B ', ' ", ' F '); query ok, 1 row affected  (0.01&NBSP;SEC) [[email protected] ~] $innobackupex  --incremental /backups/Inc2/ --incremental-basedir=/backups/inc1/2018-02-25_15-31-17/[[email protected] ~] $du  -sh  /backups/23m    /backups/[[email protected] ~] $SCP  -pr /backups/  192.168.27.17:/app/[[email protected] ~] $ls  /app/backups/2018-02-25_15-21-53   inc1  inc2[[email protected] ~]$

Database restore and data grooming operations:

Backup restore:     The MySQL service is not started during the restore process; Data pre-collation:     [[email protected] ~]$ innobackupex --apply-log --redo-only /app/backups/2018-02-25_15-21-53/         --redo-only indicates that there is a corresponding incremental backup; Merge data from the first incremental backup into a full backup; [[email protected] ~] $innobackupex  --apply-log --redo-only /app/backups/2018-02-25_15-21-53/ --incremental-dir=/app/backups/ inc1/2018-02-25_15-31-17/merge data from the second incremental backup into a full backup; [[email protected] ~] $innobackupex  --apply-log  --redo-only /app/backups/2018-02-25_15-21-53/ --incremental-dir=/app/backups/inc2/2018-02-25_ 15-34-00/If the data directory is not empty, then you can delete him; [[email protected] ~] $ls  /var/lib/mysql/Copy the collated data into the data directory; [email  protected] ~] $innobackupex  --copy-back /app/backups/2018-02-25_15-21-53/[[email  protected] ~] $ll  /var/lib/mysql/total 18444drwxr-x--- 2 root root       146&nbsp 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[[email protected] ~]$[[email  Protected] ~] $chown  -r mysql.mysql /var/lib/mysql/[[email protected] ~] $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[[email protected] ~]$

Start the database and check the database data:

[[email protected] ~] $systemctl  start mariadb[[email protected] ~] $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&nbsP 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)

Database backup and Restore series--full backup + two incremental backup and restore