Categories: Specifications | MySQLDevelopment | Software Preview

OnlineBackup

Contents

[edit] Introduction

MySQL Backup is currently being developed and this page describes the work in progress.

MySQL Backup enables you to backup a consistent image of a MySQL Server's data and associated metadata via a direct connection to the MySQL server.

The backup is synchronized between different storage engines and with the binary log (that can be used for point in time recovery). Different techniques are used by different storage engines to provide the best possible backup and restore. The backup image is stored as a file by the MySQL server.

You can read more about this in MySQL Backup Reference Manual

This project is run by the MySQL Replication and Backup Team with the help of contributors to separate modules:

Please send us (Lars Thalmann <lars@mysql.com>, Rafal Somla <rafal@mysql.com>, or Chuck Bell <cbell@mysql.com>) any comments you may have.

We'll are looking for and encouraging people to help review and test the code. The first parts of MySQL Backup was released in MySQL 6.0.5.

[edit] Download

[edit] MySQL 6.0.5 Features - completed (phases 1-6)

  1. Online backup of table data, i.e. non-blocking for DML
  2. Version handling to ensure that backup files are always restorable
  3. Simple-to-use SQL interface
  4. Fast backup and restore using engine-specific drivers that take advantage of each storage engine's unique features
  5. Default blocking driver for any engine (note that this is not online)
  6. Default consistent snapshot driver for InnoDB, Falcon
  7. Backup of table and database definitions
  8. Privileges to control who can do backups and restores
  9. WL#3576 Point-in-time restore using binlog synchronization
  10. WL#3956 Consistent backup between and within engines
  11. WL#4062 Blocking of metadata statements during backup/restore process so that they don't corrupt the ongoing backup
  12. WL#4116 Information schema with progress information
  13. WL#3574 Backup of Views
  14. WL#3574 Backup of Stored procedures and Stored functions
  15. WL#3582 Backup of Triggers
  16. WL#3574 Backup of database-owned Events
  17. WL#4240 Save/restore table space information
  18. BUG The information returned by BACKUP and RESTORE statements is not machine parsable (part of WL#4116)
  19. BUG The size of a buffer used for data transfers between backup kernel and drivers is fixed (2MB currently). This can make it impossible to backup some tables. E.g., the built-in backup drivers will refuse to backup tables in which single row is bigger than this limit.
  20. BUG Views can cause the server to crash
  21. BUG If a table name has non-standard characters in it, its name can be corrupted
  22. Bug#30340 Backup images can be large for default drivers (Bug#30340)
  23. Bug#32301 Falcon storage engine does not support consistent read via handler methods
  24. Bug#32190 Online backup does not save character set settings for databases
  25. Bug#32385 The test backup_no_data does not clean up after itself

[edit] MySQL 6.0.6 Features - completed (phase 7)

  1. WL#4212 Stabilize kernel code and correct memory allocation
  2. WL#3572 No-data backup engine (Code ready; to fix Bug#36380)
  3. WL#3572 Native driver for Blackhole engine
  4. WL#3572 Native driver for Example engine
  5. WL#3572 Native driver for Merge engine
  6. WL#3572 Native driver for Federated engine
  7. WL#4211 Dependency checking (Code will be ready May 8th; To fix Bug#36531)
  8. WL#4326 Disable events and triggers during restore (To fix Bug#36530)
  9. WL#866, WL#4279 Native driver for MyISAM
  10. WL#4037 Compression

[edit] MySQL 6.0.8 Features - completed (phase 7)

  1. WL#4296 Refine backup progress mechanism
  2. WL#4073 Backup/restore db objects permissions
  3. WL#4384 Revise error reporting (To fix Bug#36529, Bug#39089)

[edit] MySQL 6.0.X Features - completed (phase 7a)

  1. WL#4209 Integrate backup with replication (To fix Bug#36533)
  2. WL#4264 Backup: Stabilize Service Interface (Alexander)

[edit] Limitations and Features: To be fixed in 6.1

  1. WL#4271 Encryption
  2. WL#3781 Native driver for Falcon
  3. WL#4243 DDL blocker enhancements (Was strongly requested on MySQL UC BOF)
  4. WL#4405 Native driver for Memory Storage Engine
  5. WL#4385 Checksum checking (Was strongly requested on MySQL UC BOF)
  6. WL#4275 Online Backup: Driver Interface Simplification
  7. WL#3328 Native driver for Archive
  8. WL#4295 Update the main read/write loop
  9. WL#3505 Extend I_S.ENGINES with info about native backup support
  10. WL#4090 Backup privileges
  11. WL#4280 ST: Service Interface for Replication Services (part of WL#4209)

[edit] Limitations and Features: To be fixed in MySQL 6.1 or later

  1. WL#4190 Error handling II
  2. WL#3328 Native driver for Archive engine
  3. Selective backup/restore
  4. No Native driver for InnoDB
  5. WL#3960 Engine agnostic image format
  6. WL#4246 Stabilize object service interface
  7. WL#4279 Move native driver into library
  8. WL#4242 Asynchronous backup commands
  9. WL#3329 Online backup: Design Advanced SQL Interface
  10. WL#4272 Data inspection utilities (consistency, extract data, etc)/Client tool
  11. WL#4274 Incremental backup
  12. WL#4273 Streaming to other server
  13. WL#155 Initialization of replication slave/Auto-magic slave setup
  14. WL#4213 Consistency checking
  15. WL#4089 XBSA support
  16. WL#4105 Save slave status
  17. WL#2354 Native backup for NDB
  18. WL#4036 Refactor opening and locking tables for backup
  19. Native backup for CSV
  20. Native backup for Maria
  21. backup complete instance (BACKUP ALL)
  22. Differential backup (there is WL#4274 for incremental backup)
  23. Atomic restore
  24. Selective backup/restore implementation (this is not WL#3713).
  25. Backup of binary log
  26. Error overwrite (continue even if there is an error, i.e. best effort)
  27. No Streaming of backup image to other machine
  28. No Backup of User accounts
  29. No Backup of Generic Privileges
  30. No Backup of connections for federated tables
  31. No Client program to receive the backup image (for piping it somewhere)
  32. No Backup of partitions
  33. No Native driver for MySQL Cluster/NDB (WL#2354)
  34. No Backup of System variables
  35. No Backup of Plugins and UDFs
  36. No Backup of Log groups and Table spaces
  37. No Backup of Storage engine settings
  38. No Backup of Character set settings
  39. No Backup of Collations sequence settings

[edit] Limitations that will probably not be fixed ever

[edit] Objects to Backup and Restore (Defined in WL#3713)

The following tables include information about all of the objects in the MySQL server grouped by categories. The tables include references to which release these objects will be included for backup and restore as well as links to the relevant worklogs. "Backup Release 1" is the same as the "MySQL 6.0 Server release". The mysqldump column (will) show what can be backed up with the mysqldump program.

RefIdNon-Persistent ObjectsWorklogReleasemysqldump
1GLOBAL_STATUSnoneNeverNever
2PROCESSLISTnoneNever Never
3SESSION_STATUSnoneNever
4SESSION_VARIABLESnoneNever
5All FALCON_* tables (if we get rid of FALCON_TABLES)noneNever
6All tables made for 'community' noneNever
7Contents of tables strictly for 'monitoring' purposesnoneNever
8Temporary tables noneNever
9Prepared statementsnoneNever
10CachesnoneNever
RefIdStatic ObjectsWorklogRelease
11Static files that come as part of MySQL installationnoneNever
12User programsnoneNever
13Operating system shared librariesnoneNever
RefIdExternal ObjectsWorklogRelease
14Scripts for starting MySQL server when booting OSnoneNever
15Environment inits for $MYSQL_HOST or similar variablesnoneNever
RefIdNon-exclusive ObjectsWorklogRelease
16Option files (also known as configuration files)none3
17Plugins (the loadables)none3
18Files in the basedir 'shared' directorynone3
RefIdServer ObjectsWorklogRelease
19Global variables except global-statusnone2
20-amaster.infonone2
20-brelay-log.infoWL#41052
21Tablespacesnone2
22LogsnoneNever
23Binary lognone3
24Users and global-level privilegesnone2
25Plugin info (mysql.plugin without code)none2
26Federated table connections (mysql.servers)none2
27"Help (4 tables: help_category, etc.)"none3
28"Time zone (5 tables: time_zone, etc.)"none2
29-ageneral_lognone3
29-bslow_lognone3
30Information about UDFs (without loadable)none3
31LOGFILE GROUPSnone3
32mysql.hostnone3
33mysql.dbnone3
35mysql.ndb_binlog_indexnone3
36mysql.online_backupnone3
RefIdDatabase ObjectsWorklogRelease
37View WL#3574none1
38Stored ProcedureWL#35741
39FunctionWL#35741
40TriggerWL#3582, WL#35741
41EventWL#35741
42Privileges (db tbl view sp/sf cols ev)WL#35741
43-aConstraint (Primary Key)DONE1
43-bConstraint (Unique)DONE1
43-cConstraint (Foreign key)noneFuture
RefIdFuture (non existing) ObjectsWorklogRelease
44CatalogWL#2073, WL#942Future
45"Character Set (""dynamic"")"WL#744Future
46"Collation (""dynamic"")"WL#744Future
47Constraint (CHECK)WL#929Future
48DomainWL#933Future
49ModuleWL#3416Future
50Profile (as in Oracle)WL#3824Future
51RoleWL#988Future
52SequenceWL#827Future
53Privilege (new ones)WL#2227Future
54Registry (of plugins)WL#4102Future
55Repository (of backups)WL#3329Future
56SynonymWL#1048Future
57Trigger (DDL)WL#2418Future
58Type (distinct)WL#933Future
59Type (structured)WL#3862Future
RefIdOther ObjectsWorklogRelease
60Table definitionnone1
61Database definitionnone1
62Index (the actual index for physical native drivers)none1

[edit] How to do backup

[edit] How to write native backup and restore drivers

[edit] High-level description

Online Backup enables external clients to back up a consistent image of an instance's data and associated metadata via a direct connection to the MySQL server.

"Consistency" includes the following constraints:

[edit] Main requirements influencing the current design

[edit] Q: How tables stored in engine X are backed up?

There are three methods to back up an engine:

  1. Native driver. Storage engine can use a specialized method (a so called native driver) for creating backup image in the most efficient way for the particular engine, possibly exploiting the physical layout of its data. At the moment there are no engine-specific backup solutions available in MySQL 5.2.
  2. Consistent snapshot driver. For engines that do not have a native driver but that support consistent snapshot (e.g. InnoDB), there is a consistent snapshot algorithm in the MySQL server to do backups of these engines.
  3. Default driver. For engines that do not have a native driver and do not support consistent snapshot, there is a default algorithm that does a backup of the engine. In contrast with the methods above, this algorithm is blocking.

Currently the method is automatically selected by the MySQL server and it is not possible for the user to select method.

[edit] Architecture of online backup system

Image:BackupArchitecture3.png

[edit] Backup Kernel

The metadata saved by backup kernel has several components and in the future versions of the system user is supposed to be able to select which of these components to save/restore. Current prototype backs up only the basic metadata, i.e., table definitions. This is to be soon extended by

[edit] Backup Engine

Note that a backup engine is not responsible for backing up table definitions or other metadata (this is done by the backup kernel). Only data contained in tables is to be saved.

Consistency of the image means that data saved there coresponds to the data which was stored in the tables at some definite point in time. This moment is called the validity point of the image. The image should contain only data which was commited at the time of validity point.

To synchronize several images created by different backup engines, validity point of each image must be chosen by the backup kernel, not by the involved engines. This adds extra complexity to the design and is implemented using backup API and protocols described below.

[edit] Backup/Restore API

This is the API used for communication between backup kernel and backup engines. It has form of virtual classes defining necessary interfaces to be implemented by a backup solution provider (a storage engine usually).

Handlerton structure has been extended with one extra pointer to a factory function creating Backup_engine object, which encapsulates backup and restore functionality of the engine. 

 typedef backup_result_t backup_factory(handlerton*, Backup_engine*&);
 
 struct handlerton
 {
   ...
   backup_factory *get_backup_engine;
 }

Engines which don't support native backup/restore functionality should set this pointer to NULL. If the pointer is set, backup kernel uses the function to create an instance of Backup_engine object which contains further information about provided backup/restore functionality.

The main methods of Backup_engine object are get_backup() and get_restore(). When backing-up or restoring a given list of tables they create instances of backup/restore driver objects (of type Backup_driver and Restore_driver respectively) with which backup kernel interacts to create backup image or restore data from such an image.

Note that a driver object corresponds to and realizes a single backup/restore operation. For each executed backup/restore, a new driver is created.

[edit] Default Backup Engine

Some storage engines may not supply the interface to this API. Initially this will be the case for all engines, but it is possible that some engines may never be converted. Online backup system implements a default backup engine which will be used for storage engines which don't support native backup. This engine uses standard storage engine API to scan table data. Currently, this default backup method blocks all the backed-up tables for the period of backup operation.

[edit] Server Interface

We try to clearly define the interface between Online Backup module and the rest of the server. Here is a page documenting a discussion with Runtime team about the interface.

[edit] Backup Protocol - Multi Engine Synchronization

To correctly operate with backup kernel, backup driver needs to follow the backup protocol described here. The protocol is designed to allow for synchronization of several backup drivers, possibly using different backup techniques.

[edit] Types of Backup Drivers

Drivers can use different methods for creating their backup images. These methods can be divided into two broad classes:

Regardless of the method used, backup kernel treats all drivers in a uniform way. From its perspective, backup process always consists of 7 phases described in the documentation of Backup_driver class. Depending of the backup method used, some of these phases can be empty.

A driver informs backup kernel about its type by estimating size of data it is going to send in the initial phase of the backup. For example, "at begin" drivers send no data in the initial phase thus their estimate is 0. "At end" drivers send all their data in the initial phase and they should give a non-zero estimate. If estimating is not possible or too expensive, an "at end" driver can return UNKNOWN_SIZE as the estimate (see init_size() method).

[edit] Driver-Kernel Interaction

Most of the time during the backup process, kernel is polling a driver for the backup image data using get_data method. Driver informs the kernel about its state via the returned value.

When all drivers are done sending the initial image data, kernel initializes the process of synchronizing all the images. First, prelock() is called on all drivers so that they can prepare for synchronization. When all drivers are ready, their lock() methods are called. Drivers are supposed to ensure that the time when lock() was called is the validity point of their images. Their should also freeze any changes to the data after a call to lock(), until the following unlock() call. This is crucial to ensure cross-driver consistency of the whole backup archive.

Since a driver which freezes after a lock() call must wait for all other drivers to complete their calls to lock(), it is important that lock() returns fast (in a matter of seconds). No time consuming operations or ones which involve waiting (e.g., file access) should be done inside the lock() method.

After the unlock() call, when the synchronization phase is finished, kernel continues to poll data from drivers until they signal that all data has been sent.

A typical interaction between kernel and a backup driver should look as follows: 

// Preparation

drv->size();      // get estimate of the total size of backup image

drv->init_size(); // get estimate of the amount of data to be sent in the 
                  // initial phase of backup (can be 0) 

drv->begin(size); // from now on driver should be ready for get_data()      
                  // requests; buffers sent by kernel will be at leas size long. 

// Initial data transfer

drv->get_data(buf);  //  kernel polls data from driver
...                  //  driver signals that it is done with the
drv->get_data(buf);  //  initial phase by returning READY.

// Wait for prelock

drv->get_data(buf);  // driver is waiting for other drivers to finish they initial
...                  // data transfer; kernel still polls data during that phase

// Prepare for lock

drv->prelock();     // kernel ask all drivers to prepare for a lock() call 
                    // below; driver signals that it is ready by returning READY,

drv->get_data(buf); // if driver returns OK from prelock() call above, kernel will
...                 // continue calling get_data() method until it sees READY answer
     
// Wait for lock

drv->get_data(buf); // driver waits for other drivers to finish preparations for lock;
...                 // kernel continues polling data

// Synchronization

drv->lock();        // this is a request to create local "validity point" of 
                    // driver's backup image; the engine should be frozen so 
                    // that this validity point remains valid while other 
                    // engines process their lock() requests

drv->unlock();      // this method is called after all engines have been 
                    // locked and the global validity point established; the 
                    // engine should unlock data changes 

// Final data transfer

drv->get_data(buf);   // kernel polls for further data; driver singals end of data
...                   // for each table by setting buf.last flag
drv->get_data(buf);   // when all data is sent, driver returns DONE from get_data() call

// Epilogue

drv->end();  // end of backup process -- driver can do additional cleaning.

drv->free(); // free allocated resources (can delete the driver).

[edit] Backup Driver Scheduling

Not all drivers are initialized by the kernel at the same time. Instead, backup image size estimates are used to time polling of the drivers so that they are ready for synchronization at approximately the same time. The rule is that drivers with biggest size of the initial transfer are initialized first. Thus kernel will start with polling any "at end" drivers and will activate "at begin" drivers (whose initial data size is 0) only when all other drivers have finished their initial transfer.

If there is more than one driver with non-zero estimate of the initial data size, the relative timing will be based on the amount of data received from them. For example, consider two drivers D1 and D2, the first one sending 1MB of data in the initial phase and the second one 1TB. To minimize the time D1 has to wait for D2, D1 will be initialized only after D2 sends 999MB of its initial data. 

        D1 initialized after E2 has sent 999MB of data -----+
D1                                                          |===|--|----
  
D2  |============================================================|-|----
            D2 needs to send 1TB in the initial phase...           |
                                                                   +- creating
                                                                      validity
                                                                      point.

[edit] Data Transfer Protocol

The data produced by a backup driver is mostly opaque for the backup kernel. The kernel doesn't try to understand the contents of an image: which part contains data, which is a log or indexes (if this type of backup is used). The image is to be read and interpreted by a restore driver during the restore operation.

However, certain assumptions about the format of a backup image data are made. It is assumed that the image consists of several blocks of data which can be of varied sizes. When a get_data(buf) call returns OK or READY, the buffer contains next block of the backup image data. Its actual size is stored in buf.size member which is set by the driver. Upon restore, kernel will send the same block (identical length and contents) back to a restore driver. Also, the order of data blocks will be preserved by the kernel but see the following description regarding block streams and handling of selective restores. If buf.size is set to 0, such empty block is ignored by the kernel and will not be sent back during restore.

It is possible that a user will select only certain tables to be restored from a backup archive. In that case one can reduce amount of data sent to a restore driver by sending only the data which is needed to restore the selected tables. To support this, a backup driver can assign each block of backup data to one of the tables being backed-up. This is done by setting buf.table_no member of the buffer structure. The member should contain number of the table to which the data belongs (tables are numbered from 1 according to the position in the list passed when driver is created). If some of the data doesn't correspond to any particular table, then buf.table_no should be set to 0.

This way, several "streams" of data blocks are created. For each table there is a stream corresponding to that table and there is one "shared stream" consisting of blocks with table_no set to 0. Upon restore, kernel sends to a restore driver only blocks corresponding to the tables being restored plus all the blocks from the shared stream.

For example, consider backing-up three tables t1, t2 and t3. Data blocks produced by a backup driver are divided into four streams: 

#0: shared data
#1: data for table t1
#2: data for table t2
#3: data for table t3

When a user restores tables t1 and t3, only blocks from streams #0, #1 and #3 will be sent to a restore driver, but not the ones from stream #2.

Using this approach, backup engine can arrange its backup image data in the way which best suits its internal data representation. If needed, all data can be put in the shared stream #0, so that all of it will be sent back to a restore driver. On the other hand, if possible, backup data can be distributed into per table streams to reduce the amount of data transferred upon a selective restore.

Backup driver signals end of data in a given stream by setting buf.last flag to TRUE when get_data(buf) fills the last block of data from that stream. This should be done for each stream used by the driver. Upon restore, kernel sets buf.last to TRUE when sending to a restore driver the last block of data from a stream.

Upon restore, backup kernel will send data blocks in the same order in which they were created.

[edit] Points for discussion

Currently no. Is this important?

[edit] Restore

Restore operation is performed using similar but simpler interface and protocols. Backup kernel creates all involved tables from the metadata stored in a backup archive. Then it asks backup engines to create restore drivers and sends data blocks stored in the archive to these drivers using send_data() method. The drivers interpret the data created during backup process and fill tables with rows. All tables are blocked during restore process.

[edit] Changes to Driver API

2007-04-20 
Rename Buffer::stream_no -> Buffer::table_no (CSet).


[edit] Current list of people who have tried out the feature

  1. Padmanabhan Sreenivasan, Zmanda
  2. Jan Knechke, MySQL
  3. Robin Schumacher, MySQL
  4. Guilhem Bichot, MySQL
  5. Alexander Ivanov, Solid
  6. Peter Gulutzan, MySQL

Retrieved from "http://forge.mysql.com/wiki/OnlineBackup"

This page has been accessed 55,964 times. This page was last modified 14:35, 20 October 2009.

Find

Browse
MySQLForge
Main Page
Current events
Recent changes
Random page
Help
Edit
Edit this page
Editing help
This page
Discuss this page
Post a comment
Printable version
Context
Page history
What links here
Related changes
My pages
Special pages
New pages
File list
Statistics
Bug reports
More...