MySQL uses XtraBackup to summarize detailed Incremental Backup processes

MySQL uses XtraBackup to summarize detailed Incremental Backup processes

MySQL uses XtraBackup to summarize detailed Incremental Backup processes

For Xtrabackup download, installation, and full backup, see:

MySQL management-using XtraBackup for Hot Backup

MySQL open-source backup tool Xtrabackup backup deployment

MySQL Xtrabackup backup and recovery

Use XtraBackup to implement MySQL master-slave replication and quick deployment [master-slave table lock-free]

Install and use Xtrabackup from Percona to back up MySQL

1. Create a mysql backup user

Mysql-uroot -- password = ""-e "create user 'backup '@ '2017. 192. %' identified by '20160301 '";

Mysql-uroot -- password = ""-e "grant reload, lock tables, replication client, create tablespace, SUPERON *. * TO 'backup '@' 192. 168.% '";

2. xtrabackup for full backup

Backup command:

Xtrabackup -- defaults-file =/etc/my. cnf -- user = backup -- password = "123456" -- port = 3306 -- backup -- target-dir =/data/backups/mysql/full_incre_$ (date + % Y % m % d)

PS: 2>/tmp/fullbackup. log: Put the backup information into the file.

The backup process information is as follows:
[Root @ data01 ~] # Xtrabackup -- defaults-file =/etc/my. cnf -- user = backup -- password = "123456" -- port = 3306 -- backup -- target-dir =/data/backups/mysql/full_incre_$ (date + % Y % m % d)
Xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id :)
Xtrabackup: uses posix_fadvise ().
Xtrabackup: cd to/home/data/mysql/data
Xtrabackup: open files limit requested 0, set to 1024
Xtrabackup: using the following InnoDBconfiguration:
Xtrabackup: innodb_data_home_dir = ./
Xtrabackup: innodb_data_file_path = IBdata1: 128 M, IBdata2: 128 M: autoextend
Xtrabackup: innodb_log_group_home_dir = ./
Xtrabackup: innodb_log_files_in_group = 3
Xtrabackup: innodb_log_file_size = 67108864
> Log scanned up to (423920168)
Xtrabackup: Generating a list of tablespaces
[01] Copying./IBdata1 to/data/backups/mysql/full_incre_20150319/IBdata1
> Log scanned up to (423920168)
> Log scanned up to (423920168)
[01]... done
[01] Copying./IBdata2 to/data/backups/mysql/full_incre_20150319/IBdata2
> Log scanned up to (423920168)
> Log scanned up to (423920168)
[01]... done
[01] Copying./mysql/slave_master_info.ibdto/data/backups/mysql/full_incre_20150319/mysql/slave_master_info.ibd
[01]... done
[01] Copying./mysql/slave_relay_log_info.ibdto/data/backups/mysql/full_incre_20150319/mysql/slave_relay_log_info.ibd
[01]... done
[01] Copying./mysql/innodb_index_stats.ibdto/data/backups/mysql/full_incre_20150319/mysql/innodb_index_stats.ibd
[01]... done
[01] Copying./mysql/slave_worker_info.ibdto/data/backups/mysql/full_incre_20150319/mysql/slave_worker_info.ibd
[01]... done
[01] Copying./mysql/innodb_table_stats.ibdto/data/backups/mysql/full_incre_20150319/mysql/innodb_table_stats.ibd
[01]... done
[01] Copying./sbtest/sbtest1.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest1.ibd
[01]... done
[01] Copying./sbtest/sbtest5.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest5.ibd
[01]... done
[01] Copying./sbtest/sbtest8.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest8.ibd
[01]... done
[01] Copying./sbtest/sbtest4.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest4.ibd
[01]... done
[01] Copying./sbtest/sbtest2.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest2.ibd
[01]... done
[01] Copying./sbtest/sbtest6.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest6.ibd
[01]... done
[01] Copying./sbtest/sbtest7.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest7.ibd
[01]... done
[01] Copying./sbtest/sbtest3.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest3.ibd
[01]... done
[01] Copying./test/t1.ibd to/data/backups/mysql/full_incre_20150319/test/t1.ibd
[01]... done
[01] Copying./test/t3.ibd to/data/backups/mysql/full_incre_20150319/test/t3.ibd
[01]... done
[01] Copying./test/t. ibd to/data/backups/mysql/full_incre_20150319/test/t. ibd
[01]... done
[01] Copying./user_db/t1.ibd to/data/backups/mysql/full_incre_20150319/user_db/t1.ibd
[01]... done
[01] Copying./user_db/test. ibd to/data/backups/mysql/full_incre_20150319/user_db/test. ibd
[01]... done
[01] Copying./user_db/test2.ibd to/data/backups/mysql/full_incre_20150319/user_db/test2.ibd
[01]... done
[01] Copying./user_db/t3.ibd to/data/backups/mysql/full_incre_20150319/user_db/t3.ibd
[01]... done
[01] Copying./user_db/UC_USER.ibd to/data/backups/mysql/full_incre_20150319/user_db/UC_USER.ibd
> Log scanned up to (423920168)
[01]... done
[01] Copying./user_db/t. ibd to/data/backups/mysql/full_incre_20150319/user_db/t. ibd
[01]... done
Xtrabackup: The latest check point (forincremental): '123'
Xtrabackup: Stopping log copying thread.
.> Log scanned up to (423920168)

Xtrabackup: Transaction log of lsn (423920168) to (423920168) was copied.
[Root @ data01 ~] #

3. xtrabackup Incremental Backup

3.1 first input a table to achieve the first incremental data: