By SanjayM on Sep 26, 2013
Here are the slides for my talk at MySQL connect,
MySQL 5.6 introduced the TTS(transportable table spaces) feature which enables moving a table from one server to another. This feature coupled with MEB 3.9 enables backing up a set of tables matching (regex specified with) the –include option.
The backup of selective tables using transportable tablespaces feature of innodb is referred as tts/selective backup in the remainder of the section.
The difference between a regular partial backup and with using tts is that the regular partial backups are stand alone and cannot be plugged into a another server where as the tts backups in contrast enables the tables to be plugged into another server instance
The Selective Backup Operation
To specify a set of tables to be backed up use the --use-tts option along with the --include=[regex] option for the backup operation. --use-tts option supports two values with-minimum-locking and with-full-locking.
with-minimum-locking - This is the default option. The tables being backed up are hot copied in parallel along with the redo log. After the data file copy, the tables are locked in read only mode, the delta of the log is copied into the backup, and the locks are released. The advantage of this option is that the tables are available for modifications during most part of the back up process and are available in read only mode for a short duration.
with-full-locking - With this options the tables are locked in read only mode during the entire duration of the backup. As there cannot be any modifications while the backup is happening, the tables are consistent and the redo log is not backed up. This saves space and makes the apply log step faster as it just involves some book keeping operations.
mysqlbackup --port=3306 --protocol=tcp --user=root –backup-dir=backupdir –include=Sales.Sales_* --use-tts backup-and-apply-log
The Restore(copy-back) Operation
Restoring from a tts backup requires the server to be running in case of a tts backup unlike the other types of backup. The connection options of the server where the set of tables have to be restored need to be provided for the copyback of a tts backup.
mysqlbackup --defaults-file=/backup-my.cnf --port=3406 --protocol=tcp --user=root --backup-dir=backupdir --datadir=<target_server_datadir> copy-back
Advantages of using MEB for tts Backup
Have a backup strategy for backing a subset of tables(for eg: backing up only important/most used tables).
Take advantage of compressed and image backup options supported by MEB.
This feature can effectively be used to copy a set of innodb tables from one server instance to another.
This feature handles only tables having their own tablespaces(innodb_file_per_table on) and does not support partitioned tables.
MySQL Enterprise Backup has been improved to support single step restore from the latest release 3.9.0. It enables you to restore the backup image to remote machine in single step. However, first you would have to create the backup image in local disk, copy the backup image to remote machine, and then restore in remote machine by running copy-back-and-apply-log command.
You don't have to store the backup contents anywhere,Pipe backup stream directly to remote machine,Optionally, perform compression and decompression on the fly andPerform restore operation simultaneously.
mysqlbackup --user=root --port=3306 --backup-dir=backup --socket=/tmp/mysql.sock --backup-image=- backup-to-image | ssh <user name>@<remote host name> 'mysqlbackup --backup-dir=backup_img --datadir=/data/datadir --innodb_log_group_home_dir=. --innodb_log_files_in_group=8 --innodb_log_file_size=5242880 --innodb_data_file_path="ibdata1:12M:autoextend" --backup-image=- copy-back-and-apply-log'
mysqlbackup --user=root --port=3306 --backup-dir=backup --socket=/tmp/mysql.sock --backup-image=- --compress backup-to-image | ssh <user name>@<remote host name> 'mysqlbackup --backup-dir=backup_img --datadir=/data/datadir --innodb_log_group_home_dir=. --innodb_log_files_in_group=8 --innodb_log_file_size=5242880 --innodb_data_file_path="ibdata1:12M:autoextend" --uncompress --backup-image=- copy-back-and-apply-log'
MySQL Enterprise Backup team is excited to announce the new release of MySQL Enterprise Backup (MEB) 3.9.0.
MEB 3.9.0 focusses more on the ease of use of the product and addresses some of the challenges faced currently by database administrators. With growing data the need for hardware for database backups has significantly increased. Keeping that in mind MEB 3.9.0 has extended the product capability to save disk space during backup and restore. MEB 3.9.0 has also enhanced some of the existing features helping MEB users to perform their tasks in a more user friendly way without much overhead.
MEB 3.9.0 is a major release with the following valuable features.
· Single Step Restore
- Direct restore from backup directory or backup image to the target server
- The restore process is simple and faster and saves significant disk space
- This addresses the customer requirement Bug #68390 Enterprise Backup requires 2x disk space of data dir to restore
- Core feature in this release as it gives advantage of both disk space and performance and thus saving resources.
· Selective Backup and Restore
- MEB 3.9.0 has an improved version to do a selective backup and restore of a single or a subset of tables.
- This is done using TTS (Transportable Table Spaces) and without any need for manual entry of MySQL statements.
- This feature helps in adding the backed up tables onto the same or different online server.
- DBAs would benefit a lot from the ease of use of this command.
· Full Instance Backup
- This feature helps in providing a complete backup so that the restored server can be run with exactly the same configuration as that of the backed up server.
- This helps the DBAs in easy restoration as most of the server details are backed up and available for the restored server to start.
· Skip Unused Pages
- When this feature is enabled MEB skips unused Innodb pages while taking backups. This helps in reduced backup size when there is lot of unused pages in the database.
- This helps in low storage space requirement for the backups taken.
· Console Output Logging
- MEB errors are now redirected to a log file for every operation by default so that it can be referred later to analyze any issues.
- This relieves DBAs from the overhead of saving the error stream to a file manually or performing the operation again for debugging.
For a detailed overview on the above features please refer 3.9.0 documentation.
MEB 3.9.0 is supported on Windows, Oracle Enterprise Linux, RHEL 4, 5, SLES 10,11, Solaris 10 Sparc and X86. Solaris 11 Sparc and X86 is also supported in this release.
In a nutshell, MEB 3.9.0 has extended the backup and restore capabilities making life easier for database administrators.
Stay tuned to read more blogs about the new features in the next coming days.
MySQL Enterprise Backup (MEB) is the ideal solution for backing up MySQL databases. MEB 3.8.2 is released in June 2013.
MySQL Enterprise Backup 3.8.2 release’s main goal is to improve usability. With this release, users can know the progress of backup completed both in terms of size and as a percentage of the total. This release also offers options to be able to manage the behavior of MEB in case the space on the secondary storage is completely exhausted during backup.
The progress indicator is a (short) string that indicates how far the execution of a time-consuming MEB command has progressed. It consists of one or more "meters" that measures the progress of the command. There are two options introduced to control the progress reporting function of mysqlbackup command (1) –show-progress (2) –progress-interval.
The user can control the progress indicator by using “--show-progress” option in any of the MEB operations. This option instructs MEB to output periodically short reports on the progress of time-consuming commands. The argument of this option instructs where the output could be sent. For example it could be stderr, stdout, file, fifo and table.
With the “--show-progress” option both the total size of the backup to be copied and the size that’s already copied will be shown. Along with this, the state of the operation for example data or meta-data being copied or tables being locked and other such operations will also be reported. This gives more clear information to the DBA on the progress of the backup that’s happening.
Interval between progress report in seconds is controlled by “--progress-interval” option.
For more information on this please refer progress-report-options.
MEB can also be accessed through GUI from MySQL WorkBench’s next version. This can be used as the front end interface for MEB users to perform backup operations at the click of a button. This feature was highly requested by DBAs and will be very useful. Refer http://insidemysql.com/mysql-workbench-6-0-a-sneak-preview/ for WorkBench upcoming release info.
Along with the progress report feature some of the important issues like below are also addressed in MEB 3.8.2.
In MEB 3.8.2 a new command line option “--on-disk-full” is introduced to abort or warn the user when a backup process encounters a full disk condition. When no option is given, by default it would abort.
A few issues related to “incremental-backup” are also addressed in this release. Please refer 3.8.2 documentation for more details. It would be good for MEB users to move to 3.8.2 to take incremental backups.
Overall the added usability and the important defects fixed in this release makes MySQL Enterprise Backup 3.8.2 a promising release.
MySQL Enterprise Backup v3.8.2, a maintenance release of online MySQL backup tool, is now available for download from My Oracle Support (MOS) website as our latest GA release. It will also be available via the Oracle Software Delivery Cloud in approximately 1-2 weeks. A brief summary of the changes in MySQL Enterprise Backup version 3.8.2 is given below.
A. Functionality Added or Changed:
B. Bugs Fixed:
Please post your questions / comments about Backup in forums.
As promised we are back with the 2nd edition of Bangalore MySQL User camp this Wednesday 19 June.
As requested we are going into the details of InnoDB and Performance Schema.
See the details here:
Venue remains the same as last time :
Our Kalyani Magnum Office behind Apollo hospital on the Bannerghatta road
Time - 5PM-6PM
Don't miss the chance to meet the MySQL engineering team at Bangalore and learn internals of MySQL.
Hope to see you there.
MySQL Enterprise Backup (MEB) was born 3 years ago as a newly branded avatar of InnoDB Hot backup. Wanted to share what has gone on so far, how we at Oracle think about backup, the milestones that we have achieved and the road ahead. The idea for this blog came to me after looking at Mikael's latest blog. While Mikael talks about MySQL, I want to talk about MEB.
When we started with InnoDB Hot backup the first challenge was to have it adhere to the development, quality and release processes for MySQL. This meant creating a quality plan, getting it into the development trees of MySQL and ensuring that each piece of new code went through architecture and code review. Though the initial implementer and architect of Hot backup continues to work with the MEB team, there were a host of new engineers to be trained. We also needed to ensure that the new (at that time) Barracuda InnoDB file format and incremental backup was supported. MEB 3.5.1 was the release which got these things along with the adherence to the development and quality model of MySQL.
The next challenge we faced was that of ensuring that MEB was on equal footing for both Linux and Windows. InnoDB hot Backup consisted of 2 programs - ibbackup and innobackup; innobackup is a Perl module. The main issue with using the program on Windows was the requirement to install Perl. With multiple Perl implementations and changing Perl versions, we did not want to check MEB compatibility for every implementation and new version of Perl when it was released. Even though the problem is the similar for Linux; the users of Linux are used to hacking around, changing paths and managing multiple versions of software like Perl. Windows users however expect things to just work. So we set about removing the Perl code altogether. This meant that the innobackup functionality had to be re-coded as a C program. Merging these 2 programs meant a major re-think on how the combined command line interface needed to look. The solution we came up with was to let the ibbackup and innobackup command line syntax remain as is, while the combined program had a similar but more logical "mysqlbackup" command line syntax. We were very happy with the new syntax because it freed us from history and MEB syntax became very much in line with the syntax of other MySQL clients. With the release of 3.6 we had a single C program, a more logical syntax , a product which was easier to install and worked exactly the same for all platforms.
We were getting to 2012 and database sizes were commonly approaching the 1 TB size. Such large databases meant the backup should ideally be streamed to tape. Interfacing with tape drives is a complicated and specialized activity. We neither had the bandwidth nor the expertize to handle tapes in MEB. The best solution was to adhere to a good common standard interface that was adopted by software which dealt with tapes. The interface we decided to support was Oracle's System Backup to Tape (SBT) . MEB was modified to be able to to stream the backup output to this interface. A common requirement for these interfaces is that they ideally want to deal with the backup as a single file. A single file can be streamed and restored by any software that speaks SBT. There is a whole ecosystem around SBT because it is the preferred way to backup the Oracle database. Changing MEB to think streaming instead of random access directory output was the challenge we overcame with the release of version 3.7 of MEB. With version 3.7, MEB could interface with Oracle Secure Backup, Symantec Net Backup and Tivoli Storage Manager and any other backup software that understood SBT.
After having resolved what we saw as the "basic" requirements for backup , our customers were demanding more performance and usability. We took up the challenge of performance for the 3.8 version of MEB. MEB was a monolithic single threaded program. We decided to internally break up MEB into 3 separate modules. The read phase, the process phase and the write phase. Each of these 3 phases could be multi-threaded. The number of threads dedicated for each phase were also made user configurable. All operations of backup including the "Applylog" and "copyback" were made multi-threaded. Read more details about this design approach and the performance gains in my blog - Truly Parallel backup. Meanwhile the new release of the MySQL Server 5.6 was also out. It was an interesting challenge to ensure that MEB understood the new MySQL 5.6 features and was able to take advantage of them. As on date MEB 3.8.1 is the only online backup solution that is compatible with the new features of MySQL 5.6.
Backup is like buying insurance. When all else fails you need to be sure that there is a working backup that is available to bring back your database. Backup is not something that can fail when it is needed. It is required that we are surefooted when dealing with such a critical activity. We take your trust in our solution very seriously. Thanks for being a part of the MEB journey (and for reading this blog) so far. The MySQL landscape is ever changing and we know that you desire more usability, performance and flexibility from MEB. We will try and ensure that we meet these expectations with the best possible quality. With every new MEB release you will see a more usable, flexible and performant MEB.
We had a great kickoff to the Bangalore MySQL User camp on 22 March 2013 at the Oracle Kalyani Magnum Office.There were 17 attendees from the MySQL community besides more than 15 MySQL engineers who attended the meet. Of these 3 community members were from groups inside Oracle. There was a mix of new and veteran MySQL users. The MySQL India teams gave a brief introduction about themselves, followed by a quick overdrew of MySQL and how it is different from other databases. The presentation about 5.6 features was presented last and the one that was most liked. There was a great networking over juice and Donuts after the meeting. There were lots of questions by the community on technical details of 5.6 features. Everyone interacted and there was help offered for ensuring that the next meeting on 19th June 2013 is an even greater success.
Thanks to all the attendees, and organizers. Hope to see you in person again in 3 months – on 19 June.
Meanwhile do keep writing and keep connected via linked-in(MySQL India) , Facebook (MySQL User Camp) and Google group(bangalore-mysql-user-camp).
We are planning the kickoff meeting of the Bangalore MySQL User Camp:
We would like to invite all MySQL enthusiasts at Bangalore to join.
Plan is to have at-least one meeting every quarter and give an opportunity to all to participate, attend and speak.
Agenda for 22 March Meeting:
1715-1730 : Meet the MySQL Development team @ Bangalore
1730-1815 : Introduction to MySQL 5.6
1815-1830 : Q&A & Plan for the next meeting
1830-1900 : Tea and informal networking
Conf Rom 0C001,
Ground Floor, B Wing, Oracle,
Kalyani Magnum Software technology Park
1st Main, J P Nagar 7th Phase,
You can also join the google group "bangalore-mysql-user-camp" where we will be announcing further meetings etc.
In the vertical strategy, each thread acts on a separate file. This
limits streaming since the file sizes can vary.
Note : This is a 16 GB, 2 x 2000 MHz, 2 RAID DISKS (1027 GB,733.9GB) machine running Oracle Linux.
As you can see above; MEB 3.8 provides options to configure the number of threads you use for reading, writing and processing. Lets denote RT, PT and WT as number of Read, Process and Write threads respectively. Default values for MEB 3.8 is RT=3,PT=3, WT=3 which is changing in MEB 3.8.1 to RT=1, PT=6, WT=1.
This is close to the fastest backup we get in the graph above. The reason for not choosing RT=1, PT=12, WT=1 (which is the fastest) is because the CPU gets very highly utilized in the 1,12,1 configuration.
Remember, the read write throughput depends on your input and output devices. It is possible that multiple threads do not give you a better performance for read or write v/s a single thread.
There are also options available to have a configurable number of buffers used by these threads.
Each buffer is of size 16MB. You should have at-least [RT+PT+WT+ (MAX(RT,PT,WT) ] number of buffers so that you get optimal parallelism.
For Example if RT=1, PT=6, WT=1 then you should configure 1+6+1+6 = 14 buffers (default in MEB 3.8.1)
If for example you configure multiple threads but configure only 1 buffer then your backup is not taking advantage of parallelism at all. The read thread reads into the single buffer, buffer is then processed, written and then freed. The read thread is waiting for a buffer to be free to read into it; so it is like a serial process.
One more thing to note is that the number of buffers is limited by the memory limit configured for backup (default 300MB). Please ensure that you configure enough memory to be able to distribute it to the buffers you have configured. If the memory limit configured is less then what is required for the configured number of buffers; MEB will automatically decrease the number of buffers to fit into the memory limit. Based on the default values, if you are configuring more than 18 buffers you will need to increase the memory limit.
Please look at the previous 3.8 blog for detailed configuration examples :
or into our documentation of this feature at
and remember the wise DBA advise:
If you don't verify your backups periodically it is like not having backups at all
The MySQL Enterprise Backup 3.8.1 release's main goal was support MySQL 5.6 server. But also beyond that primary goal MEB team added some valuable new options and features to ensure you'll get most from the new features in 5.6 as well. At a glance, here are some of the highlights,
MEB copy of InnoDB undo log tablespaces
MySQL 5.6 introduces a new feature to store undo logs in separate files called as undo tablespaces for improved performance. These undo tablespaces are logically part of system tablespace. All the commands associated with MEB - "backup", "apply-log" and "copy-back" now take care of the undo tablespaces in the same way as they process the system tablespace. MEB now supports innodb_undo_directory[logs][tablespace] option variables. When backup is executed, undo datafiles (up to number specified by innodb_undo_tablespaces) are stored in same directory as the datafiles of system tablespace. During copy-back, files can be stored in a location specified by the user using option --innodb-undo-directory.
MEB support for Global transaction ID's
GTID feature is newly introduced in MySQL 5.6 server. GTID's help to track the data being replicated particularly with the automatic slave promotion when a master fails.
When server is started with GTID's enabled and backup is performed on the master server, mysqlbackup produces a new file called as gtid_executed.sql in meta backup directory . This file is used after restoring the backup data on slave server and contains GTID_PURGED option. This provides information from the server at the end of the backup, thereby ensuring that replication starts from the point in time when backup was taken.
UNC Path name support
MEB now supports UNC path names by specifying a location of network resource such as shared file, directory or printer. This feature helps to start backups using windows task scheduler when shared drives cannot be mapped to a drive letter. Support for UNC path names also allows MEB to take backups when user is not logged in.
eg: ./mysqlbackup --defaults-file=/home/my/my.cnf --backup-dir="\\mysql\\testmeb\" backup
Where testmeb is shared network directory on windows.
When the shared name is corrupt / invalid, MEB detects and then tries to access the files pointed to by the path and prints an error message.
MEB support for different page size settings for InnoDB
InnoDB page size is the server parameter that is associated with all the innodb tablespaces in the MySQL instance. By default the value of this size used to be 16K in the versions less than MySQL 5.6. But from MySQL 5.6, this option is made user configurable to 4k, 8k, 16k etc. Starting from MEB 3.8.1, Backup will work successfully when server is started with different innodb_page_size values. The innodb-page-size option can also be specified in the mysqlbackup command line but MEB will ignore the option provided the connection to server is available. If innodb_page_size option is not specified in command line or if connection to server is not available, then the value of innodb-page-size is read from the header of the innodb data files.
InnoDB Checksum Algorithm Support
MEB 3.8.1 introduces new option support of --innodb-checksum-algorithm in MySQL 5.6. This option can also be specified on the command line. A default option is used if its not specified in the command line and also if the connection to the server is offline. Without the support for this new option, MEB could not start the server after sequence of backup, apply-log and restore operations. One thing to be noted here is,
a. Server backed with strict_crc32, strict_innodb or strict_none checksum algorithms should be restored with the same algorithm
b. Server backed with mixed algorithms should not be restored to a server with strict_* algorithms.
Backup of system tablespace with fractional megabyte.
It sometimes happens that InnoDB engine extends datafiles of system tablespace by few megabytes. But if the disk is full, then system tablespace will actually extend to fractional megabytes. During such cases, MEB performs a consistency check on the sizes of InnoDB datafiles and if the size does not match the size of the file on the disk, a warning is reported.That is MEB does not backup the fractional datafile in the system tablespace.
Backup restore file per table tablespaces at different locations.
In MySQL 5.6, it is possible to create new InnoDB table with per-table tablespace outside of data directory where .ibd file should be created instead of default location in the database sub directory. For each .ibd file, a .isl file is created in the database subdirectory containing absolute path name acting like a symbolic link to actual tablespace file. All the MEB operations are now able to read the .isl files to locate the .ibd files during backup. During backup, both .isl and .ibd files are copied to the backup directory but .isl is renamed as .bl file. During copy-back, .ibd files are being copied to a location specified in .bl file. But if the target location is changed where the restore of the backup should be performed, then one needs to manually edit the .bl file before doing the restore and specify the abs path name where .ibd files should go.
The above mentioned are new features added to backup code but this release also includes various bug fixes, please take a look at the MEB 3.8.1 reference manuals for more details.
The MEB team has put great deal of efforts to ensure that latest release of MEB 3.8.1 is compliant with MySQL 5.6 server. Please try this new MEB 3.8.1 version with MySQL 5.6 server and as always send us your feedback / comments here. MEB 3.8.1 is now available in My Oracle Support site and will very soon be available in Oracle's Cloud delivery site.
Once again, I would like to thank entire MEB team to deliver this release on time and with many valuable new additions.
MySQL MEB Team Blog