Xtrabackup backup MySQL

Innobackupex is a script inside the Xtrabackup package that uses different script commands for different versions of MySQL, as well as processing for the MyISAM engine and InnoDB engine, and handles MyISAM to add read locks on their own.

Required permissions for MySQL user backup

RELOAD, LOCK TABLES (unless--no-lock parameter is specified) for flush TABLES with read LOCK capability
REPLICATION CLIENT has the ability to get binary log file backup
CREATE Tablespace has the ability to restore the entire tablespace and import tables
SUPER is used to start/Shut down the replication thread environment from the server

Innobackupex Command Parameters

--user//Specify MySQL user to backup MySQL

--password//Specify MYQSL user password

--defaults-file//Specifies the my.cnf file for the MySQL instance you want to back up, and if the default is/ETC/MY.CNF, the secondary parameter can be omitted.

　　--no-timestamp//Custom build directory, this directory does not exist previously

　　--USE-MEMORY=4G//This parameter is used to control the amount of memory used by the backup, which defaults to 100m! Generally used in conjunction with--apply-log.

--apply-log//Backup data is not immediately available for recovery because the data that is backed up contains uncommitted data that needs to be rolled back, the undo operation, and also the completed transactions in the Redo log file are not written to the data file, redo operation to ensure that the data file Consistency. Redo the committed transaction and rollback the uncommitted transaction.

--copy-back//To restore the backup data

--redo-only//When doing an incremental restore, the data file for the incremental backup needs to redo the committed transaction in the log file. This parameter merges both full-and incremental-backed data files, which is not required for the last incremental backup.

--compress//Compression option, this option is incompatible with--stream=tar and is compatible with--stream=xbstream only.

--slave-info//When backing up from the server, use this parameter to record the log and offset of master for making a new slave server. In general, full backups, master binary log files, and offset points are recorded in the Xtrabackup_binlog_info file.

--database//Backup only the specified database

　　--socket //Specify MySQL socket file location

--host//Specify MySQL Host

--port//Specify MySQL Port

--stream=[tar]//support "stream" function, that is, the backup data can be transferred through stdout to the TAR program for archiving, rather than the default directly saved to a backup directory

--incremental-basedir need to specify a directory for the previous full backup
--incremental will generate a new directory in the directory to hold the incremental backup data

Full backup process:

1. The first is to allow Innobackupex to generate the backup directory in its own directory, and the second custom build backup directory

1.[[email protected] ~]# Innobackupex--user=root--PASSWORD=666666/BACKUP/2. [Email protected] 2015-08-07_14-51-50]# Innobackupex--user=root--password=666666  --no-timestamp/backup/' date +%f-%h-%m-%s
Success Sign
150807 14:51:53 innobackupex:connection to database server closed
150807 14:51:53 innobackupex:completed ok!

2. View the flag point of the transaction termination thread,/backup/2015-08-07-14-57-56/xtrabackup_checkpoints

3. You can simulate inserting data into a table in MySQL, committing a transaction, and timing the transaction that has been committed after the database backup is recoverable.

mysql> INSERT into student (name) VALUES (' David '); Query OK, 1 row affected, 1 Warning (0.10 sec) mysql> insert into student (name) VALUES (' Jane '); Query OK, 1 row affected, 1 Warning (0.00 sec)
Mysql> commit;
Query OK, 0 rows Affected (0.00 sec)
Mysql> select * from student;                      +----+-------+-----+------+| ID | Name  | Cid  |+----+-------+-----+------+|  1 | Sean  |  |    6 | |  5 | Alice |   0 | NULL | |  6 | Tom   |   0 | NULL | |  7 | David |   0 | NULL | |  8 | Jane  |   0 | NULL |+----+-------+-----+------+5 rows in Set (0.00 sec)

4. Delete all database impersonation corruption

5. If you do not make an incremental backup, you need to perform--apply-log to recover, this must be done, the uncommitted transaction is rolled back, the committed transaction is redo, and any time the committed transaction is resumed, even after the backup.

[Email protected] 2015-08-07_14-51-50]# Innobackupex--apply-log/backup/2015-08-07_14-51-50/  

6. Then restore, only restore to backup the moment the transaction has been committed.

Note: 1. Because it is a full-Library restore, you need to ensure that MySQL DataDir specified below the directory is empty, otherwise it will be an error.

2. Best MSYQL Close

3. Modify the database directory for MySQL permissions

[Email protected] mydata]# chown-r mysql:mysql./*

Mysql> select * from student;
+----+-------+-----+------+
| ID | name | Age | Cid |
+----+-------+-----+------+
| 1 |  Sean |    22 | 6 |
| 5 |   Alice | 0 | NULL |
| 6 |   Tom | 0 | NULL |
| 7 |   David | 0 | NULL |
| 8 |   Jane | 0 | NULL |
+----+-------+-----+------+
5 rows in Set (0.00 sec)

7. If you do an incremental backup, the incremental backup is backed up on a full backup basis, only the InnoDB engine is supported, and the MyISAM engine cannot achieve incremental backups.

This is an incremental backup from the 3rd step.

--incremental-basedir need to specify a directory for the previous full backup,
--incremental will generate a new directory in the directory to hold the incremental backup data

[Email protected] backup]# Innobackupex--user=root--password=666666--incremental/backup/--incremental-basedir=/ Backup/2015-08-07_14-51-50/

[[email protected] backup]# Lltotal 8drwxr-xr-x 9 root root 4096  7 15:16 2015-08-07_14-51-50//Full backup directory DRWXR-XR -X 9 root root 4096  

Looking at the checkpoints, you can see that it is the end of the transaction starting at this point of 3425959 to 3432586 at this point.
[email protected] 2015-08-07_16-15-46]# cat xtrabackup_checkpoints backup_type = INCREMENTALFROM_LSN = 3425959TO_LSN = 3 432586LAST_LSN = 3432586compact = 0

8. Make a second incremental backup, before simulating inserting the data through the 3rd step operation this transaction is not committed.

mysql> INSERT into student (name) VALUES (' Bruce '); Query OK, 1 row affected, 1 Warning (0.00 sec) mysql> select * from student;                      +----+-------+-----+------+| ID | Name  | Cid  |+----+-------+-----+------+|  1 | Sean  |  |    6 | |  5 | Alice |   0 | NULL | |  6 | Tom   |   0 | NULL | |  7 | David |   0 | NULL | |  8 | Jane  |   0 | NULL | | 10 | Bruce |   0 | NULL |+----+-------+-----+------+6 rows in Set (0.00 sec)

9. Do a second incremental backup, where incremental-basedir points to the path of the first backup

[Email protected] backup]# Innobackupex--user=root--password=666666--incremental/backup/--incremental-basedir=/ backup/2015-08-07_16-15-46/

View Chekpoints because there is no commit transaction, the start and end points of the transaction do not change.

Backup_type = Incremental
FROM_LSN = 3432586
TO_LSN = 3432586
LAST_LSN = 3432586
Compact = 0

10. Incremental backup recovery, performing--apply-log--redo-only for full and first incremental backups, respectively, for the last incremental backup performed at two times--apply-log

[Email protected] backup]# Innobackupex--apply-log--user=root--password=666666--redo-only  /backup/2015-08-07 _14-51-50/[[email protected] backup]# Innobackupex--apply-log--redo-only/backup/2015-08-07_14-51-50/-- Incremental-dir=/backup/backup/2015-08-07_16-15-46/[[email protected] backup]# Innobackupex--apply-log/backup/ 2015-08-07_14-51-50/--incremental-dir=/backup/2015-08-07_16-44-04/

11. Perform the 10 operation and then the recovery and full Restore command, as the above operation consolidates the contents of the incremental backup into the first backed up file.

[Email protected] backup]# Innobackupex--copy-back/backup/2015-08-07_14-51-50/

　　

Xtrabackup backup MySQL