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

  1. GRANT CREATE , INSERT , DROP , UPDATE ON mysql . backup_progress 'mysqlbackup' 'localhost' ;
  2. GRANT CREATE , INSERT , SELECT , DROP , UPDATE ON mysql . backup_history 'mysqlbackup' 'localhost' ;
  3. GRANT REPLICATION CLIENT ON * . * TO 'mysqlbackup' 'localhost' ;
  4. GRANT SUPER ON * . * TO 'mysqlbackup' 'localhost' ;
  5. 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

  1. full Backup Script
  2. [ ] # mysqlbackup --user=mysqlbackup --password=123 --backup-dir=/backup --with-timestamp backup
  3. restore script
  4. [ ] # 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.
  5. [ ] # 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
  1. full Backup Script
  2. [ ] # mysqlbackup - - defaults - file = / etc/my . CNF - - user = mysqlbackup - - password = 123 - - backup - dir = / backup - - with - timestamp backup - and - apply - log
  3. restore script
  4. [ ] # 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
  1. full Backup Script
  2. [ ] # mysqlbackup - - user = mysqlbackup - - password = 123 - - backup - image = backup . mbi - - backup - dir = / backup - - with - timestamp backup - to - image
  3. restore script
  4. [ ] # 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
  1. backup operations: perform full backup before backup.
  2. mysqlbackup - - defaults - file = / etc/my . CNF - - user = mysqlbackup - - password = 123 - - backup - dir = / backup - - with - timestamp backup# full backup
  3. 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
  4. - - incremental - base is the path of the previous full backup.
Click ( here ) fold or open
  1. restore operations
  2. mysqlbackup - - backup - dir = / backup/2017 - 09 - 18_15 - 25 - 59 apply - log# apply the full backup database first - log
  3. 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.
  4. 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
  1. backup operations: perform full backup before backup.
  2. mysqlbackup - - user = mysqlbackup - - password = 123 - - backup - image = backup . mbi - - backup - dir = / backup - - with - timestamp backup - to - image# back up to image first
  3. 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
  1. restore operations
  2. 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.
  3. mysqlbackup - - backup - dir = / backup/full apply - log
  4. mysqlbackup - - backup - dir = / backup/full - - incremental - backup - dir = / backup/2017 - 09 - 18_15 - 56 - 13 apply - incremental - backup
  5. 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
  1. mysqlbackup - - backup - image = / backup/2017 - 09 - 18_15 - 53 - 57/backup . mbi validate checks the validity of a backup;
  2. mysqlbackup - - backup - image = / backup/2017 - 09 - 18_15 - 53 - 57/backup . mbi list - image list backup objects
  3. mysqlbackup - - backup - image = / backup/2017 - 09 - 18_15 - 53 - 57/backup . Decompress mbi extract and operate it directly in the backup directory
  4. 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.
Selected, One-Stop Store for Enterprise Applications
Support various scenarios to meet companies' needs at different stages of development

Start Building Today with a Free Trial to 50+ Products

Learn and experience the power of Alibaba Cloud.

Sign Up Now