MySQL Backups and Recovery

Backup is the most important task of an database administrator, you must protect your data at all costs, this means regular backups and regular restores even to another system just to check the integrity of those backups.

Backup and Recovery Types
MySQL Backups and RecoveryLogical Backup (SQL Dumps)

The result of a database dump is one or more files that contain the SQL statements (mostly INSERT and CREATE TABLE) necessary to re-create the data. Dumps are produced using mysqldump. Dumps can be performed over the network so that the backups are created on a host other than your database server. It’s possible to produce dumps of any MySQL table type.

Having the contents of the tables as SQL files provides a lot of flexibility. To look for a few records, load the file in your favorite editor like notepad. The dumped data is quite readable.

Restoring a dump is easy. Because the dump file contains all the necessary information to re-create the table, simply feed that file back into the mysql command-line tool. To restore only some of the records, the file can be edited directly or a script written to prefilter out the records that aren’t needed. Raw backups don’t provide this flexibility. When using a raw backup, only whole tables can be operated.

There are some downsides to using dumps. Dumps are more CPU-intensive to produce and will take longer than other methods. Also, reloading a dump requires that MySQL spend considerable CPU time to rebuild all the indexes.

Logical backup methods have these characteristics:
• The backup is done by querying the MySQL server to obtain database structure and content information.
• Backup is slower than physical methods because the server must access database information and convert it to logical format.
• Output is larger than for physical backup, particularly when saved in text format.
• The backup does not include log or configuration files, or other database-related files that are not part of databases.
• Logical backups are performed with the MySQL server running. The server is not taken offline.

Physical Backup (Raw Backups)

A raw backup is a direct copy of MySQL’s data files as they exist on disk. Raw backups are much faster and more efficient than dumps.

Raw backups are most often used to back up a live server. To get a consistent backup, tables need to be locked so that no changes can occur until the backup completes.

Restoring a raw backup is relatively easy. For MyISAM tables, put the data files in MySQL’s data directory. InnoDB tables can’t be restored individually from a raw backup because they are stored in shared tablespace files rather than individually. Instead, shut down MySQL and restore the tablespace files.

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.
• Physical backup methods are faster than logical because they involve only file copying without conversion.
• 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.

Below are the terms used for MySQL Backup

MySQL Backups and RecoveryLogical Backup
This type of backup is created by saving information that represents the logical database structures using SQL statements like create database, create table and insert. This type of backup is ideal when you want to upgrade from one version of MySQL to another however it is a slower method of backing up.

Physical Backup
This type of backup is a backup of the actual database files or disk partitions, this type of backup can be very fast to backup and restore.

Full Backup
A full backup is a standalone backup containing everything in the database, this could then be restored on another server. A full backup can be either logical or physical.

Incremental Backup
This type of backup only contains the data that has changed from the last backup. The advantage of this type of backup is that it is faster as there is not some much data to backup, however the disadvantage is that it takes longer to recover.

Consistent Backup
This is a backup at an exact moment in time, generally you shutdown the database (or quiescent mode) then take the backup.

Hot Backup
This type of backup is taken when the database is running, during the backup both reads and writes are not blocked.

Warm Backup
This type of backup is taken when the database is running, however reads are not blocked but writes are prohibited from making any modifications to the database.

Cold Backup
Similar to a consistent backup as the database is shutdown before the backup begins.

<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

1 comment on “MySQL Backups and Recovery”

  1. nilesh palve Reply

    Hey Bro good explanations about backup.good job and post more and more about mysql like master-master,master-slave replications.

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.