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

First, the mysqldump backup mode is logical backup. The biggest drawback is that the speed of backup and recovery is slow, which is acceptable for a 50G database, but if the database is very large, it is not appropriate to use mysqdump backup.

At this time, you need a tool that is easy to use and efficient, and xtraback is one of them, known as the free version of innodb hotbackup.

Xtraback has the following characteristics:

The backup process is fast, and the reliable backup process does not interrupt the transactions being executed. It can save disk space and traffic automatically based on functions such as compression. Backup verification and restore speed is fast.

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) decompression

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

3) enter the unzipped 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 data in innodb,xtradb tables. It supports online hot backup. Innodb tables can be backed up without locks, but this tool cannot operate myisam engine tables.

4) install the relevant 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 # #

III. Backup

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

Create a backup directory

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

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

After inserting the data, we make a full backup as follows:

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

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

The following prompt appears to prove that the backup was successful

The backed up files are as follows:

At this point, it is completely successful, then insert a few pieces of data into a library in mysql, and then make an incremental backup.

Perform a binary log incremental backup of the post-database changes of the full backup:

View the binlog log location (position) when viewing a full backup:

The above is the location after our full backup is completed, so if we insert data now, it starts from this location, which we call incremental backup, and now we insert data into the tb1 table as follows:

Incremental backup binaries:

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

After the backup is completed, we simulate database corruption and erroneous deletion, as follows

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

Restore the database

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

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

The following prompt appears to prove that the restore was successful

When the data is restored to the DATADIR directory, you also need to make sure 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 file before starting mysqld. Such as:

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

Restart the mysql service to see if the restore is successful

We can see that the full backup has been restored successfully, because we inserted 3 rows of data during the full backup, so let's restore the incremental backup as follows:

In order to prevent a large number of binary logs from being generated during restore, you can temporarily close the binary logs during restore and then restore:

Then we verify whether the restore is successful!

At this time, we have all recovered successfully. Does it feel a little troublesome to back up the above backups through the binlog+xtrabackup tool? If you feel trouble, please take a look at the following plan 2.

2. Xtrabackup full backup + xtrabacup incremental backup

Test environment preparation

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

The above operation explains:

Create a test library, create an xx table in the test library, and insert tom1 and tom2 data 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 above black part is the storage location. The following proves that the backup was successful

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

The first increment of the 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 indicates that the backup is successful!

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

Next, insert the data into the xx table again for 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 was successful

Incremental recovery by xtrabacup

To achieve the effect, I deleted the xx table.

Start restoring the 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

Second, 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/

Third, 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 access to mysql

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

Start the mysql service to see if the restore was successful

Summary

The above is the editor to introduce to you to delete the library run? Use xtraback backup MySQL database method, hope to help you, if you have any questions, please leave me a message, the editor will reply you in time. Thank you very much for your support to the website!

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.