MARIADB Data Backup Recovery xtrabackup use

the content of the Enterprise test Environment usage Summary.

Operating System CentOS7

The idea of restoring a database or a table:

First, it is important to view the lost database

The point to be aware of is to do the advantages and disadvantages of database recovery, restore the library in this period of time to the company's losses, or directly discard the library to the company's losses, according to their business situation to decide.
If this is an important database recovery method:
1. Stop the service and hang the maintenance page.
2. A new database to ensure data consistency to find.

3. At the same time the failure of the library to backup.

4. Back up before restoring. After the reply is complete. SCP Binlog log.
5. Get Backup bin location. Gets the binlog position before the failure.
6. Start, Test.
7. External use, start service.
8. Back up again, optional, suggest a backup again.

Two. Non-important methods of data recovery

Find the right time to stop the service.

If no backup can be manually recovered based on the action log,

Not explained in detail

Iii.use of Xtrabackup

https://www.percona.com/#xtrabackup官网

Xtrabackup is an open-source alternative to InnoDB hotbackup, a database backup tool:
Xtrabackup Function Introduction:
1. Implement incremental backups
2. Can be on-line hot standby for InnoDB engine table
3. A physical backup of the INNODB/XTRADB engine's tables can be done online
4.xtrabackup is the open source MySQL Backup tool, physical backup
5.xtrabackup compared to mysqldump
Support for online backup, but logical backup, inefficient.

Copy files do not guarantee data-induced, first copy the file and then produce a thread to view the log if the log file is changed to copy, after the copy will also have application log operation, and finally give you only one point in time.

It is not recommended to use the Percona Yum Warehouse, which will upgrade the other LIB packages in MySQL. I've stepped on the pit.

From official website wget xtrabackup RPM installation package

[Root@localhost] wget https://www.percona.com/downloads/XtraBackup/XtraBackup-2.2.8/binary/redhat/7/x86_64/ percona-xtrabackup-2.2.8-5059.el7.x86_64.rpm

Install the wget RPM installation package

[Root@localhost]# yum-y Localinstall percona-xtrabackup-2.2.8-5059.el7.x86_64.rpm

Iv. Global backup and recovery databases

If the backup data is too large, you can use compression, recommended uncompressed backup after 2 days in compression, if the compressed data is too large to consider when data recovery will occupy time.
1. Backup MySQL data to/data/mysql/backup/default to create a backup timestamp directory in the backup directory

[Root@localhost percona]# Innobackupex--defaults-file= "/etc/my.cnf"--user= "username"--password= "password"--socket=/var/lib/ mysql/mysql.sock/data/mysql/backup/

Backup successful

Data after backup is not immediately available and requires preprocessing of backed-up data

2. Preprocessing (preprocessing and application log), after the completion of the backup data will be complete data. After preprocessing the backup data, you need to restore the data that can be directly MV Backup to MySQL and directory can be used. It is recommended that you do preprocessing after each backup to facilitate the recovery of data.
If you use the memory option, the log processing speed is very fast and the memory size is set according to your actual situation.
[Root@localhost percona]# Innobackupex--defaults-file= "/etc/my.cnf"--user=root--socket=/var/lib/mysql/mysql.sock --apply-log--use-memory=1g/data/mysql/backup/2016-09-14_21-01-38

General Recovery Database

1. Stop the database First
Stop the database before restoring and delete data and log files

[Root@localhost ~]# Systemctl Stop Mariadb.service

############################################################################################################### #########

Backup only for one database

--database is the database that specifies that you want to back up
[Root@localhost ~]# innobackupex--user= user name--password= password--defaults-file=/etc/my.cnf Mysql.sock--database=app/data/mysql/backup Full Backup incremental backup and its recovery

01. Incremental backups are based on all of the backup files of a database, and in just one full backup, our current incremental backup is based on all the backups we have just made/2016-09-15_01-39-48
The following two parameters need to be noted:
--incremental-basedir point to a fully-prepared directory;
--incremental the directory that points to the incremental backup;

02. Start an Incremental backup
Innobackupex--defaults-file=/etc/my.cnf--socket=/var/lib/mysql/mysql.sock--user=root--password=123456-- incremental-basedir=/data/mysql/backup/2016-09-15_01-39-48--incremental/data/mysql/backup/

03. See if there are any backup files, the latest profile is the incremental backup file we just made
[Root@localhost ~]# ls/data/mysql/backup/
2016-09-15_01-39-48 2016-09-15_01-55-09 (folders resulting from incremental backups)
[Root@localhost ~]# du-sh/data/mysql/backup/*
19m/data/mysql/backup/2016-09-15_01-39-48
1.3m/data/mysql/backup/2016-09-15_01-55-09 (folders resulting from incremental backups)

In the following file we can find that the incremental backup file From_lsn exactly equal to the to_lsn of all backups
[Root@localhost ~]# cat/data/mysql/backup/2016-09-15_01-39-48/xtrabackup_checkpoints #查看全局备份
Backup_type = full-backuped
FROM_LSN = 0
TO_LSN = 1597945
LAST_LSN = 1597945
Compact = 0
[Root@localhost ~]# cat/data/mysql/backup/2016-09-15_01-55-09/xtrabackup_checkpoints #查看增量备份
Backup_type = Incremental
FROM_LSN = 1597945
TO_LSN = 1597945
LAST_LSN = 1597945
Compact = 0
[Root@localhost ~]#

04. Write something to the database incremental backups based on incremental backups, as follows

Innobackupex--defaults-file=/etc/my.cnf--socket=/var/lib/mysql/mysql.sock--user=root--incremental-basedir=/ data/mysql/backup/2016-09-15_01-39-48--incremental/data/mysql/backup/

In the use of incremental recovery can no longer use the above global backup recovery method is an error encountered, you need to use the following methods to restore global backup,

05. Incremental Recovery

Innobackupex--apply-log--redo-only/data/mysql/backup/2016-09-15_01-39-48 #恢复全局备份 Innobackupex--apply-log-- redo-only/data/mysql/backup/2016-09-15_01-39-48--incremental-dir=/data/mysql/backup/2016-09-15_01-55-09
Innobackupex--apply-log--redo-only/data/mysql/backup/2016-09-15_01-39-48--incremental-dir=/data/mysql/backup/ 2016-09-15_02-05-06

Global Backup-generated folders 2016-09-15_01-39-48
folders resulting from first incremental backup 2016-09-15_01-55-09
Second incremental backup 2016-09-15_02-05-06