Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you an analysis of how to carry out mysqlbackup. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. Introduction of mysqlbackup tools 1.1 description of mysqlbackup software undefined

There are many backup methods for Mysql. When the amount of data is very small, most enterprises choose mysqldump to export the database for backup, but when the amount of data is large, this method is not recommended.

The company's Mysql database 300GB, when using the mysqldump method to recover, took a whole week. If you really need to recover in the event of a disaster, it will be a disaster.

After searching and searching on the Internet, it is found that there are indeed too many backup software for mysql, and third-party organizations also provide corresponding backup software, and ORACLE also provides backup software for enterprises: MySQL Enterprise Backup abbreviation: mysqlbackup.

Taking into account the lifecycle and documentation of the software, use mysqlbakcup for the following reasons:

Mysqlbackup from 3.5 to 4.1 versions show that mysql has been updating this software, so that with the update of mysql software, the backup software will also be updated to avoid bug not being solved. At the same time, mysql dba can continue to use this technology after mastering this technology, which improves the income of learning.

There are complete user manuals on the mysql website, and these documents are essential for the use of the entire software.

1.2 download of mysqlbackup software

Mysqlbackup Enterprise Edition is not available for download in mysql's open source community. You need an metalink account to download it. (it can be downloaded in the early days, but it is estimated that the user's experience is good later, so you need to authorize the purchase of oracle services.)

At present, my environment is still mysql5.6, so I choose version 3.12 (version 3.12 has fixed several important bug, which is recommended)

1.3 installation of mysqlbackup software

Select the appropriate version to download, and each mysql server to be backed up needs to install this software. Mysql provides two packages and installs them differently. Common installation steps are as follows:

Sudo rpm-ipackage_name.rpm

Tar xvzfpackage.tgz

The installation directory is as follows: / opt/mysql/meb-3.12

Shortcut used by other setup commands: ln-s / opt/mysql/meb-3.12/bin/mysqlbackup / usr/bin/mysqlback

II. Introduction of mysqlbackup tools

The main functions of mysqlbackup and mysqldump are backup and restore. Mysqlbackup is an enterprise product of oracle. The idea of the whole product is very similar to oracle's rman. The function of mysqlbackup is compared to the expdp of oracle.

Mysqlbackup provides backup and restore functions of some enterprises: faster backup and faster restore, support online full and doubling, backup compression, parallel backup, backup encryption, database restore and point-in-time restore.

Back up the configuration of location and space through mysqlbackup3.1.1.

It is recommended to separate the backup directory from the directory of the database file, so as to prevent the backup from bursting the space and causing the system to be unable to use it.

3.1.2 set backup policy (backup frequency, backup retention cycle)

At the same time, calculate the disk space required for each backup and arrange the backup space reasonably according to the backup strategy.

3.1.3 create a separate backup user

Other businesses separate the use of accounts. As an important system, the database usually changes the management password every year. Here, a separate one is used to avoid later changes.

Basic permissions required by the backup user

3.2 main options for Reserve Library

In fact, the main contents of backup are backup, restore and verification. The main parameters used in each phase are as follows:

Backup operations: backup, backup-and-apply-log, backup-to-image

Update operations: apply-log, apply-incremental-backup

Restore operations: copy-back, copy-back-and-apply-log

Validation operation: validate Single-file backup

Operations: image-to-backup-dir, backup-dir-to-image, list-image, extract

In versions below 3.12.3, backup-and-apply-log cannot be used with the parameter compress when backing up. Mysqlbackup's official documentation recommends the backup-to-image option, which performs better and backs up smaller files.

Note:

When used with the apply-log or copy-back-and-apply-log operation, uncompresses a compressed backup before applying the InnoDB log. When used with the copy-back operation,uncompresses a compressed prepared backup (created by the backup-and-apply-log command with the-- compress option) before restoring it to a server (only supported for MySQL Enterprise

Backup 3.12.3 and later).

Backup-to-image

Produces a single-file backup holding the backup data. In most cases, single-file backups are preferred over directory backups, which are created using the backup command.

3.3 main parameters of reserve library

You can use mysqlbackup-- help will display a rich description of parameters. Here are some common parameters.

Remarks on parameter description

-- backup-dir

The directory to store the backup data.

-- backup-image

Specifies the path name of the backup image.

Use in conjunction with backup-to-image

-- compress

Create backup in compressed format.

The default level is 1

-- compress-level

Specifies the level of compression.

-compress-level=LEVEL-uncompress

Uncompress the compressed backup before an apply-log

Copy-back, or copy-back-and-apply-log operation.

If the backup uses compress, you have to use this parameter-datadir when restoring

Path to mysql server data directory.

-- defaults-file

Only read default options from the given file.

-- force

Force overwriting of data, log, or image files, depending on

The operation.

Make a forced overlay

-- incremental

Specifies that the associated backup or backup-to-image

Operation is incremental.

Incremental backup option

-- incremental-backup-dir

Specifies the location under which to store data from an

Incremental backup.

Incremental backup save location

-- incremental-base

The specification of base backup for-- incremental option.

Location of the last full backup

-- with-timestamp

Create a subdirectory underneath the backup directory

With a name formed from the timestamp of the backup

Operation.

Generate a file according to the timestamp, because the file needs to be saved in an empty directory when backing up, so it is generally recommended to use the whole parameter

IV. Mysqlbackup backup and restore

Method 1: use backup

Method 2: click (here) to collapse or open using app-log at different times

[note] backup-and-apply-log uses this script. The mysql official documentation contains the following instructions, which cannot be used for incremental backups, but it is possible in the experiment.

Backup-and-apply-log

A combination of backup and apply-log. It cannot be used for an incremental backup.

Method 3: use backup-to-image

This method is officially recommended by mysql. Only a single backup binary is generated, and the size of the entire backup is smaller than the previous two.

Click (here) to collapse or open

Method 1: click (here) to collapse or open the incremental backup using the bakcup method

Method 2: click (here) to collapse or open the incremental backup using the image method

Backup operation: complete it first and then add it.

Mysqlbackup-- user=mysqlbackup-- password=123-- backup-image=backup.mbi-- backup-dir=/backup-- with-timestamp backup-to-image # backup to image first

Mysqlbackup-- defaults-file=/etc/my.cnf-- user=mysqlbackup-- password=123-- with-timestamp-- incremental--incremental-backup-dir=/backup/-- incremental-base=dir:/backup/2017-09-18cm 15-53-57 backup # perform incremental backups based on the original backup

Click (here) to collapse or open

The operation of restoring

Mysqlbackup-- backup-dir=/backup/full-- backup-image=/backup/2017-09-18cm 15-53-57/backup.mbi image-to-backup-dir is first converted to the backup file of method 1, and the subsequent steps are the same as before

Mysqlbackup-backup-dir=/backup/full apply-log

Mysqlbackup-backup-dir=/backup/full-incremental-backup-dir=/backup/2017-09-18 apply-incremental-backup 15-56-13 apply-incremental-backup

Mysqlbackup-defaults-file=/etc/my.cnf-force-backup-dir=/backup/full-datadir=/data/mysql copy-back-and-apply-log

VI. Other operations 6.1 some operations about image backup

Click (here) to collapse or open

Mysqlbackup-- backup-image=/backup/2017-09-18 October 15-53-57/backup.mbi validate check the validity of backups

Mysqlbackup-- backup-image=/backup/2017-09-18 backup 15-53-57/backup.mbi list-image lists backup objects

Mysqlbackup-- backup-image=/backup/2017-09-18 upload 15-53-57/backup.mbi extract decompress and operate directly in the backup directory

Mysqlbackup-- backup-dir=/backup/backup-- backup-image=/backup/2017-09-18 backup 15-53-57/backup.mbi image-to-backup-dir specify the backup directory to decompress

6.2 other

About backup compression: in fact, backup compression is very useful, after testing can significantly save backup space. Although there is no demonstration during the above test, in fact, I have operated backup compression, and it does not consume a lot of time compared to normal backup.

About the incremental backup of image: the incremental backup and recovery steps of image are relatively complete and troublesome. I have been looking for a more convenient operation in the official documents for a long time, but I have never found the relevant operation instructions. I would appreciate it if my friends who know leave a message.

About incremental backup: incremental backup is necessary, in fact, it depends on the importance of our system and the speed of data growth. At present, the largest system on our side only produces a G binlog in 2 days, and the overall database is not very large, so it is now fully used every day.

About the software: mysqlbackup actually has a lot of functions, which can be used to back up tables and database objects. At the same time, it can also be used to build a slave library, which is a boon for users with a large database.

But because this software is the enterprise version of Oracle and can only be used by paying users, there is not much information about it on the Internet.

The above is the editor for you to share how to carry out mysqlbackup analysis, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.