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  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&NBSP;SEC)
Database backup and Restore series--full backup + two incremental backup and restore