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 26, 2013
By SanjayM on Sep 26, 2013
Here are the slides for the talk given at Oracle Open world about MEB & OSB.
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.
By Dhamotharan on Sep 19, 2013
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.
This approach has two overheads:
Serial execution: You have to wait for each step to finish before beginning the next (e.g. You must have to wait for backup-to-image operation to finish before beginning copy).
Disk consumption: You might not have enough space on the source disk to store that backup-image in the first place.
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.
How to do it:
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'
Sample command with compression:
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'
Wednesday Sep 18, 2013
By Ingo Strüwing on Sep 18, 2013
DisclaimerThe views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
IntroductionThere are database usage patterns, where tables grow big at times and many rows are deleted from them later. InnoDB does never shrink a table space. In these cases we can end up with big data files, which contain a lot of unused pages. It is a waste of disk- and I/O- resources to back them up.
Users have manifold requested that MySQL Enterprise Backup does not back up unused InnoDB data pages. Some want smaller backups, some want less I/O, some want shrinked table spaces.
MySQL Enterprise Backup 3.9.0 can help with smaller backups. The effect on I/O is not that remarkable. InnoDB data files must be expanded to their original size when they are restored. Backup cannot accomplish a shrinkage of InnoDB table spaces.
In the following I will try to explain, how things work, and why not all wishes can be satisfied. I will also try to show the complexity of the feature. This should clarify, why it comes so late. Because my explanations might be a bit technical, I'll try to summarize important facts in advance.
Administrative SummaryYou can use the command line option --skip-unused-pages with any backup operation, but it will be ignored and give a warning for the following cases:
- backup-and-apply-log. The skipped pages must be re-inserted at the beginning of the apply-log operation. So it would be a waste of resources to do this when backup and apply-log are combined in one operation.
- incremental-with-redo-log-only. This operation copies log pages only. No data pages are copied. So there is no data page to be skipped.
- incremental backup: This is simply not implemented in MEB 3.9.0.
At the beginning of the apply-log operation, the skipped pages must be re-inserted. This means that every backed-up InnoDB data file must be copied over with the skipped pages stuffed in. This means that the total apply-log operation will take at least as long as the backup operation took.
Be prepared that the backup directory grows up to the original database size. At the end of each file, the backup file will be removed, but right before that point, the file exists in the compact and expanded form. You need to have the space for one extra copy of your biggest table.
You can combine --compress and --skip-unused pages. However, in MEB 3.9.0, the uncompression and expansion operations are executed in separate steps. Since both operations copy over all data files, it will take about twice the time as the backup operation took.
In MEB 3.9.0, the single-step restore operation copy-back-and-apply-log cannot be used on backups that have been taken with --skip-unused-pages.
PrerequisiteThe InnoDB table spaces contain bitmap pages, which indicates pages as free or not free (in-use, that is). The bitmap pages occur every page_size pages in the tablespace. For uncompressed tablespaces, that would be on pages 0, 16384, 32768, 49152, 65536, and so on. For a compressed tablespace with a 2K page size, the bitmap would be on pages 0, 2048, 4096, and so on. The bitmap page always covers the following page_size pages.
NomenclatureIn the following, the set of pages, which are covered by a bitmap page, is called a "map-set".
In the following, the terms "free page" and "unused page" are used interchangeably.
In the following, the term "zero page" means an InnoDB page, which has all its bytes set to zero.
The "free limit" is an InnoDB internal term. It is a page number. It is less or equal to the table space size in pages. If the free limit is less than the table space size in pages, itself and all pages above it are free.
A LSN is a Log Sequence Number. It is the offset in bytes from the logical start of the redo log. The LSN marks the start of a log entry.
The general problemThe task sounds simple. Read the bitmaps. Identify unused pages. Omit them from backup. Replace them with empty pages on restore. All over.
It would be almost as easy as this if we would take backups from cleanly shut down databases. That is, if all data files would be in a consistent state and not be modified during the backup operation.
But if a backup is taken from a hot database, the data files are modified while the backup operation is reading them. Since we cannot read all pages at once, we likely read pages that are modified at different times. When we read a bitmap page and later the pages, which are covered by this bitmap, then they may not be consistent. The bitmap could declare some page as free, but while we read other pages in between, the page may no longer be free when we read it. And vice versa.
Another problem is the InnoDB data cache. Pages are written from the cache to disk at different times. The main constraint is, that a page is never written before all log entries, which describe its modifications, are on disk. Another constraint is that at the time, when an InnoDB checkpoint is noted in the redo log, all page modifications up to that log entry are on disk. Since we start copying the redo log from the latest checkpoint, each page on disk can have any state between that
checkpoint and the current state.
Sure, we have the redo log. We keep copying it in parallel to the data file copy. It should be able to replay all changes that are done on the data pages during the backup operation. But the replay algorithm requires each modified page to be in a certain state. That is, it expects that there are certain data in the page. Each redo log entry describes a transformation of the page contents from one state to another. If the page doesn't have the expected contents, the algorithm fails.
This means that we need to take care, what page contents to restore for pages that were marked free when the corresponding bitmap page was copied. The following shall show, which problems needed to be resolved.
Empty pagesAbove we said that we will replace skipped unused pages by empty pages. However, the term "empty page" does not mean a zero page. This won't work because of the following reasons.
The redo algorithm is an idempotent algorithm. The idem-potency is based on the page LSN. Every change to a data page is logged in a log entry of the redo log. That log entry's LSN is written into the page. When the redo log is applied to a database or backup, a log entry is applied to a page only if the page's LSN is lower than the log entry's LSN.
The redo algorithm does not use a pure physical logging. Most log entries do not set a certain number of bytes at a certain offset in a page, but transform a page from one state to another. In other words, the algorithm relies on the correct page contents.
We copied the redo log from the latest checkpoint onward. Usually this contains log entries from before the backup start. So it could happen that there were log entries, which modified a page, before it became unused and before the backup started.
If the unused pages had been recreated as zero pages, their LSN would be zero. Every log entry's LSN is greater than zero. So every log entry for that page would be tried to apply. But the page contents is not, what the log entry expected. The redo log algorithm would fail.
If the unused pages had been recreated as zero pages, but with their LSN set to infinite, no log entry would be applied to unused pages. The apply-log algorithm would finish without errors and warnings. But if there are log entries, which re-initialize and modify the page after the backup operation read the bitmap page, they would not be applied due to the high LSN. The result would be an inconsistent table space.
The correct solution is to use empty pages with the LSN of the bitmap page, which claims it to be an unused page. We know that the page was unused at that LSN. If a page gets freed or re-used, the bitmap is changed and that makes an redo log entry for the bitmap page. The bitmap page gets that LSN. When we copy the bitmap page, we get the bitmap and the corresponding LSN into the backup. Our algorithm assures that each page is consistent in itself. So we have assured knowledge, which pages were free at that LSN. The next higher LSN that affects an unused page must consequently be a log entry, which re-initializes the page. Any log entry with an LSN lower than the bitmap LSN is irrelevant to the pages that are marked free by that bitmap.
Besides the LSN, empty pages will also have the page number, which corresponds to their location in the table space, the table space id, and the checksums set. The remaining part of empty pages are all zero bytes.
Non-free zero pagesThe page number of a zero page is zero. Usually one would expect that pages with a page number of zero have never been used and are marked free (except of the page at offset zero - the table space header). But sometimes a page like this is not marked free.
One possible situation in which a page could have a zero page number and not be marked free could occur if a page did become used for the first time short before the backup started, and was not flushed to disk until backup read the page. When the page became used, the bitmap was updated and flushed and the page was updated in memory but not flushed yet. It is possible, yes even probable, that there are redo log entries in the log file, which manipulate the page. The corresponding log entries could have LSNs below the bitmap page's LSN if later more changes were done to the bitmap page. If we would skip the page, and thus effectively declare it as free, the expansion algorithm would insert a page with the bitmap page's LSN, which could be too high.
To be safe, we include non-free zero pages in a backup. They are rare and thus don't make a big difference.
Skip unused pages when reading from the original data filesIf we use the bitmaps to avoid reading of unused pages, we turn sequential reading into random reading. Depending on the distribution of unused pages among used pages, this could even drop the read performance. Only if unused pages occur in big contiguous chunks, skipping those could give a speed increase.
Since a bitmap page occurs every page_size pages, only page_size - 1 pages can be skipped in one go at best. Hence, the optimal performance enhancement cannot be reached. All bitmap pages are used pages and must be copied to the backup, even if they mark all "their" pages as free. After all, we could detect such case only after having read the bitmap page. Anyway, it will be rare cases that multiple bitmap pages in a table space mark all their pages free. In theory this could happen in a new table space, which was created way too big for the data. But then InnoDB maintains the free limit. Bitmap pages at and above the free limit are not initialized and don't need to be backed up.
At the beginning of each map-set, only the bitmap page needs to be read. Then unused pages can be identified in contiguous chunks. If a chunk is big enough, then that chunk can be skipped from reading.
It does not seem desirable to read in different chunk sizes. So the read algorithm is now designed so that the read size is the data buffer size. At the beginning of a map-set, a data buffer is always read. Skipping of read pages is done in multiples of the data buffer size. A buffer can only be skipped, if all its pages are unused. Since MEB does currently use a fixed buffer size of 16 MB, it contains at least 1024 pages, depending on the table's page size. The probability for 1024 contiguous pages to be free isn't that high. That's why we won't reduce the I/O load much. And so we haven't implemented this part yet.
Skip unused pages when writing to the backup filesOn the write side it is advantageous to suppress even single unused pages. It doesn't break sequential writes.
The algorithm is similar to incremental backup. From the read side we get a buffer, which could contain unused pages. For the write side we produce a buffer with only the used pages from the read buffer. For each page, we decide independently, if we copy it to the output buffer. Every bitmap page needs to be included.
Compressed files must not be empty. If the backup files are compressed before they are written, we must assure that they contain some contents. This is a requirement of our compression algorithm. Since we include all bitmap pages, this is not a problem for file-per-table table spaces, nor for the first file of the system table space. But it can happen if a follow-up data file of the system table space has all its pages free, and does not have a bitmap page below the free limit. To work around this problem, we do always include the first page of a file.
A map-set can cover pages from multiple buffers. We need to keep them in memory until all covered buffers are written.
RestoreOn restore, MEB has to recreate unused pages at the right places. The reason for this is explained below in the section "Backup cannot shrink table spaces". Since the contents of the inserted pages do not matter, except of page number, space id, LSN, and checksums, empty pages are written.
In the following, the algorithm to recreate the skipped pages is called "expansion".
Please note that the expansion must take place before an apply-log operation. The apply-log algorithm works on a data file where all pages are at their correct places. Apply-log can modify initially unused pages. Hence those must also be present at the right places in the data file, and have the right LSN.
In MEB 3.9.0, a sequential algorithm is used, which detects skipped pages by a mismatch of a page number and the current write position in the expanded file. Empty pages are inserted until the page number matches the current write position.
If the last page from the backup file is below the free limit from the table space header, empty pages are appended up to the free limit. If the resulting page is below the table space size from the table space header, zero pages are appended up to the table space size.
Backup cannot shrink table spacesWhat users really want, is a feature to remove unused pages from a table space, and make the data file(s) smaller.
MEB cannot help with it. Every InnoDB page has a page number, which corresponds to its position in a data file. The InnoDB tablespaces contain tree structures, where a page can reference one or more other pages. These references are done by page numbers. Hence it is vital that every page retains its position in a data file. If there are unused pages among used pages, the used pages cannot be simply shifted down in a file to take the place of an unused page.
If one wants to change a position of a page, one must assign it a new page number (the one that corresponds to its new place) and modify the page number in all places that reference the page. This means that a bunch of random-access page-read and page-write operations can be necessary for each shifted page.
If a table has a single-file table space (innodb-file-per-table=ON) then an OPTIMIZE TABLE statement would create a new tablespaace with freshly constructed trees and thus take the minimum amount of space. Unfortunately that operation can put a too big burden on the database. With MEB, one could think of a workaround. Do a backup of the table, restore it to a temporary place, run a server on it, OPTIMIZE TABLE, and transport the resulting tablespace to the original server.
Wednesday Aug 28, 2013
By Priya Jayakumar on Aug 28, 2013
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.
Friday Jul 19, 2013
By Raveendra Reddy on Jul 19, 2013
Friday Jun 28, 2013
By Priya Jayakumar on Jun 28, 2013
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.
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.
Monday Jun 17, 2013
By SanjayM on Jun 17, 2013
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.
Friday May 03, 2013
By SanjayM on May 03, 2013
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.
Thursday Mar 28, 2013
By SanjayM on Mar 28, 2013
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).
Tuesday Mar 19, 2013
By SanjayM on Mar 19, 2013
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.
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