MYSQL - Backup & Recovery

 

There are four methods for backup and recovery a MySQL database. All other methods are based on these 4 methods. These are logical and physically backup methods.

 

 

With this method the real MySQL database files for the tables, in which all data is physically stored on the hard disk, are copied to a save location. If a backup is needed the files can be copied back to the server.

 

 

 

• fast

• easy for backup

• multiple files, if one is broken, hopefully just this table is lost, not the whole database

 

• takes a lot of diskspace, all the indexes etc. are copied too

• The database has to switched off for a certain time during the backup

• Restoring can become a little complex

• you need special permissions on the operation system

 

Linux provides a Logical Volume Manager (LVM) (http://en.wikipedia.org/wiki/Logical_Volume_Manager_(Linux)) and is a layer to manage the filesystem. The LVM provides the ability to create snapshots of any logical volume. So, you create a backup of the physical volume which can be easily used for recovering in the future. This is one of the best and easiest solutions, it is very fast, very easy and the potntial error level is very low. You don't have to set your database or application offline, there will be no locks on the tables and you get a stable snapshot of the current status.

 

 

 

 

 

 

 

 

 

 

 

• very fast

• easy

• no stop of the database, no lock on tables

 

• LVM needed

• maybe root access for the operation system needed

 

 

A mysql dump is a common strategy for backups. This is a logical backup, means the structure and the content of the database are exported into a special file in a special format. This is done by using the MySQL syntax and stores all relevant information’s that are needed to rebuild the database. Normally there is a statement to create the database again, statements to rebuild the tables and their structure and then statements to import the data into the tables itself. All these information’s are stored in one file and this can be copied to a save location. When a backup is needed the file can be imported and the database will be restored with the information’s stored in the file.

 

 

mysql -u Username -pPassword database_name < dump_database_name_YYYMMDD_His.mysql

 

Advantages: 

• Very easy

• Can be done by users without special permissions on the operation system, esp. root permissions

• The indexes are not stored, so this backup does not uses as much disk space as a physical file backup

• You can have a look into the backup and also search in it, data manipulation is also possible if something has to be changed

 

• Slower than a physical backup

• Slower in recovery, because everything has to be imported first and then the indexes have to be built again

• One file, if this is broken the backup is not possible

 

• --lock-all-tables: locks all tables during creation of the dump, so the application cannot access them, avoids data inconsistences!

• --single-transaction: the whole dump is executed as an transaction, so the application can still access and write the database, the dump is made via transaction

• --master-data: set the location of the master in a database replication, so the slave knows the position and where to start with the replication

• --add-drop-database: add a DROP DATABASE statement before each CREATE DATABASE statement

 

There are special tools to create a innobackup, see also here.This is a special case for databases where the storage engine innodb is used. As innodb became the default storage engine and mysisam will be removed in the future, this is also a very common possibility to create a database backup. It is nearly the same as a normal MySQL dump but also considers the special possibilities of innodb like foreign key constraints etc. There is the MySQL Enterprise Backup (MEB) (http://www.mysql.com/products/enterprise/backup.html) for creating innobackups, but it costs money. 

There is also an open source tool from percona named xtrabackup - http://www.percona.com/software/percona-xtrabackup. There is a free version available but also a enterprise edition which also costs money. As percona offers a lot of useful tools around mysql, this is maybe also a good choice for your MySQL toolkit. There are also other tools from percona which help to improve you daily live with MySQL.

 

Master-Slave replication for backups and avoiding downtimes

A special hint at this point: if possible, use a master-slave replication and use the slave for building the backups, so the main system if not affected and performance for the application is not affected. It is also a good setup to avoid long downtimes of your application. If one server crashes you can switch to the other with your application and it will stay online. In the meantime you can repair the broken system and then restore the old setup. So, if the master breaks, you can switch to the salve and it becomes master. If the slave fails, just the read requests of your application have to be routed to the master.

 

Fromdual.com offers the tool fromdual_bman which is a wrapper for seven different backup methods (the ones mentioned above and combinations of them).

 

A backup is always better than no backup. But if the backup is stored on the same logical disk on the server where your website also runs on, you mabe get in trouble. If the disk crashes your website is offline and you also loose your backup. In this case it is impossible to restore your website on another server and bring it back online. So, always save your backups on another logical volume or on another server. If the data is very important also consider to save your backups on multiple locations maybe also in other data centers. In a case of fire or something similar your backup can be fetched from somewhere else and recovery can run in another data center.

 

So called uuuups queries are queries where accidentially a wrong query was executed in a production system. This mostly happens because somebody executes the query manually by accident. There are multiple reasons why this can happen, for example mixing up the consoles etc.

Time is the key, so you have to act immediately!

Stop the database and your application immediately! Set your application to maintenance mode!

Two possibilities: 

 

You can only reimport the last backup, whenever it was made. Hopefully it was made not long time ago, maybe last night. By this you loose all changes between your last backup and the time of your uuuups query. Or, if you know the changes that were made in this time, you can fix the changes manually or by writing a script which does the changes for you.

 

 

We hope these hints can help you in your daily life with MySQL. There are also other posts about MySQL. Because in this post there was a lot about database replication mentioned, the post about MySQL - setup is also interesting for you.