Detailed explanation of MySQL backup and recovery practice of mysqlbackup

1. Introduction to mysqlbackup

mysqlbackup is the abbreviation of MySQL Enterprise Backup, a backup software for enterprises also provided by ORACLE. It is a backup utility for MySQL servers. It is a multi-platform, high-performance tool with rich features such as "hot" (online) backup, incremental and differential backup, selective backup and restore, support for direct cloud storage backup, backup encryption and compression, and many other valuable features. Optimized for use with InnoDB tables, MySQL Enterprise Backup is able to back up and restore a wide variety of tables created by any storage engine supported by MySQL. The parallelism of its read and write processes (which are executed in independent, multiple threads) and its block-level parallelism (different threads can read, process, or write different blocks in a single file) allow for fast backup and restore processes and often provide significant performance improvements over logical backup using tools such as mysqldump. MySQL Enterprise Backup is a valuable tool for maintaining and protecting your MySQL data, and for recovering it quickly and reliably in the event of an incident or disaster. It is part of MySQL Enterprise Edition, which is available to subscribers under a commercial license.

2. Full backup and recovery practice

1. Create database test1

mysql> create database test1;

2. Back up the entire database

#./mysqlbackup --host=127.0.0.1 --user=root --password=Yhxx@2020 --port=3306 --with-timestamp --backup-dir=/home/test/mysql/backup backup

3. View backup data

#ll /home/test/mysql/backup/ 

4. Create database test2

mysql> create database test2;
Query OK, 1 row affected (0.04 sec)
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
| test1 |
| test2 |
±-------------------+
6 rows in set (0.00 sec)

5. Perform recovery file preparation

#./mysqlbackup --defaults-file=/home/test/mysql/my.cnf --backup-dir=/home/test/mysql/backup/2020-12-12_15-15-23/ apply-log

6. Stop the database and delete all data in the data directory

#cd /home/test/mysql
#rm -rf data/*

7. Restore backup files

#./mysqlbackup --defaults-file=/home/test/mysql/my.cnf --backup-dir=/home/test/mysql/backup/2020-12-12_15-15-23/ copy-back

8. Log in to the database to view the verification

mysql > show databases;

The database has been restored to the state where only the test1 database exists.

3. Backup and recovery practice using backup-to-image

1. Create a database table runoob_tbl under the test1 library

mysql> CREATE TABLE IF NOT EXISTS runoob_tbl( runoob_id INT UNSIGNED AUTO_INCREMENT, runoob_title VARCHAR(100) NOT NULL, runoob_author VARCHAR(40) NOT NULL, submission_date DATE, PRIMARY KEY ( runoob_id ))ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
±----------------+
| Tables_in_test1 |
±----------------+
| runoob_tbl |
±----------------+
1 row in set (0.00 sec)

2. Use backup-to-image to back up the database to a single binary file

#./mysqlbackup --host=127.0.0.1 --user=root --password=Yhxx@2020 --port=3306 --backup-image=backupfull.mbi --with-timestamp --backup-dir=/home/test/mysql/backup backup-to-image

3. Delete table runoob_tbl

mysql> drop table runoob_tbl;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)

4. View backup files

5. View the contents of the backup file

#./mysqlbackup --backup-image=/home/test/mysql/backup/2020-12-12_16-08-18/backupfull.mbi list-image

6. Verify backup files

#./mysqlbackup --backup-image=/home/test/mysql/backup/2020-12-12_16-08-18/backupfull.mbi validate

7. Stop the database and delete the files in the database data directory

#rm -rf data/*

8. Perform recovery operations

#./mysqlbackup --defaults-file=/home/test/mysql/my.cnf --backup-image=/home/test/mysql/backup/2020-12-12_16-08-18/backupfull.mbi --backup-dir=/home/test/mysql/backup/2020-12-12_16-08-18 copy-back-and-apply-log

9. View the restored database

The table runoob_tbl has been restored.

4. Download and install the mysqlbackup tool

1. Download the mysqlbackup tool

Use the metalink account to log in to the MySQL official website, search and download. It is recommended to download the .tgz format installation package.
If you don’t have a Metalink account, you can also search on Baidu. Some netizens have provided the download of this tool.

2. Installation of mysqlbackup tool

Unzip the installation package#tar ​​xvzf package.tgz
#cd ./meb-4.1.0-el7-x86-64bit/bin
Enter the mysqlbackup directory#cd meb-4.1.0-el7-x86-64bit/bin
[test@testenv bin]$ ll
total 10092
-rwxr-xr-x. 1 test test 10331202 Feb 28 2017 mysqlbackup
[test@testenv bin]$ which mysql
~/mysql/mysql5.7/bin/mysql
Copy the mysqlbackup tool to the mysql installation bin directory [test@testenv bin]$ cp mysqlbackup ~/mysql/mysql5.7/bin/mysql

3. Explanation of common parameters

• –user: User name.
• –password: Password.
• –port: port, the default value is 3306.
• –backup-dir: can be regarded as the working directory of mysqlback.
• –backup-image: backup file name.
• backup-to-image: Output all backup information to a backup file.
• backup performs backup operations
• apply-log executes the prepare-to-restore file operation
• copy-back performs a restore operation
• validate Check the integrity of the backup file
• extract decompress the mbi file to the current directory
• image-to-backup-dir specifies the directory to decompress

This is the end of this article about the detailed explanation of MySQL's mysqlbackup backup and recovery practice. For more relevant mysqlbackup backup and recovery content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!