Shulou(Shulou.com)06/01 Report--

Xtarbackup Introduction

Xtrabackup is a free database hot backup software open source by percona, which can backup InnoDB database and XtraDB storage engine database non-blocking (for MyISAM backup also need to add table lock);mysqldump backup method is adopted logical backup, its biggest defect is that the backup and recovery speed is slow, if the database is larger than 50G, mysqldump backup is not suitable.

Xtrabackup Benefits

1) Fast backup speed, reliable physical backup

2) The backup process does not interrupt the transaction in progress (no lock table required)

3) Can save disk space and traffic based on compression and other functions

4) Automatic backup verification

5) Fast reduction speed

6) It can be circulated to transfer backups to another machine

7) Backup data without increasing server load

Xtrabackup backup principle

At the beginning of the backup, a background detection process will be started to detect changes in mysql redo in real time. Once a new log is found, the log will be recorded in the background log file xtrabackup_log immediately, and then the innodb data file-system table space file ibdatax will be copied. After the copy is completed, flush tables with readlock will be executed, and then files such as.frm MYI MYD will be copied. Finally, unlock tables will be executed, and xtrabackup_log will be stopped finally.

Introduction to Xtrabackup Incremental Backup

The principle of xtrabackup incremental backup is:

1)First complete a full backup and record the checkpoint LSN at this time;

2), and then an incremental backup, compares whether the LSN of each page in the table space is greater than the LSN of the last backup, and if so backs up the page and records the LSN of the current checkpoint.

Benefits of incremental backup:

1)The database is too large to have enough space for full backup. Incremental backup can effectively save space and be efficient.

2)Support hot backup, the backup process does not lock the table (for InnoDB), does not block the reading and writing of the database;

3)Daily backup only produces a small amount of data, or remote backup can be used to save local space;

4)Backup recovery is based on file operations, reducing the risk of direct database operations;

5)More efficient backup and recovery.

installation

The operating environment is Centos 7.2.

Download XtraBackup:

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.9-ra467167cdd4-el7-x86_64-bundle.tar

(Percona-XtraBackup-8.0 Download:

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-8.0.4/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm

Dependency: yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr bison libtool ncurses5-devel perl perl-devel perl-Digest-MD5 perl-Time-HiRes perl-DBD-MySQL libev libev-devel libgcrypt libgcrypt-devel curl curl-devel python-setuptools libarchive-devel）

Unzip: tar -xf Percona-XtraBackup-2.4.9-ra467167cdd4-el7-x86_64-bundle.tar

Check the dependencies required to install percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm:

yum -y install percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm --skip-broken

Follow the prompts to install:

yum -y install perl-* mariadb-libs

There is also a dependency package that needs to be manually downloaded and installed

wget http://mirror.centos.org/centos/7/extras/x86_64/Packages/libev-4.15-7.el7.x86_64.rpm

Install several dependencies, try percona-xtrabackup

yum -y install percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm

I see it installed correctly, no errors.

[root@localhost src]# which xtrabackup

/usr/bin/xtrabackup

[root@localhost src]# innobackupex -v

innobackupex version 2.4.9 Linux (x86_64) (revision id: a467167cdd4)

[root@localhost src]#

mysql creates databases and tables

mysql> create database home ;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

mysql> create table address (name varchar(10) not null,homeaddress varchar(20) not null);

Query OK, 0 rows affected (0.08 sec)

mysql> use home ;

Database changed

mysql> insert into address values('zhuangsan','guangzhou');

Query OK, 1 row affected (0.02 sec)

mysql> insert into address values('lisi','shanghai');

Query OK, 1 row affected (0.01 sec)

mysql> insert into address values('waner','shenzhen');

Query OK, 1 row affected (0.03 sec)

Full backup:

[root@localhost run]# innobackupex --defaults-file=/etc/my.cnf --user=root --password="123abc" --backup /data/mysqldata

--user :mysql User account

--password: password for the account

/data/mysqldata: Data backup directory

[root@localhost run]# ls /data/mysqldata/2018-03-09_17-25-31/ #You can see the backup information in this directory, as well as the name of the library we created.

[root@localhost ~]# innobackupex --apply-log /data/mysqldata/2018-03-09_17-25-31/ #Use this parameter to keep related data files consistent.

Recovery:

Delete table information:

mysql> show databases

-> ;

+------------------------------+

| Database |

+------------------------------+

| information_schema |

| #mysql50#2018-03-09_17-25-31 |

| home |

| mysql |

| performance_schema |

| sys |

+------------------------------+

6 rows in set (0.09 sec)

mysql> use home;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+----------------+

| Tables_in_home |

+----------------+

| address |

+----------------+

1 row in set (0.00 sec)

mysql> drop table address;

Query OK, 0 rows affected (0.02 sec)

mysql> select * from address

->;

ERROR 1146 (42S02): Table 'home.address' doesn't exist

mysql>

Before restoring, you must ensure that the data directory is empty, that is, the mysqldata directory. Since the previous backup data is in the mysqldata directory, you need to copy out the data inside and ensure that the mysqldata directory is empty. To recover the data properly. Otherwise, mysqldata directory non-empty error will be reported.

[root@localhost mysqldata]# innobackupex --defaults-file=/etc/my.cnf --copy-back /data/mysqldata.bak/2018-03-09_17-25-31/

Handling of problems encountered Note:

After recovering the data, an error occurred when starting mysql, and it could not be started.

[root@localhost mysqldata]# service mysqld start

Starting MySQL.. ERROR! The server quit without updating PID file (/var/run/mysqld/mysqld.pid).

After checking the directory/var/run/mysqld, there is no problem. Enter the recovery data file directory and compare it with the previous data. It is found that the owner of the data is not mysql but root.

Modify data permissions for recovery directories

[root@localhost data]# chown -R mysql:mysql mysqldata

Restart mysql, start normally

Check to see if the data is recovered.

Data is back to normal.

Incremental Backup and Recovery:

Insert some new data, create data for incremental backup, and simulate whether the deleted data can be restored through incremental backup files.

mysql> select * from address;

+-----------+-------------+

| name | homeaddress |

+-----------+-------------+

| zhuangsan | guangzhou |

| lisi | shanghai |

| waner | shenzhen |

| jinjin | jiangxi |

| xiaoxiong | xi`an |

| meimei | fujian |

+-----------+-------------+

6 rows in set (0.00 sec)

mysql>

Incremental backup:

[root@localhost mysqldata.bak]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123abc --incremental /data/mysqldata --incremental-basedir=/data/mysqldata.bak/2018-03-09_17-25-31

--incremental /data/mysqldata specifies the directory where incremental backup files are stored

--incremental-basedir=/data/mysqldata.bak/2018-03-09_17-25-31 Specify the directory where the last full or incremental backup is stored

Success is shown by:

180323 14:54:20 Executing UNLOCK TABLES

180323 14:54:20 All tables unlocked

180323 14:54:20 [00] Copying ib_buffer_pool to /data/mysqldata/2018-03-23_14-54-12/ib_buffer_pool

180323 14:54:20 [00] ... done

180323 14:54:20 Backup created in directory '/data/mysqldata/2018-03-23_14-54-12/'

180323 14:54:20 [00] Writing /data/mysqldata/2018-03-23_14-54-12/backup-my.cnf

180323 14:54:20 [00] ... done

180323 14:54:20 [00] Writing /data/mysqldata/2018-03-23_14-54-12/xtrabackup_info

180323 14:54:20 [00] ... done

xtrabackup: Transaction log of lsn (2564189) to (2564198) was copied.

180323 14:54:20 completed OK!

[root@localhost mysqldata.bak]#

View backups

Delete a piece of data to test incremental recovery

Remember to shut down database services before restoring data.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.