0

What does it mean to backup a database?

My impression is that you would simply dump everything that makes up the database into some format that can be stored externally and used to restore from when needed.

Are there different levels of backups? For example, maybe it is not necessary to backup absolutely everything about the database. Data is critical, so the table structure is important, but there's probably more to databases than just that.

MxLDevs
  • 339
  • 1
  • 4
  • 10

1 Answers1

4

Database backups are copies of your database contents to prevent data loss in case of a disaster or data corruption.

There are two general types of backup Physical & Logical.

A Logical Backup exports the data stored in a table regardless of the location of the table. Logical backups recreate the tables,indexes and insert all the data to the tables. Therefore, are relatively slower than physical backups. Depending on the RDBMS and the software you use you can change the way the logical backup is performed for the example you mentioned, in MySQL using mysqldump dbname --no-data you can get a backup of table definitions without data in the tables for a database. A logical backup creates a file that when executed restores and recreates all the data in the database.

A Physical Backup is an actual copy of the data files that store a database's Structure,Index and Data which are on the hard disk.

One of the challenges of taking backups is making consistent backups.When a database is active it may change while the backup is being performed. During a hot backup( a backup when the database server is running) there are several methods to ensure consistency.

1) Using Table locks: Tables are temporarily locked and they won't execute any UPDATE, INSERT or DELETE.

2) Another method is using Multi-Version Concurrency Control (MVCC) where the database takes a point of time in the database as the backup reference point and backs everything up from there, while accepting UPDATE, INSERT or DELETE while backup is being performed. The advantage of MVCC is that the tables will completely operational while backup is taking place.

NOTE: Locking mechanisms depend on the RDBMS

A Cold backup is another way to ensure consistency. In a cold backup the database server is turned off and not accepting any new clients.

Sam
  • 334
  • 2
  • 12
  • Thanks, it is good to know that the whole backup process is fairly straightforward. – MxLDevs Feb 12 '14 at 20:07
  • You probably need to mention that the most important feature of a proper database backup is the ability to create a logically consistent copy of the database while users and/or applications are actively changing the data. – mustaccio Feb 12 '14 at 21:00
  • sure I'll update the answer shortly – Sam Feb 12 '14 at 21:03
  • I didn't consider what happens when the database is being used during the backup process (eg: if it takes a long time). I looked up how mysql handles hot backups and it looks like something I won't have to worry about! – MxLDevs Feb 12 '14 at 21:29
  • in MySQL using Innodb storage engine will take advantage of MVCC and row level locking where as MyISAM will cause table locks – Sam Feb 12 '14 at 21:31
  • To be totally complete however, your backup also need meta data like users, access permissions, server configuration. Backing up the data is good but wouldn't be complete without your server configuration to restore a server rapidly. – ETL Feb 13 '14 at 02:27