Mysql Enterprise Backup verison 3.9

Mysql Enterprise Backup 3.9 is availble - her are some notes on it:
https://blogs.oracle.com/mysqlenterprisebackup/entry/mysql_enterprise_backup_3_9

We have just started using MySQL Enterprise Backup (MEB) version 3.8/3.9.

There are few major changes since 3.7.   The most noticeable is the use of parallel threads.  The parallel threads which comes in the form of read-threads, process-threads and write-threads.  The thread information is displayed within the logfie when you run a backup
For example:
130917 02:06:57 mysqlbackup: INFO: Full Backup operation starts with following threads
                3 read-threads    3 process-threads    3 write-threads
The default is 3 for each

From the command line you can specify this by using the following:
$mysqlbackup -u root --backup-dir=/db/backup/FULL_38TEST --limit-memory=1024 --read-threads=2 --process-threads=6 --write-threads=2 backup-and-apply-log 
 In the logfile:
  mysqlbackup: INFO: Creating 16 buffers each of size 16777216.
130424 04:44:07 mysqlbackup: INFO: Full Backup operation starts with following threads
                2 read-threads    6 process-threads    2 write-threads


Some of the key parameters:
Number-of-buffer - specify the number of buffers to be used each is 16MB.  If not specified this is computed internally.   The formula for setting this is
(read threads + write threads + process threads + max(read threads, write threads, process threads)).  So in the above example,  2+2+6+6=16, each is 16MB, so it was 16 threads of 16MB each.                                                       


Read-threads - Applies to copy-back, extract and backup.  Maximum is 15.  For the apply process it is always set to 1.  I assume this is adjusted if you run a backup-and-apply-log.

Process-threads - Used for processing data, such as compressing and uncompressing data.  Applies to these kinds of operations: extract, and backup. It is ignored when you use any of the options --incremental-with-redo-log-only, apply-incremental-backup, copy-back, or backup-dir-to-image.

Write-threads -  For copy-back, extract and backup.  Maximum is 15.  For the apply process it is always set to 1.  I assume this is adjusted if you run a backup-and-apply-log.

Limit-memory - Default is 300MB Specify maximum memory in megabytes that can be used by the mysqlbackup command. Formerly applied only to apply-log operation, but in MySQL Enterprise Backup 3.8 and higher it applies to all operations. Do not include any suffixes such as mb or kb in the option value.

Testing note - I have been testing the use of threads and backups and have found using threads is a fine balancing act, if you use too many threads then it can slow the backup down.  When I started testing did a baseline backup with 3 threads and then doubled it.  It was considerably slower.


Other useful extras are:

--on-disk-full - in previous version if the backup used all the disk space it would just hang.  Now you at least know it has run out of space and can print a warning to the logfile.
--on-disk-full={abort|abort_and_remove|warn}
For example --on-disk-full=warn:
130918 11:49:40 mysqlbackup: INFO: Copying /db/DB1/data/vantage/assumptivedata#P#P20130415.ibd (Barracuda file format).
130918 11:50:10 mysqlbackup: WARNING: Disk full while writing '/db/backup/FULL_39_18thSept/2013-09-18_11-49-15/datadir/vantage/assumptivedata#P#P20121115.ibd'
130918 11:50:42 mysqlbackup: WARNING: Disk full while writing '/db/backup/FULL_39_18thSept/2013-09-18_11-49-15/datadir/vantage/assumptivedata#P#P20121115.ibd'
130918 11:51:14 mysqlbackup: WARNING: Disk full while writing '/db/backup/FULL_39_18thSept/2013-09-18_11-49-15/datadir/vantage/assumptivedata#P#P20121115.ibd'
130918 11:51:46 mysqlbackup: WARNING: Disk full while writing '/db/backup/FULL_39_18thSept/2013-09-18_11-49-15/datadir/vantage/assumptivedata#P#P20121115.ibd'
130918 11:52:18 mysqlbackup: WARNING: Disk full while writing '/db/backup/FULL_39_18thSept/2013-09-18_11-49-15/datadir/vantage/assumptivedata#P#P20121115.ibd'

--skip-unused-pages - this could be a space saver, hopefully reducing backup sizes.  This option is applicable to the backup and backup-to-image operations, but not to incremental backups. The option is ignored by the backup-and-apply-log operation.
You will see something like:
130918 15:18:33 mysqlbackup: INFO: Skipped 2 of 10 pages, 20%, '/db/DB1/data/test1.ibd'

A new operation, copy-back-and-apply-log, allows users to restore a backup from an image file or a backup directory and perform an apply-log operation to the restored data to bring them up-to-date, all by a single command.


Support for GTID, if using MySQL 5.6 and replication.   It creates a file with the GTID_Purged information, which can be executed to start replication.

Now backs up the my.cnf as default
$ cd 2013-09-18_12-58-58/
$ ls -lrt
total 28
-rw-r--r-- 1 mysql mysql  247 Sep 18 12:58 backup-my.cnf
drwx------ 9 mysql mysql 4096 Sep 18 12:59 datadir
-rw-r--r-- 1 mysql mysql 2585 Sep 18 12:59 server-my.cnf
-rw-r--r-- 1 mysql mysql 8679 Sep 18 12:59 server-all.cnf

drwx------ 2 mysql mysql 4096 Sep 18 12:59 meta


Lastly the --use-tts option. I assume this is only something you can use with mysql 5.6 as I think it probably uses transportable tablespace.  The problem with transportable tablespaces is it can not currently be used with  partitioned tables at all.  So there will be some limitations to it.  I plan to do some testing.  Will post the results when I have them.

Documenation reference:
http://dev.mysql.com/doc/mysql-enterprise-backup/3.9/en/backup-capacity-options.html

No comments:

Post a comment