Use XtraBackup to back up and restore the MariaDB Database

When innobakupex is used for backup, it will call xtrabackup to back up all InnoDB tables, copy all files related to table structure definition (. frm), and MyISAM, MERGE,

When innobakupex is used for backup, it will call xtrabackup to back up all InnoDB tables, copy all files related to table structure definition (. frm), and MyISAM, MERGE,

Use xtrabackup for backup
Introduction to Xtrabackup
Xtrabackup is a mysql database backup tool provided by percona. According to the official introduction, Xtrabackup is the only open-source tool in the world that can perform hot backup for innodb and xtradb databases. Features:
(1) The backup process is fast and reliable;
(2) The backup process will not interrupt ongoing transactions;
(3) Saving disk space and traffic based on compression and other functions;
(4) automatic backup check;
(5) Fast Restoration;

Official introduction and https://www.percona.com/software/percona-xtrabackup
Install xtrabackup
The official system has rpm package version of the software, you can download the rpm package version of the software for direct installation, because xtrabackup is written using perl scripts, so you need to install perl-DBD-MySQL or else report dependencies.
[Root @ MariaDB ~] # Yum-y install perl-DBD-MySQL
[Root @ MariaDB ~] # Rpm-ivh percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm

Innobackupex: client tool that connects to mysqld using mysql protocol. offline backup is not supported, but offline recovery is supported.

-------------------------------------- Split line --------------------------------------

Install LAMP (Apache with MariaDB and PHP) in CentOS/RHEL/Scientific Linux 6)

Implementation of MariaDB Proxy read/write splitting

How to compile and install the MariaDB database in Linux

Install MariaDB database using yum in CentOS

Install MariaDB and MySQL

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

Install MariaDB on the Ubuntu 14.04 (Trusty) Server

-------------------------------------- Split line --------------------------------------

One full backup and recovery
1. Full backup
When innobakupex is used for backup, it will call xtrabackup to back up all InnoDB tables and copy all the files related to table structure definition (. frm), and files related to MyISAM, MERGE, CSV, and ARCHIVE tables. Files related to triggers and database configuration information are also backed up. These files are saved to a time command directory.

When innobackupex is used for backup, you can also use the -- no-timestamp Option to prevent the command from automatically creating a directory named after time. As a result, the innobackupex command creates a BACKUP-DIR directory to store backup data.

Example:
[Root @ MariaDB ~] # Innobackupex -- user = root -- password = centos/backup/
[Root @ MariaDB ~] # Ll/backup/
Total4
Drwxr-xr-x2 root 4096 Jun 16 2015-06-16_02-28-54

An error is reported during Backup.
Innobackupex: Error: The xtrabackup child process has died at/usr/bin/innobackupex line2672.

Solution: Modify the innodb log file size to 5 MB, and restart the service after modification.
12 [root @ MariaDB ~] # Vim/etc/my. cnf
Innodb_log_file_size = 5 M

After the backup is complete, the following content is available in the/backup directory:
[Root @ MariaDB ~] # Ll/backup/2015-06-15_23-49-43/
Total 18468
-Rw-r -- 1 root 356 Jun 15 backup-my.cnf
Drwxr-xr-x 2 root 4096 Jun 15 :49 hellodb
-Rw-r ----- 1 root 18874368 Jun 15 23: 49ibdata1
Drwxr-xr-x 2 root 4096 Jun 15 23: 49 mysql
Drwxr-xr-x 2 root 4096 Jun 15:49 performance_schema
Drwxr-xr-x 2 root 4096 Jun 15 23: 49 test
-Rw-r -- 1 root 23 Jun 15 23:49 xtrabackup_binlog_info
-Rw-r ----- 1 root 89 Jun 15 xtrabackup_checkpoints
-Rw-r -- 1 root 559 Jun 15 23:49 xtrabackup_info
-Rw-r ----- 1 root 2560 Jun 15 xtrabackup_logfile

File description:
(1) xtrabackup_checkpoints -- Backup Type (such as full or incremental), backup status (such as whether it is already in prepared status), And LSN (log serial number) range information;
Each InnoDB page (usually 16 KB) contains a log serial number, that is, the LSN. The LSN is the system version number of the entire database system. The LSN related to each page can indicate how the page has changed recently.
[Root @ MariaDB 2015-06-15_23-49-43] # catxtrabackup_checkpoints
Backup_type = full-backuped # Backup Type. full-backuped indicates full backup.
From_lsn = 0 # Start of log Sequence
To_lsn = 1597945 # maximum log serial number
Last_lsn = 1597945 # current log serial number
Compact = 0 # indicates no package

(2) xtrabackup_binlog_info -- the binary log file currently in use by the mysql server and the location of the binary log event until the moment of backup.
[Root @ MariaDB 2015-06-15_23-49-43] # catxtrabackup_binlog_info
Mysql-bin.000005 245

(3) backup-my.cnf -- the configuration option information used by the BACKUP command and backup-independent will not be recorded, backup configuration files need to be separately backed up
[Root @ MariaDB 2015-06-15_23-49-43] # catbackup-my.cnf
# This MySQL options file was generated byinnobackupex.

# The MySQL server
[Mysqld]
Innodb_checksum_algorithm = innodb
Innodb_log_checksum_algorithm = innodb
Innodb_data_file_path = ibdata1: 10 M: autoextend
Innodb_log_files_in_group = 2
Innodb_log_file_size = 5242880
Innodb_fast_checksum = 0
Innodb_page_size = 16384
Innodb_log_block_size = 512
Innodb_undo_tablespaces = 0

(4) xtrabackup_info -- records the mariadb version information and some attribute information. Restoration is used to detect version matching.
[Root @ MariaDB 2015-06-15_23-49-43] # catxtrabackup_info
Uuid = 25b79086-1376-11e5-980a-000c29bad792
Name =
Tool_name = innobackupex
Tool_command = -- user = root -- password =.../backup/
Tool_version = 1.5.1-xtrabackup
Ibbackup_version = xtrabackup version 2.2.3 basedon MySQL server 5.6.17 Linux (x86_64) (revision id :)
Server_version = 5.5.43-MariaDB-log
Start_time = 2015-06-15 23:49:43
End_time = 2015-06-15 23:49:46
Lock_time = 1
Binlog_pos = filename 'mysql-bin.000005 ', position245
Innodb_from_lsn = 0
Innodb_to_lsn = 1597945
Partial = N
Incremental = N
Format = file
Compact = N
Compressed = N
Encrypted = N