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
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 ## backup multiple databases ## backup some specific data ## dumping from one database to another |
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 ## restore a database |
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 ## use pattern match to backup databases and tables |
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
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 ## use a date to end at a specific time |
restore the converted file | mysql --user=root -password < mysql-bin.010310.sql |