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

Scenario: full backup on Sunday, incremental backup on Monday and Tuesday, problems occurred on Wednesday and found a few minutes later; database installation: yum install mariadb-server-ysystemctl start mariadbyum install percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm defaults to real-time innodb_file_per_table; on the high version of 10.2.The version here is 5.5, which is temporarily disabled and needs to be manually written to the configuration file. [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_ [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 Query OK, 1 row affected [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 backup 15-21-53 /-- incremental / backups/inc1/ means that this is an incremental backup and the location of the backup -- incremental-basedir=/backups/2018-02-25 backup 15-21-53 / refers to the incremental backup based on which full backup or incremental backup [root@mysql ~] $ll / backups/total 0drwxxmuri-6 root root 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 Makoto-6 root root 213 Feb 25 15:31 2018-02-25 15-31-17 Tuesday to modify the data and make incremental backups MariaDB [hellodb] > insert students (name,age,gender) values Query OK, 1 row affected [root@mysql ~] $innobackupex-- incremental / backups/inc2/-- incremental-basedir=/backups/inc1/2018-02-25 sec 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 cycles 15-21-53 inc1 inc2 [root@mysql17 ~] $

Database restore and data collation operations:

Backup restore: the MySQL service is not started during the restore process; data pre-processing: [root@mysql17 ~] $innobackupex-- apply-log-- redo-only / app/backups/2018-02-25 backup 15-21-53 /-- redo-only indicates that there is a corresponding incremental backup; merge the data from the first incremental backup into the full backup [root@mysql17 ~] $innobackupex-- apply-log-- redo-only / app/backups/2018-02-25 backup 15-21-53 /-- incremental-dir=/app/backups/inc1/2018-02-25 backup 15-31-17 / merge the data from the second incremental backup into the full backup [root@mysql17 ~] $innobackupex-- apply-log-- redo-only / app/backups/2018-02-25room15-21-53 /-- incremental-dir=/app/backups/inc2/2018-02-25room15-34-00 / if the data directory is not empty, you can delete it; [root@mysql17 ~] $ls / var/lib/mysql/ copies the sorted data to the data directory [root@mysql17 ~] $innobackupex-- copy-back / app/backups/2018-02-25 October 15-21-53 / [root@mysql17 ~] $ll / var/lib/mysql/total 18444drwxrkashi-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_ in [root @ mysql17 ~] $[root@mysql17] $chown-R mysql.mysql / var/lib/mysql/ [root@mysql17 ~] $ll / var/lib/mysql/total 18444drwxr hellodb-rw-r--2 mysql mysql 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_ [r oot@mysql17 ~] $

Start the database and check the database data:

[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) |