mysqlbackup summary_20170918-Alibaba Cloud Developer Community

1.1 mysqlbackup software description undefined

Mysql there are many backup methods, most enterprises choose when the data volume is small mysqldump is used to export databases for backup. However, this method is not recommended when the data volume is large.

Company's Mysql database 300GB , use mysqldump it took a whole week to recover. If you really need to recover in the event of a disaster, it would be a disaster.

After searching and searching on the Internet, we found that mysql does have too many backup software. Third-party organizations also provide corresponding backup software, ORACLE the company also provides backup software for enterprises MySQL Enterprise Backup abbreviation: mysqlbackup .

Considering the life cycle of the software and the documentation of the software, or using mysqlbakcup following reason:

mysqlbackup from 3.5 4.1 description mySQL the company has been updating this software, so mysql software updates backup software to avoid bug not resolved. After mastering this technology, mysql dba can continue to use it, improving the learning benefits.

In the mysql website has a complete user manual, which is crucial for the use of the entire software;

1.2 mysqlbackup download Software

mysqlbackup enterprise Edition mysql open-source community cannot be downloaded. metalink to download. (It can be downloaded in the early stage. It is estimated that the user experience is good later, and you need to authorize the purchase oracle service is OK)

currently, my environment is still mysql5.6, so I chose version 3.12 (version 3.12 fixed several important bugs, and we recommend this version)

1.3 mysqlbackup software Installation

select the appropriate version to download. This software must be installed on mysql servers. Mysql two packages are provided and the installation method is different. Common installation steps are as follows:

sudo rpm -i package_name .rpm

tar xvzf package .tgz

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

shortcuts used by other configuration commands: ln -s /opt/mysql/meb-3.12/bin/ mysqlbackup /usr/bin/mysqlback II, mysqlbackup introduction to tools

mysqlbackup and mysqldump the main function of is Backup and Restore. mysqlbackup is an enterprise product of oracle. I feel that the idea of the whole product is very similar to that of rman of oracle. The functions of mysqlbackup are compared oracle expdp.

Mysqlbackup provides backup and restoration features for Enterprises: provides faster backup and restoration methods, supports online full backup and doubling, Backup compression, parallel backup, backup encryption, database restoration and time-based restoration.

Pass mysqlbackup -- help allows you to view related command operations.

this topic describes how to use mysqlbakcup. Preparation and usage;

3.1 preparations for backup

backup is no small matter. Before backup, make the following preparations to ensure the normal operation of backup.

The following table describes the tasks that need to be prepared:

3.1.1 backup location and space configuration.

We recommend that you separate the backup directories from the directories of database files to prevent the system from being unavailable due to insufficient backup space;

3.1.2 set backup policies (backup frequency and backup retention period)

calculate the disk space required for each backup, and then arrange the backup space according to the backup policy;

3.1.3 create a separate backup user

the database, as an important system, usually changes the management password every year for the use of other business accounts separately. Use a separate one here to avoid later changes; basic permissions required by backup users

GRANT CREATE , INSERT , DROP , UPDATE ON mysql . backup_progress 'mysqlbackup' 'localhost' ;
GRANT CREATE , INSERT , SELECT , DROP , UPDATE ON mysql . backup_history 'mysqlbackup' 'localhost' ;
GRANT REPLICATION CLIENT ON * . * TO 'mysqlbackup' 'localhost' ;
GRANT SUPER ON * . * TO 'mysqlbackup' 'localhost' ;
GRANT LOCK TABLES , SELECT , CREATE , DROP , FILE ON * . * TO 'mysqlbackup' 'localhost'

3.2 backup database options

the main content of backup is 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

for backup-and-apply-log versions earlier than 3.12.3, you cannot compress the backup parameters. We recommend that you use the backup-to-image option in the official mysqlbackup documentation. This method has better performance and smaller backup files. Remarks: When used with apply-log or copy-back-and-apply-log operation, uncompresses a compressed backup before applying the InnoDB log. When used with copy-back operation uncompresses a compressed prepared backup (created by backup-and-apply-log command with --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 backup command.

3.3 main parameters of the backup database

you can use mysqlbackup -- help to display a variety of Parameter descriptions. Here are some common parameters.

Parameter description note --backup-dir The directory to store the backup data. --backup-image Specifies the path name of the backup image. Use backup-to-image --compress Create backup in compressed format. The default level is 1. --compress-level Specifies the level of compression. --compress-level=LEVEL Specify value from 0-9. Specify value 0 to disable compression. Specify value 1 for fastest compression. Specify value 9 for best compression. --uncompress

Uncompress the compressed backup before an apply-log

copy-back, or copy-back-and-apply-log operation. If compress is used for backup, this parameter must be used for restoration. --datadir 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. Forcibly overwrite --incremental

Specifies that the associated backup or backup-to-image

operation is incremental. Incremental Backup options --incremental-backup-dir

Specifies the location under which to store data from

incremental backup. Storage location of incremental backup --incremental-base The specification of base backup for --incremental option. The 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. A file is generated based on the timestamp. You need to save the file in an empty directory during Backup. Therefore, we recommend that you use the entire parameter.

IV, mysqlbackup backup and Restore

method 1: Use backup

click ( here ) fold or open

full Backup Script
[ ] # mysqlbackup --user=mysqlbackup --password=123 --backup-dir=/backup --with-timestamp backup
restore script
[ ] # mysqlbackup - - backup - dir = / backup/2017 - 09 - 18_13 - 49 - 11 apply - log# because the database is still reading and writing during backup, apply the logs during this period to achieve data consistency.
[ ] # mysqlbackup - - datadir = / data/mysql - - backup - dir = / backup/2017 - 09 - 18_13 - 49 - 11 copy - back

method 2: Use app-log at different times

click ( here ) fold or open

full Backup Script
[ ] # mysqlbackup - - defaults - file = / etc/my . CNF - - user = mysqlbackup - - password = 123 - - backup - dir = / backup - - with - timestamp backup - and - apply - log
restore script
[ ] # mysqlbackup - - datadir = / data/mysql - - backup - dir = / backup/2017 - 09 - 18_13 - 49 - 11 copy - back

[Note] backup-and-apply-log uses this script. mysql official documents provide the following instructions and cannot be used for incremental backup. However, this script is applicable in experiments;

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 file is generated, and the size of the entire backup file is smaller than the previous two.

Click ( here ) fold or open

full Backup Script
[ ] # mysqlbackup - - user = mysqlbackup - - password = 123 - - backup - image = backup . mbi - - backup - dir = / backup - - with - timestamp backup - to - image
restore script
[ ] # mysqlbackup - - defaults - file = / backup/2017 - 09 - 15_17 - 06 - 07/server - my . CNF - - datadir = / data/mysql - - backup - dir = / backup/2017 - 09 - 15_17 - 06 - 07 copy - back

method 1: incremental backup using bakcup

click ( here ) fold or open

backup operations: perform full backup before backup.
mysqlbackup - - defaults - file = / etc/my . CNF - - user = mysqlbackup - - password = 123 - - backup - dir = / backup - - with - timestamp backup# full backup
mysqlbackup - - defaults - file = / etc/my . CNF - - user = mysqlbackup - - password = 123 - - with - timestamp - - incremental - - incremental - backup - dir = / backup/ - - incremental - base = dir : / backup/2017 - 09 - 18_14 - 51 - 25 backup# incremental backup
- - incremental - base is the path of the previous full backup.

Click ( here ) fold or open

restore operations
mysqlbackup - - backup - dir = / backup/2017 - 09 - 18_15 - 25 - 59 apply - log# apply the full backup database first - log
mysqlbackup - - backup - dir = / backup/2017 - 09 - 18_15 - 25 - 59 - - incremental - backup - dir = / backup/2017 - 09 - 18_15 - 42 - 56 apply- incremental - backup# continue to restore data based on step 1.
mysqlbackup - - defaults - file = / etc/my . CNF - - force - - backup - dir = / backup/2017 - 09 - 18_15 - 25 - 59/ - - datadir = / data/mysql copy - back - and - apply - log# Copy and restore the incremental log.

Method 2: Use image the method of incremental backup

click ( here ) fold or open

backup operations: perform full backup before backup.
mysqlbackup - - user = mysqlbackup - - password = 123 - - backup - image = backup . mbi - - backup - dir = / backup - - with - timestamp backup - to - image# back up 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 - 18_15 - 53 - 57 backup# perform incremental backup based on the original backup

click ( here ) fold or open

restore operations
mysqlbackup - - backup - dir = / backup/full - - backup - image = / backup/2017 - 09 - 18_15 - 53 - 57/backup . mbi image - to - backup - convert dir to the backup file of Method 1. 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_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

6.1 some operations on image backup

click ( here ) fold or open

mysqlbackup - - backup - image = / backup/2017 - 09 - 18_15 - 53 - 57/backup . mbi validate checks the validity of a backup;
mysqlbackup - - backup - image = / backup/2017 - 09 - 18_15 - 53 - 57/backup . mbi list - image list backup objects
mysqlbackup - - backup - image = / backup/2017 - 09 - 18_15 - 53 - 57/backup . Decompress mbi extract and operate it directly in the backup directory
mysqlbackup - - backup - dir = / backup/backup - - backup - image = / backup/2017 - 09 - 18_15 - 53 - 57/backup . mbi image - to - backup - dir specifies the backup directory to decompress.

6.2 Other

about the compression of backup: in fact, the compression of backup is very useful. After testing, it can significantly save backup space. Although it was not demonstrated in the preceding Test, I have actually performed backup compression, and it does not take much time compared with normal backup;

about incremental backup of image: it is much more troublesome to perform incremental backup and recovery of image than to perform full backup. I have been looking for it for a long time in the official document, but I still haven't found the relevant operation instructions, if you know it, please leave a message, thank you very much;

about incremental backup: whether incremental backup is necessary depends on the importance of our system and the speed of data growth. At present, the largest system on our side only generates a G binlog in 2 days, and the overall database is not very large, so now we use full backup every day.

About this software: mysqlbackup actually has many functions, which can be used to back up tables and database objects. At the same time, it can also be used to build slave databases. This function is a good news for large users of databases, and it is strongly against this function;

however, since this software is the Enterprise Edition of Oracle, only paid users can use it, so there is not much online related information. But there are complete official documents.

Please read this disclaimer carefully before you start to use the service. By using the service, you acknowledge that you have agreed to and accepted the content of this disclaimer in full. You may choose not to use the service if you do not agree to this disclaimer. This document is automatically generated based on public content on the Internet captured by Machine Learning Platform for AI. The copyright of the information in this document, such as web pages, images, and data, belongs to their respective author and publisher. Such automatically generated content does not reflect the views or opinions of Alibaba Cloud. It is your responsibility to determine the legality, accuracy, authenticity, practicality, and completeness of the content. We recommend that you consult a professional if you have any doubt in this regard. Alibaba Cloud accepts no responsibility for any consequences on account of your use of the content without verification. If you have feedback or you find that this document uses some content in which you have rights and interests, please contact us through this link: https://www.alibabacloud.com/campaign/contact-us-feedback. We will handle the matter according to relevant regulations.