Overview
Xtrabackup (innobackupex) is a 3rd Party tool, developed by Percona and is able to provide with online backup for InnoDB and warm backup for MyISAM Tables. The backup of the InnoDB is achieved using: innobackupex tool which is a symlink to the: Xtrabackup, as follows:
Backup
The backup seems pretty simple, just specifying the username, password and the backup destination:
[root@mysqlmaster ~]# innobackupex --user=root --password=<password> /backup/ 180220 09:24:51 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!". xtrabackup: Transaction log of lsn (2628243) to (2628252) was copied. 180220 09:25:25 completed OK! [root@mysqlmaster ~]#
All the backup information including the log position and log name are recorded in the xtrabackup_info file:
[root@mysqlmaster ~]# cat /mysqldata/backup/2018-02-20_09-24-51/xtrabackup_info uuid = e4183967-1649-11e8-bdce-08002795cbb8 name = tool_name = innobackupex tool_command = --user=root --password=... /mysqldata/backup/ tool_version = 2.4.9 ibbackup_version = 2.4.9 server_version = 5.7.21-log start_time = 2018-02-20 09:24:51 end_time = 2018-02-20 09:25:25 lock_time = 0 binlog_pos = filename 'mysql-bin.000011', position '424' innodb_from_lsn = 0 innodb_to_lsn = 2628243 partial = N incremental = N format = file compact = N compressed = N encrypted = N [root@mysqlmaster ~]#
Bear in mind that the locking is done ONLY for the MyISAM and non-InnoDB tables. InnoDB tables aren't locked and Percona will apply the redo logs to make the backup consistent as follows:
Prepare backup
In order for the backup to become consistent, we have to apply the logs on it.
[root@mysqlmaster backup]# innobackupex --apply-log /backup/2018-06-08_14-27-54/ 180608 14:31:23 innobackupex: Starting the apply-log operation IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!". innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4) xtrabackup: cd to /mysqldata/backup/2018-06-08_14-27-54/ xtrabackup: This target seems to be not prepared yet. InnoDB: Number of pools: 1 xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2790154) xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 8388608 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . InnoDB: page_cleaner: 1000ms intended loop took 6991ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.) InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 2790440 180608 14:31:35 completed OK! [root@mysqlmaster backup]#