Backups and Recovery

This 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. There is no point in putting yourself in a position where you are holding your breathe when a restore is happening only to find out that the backup is corrupt, try if possible to perform regular restores if not then at least you should be performing a disaster recovery test once per year. Not being able to restore could be a disaster for your company and your job.

To check your backups you can use one or more of the below which I have used in the past

Backups and restoring

First lets start with a few terms associated with backups

logical 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
point-in-time restore is a restoration of a database to a specified date and time , some databases use a full backup and recovery logs to restore to that point-in-time, others can only use the last full backup which means that data might have to be re-keyed into the system.

As well as obtaining a backup in your maintenance window you should also be aware on how long a restore will take thus to make sure that you meet you SLA agreements during a DR or if you have to recovery a database due to corruption or user error.

The $64,000 question is how often you should take your backups, and this i am afraid depends, so company are happy for once a month backups other may take two backups per day. The answer generally has to come from the business on what they are prepared to lose, amount of data lost or what has to be re-keyed into the system again. If you have a small company that say has to re-key in 20-50 invoices then that's no big deal, however if you have a trading company that many have to re-key in 10's of thousands of entries/trades then that becomes a problem. You have to add the time it takes to restore the system plus the time it takes to recover the system so that users are able to use it, it is this time that you give to the business to make there decision on what is a acceptable time period that the business can be down for, the shorter the time the more money that will have to be thrown at the solution, if you are talking about zero downtime then we would have to implement a high availability solution which could cost a lot of money, if you are happy with 1 days downtime then this should be enough to restore and recovery a database and to re-key in some entries to make the database consistent with the companies paper work.

As you saw above there are a number of ways to backup a database, depending on the the available time to perform a backup will make you decide on what method to use, if you have a short maintenance window with a large database then a incremental backup maybe the only option, but you have a large maintenance window with a small database then you could perform a full backup, remember what ever option you use with have a impact on the recovery time.

One point to make is that you backups should be taken off-site if held on tape or copied across to an other system in another location, if an incident happened on the original system for example a fire you don't want to lose your backups as well, the storing of off-site data should be part of you DR plan.

Enough of talking about backups lets see how you can actually take one, there are a number of backup tools that MySQL can use, see the table below

Backup tools for MySQL
Backup method
Storage engine
Impact
Backup speed
Recovery speed
Recovery granularity
mysqldump
ALL
WARM
MEDUIM
SLOWEST
MOST FLEXIBLE
mysqldump
INNODB
HOT
MEDUIM
SLOWEST
MOST FLEXIBLE
select into outfile
ALL
WARM
SLOW
SLOW
MOST FLEXIBLE
mk-parallel-backup
ALL
WARM
MEDUIM
MEDUIM
FLEXIBLE
ibbackup
INNODB
HOT
FAST
FAST
FLEXIBLE
ibbackup
ALL
WARM
FAST
FAST
FLEXIBLE
backup command in mysqld
ALL
HOT
FAST
FAST
FLEXIBLE
filesystem (copy files)
ALL
COLD
FASTEST
FASTEST
NOT FLEXIBLE
snapshot (using LVM, ZFS, VMWare)
ALL
ALMOST HOT
FAST
FAST
LEAST FLEXIBLE
mysqlhotcopy
MyISAM
MOSTLY COLD
FAST
FAST
FLEXIBLE

The mysqldump program has been around a long time, it provides a logical backup of the entire database, individual databases, individual tables or even subsets of data using the --where option, it is often called a data dump. The output is in ascii format which means that you can open it in vi or notepad and change the contains if desired. I am not going to detail all options of the mysqldump command but show you a few examples

mysqldump

## backup all databases
mysqldump --user=root --password --all-databases > backup_<date>_all.sql

## backup a specific database
mysqldump --user=root --password <database_name> > backup_<date>_<database_name>.sql

## backup multiple databases
mysqldump --user=root --password <database_name>,<database_name> > backup_<date>.sql

## backup a table from a database
mysqldump --user=root --password <database_name> <table_name> > backup_<date>_<database_name>_<table_name>.sql

## backup some specific data
mysqldump --user=root --password <database_name> <table_name> --where "last_name='VALLE' order by first_name > backup_<date>.sql

## dumping from one database to another
mysqldump --databases <database_name> | mysql -h <destination_host> <database_name>

restore a mysqldump ## all databases
mysql --user=root --password < backup.sql

## specific database
mysql --user=<user> --password <database_name> < backup_<dataabse_name>.sql

You can use the into outfile clause of the select statement to backup individual tables, the command used to load the dump created is load data infile

select into outfile / load data infile ## dump of the accounts table
select * into outfile '/tmp/accounts.txt' from accounts;

## load the dump
load data infile '/tmp/accounts.txt' into table accounts;

The Maatkit parallel dump and restore toolkit can be downloaded from http://www.maatkit.org basically it's a wrapper around mysqldump which provides the programs mk-parallel-dump and mk-parallel-restore, what this means is that if you have a 16 core server and you are dumping 32 tables, the script will start up 16 separate copies of mysqldump with each process dumping a separate table.

mk-parallel-dump, mk-parallel-restore

## backup a database
mk-parallel-dump --basdir=/backups

## restore a database
mk-parallel-restore /backups

Snapshots for a filesystem depend on what operating system or software you are using, here are some links to my web pages regarding LVM, ZFS and VMWare

New in MySQL 5.6 is the online logical host backup, you can also use compression and encryption which is important when using sensitive data.

backup backup database <database_name> to '<database_name>-backup.sql'
restore restore from '<database_name>-backup.sql'
history select * from backup_history where backup_id = 321\G

There currently is a number of limitations of this command

The mysqlhotcopy is a perl script written to provide a consistent backup of MyISAM and ARCHIVE tables, it does some limitations one of which when run it uses the lock tables command to create read locks on the tables being backed up, this allows for a consistent backup. again there are a number of options that you can use so have a look at the man page, here are a few examples

mysqlhotcopy

## backup a database
mysqlhotcopy <database_name> /backups

## backup multiple databases
mysqlhotcopy <database_name> accounts /backups

## backup a database to to another server
mysqlhotcopy --method=scp <database_name> \ username@backup.server:/backup

## use pattern match to backup databases and tables
mysqlhotcopy <database_name>./^employees/ /backup

Lastly ibbackup is a 3rd party software which allows you to perform non-blocking hot backups of InnoDB tables, it is entirely command-line driven which means that it is ideal for scripts, here is a link to the web site http://www.innodb.com/doc/hot_backup/manual.html

Recovering from Crashes

Most often you have to recover to a point-in-time after the last backup, the normal procedure is as follows

MySQL server uses a binary format for the log files to save space, this means that you cannot view these files directly, a utility called mysqlbinlog is supplied to convert these log files into a text format that you can view. So the process for performing a point-in-time restore for MySQL is

convert the log files

## convert to a specific binary log file
mysqlbinlog mysql-bin.010310 > mysql-bin.010310.sql

## use a date to end at a specific time
mysqlbinlog --stop-datetime='201204-29 17:00:00' mysql-bin.010312 > mysql-bin.010312.sql

## other options are
--stop-datetime
--start-datatime
--start-position
--stop-position

restore the converted file mysql --user=root -password < mysql-bin.010310.sql