Monday Nov 25, 2013
Sunday Sep 29, 2013
By Raveendra Reddy on Sep 29, 2013
MySQL Enterprise Backup(MEB) takes fast, consistent backups of MySQL server data, and helps in restoring the server to source server's data at the time of backup. But most of the times it is as much important to have same source server's state(server configuration like server global variables, plugins), as data. As backups become more frequent, server variables modified, plugins added or removed, it is very difficult to keep track of this changing server states for every backup. MEB 3.9.0 helps user in providing a complete backup so that the restored server can run with exactly the same state as that of the source server at the time of the backup.
MEB 3.9.0 performs full server instance backup, which, on top of the log files and data files, also includes all the global variables and plugins(both internal and external) details. With this feature, backup-content.xml, a meta file under "meta" folder of the backup directory , now additionally contains all the plugins details like name, status, type etc under <plugins> section. In addition, there are two new files created under backup directory
- server-my.cnf - contains all the global variables with non-default values for that server environment(MySQL server version, Operating System, Hardware Architecture etc).
- server-all.cnf - contains all the global variables, that includes all the variables with non-default values and the other global variables with default values.
Advantages of Full Server Instance Backup:
- Create replica - User can clone source server state by using either server-my.cnf or server-all.cnf file in the backup as defaults-file for starting the target server. As most of the global variables default value depend on the server environment, user can create a server with same state even if the target environment is different from source by using server-all.cnf as defaults file. If the target environment is same as source, user can use either of the files.
- Keep a history of global variables - This new feature reduces the user's task for storing the state of the server, if changed, before every backup or incremental backup, so user need not worry about keeping track of server global variables. With this feature, now we can figure out non-default values of global variables for the running server with server-my.cnf.
- Full plugins information - With all the plugins information backed up, this information will be used while installing missing plugins on restored server. Plugin details like type, status, library can be used to install missing plugins with the same configuration of the source.
Using Full Server Instance Backup:
From MEB 3.9 onwards, this feature is enabled by default for all kinds of backup(i.e normal, incremental, image, compressed etc). That is there is no need to turn on any feature or use any option and all backups are full server instance backups.
Note: Binary logs and *info files used for replication and Innodb buffer pool details, which are also part of server instance, are not included in backups. Server plugins details are copied, but actual plugins binaries are not copied.
After copy-back operation, server-all.cnf, server-my.cnf will be present in restored data directory. If there were any external plugins exist in source server, copy-back operation throws a warning about the missing plugins to install.
The files server-my.cnf, server-all.cnf, which could be used as defaults-file to start the server on the restored data directory. When source and target environments are same, restoring server using server-my.cnf will be easier than using server-all.cnf as server-my.cnf has fewer global variables to verify or modify.
Note: User has to be careful while starting another server instance on the same host using server-my.cnf, server-all.cnf files without changes. There is a possibility of modifying source server settings or data, as some file paths like innodb_log_group_home_dir, innodb_log_group_home_dir,tmpdir,general-log etc are related to source server.
The files server-my.cnf,server-all.cnf reflect the state of the server at the time of a incremental backup and it is desirable to have the same state after applying the incremental backup. So after apply-incremental-backup operation, full backup's server-my.cnf and server-all.cnf will be overwritten by corresponding incremental backup's server-my.cnf and server-all.cnf files.
Thursday Sep 19, 2013
By Maheedhar PV on Sep 19, 2013
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.
Friday Jul 19, 2013
By Raveendra Reddy on Jul 19, 2013
Wednesday Jun 26, 2013
By Hema Sridharan on Jun 26, 2013
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:
- MySQL Enterprise Backup has a new --on-disk-full command line option. mysqlbackup could hang when the disk became full, rather than detecting the low space condition. mysqlbackup now monitors disk space when running backup commands, and users can now specify the action to take at a disk-full condition with the --on-disk-full option. For more details, refer this page
- MySQL Enterprise Backup has a new progress report feature, which periodically outputs short progress indicators on its operations to user-selected destinations (for example, stdout, stderr, a file, or other choices). For more details on progress report options, refer here
B. Bugs Fixed:
- When --innodb-file-per-table=ON, if a table was renamed and backup-to-image was in progress, apply-log would fail when being run on the backup. (Bug #16903973)
- MySQL Server failed to start after a backup was restored if there had been online DDL transactions on partitioned tables during the time of backup. (Bug #16924499)
- apply-log failed if ALTER TABLE ... REORGANIZE PARTITION was applied to partitioned InnoDB tables during backup. (Bug #16721824, Bug #16903951)
- apply-incremental-backup might fail with an assertion error if the InnoDB tables being backed up were created in Barracuda format and with their KEY_BLOCK_SIZE values different from the innodb_page_size . This fix ensures that different KEY_BLOCK_SIZE values are handled properly during incremental backup and apply-incremental-backup operations.
- If a table was renamed following a full backup, a subsequent incremental backup could copy the .frm file with the new name, but not the associated .ibd file with the new name. After a restore, the InnoDB data dictionary could be in an inconsistent state. This issue primarily occurred if the table was not changed between the full backup and the subsequent incremental backup. Bug #16262690)
- After a full backup, if a table was renamed and modified, apply-incremental-backup would crash when run on the backup directory. (Bug #16262609)
- The value of the binary log position in backup_variables.txt could be different from the output displayed during the backup-and-apply-log operation. (This issue did not occur if the backup and apply-log steps were done separately.) (Bug #16195529)
When using the --only-innodb-with-frm option, MySQL Enterprise Backup tried to create temporary files at unintended locations in the file system, which might cause a failure when, for example, the user had no write privilege for those locations. This fix makes sure the paths for the temporary files are correct. (Bug #14787324)
- A backup process might hang when it ran into an LSN mismatch between a data file and the redo log. This fix makes sure the process does not hang and it displays an error message showing the name of the problematic data file (Bug #14791645)
Please post your questions / comments about Backup in forums.
Friday Feb 08, 2013
By SanjayM on Feb 08, 2013
How do you ensure that you have the capability to be able to tune the level of parallelism for varying input and output devices and varying levels of load?
These were some of the questions that we needed to answer when we were trying to implement multi-threading capability for MySQL Enterprise Backup (MEB).
The trivial way of achieving parallelism is by having the multiple threads pick up the different files (in a file per table) scenario. But this did not seem adequate because:
a) The sizes of these files (corresponding to the tables) could be different and then one large file would limit the level of parallelism since it would be processed by a single thread.
b) If you have to stream the backup how do you reconcile these multiple files being streamed by separate threads? Large backups are streamed directly to tape so it is better to have a single file being output and not multiple files.
c) If you buffer each file and wait for a file to be completely processed and then push it to tape then it is not true streaming because you are using intermediate disk space to save the incomplete portions of all the files.
The answer that we found was to implement the parallel algorithm using a horizontal strategy instead of a vertical strategy.
In the vertical strategy, each thread acts on a separate file. This
limits streaming since the file sizes can vary.
In the horizontal strategy, each file is broken into a sections (denoted by multiple colors). A separate thread is assigned to operate on a single section.
Parallel operations are then possible for reading , processing and writing of these file subsections because no two threads will be operating on the same section of the file.
This setup is especially useful when using compression since there can be multiple threads performing compression while the read and write continues in parallel.
There may be additional overhead of ensuring that the buffers are in the correct order when they are written out, but since most of the buffers of the same size and having similar operations being performed, the overhead is minimal.
You get truly serialized output that is streamed to tape as it gets processed. If you are streaming to a remote host or to tape, there is almost no additional space required on your main server. We call this new mechanism parallel backup because we are achieving parallelism thereby making the backup faster. Indeed, using parallel backup may see up to 10 times the speed of a normal backup in certain scenarios.
The graph below shows the time it took for backup for MEB 3.7.1 v/s MEB 3.8 using varying number of threads.
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
Thursday Feb 07, 2013
By Hema Sridharan on Feb 07, 2013
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.
Friday Oct 05, 2012
By Hema Sridharan on Oct 05, 2012
1. Performance improvements in MEB 3.8.0
2. Incremental Base option from MEB 3.7.1 where there is no need to specify the directory name of the previous backup to fetch the lsn values and instead can directly fetch from backup_history table using --incremental-base=history: last_backup
3. only-innodb-with-frm option introduced in MEB 3.7 version. A true online hot backup of InnoDB tables.
I also attended a session with similar topic "MEB Best Practices" conducted by Sanjay Manwani, where he double clicked all the features and best strategies of backup & restore. I also got an opportunity to attend other sessions including,
* Enabling the new generation of web and cloud services with MySQL 5.6 replication
* Getting the most out of MySQL with MySQL Workbench
* InnoDB compression for OLTP
* Scaling for the Web and Cloud with MySQL replication.
Above all, had some special moments in the conference including meeting some of the executives / colleagues for the first time f2f. On a whole, the first MySQL Connect conference was a great success in terms of manifesting the features of our products, direct feedback from customer and team building. We also had some applauding yahoo moments when Tomas Ulin announced different releases including MySQL 5.6 RC, Connector Python 1.0 and ODBC 5.2 release, MySQL Cluster 7.3, additions to MySQL Enterprise edition etc.
MySQL MEB Team Blog
- MEB integration with Workbench
- Backing up full server instance using MySQL Enterprise Backup
- MEB & OSB slides for the the talk at Oracle open World
- Slides for MEB talk at MySQL Connect
- Backing up selective innodb tables using MEB.
- How to restore directly on a remote machine from the backup stream
- Skip Unused Pages with MySQL Enterprise Backup 3.9.0
- MySQL Enterprise Backup 3.9.0 – An Insight
- Improved Backup Progress in MySQL Enterprise Backup(MEB) 3.8.2
- MySQL Enterprise Backup 3.8.2 - Overview