Types Of MySQL Backup

Hello everyone. Today I am sharing how many types of MySQL backup have.In this article i will share my experience with you, what type of backup i used in MySQL.

These are the MySQL Backup Types.

Logical Backup

A logical backup is created by saving information that represents the logical database structures. It use SQL statements like CREATE DATABASE, CREATE TABLE, and INSERT. It is not correct to say that a logical backup is a text representation of the database server.  Logical backup contain non-text binary . Other names for a logical backup are a logical export and an export.

Advantage :

The advantage of a logical backup are that it allows the database administrator to manipulate the backup. We use tools such as dd and grep and programming languages such as awk and Perl. In addition, logical backups are more compatible between different versions of MySQL when you are upgrading your database server.  When you are upgrading between different versions(that is, when upgrading from version 5.1 to 6.0) of mysqld, Sun (Company name) recommends a logical backup.

Disadvantage :

Logical Backup doesn’t have so much disadvantage. When we compare it with Physical Backup, Logical backup is slow. It take more time as compare to physical backup.

Physical Backup

A physical backup is a backup of the actual database files or disk partitions. This can be much faster to back up and restore than logical backups. Though physical backups do not compress much (the data is usually in a binary format and thus somewhat compressed already). Physical backups are often smaller than uncompressed logical backups. Physical backups may also be called raw backups.

Full Backup

A full backup is a standalone backup containing everything in the database. If necessary you could use the results of the full backup to re-create a server somewhere else. A full backup can be either a logical or physical backup.

Incremental Backup

An incremental backup is a backup that only contains the data changed since the previous backup. The previous backup may have been full or incremental.The advantage of an incremental backup compared to a full backup is quicker backup times. Incremental backups are used to be able to back up data more frequently than a full backup may allow.

Now I will explain it using an example. In any organization, Full backup take 3-4 hours in complete backup on other hand Incremental backup take only 30 minutes. Generally, Incremental backup used on daily bases.

The biggest disadvantage of an incremental backup is that it is not a full data set, and cannot be used by itself to restore a full data set. This means that incremental backups have longer recovery times than full backups, because the latest full backup and all intermediate incremental backups need to be restored.

Consistent Backup

A consistent backup is a backup at an exact moment in time. A backup process takes time to complete. Consider the following scenario:
■ The customer table has a field that references a record in the address table.
■ The address table is backed up at 03:00 am.
■ The customer table is backed up at 03:05 am.
Between 03:00 am and 03:05 am, a customer named ‘Sunder Das’ signs up for a new account using an address not previously in the system. The address table was backed up at 03:00 am, before Sunder signed up for a new account.
Therefore, the backup of address will not contain Sunder’s address.

However, customer is backed up at 03:05 am, and by then Sunder’s address is in address, and there is a reference to that record in customer. Thus, a problem occurs: in the backup of customer, Sunder’s record references an address that does not exist in the backup of address!
An inconsistent backup is usually simpler and less resource-intensive to produce than a consistent backup. However, an inconsistent backup cannot be used as a standalone backup. An inconsistent backup may be used for partial data restores (such as restoring only sunder’s e-mail address).

Hot Backup

A hot backup is a backup of a database that is still running. During a hot backup, neither reads nor writes are blocked.

Warm Backup

A warm backup is a backup of a database that is still running. During a warm backup, read queries are not blocked but writes are prohibited from making any modifications to the database for the duration of the backup.

Cold Backup

A cold backup is a backup performed while the database is shut down. This makes it very easy to make a consistent copy of your data. The disadvantage is that the server is not accessible during the time the backup is performed.

Online Backup

One of the most important new features of MySQL 6.0 Server is the online, logical hot backup feature called MySQL Backup. It provides for an integrated method of performing hot backups. In addition there is optional compression and encryption available when performing backups.

Some Noteworthy Points

• Physical backups are very useful for disaster recovery, but logical backups are much more useful when restoring partial amounts of data.
• A replicated slave can serve as a backup for disaster recovery, but not for data recovery.
• If you accidentally delete data on the master this will be replicated to the slave and you then have no method of recovering that data.
• Slaves often make ideal platforms for backups because it is often easier to have a slave out of service for a time.
• The mysqldump program has been a backup tool for mysqld for a long time.

What to Backup?

Binary logs (for incremental backups, or to create a slave of the backed-up machine)
■ InnoDB log files (for physical backups).
■ mysqld option file (such as /etc/my.cnf).
■ Slave position information (master.info) if backup occurs on a slave.
■ Relay logs on slave server if backup occurs on a slave.