MySQL 6.0 Feature #2: Online Backup
MySQL 6.0 Feature #2: Online Backup
Online Backup
MySQL 6.0 New Features Document#2
2008-05-18
By Alexander Nozdrin, Chuck Bell, Lars Thalmann, Peter Gulutzan, Rafal Somla
High Executive Summary
Online backup is a new feature in MySQL 6.0.
It protects from database loss.
It does not block all other concurrent MySQL connections.
It will interest Database Administrators.
Database Administrators Summary
BACKUP DATABASE copies all data and metadata in one or more MySQL databases, into an “image file”. RESTORE reads an image file and rewrites all the data and metadata in one or more MySQL databases. So if you lose a database, you can recover all of it as of the time of the last BACKUP DATABASE statement.And then you can re-run the statements in MySQL’s binary log to recover “from the time of the last BACKUP DATABASE statement”, “to the time that the database loss occurred”. In other words … Careful Use of Online Backup will protect from database loss.
The word “online” means “without blocking”. So, unlike earlier backup solutions which could make all other jobs hang, the Online Backup statements will allow certain Database Manipulation Language statements to go on concurrently. Some locking still occurs, but it’s minimal.
To complete the package, there are new tools for monitoring and recording each online backup process or subprocess.
BACKUP DATABASE Syntax
BACKUP DATABASE database_name_list TO ‘image-file-name’;
It is possible to use SCHEMA as a synonym for DATABASE, that is, “BACKUP SCHEMA …” instead of “BACKUP DATABASE …”.
The database_name_list may be a list of databases separated by commas, or “*” meaning “all databases except mysql or information_schema”.
There is no predefined maximum number of databases.
It is illegal to use the same database name more than once.
All databases in the list must exist.
The user must have SELECT or equivalent privileges for all objects in all databases.
The use of file names is affected by the security startup option –secure-file-priv
http://dev.mysql.com/doc/refman/6.0/en/server-options.html#option_mysqld_secure-file-priv.
The ‘image-file-name’ is the name of the image file.
This must be specified as a string literal, not as a variable.
Only one file name is allowed.
The path may be absolute, or relative to ‘backupdir’ (see section titled ‘backupdir’).
The file is always on the server, there is no LOCAL option to force the image file to go to a client computer.
There must not already be a file with the same name.
The server must have the ability to create files and write on the directory.
Examples:
BACKUP DATABASE * TO ‘/usr/local/mysql/var/t1′;
BACKUP DATABASE `C#` TO ‘t2′;
BACKUP SCHEMA a,b,c TO ‘d’;
Effects:
* All data and metadata in all listed databases is copied to the image file.
* While BACKUP DATABASE is going on, it is possible to monitor progress with a new mysql table, mysql.online_backup_progress.
* After BACKUP DATABASE completes successfully, there is a record in a new mysql table, mysql.online_backup. (The record is created when the backup starts, is updated as the backup proceeds, and reaches its final state when the backup ends.)
Minor notes: BACKUP DATABASE is classed as a “Data Manipulation Language” statement because it reads.
It does not lock table files in a way that would prevent other jobs from reading and writing, but it does block other jobs from performing any “Database Definition Language” with any object in any database in the list.
Upon completion, BACKUP DATABASE returns a result set with the backup number.
It causes an automatic “commit”.
Terminology notes: MySQL uses the word “image” for the data that BACKUP DATABASE produces. At present the “image” is always a file so ‘image-file’ is the only possibility. This has no relation to the way that the word ‘image’ is used by other DBMS vendors.
RESTORE syntax
RESTORE FROM ‘image-file-name’;
The ‘image-file-name’ is the name of an image file that was made with BACKUP DATABASE. It must be specified as a string literal.
The user must have CREATE or equivalent privileges for all objects in all databases that were backed up.
Examples:
BACKUP DATABASE test TO ‘/usr/local/mysql/var/t3′;
RESTORE FROM ‘/usr/local/mysql/var/t3′;
Effects:
Everything that was backed up with BACKUP DATABASE is restored with RESTORE. At time of writing, existing databases are overwritten. Later, it will be necessary to drop databases before restoring. Upon completion, RESTORE returns a result set with the backup number.
Minor notes: RESTORE is classed as a “Data Definition Language” statement because it alters. It causes some table locking because it will write to each table in each database, so “online restore” does not exist.
backupdir
The ‘backupdir’ i.e. ‘backup directory’ variable is new. You can see its value with “SELECT @@backupdir” but it is read only so you may not set it while the server is up. You may specify –backupdir on the mysqld command line, for example:
mysqld –backupdir=’/’
If the ‘image-file-name’ in BACKUP or RESTORE has a relative path (for example ‘x’ instead of ‘/usr/local/mysql/var/x’), then the DBMS will put the backupdir value before image-file-name. For example, if backupdir is ‘/usr/local/mysql/var’, the statement
RESTORE FROM ‘x’
will open the file ‘/usr/local/mysql/var/x’.
By default, backupdir is the same as datadir, that is, if datadir = ‘/usr/local/mysql/var’ then backupdir = ‘/usr/local/mysql/var’. But it is not good policy to put the database and the backups on the same device! MySQL recommends that you change backupdir if you have another device that backups can go to.
What is backed up
BACKUP DATABASE backups the database and all objects that are within the database:
persistent base tables created with engine = memory, archive, myisam, innodb, or falcon (including indexes, foreign-key constraints, partitions)
and
views, stored procedures, stored functions (except UDFs), triggers, events.
What is not backed up
Some items are not backed up because they are not in databases, or are not persistent, or are not currently supportable by the backup system. MySQL recommends that users take other measures, such as file copy, to ensure that all parts of the system are restorable after RESTORE.
Not-backed-up items are:
Temporary tables
Session objects
Contents of INFORMATION_SCHEMA.GLOBAL_STATUS
Contents of INFORMATION_SCHEMA.GLOBAL_VARIABLES
Contents of INFORMATION_SCHEMA.PROCESSLIST
Contents of INFORMATION_SCHEMA.PROFILING (community only)
Contents of INFORMATION_SCHEMA.SESSION_STATUS
Contents of INFORMATION_SCHEMA.SESSION_VARIABLES
Contents of INFORMATION_SCHEMA.FALCON*
Tablespaces
Plugins
UDF files
Global objects:
- USER
- privileges which are not “per-database”
- PLUGIN (e.g. parser plugin) not including code
- plugin code
- UDF (i.e. function defined in shared libraries) not including code
- UDF code
- LOGFILE GROUP
- TABLESPACE
- SERVER i.e. connections (for federated tables)
- logs (general log, slow query log, storage-engine logs)
- contents of mysql.host
- time zone
- configuration files
- index.xml
Objects outside all databases:
- master.info
- relay-log.info
- Storage engine logs
(Some storage engines have their own log
files. MySQL will not back them up, and
will not restore them. The plan is that
BACKUP will take place ‘as of’ a moment
of validity. Uncommitted data at the
validity-moment time is not restorable.)
- binary log
Static objects:
- Static files that come as part of the MySQL installation,
for example (Windows) mysqld-nt.exe
- User programs
- Operating system shared libraries
- Scripts for starting MySQL server when booting operating system
- Environment initializations for $MYSQL_HOST or similar variables
- Option files (my.cnf, my.ini, mysql-service-agent.ini,
/etc/init.d, /var/lib/mysql-cluster/config.ini)
Plugins:
(The plugins are all the files that can be read in the directory that you see when you say SHOW VARIABLES LIKE ‘plugin_dir’; for example /usr/local/mysql/lib/mysql.)
Files in the basedir ’shared’ directory:
(These files only change if users customize, for example by adding a new collation or translating an error message.
Examples:
/usr/local/mysql/share/mysql/charsets/*.xml
/usr/local/mysql/share/mysql/charsets/*.conf
/usr/local/mysql/share/mysql/english/errmsg.txt)
mysql.online_backup
A new table in the mysql database, mysql.online_backup, has a record of the backups.
It has these columns:
backup_id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
This is the ‘Surrogate key for (near) duplicate entries’. This will correspond to the number that is returned by the BACKUP DATABASE statement.
process_id int unsigned NOT NULL
This is ‘The process id that operation ran as’.
binlog_pos int unsigned DEFAULT ‘0′
This is ‘The recorded binlog position of backup/restore’.
binlog_file char(64) DEFAULT NULL
This is ‘The recorded binlog filename at time of backup/restore’.
backup_state enum(’complete’,’starting’,'validity point’,'running’,'error’,'cancel’) NOT NULL
This is the ‘Status of current operation’.
operation enum(’backup’,'restore’) NOT NULL
This is the ‘Type of operation’.
error_num int(11) NOT NULL DEFAULT ‘0′
This is ‘The error from this run 0 == none’.
num_objects int unsigned NOT NULL DEFAULT ‘0′
This is ‘The number of objects in the backup’.
total_bytes bigint unsigned DEFAULT NULL
This is ‘The size of the backup repository in bytes’.
validity_point_time datetime DEFAULT NULL
This is ‘The time of the validity point.’. Times are in the system time zone, which may be UTC. Changes re time zones are happening as we write this.
start_time datetime DEFAULT NULL
This is ‘The date/time of start of operation’.
stop_time datetime DEFAULT NULL
This is ‘The date/time of end of operation’.
host_or_server_name char(30) DEFAULT NULL
This is ‘The server name where operation ran’.
username char(30) DEFAULT NULL
This is ‘The user name who ran the operation’.
backup_file char(100) DEFAULT NULL
This is ‘The name of the file’.
user_comment varchar(200) DEFAULT NULL
This is ‘The comment from user entered at command line’.
command varchar(512) DEFAULT NULL
This is ‘The command used to issue operation’.
engines varchar(100) DEFAULT NULL
This is ‘The name of the storage engines used in the operation’. Soon we’ll change it to ‘drivers’ instead of ‘engines’.
With this table, it’s possible to keep track of what backups exist. You need SELECT privilege to see it.
mysql.online_backup_progress
Another new table in the mysql database, mysql.online_backup_progress, has temporary information while BACKUP or RESTORE is happening.
It has these columns:
backup_id bigint unsigned NOT NULL
This is the ‘Key for online_backup table entries’.
object char(30) NOT NULL
This is ‘The object being operated on’.
start_time datetime DEFAULT NULL
This is ‘The date/time of start of operation’.
stop_time datetime DEFAULT NULL
This is ‘The date/time of end of operation’.
total_bytes bigint DEFAULT NULL
This is ‘The size of the object in bytes’.
progress bigint unsigned DEFAULT NULL
This is ‘The number of bytes processed’.
error_num int NOT NULL DEFAULT ‘0′
This is ‘The error from this run 0 == none’.
notes char(100) DEFAULT NULL
This is the ‘Commentary from the backup engine’.
With this table, it’s possible to monitor progress. Another way to monitor progress is with SHOW PROCESSLIST. You need SELECT privilege to see it.
A session
Let us walk through a session, using all the statements and tools that we have described so far. The following is an actual copy of the mysql client screen output, after clearing the database directory and running mysql_install_db. The server was started with a –log-bin switch.
pgulutzan@linux:~> /usr/local/mysql/bin/mysql --user=root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.5-alpha-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> /* Create database. Create table. Populate table. */
mysql> CREATE DATABASE dw;
Query OK, 1 row affected (0.05 sec)
mysql> USE dw
Database changed
mysql> CREATE TABLE t (s1 INT, s2 CHAR(5)) engine=falcon;
Query OK, 0 rows affected (0.28 sec)
mysql> INSERT INTO t VALUES (1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> /* Backup the database to file 'dw.dmp' on data directory. */
mysql> BACKUP DATABASE dw TO 'dw.dmp';
+-----------+
| backup_id |
+-----------+
| 1 |
+-----------+
1 row in set (2.61 sec)
mysql> /* Pretend to monitor progress. In fact online_backup_progress
/*> will show nothing new, and SHOW PROCESSLIST will show nothing,
/*> because the BACKUP DATABASE statement has finished.
/*> But these statements would show interesting things
/*> if we were running them on another connection before
/*> BACKUP DATABASE finished. */
mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+----+------+-----------+------+---------+------+-------+------------------+
| 2 | root | localhost | dw | Query | 0 | NULL | SHOW
PROCESSLIST |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM mysql.online_backup_progress\G
*************************** 1. row ***************************
backup_id: 1
object: backup kernel
start_time: NULL
stop_time: NULL
total_bytes: 0
progress: 0
error_num: 0
notes: starting
*************************** 2. row ***************************
backup_id: 1
object: backup kernel
start_time: NULL
stop_time: NULL
total_bytes: 0
progress: 0
error_num: 0
notes: running
*************************** 3. row ***************************
backup_id: 1
object: backup kernel
start_time: NULL
stop_time: NULL
total_bytes: 0
progress: 0
error_num: 0
notes: validity point
*************************** 4. row ***************************
backup_id: 1
object: backup kernel
start_time: NULL
stop_time: NULL
total_bytes: 0
progress: 0
error_num: 0
notes: running
*************************** 5. row ***************************
backup_id: 1
object: backup kernel
start_time: NULL
stop_time: NULL
total_bytes: 0
progress: 0
error_num: 0
notes: complete
5 rows in set (0.00 sec)
mysql> /* "Lose" the database. */
mysql> DROP DATABASE dw;
Query OK, 1 row affected (0.00 sec)
mysql> /* Restore the database from the image file 'x.dmp'. */
mysql> RESTORE FROM 'dw.dmp';
+-----------+
| backup_id |
+-----------+
| 2 |
+-----------+
1 row in set (1.77 sec)
mysql> /* Confirm that table t in databasee dw is restored. */
mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
-> WHERE table_schema = 'dw' AND table_name = 't';
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.03 sec)
mysql> /* Confirm that table t has the data that it should have. */
mysql> SELECT * FROM dw.t;
+------+------+
| s1 | s2 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
mysql> /* See what it is in the new mysql.online_backup table. */
mysql> SELECT * FROM mysql.online_backup\G
*************************** 1. row ***************************
backup_id: 1
process_id: 0
binlog_pos: 520
binlog_file: ./linux-bin.000001
backup_state: complete
operation: backup
error_num: 0
num_objects: 1
total_bytes: 24
validity_point_time: 2008-04-10 19:12:00
start_time: 2008-04-10 19:12:00
stop_time: 2008-04-10 19:12:00
host_or_server_name: localhost
username: root
backup_file: dw.dmp
user_comment:
command: BACKUP DATABASE dw TO 'dw.dmp'
engines: Snapshot
*************************** 2. row ***************************
backup_id: 2
process_id: 0
binlog_pos: 0
binlog_file: NULL
backup_state: complete
operation: restore
error_num: 0
num_objects: 1
total_bytes: 24
validity_point_time: NULL
start_time: 2008-04-10 19:13:17
stop_time: 2008-04-10 19:13:17
host_or_server_name: localhost
username: root
backup_file: dw.dmp
user_comment:
command: RESTORE FROM 'dw.dmp'
engines: Snapshot
2 rows in set (0.00 sec)
mysql>
Consistent Read
WARNING: This section contains information about the Falcon engine which is not true at time of writing. Since it is an essential part of what makes online backup “online”, we include it anyway.
Suppose user#1 says “BACKUP DATABASE dw …” and then user#2 comes along and says “UPDATE dw.t …”. What happens?
With a “non-online backup”, we would expect that the UPDATE has to wait until the BACKUP DATABASE finishes (the database’s tables are locked), or we would expect that the image file has some rows from table that were changed, and some that were not — an “inconsistent backup”. Both these expectations are gloomy.
With an “online backup”, on the other hand, we would expect that the UPDATE does not have to wait, and yet the backup is guaranteed to be consistent. This depends on a feature that both InnoDB and Falcon storage engines provide: “consistent read”.
By asking for “consistent read” (or “snapshot”), BACKUP DATABASE ensures that everything the DBMS reads, in all tables, in all databases in the database list, is seen “as of” the same moment. Changes that happen during BACKUP DATABASE are ignored. With the InnoDB and Falcon engines, this happens due to their support for consistent read. With MyISAM and MEMORY engines, this cannot happen, so if there are tables created with those storage engines, any UPDATE or INSERT or REPLACE or DELETE or TRUNCATE statements are blocked during copy of those tables. For example:
* Suppose database d contains three tables: t1,t2,t3.
Table t1 is Falcon, t2 is MyISAM, t3 is MEMORY.
* User says “BACKUP DATABASE d;”.
* The DBMS does not need to lock t1 which has consistent read.
The DBMS locks t2, copies all its rows, and unlocks t2.
The DBMS locks t3, copies all its rows, and unlocks t3.
In any case, any DDL (CREATE/ALTER/DROP) statements are always blocked. The word “online” only means that DML statements can take place at the same time as the backup.
A plan
An effective backup plan would then look something like this:
1. Always start the mysqld server with –log-bin, so that there will be a log file. It’s this that allows for backups with log updates, which MySQL calls “incremental backups”. (Incremental backups are not planned for the beta version and may be available as enterprise features.)
2. Regularly back up with BACKUP DATABASE. Monitor progress with mysql.online_backup_progress table or SHOW PROCESSLIST.
3. Regularly audit that backups have happened, by looking at the mysql.online_backup table.
4. Copy image files to safe places.
Occasionally you should keep in shape by testing the RESTORE procedure. It could go like this — on a spare machine or with a set of test databases:
1. Re-read this document. You must be aware that this test can be destructive
2. Collect: your image file, and a copy of the binlog file(s).
3. Read the mysql.online_backup row that corresponds to this image file, with
SELECT * FROM mysql.online_backup
WHERE operation = ‘backup’
AND backup_file = ‘dw.dmp’; /* if ‘dw.dmp’ = image-file-name*/
Make sure that column `backup_state` says ‘complete’.
Make sure that column `command` has a list of the databases that you want to restore.
Make a note of the `binlog_pos` and `binlog_file` column values, which you will need for Step 8.
4. Drop the databases that were in the BACKUP DATABASE statement.
5. Stop the mysqld server. Restart with only one user (max-connections = 1, skip-networking).
Log in as a user with all the privileges needed for restoring (BACKUP, FILE, etc.).
6. RESTORE FROM ‘image-file’;
7. Re-run the binary log from the time of the destruction.
Use mysqlbinlog –start-position=(what you got in Step 3)
It might be possible to restore to a different place by changing @@datadir, but nobody has tried that yet.
Replacing mysqldump
The image file is also useful for copying databases from one server to another, as in mysqldump.
However, MySQL is not deprecating mysqldump and we continue to recommend it for basic import/export. In that environment, the advantages of mysqldump over Online Backup are:
It is readable by humans.
It is possible to be specific about the objects to backup/restore.
It might be faster — we haven’t done many speed tests yet.
New error messages
The following error messages are new.
ER_BACKUP_BACKUP_START 1617 HY000 Starting backup process
ER_BACKUP_BACKUP_DONE 1618 HY000 Backup completed
ER_BACKUP_RESTORE_START 1619 HY000 Starting restore process
ER_BACKUP_RESTORE_DONE 1620 HY000 Restore completed
ER_BACKUP_NOTHING_TO_BACKUP 1621 HY000 Nothing to backup
ER_BACKUP_CANNOT_INCLUDE_DB 1622 HY000 Database ‘%-.64s’ cannot be included in a backup
ER_BACKUP_BACKUP 1623 HY000 Error during backup operation - server’s error log contains more information about the error
ER_BACKUP_RESTORE 1624 HY000 Error during restore operation - server’s error log contains more information about the error
ER_BACKUP_RUNNING 1625 HY000 Can’t execute this command because another BACKUP/RESTORE operation is in progress
ER_BACKUP_BACKUP_PREPARE 1626 HY000 Error when preparing for backup operation
ER_BACKUP_RESTORE_PREPARE 1627 HY000 Error when preparing for restore operation
ER_BACKUP_INVALID_LOC 1628 HY000 Invalid backup location ‘%-.64s’
ER_BACKUP_READ_LOC 1629 HY000 Can’t read backup location ‘%-.64s’
ER_BACKUP_WRITE_LOC 1630 HY000 Can’t write to backup location ‘%-.64s’
ER_BACKUP_LIST_DBS 1631 HY000 Can’t enumerate server databases
ER_BACKUP_LIST_TABLES 1632 HY000 Can’t enumerate server tables
ER_BACKUP_LIST_DB_TABLES 1633 HY000 Can’t enumerate tables in database %-.64s
ER_BACKUP_SKIP_VIEW 1634 HY000 Skipping view %-.64s in database %-.64s
ER_BACKUP_NO_ENGINE 1635 HY000 Skipping table %-.64s since it has no valid storage engine
ER_BACKUP_TABLE_OPEN 1636 HY000 Can’t open table %-.64s
ER_BACKUP_READ_HEADER 1637 HY000 Can’t read backup archive preamble
ER_BACKUP_WRITE_HEADER 1638 HY000 Can’t write backup archive preamble
ER_BACKUP_NO_BACKUP_DRIVER 1639 HY000 Can’t find backup driver for table %-.64s
ER_BACKUP_NOT_ACCEPTED 1640 HY000 %-.64s backup driver was selected for table %-.64s but it rejects to handle this table
ER_BACKUP_CREATE_BACKUP_DRIVER 1641 HY000 Can’t create %-.64s backup driver
ER_BACKUP_CREATE_RESTORE_DRIVER 1642 HY000 Can’t create %-.64s restore driver
ER_BACKUP_TOO_MANY_IMAGES 1643 HY000 Found %d images in backup archive but maximum %d are supported
ER_BACKUP_WRITE_META 1644 HY000 Error when saving meta-data of %-.64s
ER_BACKUP_READ_META 1645 HY000 Error when reading meta-data list
ER_BACKUP_CREATE_META 1646 HY000 Can’t create %-.64s
ER_BACKUP_GET_BUF 1647 HY000 Can’t allocate buffer for image data transfer
ER_BACKUP_WRITE_DATA 1648 HY000 Error when writing %-.64s backup image data (for table #%d)
ER_BACKUP_READ_DATA 1649 HY000 Error when reading data from backup stream
ER_BACKUP_NEXT_CHUNK 1650 HY000 Can’t go to the next chunk in backup stream
ER_BACKUP_INIT_BACKUP_DRIVER 1651 HY000 Can’t initialize %-.64s backup driver
ER_BACKUP_INIT_RESTORE_DRIVER 1652 HY000 Can’t initialize %-.64s restore driver
ER_BACKUP_STOP_BACKUP_DRIVER 1653 HY000 Can’t shut down %-.64s backup driver
ER_BACKUP_STOP_RESTORE_DRIVERS 1654 HY000 Can’t shut down %-.64s backup driver(s)
ER_BACKUP_PREPARE_DRIVER 1655 HY000 %-.64s backup driver can’t prepare for synchronization
ER_BACKUP_CREATE_VP 1656 HY000 %-.64s backup driver can’t create its image validity point
ER_BACKUP_UNLOCK_DRIVER 1657 HY000 Can’t unlock %-.64s backup driver after creating the validity point
ER_BACKUP_CANCEL_BACKUP 1658 HY000 %-.64s backup driver can’t cancel its backup operation
ER_BACKUP_CANCEL_RESTORE 1659 HY000 %-.64s restore driver can’t cancel its restore operation
ER_BACKUP_GET_DATA 1660 HY000 Error when polling %-.64s backup driver for its image data
ER_BACKUP_SEND_DATA 1661 HY000 Error when sending image data (for table #%d) to %-.64s restore driver
ER_BACKUP_SEND_DATA_RETRY 1662 HY000 After %d attempts %-.64s restore driver still can’t accept next block of data
ER_BACKUP_OPEN_TABLES 1663 HY000 Open and lock tables failed in %-.64s
ER_BACKUP_THREAD_INIT 1664 HY000 Backup driver’s table locking thread can not be initialized
ER_BACKUP_PROGRESS_TABLES 1665 HY000 Can’t open the online backup progress tables
Known Bugs
This list excludes bugs which are ‘patch pending’ or ‘patch queued’. To be sure you get an up-to-date list, check the MySQL bugs database http://bugs.mysql.com and click Category = Backup.
32499 BACKUP DATABASE crashes server if database has 600 tables (any engine)
33019 Online backup process can consume too much processor time
33022 Online backup does not preserve charsets for object names
33173 Error log polluted by backup debug info
33352 Backup: crash if I use old set of mysql files.
33354 Backup: restore changes current database to null
33355 Backup: hang if database is mysql
33364 Online Backup: Purge statement missing
33414 Backup: DDL hangs indefinitely if ongoing backup
33564 Backup: deadlock during restore
33566 Backup: crash with partitions and Falcon
33567 Backup: duplicate database names allowed
33568 Backup: crash if many databases in list
33569 Backup: tablespace not restored
33570 Backup: restore failure if sql_mode change
33571 Backup: restore failure if sql_mode=ansi
33573 Backup: timestamp column gets current default
33574 Backup: restore failure if temporary table exists
33575 Backup: crash on shutdown if Falcon table
33836 Backup: tables have default character set latin1
34065 Backup: file names may be too short
34067 Backup: restore failure if directory is not empty
34171 Backup: ignoring –secure-file-priv
34180 Backup: restore fails if CREATE EVENT fails
34181 Backup: crash if @@max_heap_table_size is small
34202 Backup: RESTORE turns off the slow log
34205 Backup: restore fails if Falcon and no autocommit
34210 Backup: lost InnoDB rows if concurrent restore
34480 Backup: Backup to a file copies redundantly if database name is repeated.
34721 Backup driver can’t refuse to provide driver
34758 Server crashes if database with views backed up using CS driver
34759 Server crash when backing-up database with trigger
34766 No error message for Full disk while performing network or local backup
34767 Existing DB contents are lost, if I attempt to restore a corrupted backup file.
34867 Backup: crash if altered view
34868 Backup: restore failure if two procedures
34902 Backup: crash if view depends on dropped object
34903 Backup: restore failure if view and temporary
34915 Backup: Not able to backup view.
35079 It is not possible to interrupt long BACKUP or RESTORE operation
35117 Backup: Server crash for backup of CSV engine with CHAR data type
35118 Backup:Restore fails if previous create Procedure/Function/Trigger command
35230 Backup: no backupdir
35240 RESTORE command doesn’t write binlog position to online_backup table
35249 Mysql server crash for delete operation followed by backup in memory SE
35347 Mysql Server crash while doing restore with views for default driver
35585 Online backup: Apparent synonyms for binlog start_position
35595 Backup crash server
35659 Falcon crashes on shutdown after backup+restore+drop DB
35698 Trying to backup a Falcon database leads to assertion
35806 The current system time becomes stagnant after Restore operation is complete.
35951 Backup: restore fails after directory removal
35967 Backup: can’t delete from mysql.online_backup_progress
36213 Restore fails for a database that has views created using another database .
36257 Backup project missing dependencies
36265 Views not backed up if they are dependent on tables from different Database
36480 Backup: restore fails with changed Falcon tablespace
36530 Backup: Restore can fail due to enabled triggers, events
36531 Backup: Objects can be restored in wrong order
36533 Backup and replication don’t work together
36571 Restore hangs for database size of 33K and more
36572 During Restore, select operation from another connection hangs.
36585 Trigger hangs, if fired on database being restored.
36586 Online backup stream library can miss end of a stream.
36624 Backup stream library works wrong on 64bit platform
36635 Backup: errors should be warnings
36664 Server crash in backup_no_be test on Solaris
36749 Data Loss after Restore, if Trigger fired on the table that is being Restored.
36778 Read operation during RESTORE leads to data loss
36782 View and its related table data lost after Restore.
Comparison with Oracle
Some people, especially migrators, will wonder how MySQL’s “Online Backup” compares with Oracle 11g. This chart shows the essentials. Notice that we compare, not with Oracle Recovery Manager (RMAN), but with Oracle Data Pump export (expdp).
We do this because our backup facility has two drivers that use a table scan algorithm to read the rows thereby making backups taken with the default and consistent snapshot drivers a ‘logical’ backup. As native drivers that use a binary copy algorithm are added, our backup facility will give the user more freedom to choose the type of backup to use. However, for this release, only ‘logical’ backups are available.
Oracle 11g MySQL 6.0
Data Pump "Online Backup"
========== ===============
Type "logical database backup" "logical database backup"
with "physical" aspects
File is on server? on server
Monitoring "Status" after ctrl+c tables in mysql database
or DBA_DATAPUMP_JOBS view or SHOW PROCESSLIST
or V$SESSION_LONGOPS view
Preparable Yes, DBMS_DATAPUMP API no
Metadata yes database-level metadata
is saved except where noted
Compression yes, in Enterprise Edition no
Encryption yes, in Enterprise Edition no
Parallel yes, in Enterprise Edition no
Data Transforms yes (remap to obfuscate) no
XML "supported" sure
By Partition You can dump/restore single no
partitions.
Re-Use Yes, you can overwrite yes, unavoidably
Exclude/Include Yes, you can skip or add no
specified objects
Diff OSs Yes, you can change to a Yes, but mixed
different operating system. endianness has
not been tested
Stop+Restart Yes, you can stop an export No
and resume it later.
Methods File Copy and other ways. File Copy.
Privileges CREATE TABLE privilege (for See section titled
master table), and access "Coming Soon".
to a directory object.
Log File Optional. Always.
Remap Yes e.g. change names of No.
tables during re-import.
Flashback Yes, to a particular SCN. Yes, "as of" start time.
Read Access Yes, read-without-RESTORE No.
is possible by creating an
external table.
Example "expdp "BACKUP
system/password@db10g DATABASE
full=Y TO
directory=TEST_DIR 'DB10G.dmp'"
dumpfile=DB10G.dmp
logfile=expdpDB10G.log"
Feature#1 Multiple Image Files e.g. No.
"DUMPFILE=x1.dmp, x2.dmp".
Feature#2 "ESTIMATE_ONLY" No.
Feature#3 Picking what to dump or No.
restore:
"EXCLUDE=TABLE:EMP"
"INCLUDE=TABLE_DATA"
ALL|DATA_ONLY|METADATA_ONLY
QUERY='...' predicate clause
TABLES=...
TABLESPACES=...
Feature#4 FLASHBACK_SCN|FLASHBACK_TIME No.
Feature#5 parameter file, PARFILE=xxx No.
Feature#6 "version of objects to No.
export" =
COMPATIBLE | LATEST | or
"any database version"
Replication
What about Backup and Replication working together? This section contains only tentative suggestions, it is not useful for reference.
If BACKUP DATABASE is done on the master:
it will not be logged.
If RESTORE is done on a master:
it will not be logged.
In future version, RESTORE statement will (possibly) be logged together with the backup image provided that the user uses a special option.
If BACKUP DATABASE is done on a master:
Backup will not be synchronized with replication position. This means that it is hard to know what position one should use on the master after doing a restore. In later versions, the slave position will be synched with the backup image and thus the restore will set the replication state correctly so that it may continue replication. See MySQL’s WL#4105 worklog task description.
If RESTORE is done on a slave:
Replication shall not be allowed to execute while doing restore.
BACKUP DATABASE on a master does not affect a slave.
RESTORE on master will not affect slaves if no slaves are allowed during restore — that’s MySQL’s recommendation.
After RESTORE, the step of ‘replaying the binlog’ should normally be with binlogging off.
At the time of this writing, the work for synchronization with replication is being completed, for the first quarter of 2008 (using Sun’s system of quarters). Some of the strategies being considered include:
The BACKUP DATABASE statement is:
() replicated and the slave also makes an image file
() replicated but the slave does not make an image file
() not replicated
The RESTORE statement is:
() replicated and the slave also restores from an image file
() not replicated
() not even noted in bin log
The mysql database
There is a database named mysql which contains descriptions of objects and persistent information about all other databases. You cannot backup this database –
BACKUP DATABASE mysql TO ‘file’; /* This causes an error */
BACKUP DATABASE * TO ‘file’; /* This doesn’t back up mysql */
Therefore a backup image is not complete protection against a media crash, because the tables in mysql are necessary for reading tables in other databases. Therefore database administrators must take additional precautions with mysql database tables.
One possible set of additional precautions is:
1. LOCK TABLES for all tables in the mysql database.
2. Copy (with SQL or with operating system) all contents of mysql tables to a file, for example ‘mysql-image.bak’.
3. UNLOCK TABLES.
4. Preserve ‘mysql-image.bak’ along with the latest backup image.
Then, after a media crash and a RESTORE, a person with root privileges can add the data back with INSERT statements, using the data in the mysql-image.bak file for inspiration.
At time of writing the items in this category are:
Users and global-level privileges
Everything that’s in the table mysql.user.
Plugins
Everything that’s in the table mysql.plugin.
This does not include code. The code is in a different category, ‘Belonging but not exclusively belonging’.
Connections for federated tables
Everything that’s in the table mysql.servers.
Help
Everything that’s in the table help_category.
Everything that’s in the table help_keyword.
Everything that’s in the table help_relation.
Everything that’s in the table help_topic.
Time zone
Everything that’s in the table time_zone.
Everything that’s in the table time_zone_leap_second.
Everything that’s in the table time_zone_name.
Everything that’s in the table time_zone_transition.
Everything that’s in the table time_zone_transition_type.
Logs
general_log
slow_log
UDFs
LOGFILE GROUPS
mysql.host
mysql.db
mysql.host
mysql.ndb_binlog_index
mysql.online_backup
Known restrictions
Deliberate restrictions which will not change:
1. It is not possible to have BACKUP DATABASE or RESTORE in a function, stored procedure, event, or trigger.
2. It is not possible to PREPARE a ‘BACKUP DATABASE’ statement or a ‘RESTORE’ statement.
3. It is not possible to back up the mysql database (see section “The mysql database”) or the information_schema database.
Accidental restrictions which may someday change:
1. The maximum backup length may be restricted due to the maximum file length with some file systems. For example, file sizes may be limited for 4GB for the file system in certain releases of the Microsoft Windows operating system.
Coming Soon
Online Backup is not a completed feature. This document has only said what is actually here today. By the time that the the feature is “beta”, you should also see:
Syntax for BACKUP DATABASE statement changes:
1. There is an additional optional BACKUP DATABASE clause: [ COMMENT 'string' ]. For example:
BACKUP DATABASE d TO ‘file’ COMMENT ‘x’;
This is useful for putting a ‘tag’ on an image file, and for describing the contents.
2. There is an additional optional SKIP DEPENDENCY CHECKING clause: [ SKIP DEPENDENCY CHECKING ]. For example:
BACKUP DATABASE d to ‘file’ SKIP DEPENDENCY CHECKING;
This clause has the following effect …
If database X refers to database Y (for example because there is a view in X that selects from Y), and you are backing up database X but you are not backing up database Y, and MySQL determines that the dependency is serious enough, then you will see this error:
xxxx Database %s contains a reference to database %s
But if SKIP DEPENDENCY CHECKING is true, you will not see an error. Instead you will see this warning:
yyyy Warning: Database %s contains a reference to database %s
3. Privilege requirements for BACKUP DATABASE will be:
BACKUP, a new global privilege
FILE
SELECT or equivalent privilege on all objects in database.
4. Privilege requirements for RESTORE will be:
RESTORE, a new global privilege
FILE
CREATE USER
Further privileges depending on what you are restoring:
database: CREATE, DROP, GRANT OPTION, EVENT
table: CREATE, ALTER, DELETE, DROP, GRANT OPTION, INSERT, SELECT, UPDATE, TRIGGER
index: CREATE
routine: ALTER ROUTINE, CREATE ROUTINE, EXECUTE
view: CREATE VIEW, SHOW VIEW
You may ask: how can I be expected to have a privilege on table t if I haven’t yet restored table t’s database? Answer: your privileges are part of what is restored. Thus for RESTORE which restores database1, I need all the “databases” privileges on database1, I need all the “tables” privileges on every table in database1, I need all the “stored routines” privileges on every function in database1, I need all the “views” privileges on every view in database1, I need FILE privilege always. I need CREATE USER always.
5. Privilege checking must happen before the first database is backed up or restored. This will ensure that, if there is a privilege problem, you will find out at the start, before any data is backed up or restored.
6. A “native driver” for MyISAM will reduce blocking when a MyISAM table is being backed up.
Enterprise
Users of MySQL “Enterprise” will receive “native drivers” for major MySQL storage engines including Falcon. This will reduce blocking and put the “online” into “online backup for Falcon”.
There was talk at one time about arranging for some advance features (compression, encryption) to be deleivered only as part of the MySQL Enterprise subscription. That will not be the case, according to the current plan, as far as we know. But we’re writing the technical article not the explanation of how GPL works.
Further reading
Zmanda blog “MySQL Online Backup”
public worklog tasks containing “backup” in title/description
Leave a Reply