preface

The database needs to be started during backup and closed during recovery

1, What is Xtrabackup?

MySQL cold standby, mysqldump and MySQL hot copy cannot realize incremental backup of the database. In the actual production environment, incremental backup is very practical. If the data is greater than 50G or 100G and the storage space is sufficient, full backup can be carried out every day. If the amount of data generated every day is large, the data backup strategy needs to be customized. For example, weekly full backup and Monday to Saturday incremental backup are available. Percona xtrabackup is a mainstream backup tool for incremental backup. xtrabakackup has two tools, xtrabakup and innobakupe.

Percona xtrabackup is a backup tool developed by percona company for physical hot backup of MySQL database. It supports mysql, Percona server and MariaDB. It is open source and free. It is a popular mainstream backup tool at present. Xtrabackup can only back up the tables of innoDB and xtraDB data engines, not MyISAM data tables

2, Xtrabackup benefits

(1)Fast backup speed and reliable physical backup

(2)The backup process does not interrupt ongoing transactions (no table locking is required)

(3)It can save disk space and traffic based on compression and other functions

(4)Automatic backup verification

(5)Fast restore speed

(6)Backups can be transferred to another machine

(7)Backup data without increasing server load

3, Installation steps

1. Prepare the preface

#RHEL/Centos 6 and above
#(1) Download the package using yum or up2date
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

#RHEL/Centos 5 does not support installing software packages directly from remote locations, so you need to download the software packages first and then install them manually with rpm
#(1) rpm package installation package
 wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
 rpm -ivH percona-release-latest.noarch.rpm

#(2) Test whether the software package is installed
yum list | grep percona

#(3) Installing xtrabackup 2.4 series
yum install percona-xtrabackup-24

#(4) [verification]
xtrabackup --version

Failure may occur during installation

mysql lacks related dependencies Solution

4, xtrabackup full backup

1. Prepare test data

mysql> create database test;
 
Query OK, 1 row affected (0.00 sec)
 
mysql> use test;
 
Database changed
 
mysql> create table T1 (name varchar(10) not null,sex varchar(10) not null);
 
Query OK, 0 rows affected (0.15 sec)
 
mysql> insert into T1 values('zhang','man');
 
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into T1 values('zhan','man');
 
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into T1 values('sun','woman');
 
Query OK, 1 row affected (0.00 sec)

2.Xtrabackup full backup and recovery

innobackupex --defaults-file=/etc/my.cnf --user=user --password="password" --backup /Backup address

#Use this parameter to maintain consistency with related data files

innobackupex --apply-log /home/xtraback/backup/2021-12-16_16-46-26/

3. Destroy data backup and recovery

mysql> drop table T1;
 
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from T1;
 
ERROR 1146 (42S02): Table 'test.T1' doesn't exist

Special attention should be paid to: # before recovering data, ensure that the data directory is empty

rm -rf /var/lib/mysql/*

Execute restore command

innobackupex --defaults-file=/etc/my.cnf --copy-back /home/xtraback/backup/2021-12-16_16-46-26/

Check whether to restore

An error occurs when starting the database and adding permissions

 chown -R mysql:mysql /var/lib/mysql

4, xtrabackup incremental backup

1.

summary

Tip: here is a summary of the article:
For example, the above is what we want to talk about today. This paper only briefly introduces the use of pandas, which provides a large number of functions and methods that enable us to process data quickly and conveniently.