• Logical versus physical backups

• Online versus offline backups

• Local versus remote backups

• Snapshot backups

• Full versus incremental backups

• Point-in-time recovery

• Backup scheduling, compression, and encryption

• Table maintenance

More generally, the following discussion amplifies on the properties of different backup methods.

• Logical versus physical (raw) backups. Logical backups save information represented as logical database structure (CREATE DATABASE, CREATE TABLE statements) and content (INSERT statements or delimited-text files). Physical backups consist of raw copies of the directories and files that store database contents.

  Logical backup methods have these characteristics:

  • The backup is done by going through the MySQL server to obtain database structure and content information.

  • Backup is slower than physical methods because the server must access database information, convert it to logical format, and send it to the backup program.

  • Output is larger than for physical backup, particularly when saved in text format.

  • Backup and restore granularity is available at the server level (all databases), database level (all tables in a particular database), or table level. This is true regardless of storage engine.

  • The backup does not include log or configuration files, or other database-related files that are not part of databases.

  • Backups stored in logical format are machine independent and highly portable.

  • Logical backups are performed with the MySQL server running (the server is not taken offline).

  • Logical backup tools include the mysqldump program and the SELECT ... INTO OUTFILE statement. These work for any storage engine, even MEMORY.

    For restore, SQL-format dump files can be processed using the mysql client. To load delimited-text files, use the LOAD DATA INFILE statement or the mysqlimport client.

  Physical backup methods have these characteristics:

  • The backup consists of exact copies of database directories and files. Typically this is a copy of all or part of the MySQL data directory. Data from MEMORY tables cannot be backed up this way because their contents are not stored on disk.

  • Physical backup methods are faster than logical because they involve only file copying without conversion.

  • Output is more compact than for logical backup.

  • Backup and restore granularity extends from the level of the entire data directory down to the level of individual files. This may or may not provide for table-level granularity, depending on storage engine. (Each MyISAM table corresponds uniquely to a set of files, but an InnoDB table shares file storage with other InnoDB tables.)

  • In addition to databases, the backup can include any related files such as log or configuration files.

  • Backups are portable only to other machines that have identical or similar hardware characteristics.

  • Backups can be performed while the MySQL server is not running. If the server is running, it is necessary to perform appropriate locking so that the server does not change database contents during the backup.

  • Physical backup tools include file system-level commands (such as cp, scp, tar, rsync), mysqlhotcopy for MyISAM tables, ibbackup for InnoDB tables, or START BACKUP for NDB tables.

    For restore, files copied at the file system level or with mysqlhotcopy can be copied back to their original locations with file system commands; ibback restores InnoDB tables, and ndb_restore restores NDB tables.

• Online versus offline backups. Online backups take place while the MySQL server is running so that the database information can be obtained from the server. Offline backups take place while the server is stopped. (This distinction can also be described as “hot” versus “cold” backups; a “warm” backup is one where the server remains running but locked against modifying data while you access database files externally.)

  Online backup methods have these characteristics:

  • Less intrusive to other clients, which can connect to the MySQL server during the backup and may be able to access data depending on what operations they need to perform.

  • Care must be taken to impose appropriate locking so that data modifications do not take place that compromise backup integrity.

  Offline backup methods have these characteristics:

  • Affects clients adversely because the server is unavailable during backup.

  • Simpler backup procedure because there is no possibility of interference from client activity.

• Local versus remote backups. A local backup is performed on the same host where the MySQL server runs, whereas a remote backup is initiated from a different host.

  • mysqldump can connect to local or remote servers. For SQL output (CREATE and INSERT statements), local or remote dumps can be done and generate output on the client. For delimited-text output (with the --tab option), data files are created on the server host.

  • mysqlhotcopy performs only local backups: It connects to the server to lock it against data modifications and then copies local table files.

  • SELECT ... INTO OUTFILE can be initiated from a remote client host, but the output file is created on the server host.

  • Physical backup methods typically are initiated locally on the MySQL server host so that the server can be taken offline, although the destination for file copies might be remote.

• Snapshot backups. Some file system implementations enable “snapshots” to be taken. These provide logical copies of the file system at a given point in time, without having to physically copy the entire file system. (For example, the implementation may use copy-on-write techniques so that only parts of the file system modified after the snapshot time need be copied.) MySQL itself does not provide the capability for taking file system snapshots. It is available through third-party solutions such as Veritas or LVM.

• Full versus incremental backups. A full backup includes all data managed by a MySQL server at a given point in time. An incremental backup consists of the changes made to the data since the full backup. MySQL has different ways to perform full backups, such as those described in previous items. Incremental backups are made possible by enabling the server's binary log, which the server uses to record data changes.

• Point-in-time recovery. One use for the binary log is to achieve point-in-time recovery. This is done by recovering first from the backup files to restore the server to its state when the backup was made, and then by re-executing changes in subsequently written binary log files to redo data modifications up to the desired point in time.

• Backup scheduling, compression, and encryption. Backup scheduling is valuable for automating backup procedures. Compression of backup output reduces space requirements, and encryption of the output provides better security against unauthorized access of backed-up data. MySQL itself does not provide these capabilities. ibbackup can compress InnoDB backups, and compression or encryption of backup output can be achieved using file system utilities. Other third-party solutions may be available.

• Table maintenance. Data integrity can be compromised if tables become corrupt. MySQL provides programs for checking tables and repairing them should problems be found. These programs apply primarily to MyISAM tables. See Section 6.4, “Table Maintenance and Crash Recovery”.

• A forum dedicated to backup issues is available at http://forums.mysql.com/list.php?93.

• The syntax of the SQL statements described here is given in Chapter 12, SQL Statement Syntax.

• Details for mysqldump, mysqlhotcopy, and other MySQL backup programs can be found in Chapter 4, MySQL Programs.

• For additional information about InnoDB backup procedures, see Section 13.6.6, “Backing Up and Recovering an InnoDB Database”.

• Replication enables you to maintain identical data on multiple servers. This has several benefits, such as allowing client load to be distributed over servers, availability of data even if a given server is taken offline or fails, and the ability to make backups using a slave server without affecting the master. See Chapter 16, Replication.

• MySQL Cluster provides a high-availability, high-redundancy version of MySQL adapted for the distributed computing environment. See MySQL Cluster NDB 6.X/7.X. For information specifically about MySQL Cluster backup, see Online Backup of MySQL Cluster.

• Distributed Replicated Block Device (DRBD) is another high-availability solution. It works by replicating a block device from a primary server to a secondary server at the block level. See Chapter 14, High Availability and Scalability