|
Logical vs physical database backup in MYSQL
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 following features.
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 OUTFILEstatement. These work for any storage engine, evenMEMORY.For restore, SQL-format dump files can be processed using the mysql client. To load delimited-text files, use the
LOAD DATA INFILEstatement or the mysqlimport client.
Physical backup methods have following features
- 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
MEMORYtables 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
MyISAMtable corresponds uniquely to a set of files, but anInnoDBtable shares file storage with otherInnoDBtables.) - 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 filesystem-level commands (such as cp, scp, tar, rsync), mysqlhotcopy for
MyISAMtables, ibbackup forInnoDBtables, orSTART BACKUPforNDBtables.For restore, files copied at the filesystem level or with mysqlhotcopy can be copied back to their original locations with filesystem commands; ibback restores
InnoDBtables, and ndb_restore restoresNDBtables.
Related posts:
- Possible corruptions in MYSQL database
- Upgrading mysql database
- Schedule backup is a good habit
- A distributed database offers an antidote
- Uploading huge database files to server
- Open files have historically been a backup
Related posts brought to you by Yet Another Related Posts Plugin.
Leave a Reply
   |