Backup And Recovery with detailed information on MySQL

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

• use a reporting database if the customers don't need real time data and you have the money and time, Production data could be restored every day to this system which is a very good test
• use a performance test server with Production data, ideal to test releases of your software against Production data which is generally has more volume then a test system, restore perhaps once a week
• at least perform a DR once per year to prove the backup solution is working, for example you may have forgotten to backup something not only regarding the database but from the systems as well

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 likecreate 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

• LVM snapshotting
• ZFS snapshotting
• VMWare snapshots

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

• no backup of the internal mysql datadisk
• no native driver for InnoDB tables
• no native driver for Maria or Falcon
• no backup of partitions
• no incremental backups

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

• restore the latest backup
• recovery the data to a point-in-time using recovery log files

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

• restore the database using the last backup
• determine the first binary log and starting position needed
• determine the last binary log needed
• convert the binary log to text format with the mysqlbinlog utility using options to specify the start and stop time
• check the text file to make sure it's what you need
• import the converted binary log(s)

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