Does the production environment use mysqldump or xtrabackup to back up and restore databases?

If a qualified O & M engineer or dba engineer is engaged in database operations, backup is required first. If there is no backup, your business will go wrong, your job may even be ..

If a qualified O & M engineer or dba engineer is engaged in database operations, backup is required first. If there is no backup, your business will go wrong, your job may even be ..

If a qualified O & M engineer or dba engineer is engaged in database operations, backup is required first. If there is no backup, your business will go wrong, hong Kong server, your work may even be...

Therefore, backup is important, but only backup is not available. If there is a problem after backup, you have to use the backup data for recovery, but the data recovery time is usually very long, it does not meet business requirements, so it is necessary to quickly back up and restore the software.

Previously, I used mysqldump to back up and restore the mysql database, locked the table during the backup, and then backed up all the data. There was no problem when there was less data, however, if there is a lot of data and tables cannot be locked and data blocks need to be restored, mysqldump is not suitable. When I restore a 4G data file database, the recovered data is from mysqldump. After three hours of recovery, no response has been made. The Hong Kong server has a serious impact, so I began to look for other release software to meet the above requirements. Fortunately, I found that xtrabackup is used for backup and recovery, and it takes only 14 seconds to restore the 4G data file database, at the same time, the table will not be locked during backup, and Incremental backup and Hong Kong virtual hosts are supported. So I will share my comparison with you and hope it will be helpful to you!

Xtrabackup is an open-source project of percona to implement the innodb Hot Backup function similar to InnoDB's official Hot Backup tool. It can quickly back up and restore mysql databases. Xtrabackup contains two tools:

Xtrabackup is a tool used for hot backup of innodb and xtradb table data. It cannot back up other types of tables or data table structures;

Innobackupex is a perl script that encapsulates xtrabackup and provides the ability to back up myisam tables.

Because the functions of innobackupex are more comprehensive and complete, this article takes innobackupex as the basis for research and description.

The following describes all and Incremental backup and recovery of xtrabackup.

1. Download and install

1. Download

Wget

2. Install the dependency Library

For debian Series

Apt-get install debhelper autotools-dev libaio-dev wget automake libtool bison libncurses-dev libz-dev cmake bzr

For redhat Series

Yum install cmake gcc-c ++ libaio-devel automake autoconf bzr bison libtool ncurses-devel zlib-devel

3. Decompress

Tar zxvf xtrabackup-1.6.7.tar.gz

4. Enter the Directory

Cd xtrabackup-1.6.7

5. Copy

Where,

Innobackupex is the backup tool we want to use;

Xtrabackup is encapsulated in innobackupex and needs to be called during innobackupex runtime;

Xtrabackup_51 is the tool to be called when xtrabackup is running;

Tar4ibd is a tool used for packaging backup in the form of a tar stream.

6. Introduction to commands for backing up all databases

Note:
-- Database = test: Back up the test database separately. If this parameter is not added, the full database is backed up.
2>/tmp/data/err. log: Write the output information to the log.
1>/tmp/data/test.tar.gz package, compress, and store it in this file

Ii. All database backup and recovery

Next, we will test all the backups of xtrabackup.

(1) first enter mysql to create a new test database

(2) back up the entire database of test.

Use the following backup. sh script

Run this script now

View the result in data.

The backup is complete.

(3) restore the database

Close the mysql service first, and then delete the test database file.

Start database recovery

Decompress the compressed version of the database in/tmp/data.

Note: Here tar unpack must use the-I parameter, otherwise the extracted file only has one backup-my.cnf

View restore content

Then, apply the logs in the backup file to the data file in the backup file.

Here -- apply-log indicates that the log is applied to the data file, and then the data in the backup file is restored to the database:

Check the contents of the current directory.

Then modify the user and group of test as mysql.

Start mysql and view the table content of the test database.

The database has been restored.

You may have some questions about why I am not using many online articles after applying-log, if you use the/usr/bin/innobackupex -- copy-back command, Original data directory is not empty is reported! At/usr/local/xtrabackup/bin/innobackupex line 538. The restored directory must be empty. After checking the official website, This is a BUG in xtrabackup.
Innobackupex-copy-back was run. with this bug fix, innobackupex-copy-back operation if the destination is not empty, avoiding potential data loss or a strang combination of a restored backup and previous data. bug Fixed: #737569 (Valentine Gostev) will now error out of the did not check that MySQL datadir was empty before

So copy the data directory directly to the database location after applying-log.

Iii. Incremental backup and recovery of databases

To perform Incremental backup, add some data to the database first.

Then perform Incremental Backup

Here, -- incremental indicates incremental backup and -- incremental-basedir specifies the location of the last full backup or incremental backup file. The Incremental backup here is only for InnoDB. For MyISAM, it is still a full backup.

Shut down the database before restoring the Incremental backup, and then delete the database test

Incremental Backup Recovery

Then enter the restored directory/tmp/data.

Restore test to/var/lib/mysql, just like all backups.

Then modify the user and group

Start msyql and view the content of the test table in the test database.

You can see that the Incremental Backup has been restored.

The following links show my cases of adapting to xtrabackup in the production environment to restore all the databases backed up.

This article is from the "Yin-Technical Exchange" blog. Please keep this source