Deleting the database and running away? How to use xtraback to back up the MySQL database

1. The mysqldump backup method uses logical backup. The biggest drawback is that the backup and recovery speeds are slow. For a 50G database, this speed is acceptable, but if the database is very large, it is not very suitable to use mysqdump for backup. .

At this time, you need a very useful and efficient tool, xtraback is one of them, known as the free version of innodb hotbackup

The features of xtraback are as follows:

• The backup process is fast and reliable
• The backup process will not interrupt ongoing transactions
• Ability to save disk space and traffic based on compression and other functions
• Automatic backup verification
• Fast restore speed

2. Install xtraback 1) Download xtraback

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz 

2) Unzip

[root@master ~]# tar zxf percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

3) Enter the unzip directory and copy all the programs under bin to /usr/bin

[root@master ~]# cd percona-xtrabackup-2.4.4-Linux-x86_64/ [root@master percona-xtrabackup-2.4.4-Linux-x86_64]# cp bin/* /usr/bin/

Xtraback is a tool for hot backup of innodb and xtradb tables. It supports online hot backup and can back up innodb tables without locking them. However, this tool cannot operate myisam engine tables.

4) Install related plug-ins

yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5 –y

5) Download percona-toolkit and install

wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm 
[root@master ~]# rpm -ivh percona-toolkit-2.2.19-1.noarch.rpm warning: percona-toolkit-2.2.19-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY Preparing... ################################### [100%] Updating / installing...  
1:percona-toolkit-2.2.19-1 ################################### [100%] 

3. Backup

Solution 1: xtrabackup full backup + binlog incremental backup (bin-log needs to be enabled)

Create a backup directory

[root@master ~]# mkdir -p /opt/mysqlbackup/{full,inc}

First, we create a test database, create a table named tb1 in the test database, and insert three rows of data into the tb1 table as follows:

After inserting the data we perform a full backup as follows:

[root@master ~]# 
innobackupex --user=root --password=pwd123 /opt/mysqlbackup/full/

The above full backup path is in the mysqlbackup directory we just created.

The following prompt appears to prove that the backup is successful

The backup files are as follows:

So far, the full backup is completely successful, and then a few data are inserted into a MySQL database, and then an incremental backup is performed

To make a binary log incremental backup of the database changes after the full backup:

View the binlog log position during full backup:

The above is the position after our full backup is completed. If we insert data now, it starts from this position. We call it incremental backup from this position. Now we insert data into the tb1 table as follows:

Incremental backup binary files:

[root@master ~]
# mysqlbinlog --start-position=1279 /usr/local/mysql/data/mysql-bin.000001 > /opt/mysqlbackup/inc/`date +%F`.sql

After the backup is complete, we simulate database damage and accidental deletion as follows

[root@master ~]# rm -rf /usr/local/mysql/data/*

Recovering the Database

First, restore our full backup, that is, the data of tom1/2/3 is as follows:

[root@master ~]#innobackupex --copy-back /opt/mysqlbackup/full/2017-11-21_11-47-42/

The following prompt appears to prove that the restoration is successful

After the data is restored to the DATADIR directory, you also need to ensure that the owner and group of all data files are the correct users, such as mysql. Otherwise, you need to modify the owner and group of the data files before starting mysqld. like:

# chown -R mysql:mysql /usr/local/mysql/data/

Restart the MySQL service to see if the restoration is successful

From the above, we can see that the full backup has been restored successfully. Since we inserted 3 rows of data during the full backup, we will restore the incremental backup as follows:

To prevent a large amount of binary logs from being generated during restore, you can temporarily close the binary logs before restoring:

Then we verify whether the restoration is successful!

At this point, we have successfully recovered all the data. The above backup implemented by binlog+xtrabackup tool seems a bit troublesome. If you feel troublesome, please see the following solution 2.

2. xtrabackup full backup + xtrabacup incremental backup

Test environment preparation

Create a test database and create a table to enter a few rows of data

Explanation of the above operations:

A test library was created, a xx table was created in the test library, and the data of tom1 and tom2 were inserted into the xx table.

Next perform a full backup as follows:

[root@master ~]# xtrabackup --defaultes-file=/etc/my.cnf --user=root --password='pwd123' --popt=3306 --backup --target-dir=/opt/mysqlbackup/full/full_incre_$(date +%Y%m%d_%H%M%S)

The black part above is the storage location. The following shows that the backup was successful

At this time, we insert the data again and perform an incremental backup

The first incremental backup is as follows:

[root@master ~]# xtrabackup --defaultes-file=/etc/my.cnf --user=root --password='pwd123' --popt=3306 --backup --target-dir=/opt/mysqlbackup/inc/inc_incre_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/full/full_incre_20171121_123051/ 

The following message appears, indicating that the backup is successful!

The above backup path is: /opt/mysqlbackup/inc/ We can view the backed up files

Next, insert data into the xx table again and perform a second incremental backup

The second incremental backup is as follows:

[root@master ~]# xtrabackup --defaultes-file=/etc/my.cnf --user=root --password='pwd123' --popt=3306 --backup --target-dir=/opt/mysqlbackup/inc/inc_incre_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/inc/inc_incre_20171121_124604/

The following prompt appears to prove that the backup is successful

xtrabacup for incremental recovery

In order to achieve the effect, I deleted the xx table

Start restoring a full backup as follows:

[root@master ~]# xtrabackup --defaultes-file=/etc/my.cnf --prepare --user=root --password="pwd123" --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_20171121_123051 

2. Restore the first increment (tom3)

[root@master ~]# xtrabackup --defaultes-file=/etc/my.cnf --prepare --user=root --password="pwd123" --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_20171121_123051 --incremental-dir=/opt/mysqlbackup/inc/inc_incre_20171121_124604/ 

3. Restore the second increment

[root@master ~]# xtrabackup --defaultes-file=/etc/my.cnf --prepare --user=root --password="pwd123" --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_20171121_123051 --incremental-dir=/opt/mysqlbackup/inc/inc_incre_20171121_125202 

Restore the entire library

[root@master ~]# xtrabackup --defaultes-file=/etc/my.cnf --prepare --user=root --password="pwd123" --target-dir=/opt/mysqlbackup/full/full_incre_20171121_123051 

Stop mysql database - start rsync data file

[root@master ~]# systemctl stop mysqld [root@master ~]# cd /opt/mysqlbackup/full/full_incre_20171121_123051/ [root@master full_incre_20171121_123051]# rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' ./ /usr/local/mysql/data/ 

Grant mysql access permissions

[root@master ~]# chown -R mysql:mysql /usr/local/mysql/data/

Start the mysql service to see if the restore is successful

Summarize

The above is the method of using xtraback to back up the MySQL database. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!