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

The following is about the installation of MySQL-Xtrabackup and how to deal with problems. The secret of the text is that it is close to the topic. So, no gossip, let's just read the following, I believe you will benefit from reading this article on MySQL-Xtrabackup installation and how to deal with problems.

Introduction to Xtarbackup

Xtrabackup is a free database hot backup software opened by percona, it can back up InnoDB database and XtraDB storage engine database non-blocking (also need table lock for MyISAM backup); mysqldump backup mode is logical backup, its biggest defect is that backup and recovery speed is slow, if the database is greater than 50GB mysqldump backup is not suitable.

Advantages of Xtrabackup

1) Fast backup and reliable physical backup

2) the backup process will not interrupt the transaction in progress (no need to lock the table)

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

4) automatic backup check

5) Fast reduction speed

6) it can be spread and transferred to another machine.

7) backup data without increasing the load on the CVM

Principle of Xtrabackup backup

At the beginning of the backup, a background detection process will be started to detect mysql redo changes 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 tablespace file ibdatax will be copied. After the copy is finished, flush tables with readlock will be executed, and then .frm MYI MYD and other files will be copied. Finally, unlock tables will be executed, and finally xtrabackup_log will be stopped.

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) then, in an incremental backup, compare whether the LSN of each page in the tablespace is greater than the LSN of the last backup, and if so, back up the page and record the LSN of the current checkpoint.

Incremental backup benefits:

1) the database is too large to have enough space for full backup. Incremental backup can effectively save space and has high efficiency.

2) Hot backup is supported. Tables are not locked during the backup process (for InnoDB), and database reads and writes are not blocked.

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

4) backup and recovery is based on file operations to reduce the risk of direct operation to the database

5) backup is more efficient and recovery is more efficient.

Installation

The running 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

(download Percona-XtraBackup-8.0:

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)

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

Check the dependency packages 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 downloaded and installed manually.

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

After installing several dependent packages, try to install percona-xtrabackup

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

I show that the installation is correct and there is no error.

[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.01sec)

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: user account of mysql

-- password: the 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-09x17-25-31 / # use this parameter to keep the relevant data files consistent.

Restore:

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 >

You must make sure that the data directory is empty, that is, the mysqldata directory, before restoring. Since the previous backup data is in the mysqldata directory, you need to copy the data inside to make sure that the mysqldata directory is empty. To recover the data normally. Otherwise, the mysqldata directory will be reported as a non-empty error.

[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, there was an error in starting mysql and 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).

Check the / var/run/mysqld directory, enter the recovery data file directory and compare it with the previous data, and find that the owner of the data is not mysql but root.

Modify the data permissions of the recovery directory

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

Restart mysql and start normally

Check to see if the data is restored.

The data is back to normal.

Incremental backup and recovery:

Insert some new data to create data for incremental backup, which can be used to simulate whether the fully deleted data can be restored by incremental backup files.

Mysql > select * from address

+-+ +

| | name | homeaddress |

+-+ +

| | zhuangsan | guangzhou |

| | lisi | shanghai |

| | waner | shenzhen |

| | jinjin | jiangxi |

| | xiaoxiong | Xia'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 September 17-25-31

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

-- incremental-basedir=/data/mysqldata.bak/2018-03-09 backup 17-25-31 specifies the directory where the last full backup or incremental backup was stored

Displays the following to indicate success:

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 October 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 March 14-54-12 Universe

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

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

180323 14:54:20 [00] Writing / data/mysqldata/2018-03-23 October 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 backup status

Delete a piece of data to test incremental recovery

Remember to turn off the database service before restoring the data.

Is there anything you don't understand about the above MySQL-Xtrabackup installation and how to deal with problems? Or if you want to know more about it, you can continue to follow our industry information section.

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.