Last article This article briefly introduces mysqldump's backup and recovery of database. This article introduces another backup tool xtrabackup. In the era of InnoDB transaction engine flooding, xtrabackup can well support hot backup of database, which is very gratifying,

Xtrabackup is available in the epel source, so you can install it directly using yum;

A full backup is a direct connection to the MySQL service. A target directory is enough;

[root@www ~]# innobackupex  --user=root --host=localhost --port=3306 --password='123456'  /data/mydata
181105 20:31:30 innobackupex: Starting the backup operation
 
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
...
181105 20:31:39 Executing UNLOCK TABLES
181105 20:31:39 All tables unlocked
181105 20:31:39 Backup created in directory '/data/mydata/2018-11-05_20-31-30'
181105 20:31:39 [00] Writing backup-my.cnf
181105 20:31:39 [00]        ...done
181105 20:31:39 [00] Writing xtrabackup_info
181105 20:31:39 [00]        ...done
xtrabackup: Transaction log of lsn (9424992) to (9424992) was copied.
181105 20:31:39 completed OK!

Then we can see several files in the backup directory

[root@www ~]# ls /data/mydata/2018-11-05_20-31-30/
backup-my.cnf  ibdata1  performance_schema  xtrabackup_checkpoints  xtrabackup_logfile
hellodb        mysql    wpsdb               xtrabackup_info         zabbix

In addition to the database, there are several special files:

Xtrabackup binlog Info: records some attribute information during the whole backup process;

[root@www ~]# cd /data/mydata/2018-11-05_20-31-30/
[root@www 2018-11-05_20-31-30]# cat xtrabackup_info
uuid = 725248b0-da18-11e8-9fcc-000c29ceaa48
name =
tool_name = innobackupex
tool_command = --user=root --host=localhost --port=3306 --password=... /data/mydata
tool_version = 2.3.6
ibbackup_version = 2.3.6
server_version = 5.5.60-MariaDB
start_time = 2018-11-05 20:31:31
end_time = 2018-11-05 20:31:39
lock_time = 0
binlog_pos =
innodb_from_lsn = 0
innodb_to_lsn = 9424992
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

backup-my.cnf: record important configuration parameters related to InnoDB storage engine;

[root@www 2018-11-05_20-31-30]# cat backup-my.cnf
# This MySQL options file was generated by innobackupex.
 
# The MySQL server
[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=5242880
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=.
innodb_undo_tablespaces=0

Xtrabackup UU checkpoints: records the type of this time and LSN of start and end;

[root@www 2018-11-05_20-31-30]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 9424992
last_lsn = 9424992
compact = 0
recover_binlog_info = 0
 
xtrabackup_binlog_info: Record the consistency coordinates of binary logs currently used;
[root@www 2018-11-05_20-55-04]# cat xtrabackup_binlog_info
binlog.000001       245

Recovery of full backup (mysql data directory is empty):

Only one copy back is needed to recover data;

[root@www 2018-11-05_20-55-04]# innobackupex --copy-back ./
181105 20:59:30 innobackupex: Starting the copy-back operation
 
IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

Incremental backup:

Compared with the previous full backup, you need two options -- incremental and -- incremental basedir;

[root@www ~]# innobackupex --incremental /data/mydata --incremental-basedir=/data/mydata/2018-11-05_20-31-30
181105 20:54:41 innobackupex: Starting the backup operation
 
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
...
181105 20:55:14 Executing UNLOCK TABLES
181105 20:55:14 All tables unlocked
181105 20:55:14 Backup created in directory '/data/mydata/2018-11-05_20-55-04'
MySQL binlog position: filename 'binlog.000001', position '245'
181105 20:55:14 [00] Writing backup-my.cnf
181105 20:55:14 [00]        ...done
181105 20:55:14 [00] Writing xtrabackup_info
181105 20:55:14 [00]        ...done
xtrabackup: Transaction log of lsn (9424992) to (9424992) was copied.
181105 20:55:14 completed OK!

Just check the contents of xtrabackup checkpoints file;

[root@www 2018-11-05_20-55-04]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 9424992
to_lsn = 9424992
last_lsn = 9424992
compact = 0
recover_binlog_info = 0

The recovery process of incremental backup is the same as that of copy back;