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