How to Backup and Restore mysql databases

Strongly recommended book
Hi Guys,

Just felt like writing an article on mysql backup and restore. I know there is heaps of content available on Google but I though I should write one too. This article only highlights handy options for mysql Backups rather than explaining everything.

Mysql is a free database solution which you may have come across, either while using it as a developer or indirectly as a user on any website. With over, 11 million installations, there are hundreds of thousands of websites using mysql as their backend database solution. Mysql stores all the data somewhere on the server as a group of files. Database size can grow with the amount of data you store on your server. If you are a public website if becomes a necessity to backup your data so that if some disaster occur e.g. server crashed or your server burnt down you should be able to restore your data just from the point where you server crashed. There are many different schemes to have a solid backup plan. Some of them are listed below:

  1. Hourly incremental backups.
  2. Daily backups
  3. Weekly backups
  4. Monthly backups

It depends on your need and the data sensitivity which of the above scheme suits you.

Database Backup

The whole backup and restore process is extremely easy, in the matter of fact once you learn how to use mysqldump or mysqlhotcopy, you will find it very amazing and useful. All the backed up data is in raw format regardless of which utility method you use to backup your databases.

To backup your mysql databases either on *ix servers or Windows machine you will need “mysqldump” mysql utility that is shipped with mysql enterprise of community server.
There are few general ways you can use mysqldump utility:

mysqldump [options] database_name [tables]

mysqldump [options] multiple_database_names

mysqldump [options]  –all-databases

 

In the ways listed above if you don’t mention tables in option 1 then the entire database will be backed up.

mysqldump is a good utility to backup database which are type InnoDb, BDB or MyISAM. But specifically if your tables inside your database are all MyISAM then I would recommend you using “mysqlhotcopy”. “mysqlhotcopy” is also a free command line utility shipped with mysql server.

phpMyAdmin provides web based few click option to backup your databases

to know more about the options for mysqldump you can visit

http://dev.mysql.com/doc/en/mysqldump.html

For an example I will show you how you can

  1. backup all, single, multiple tables
  2. all available databases
  3. One of many databases.
  4. Including useful options and when to use them

Say you have a database called “test_database” and under “test_database” you have three tables

  1. Test_table1
  2. Test_table2
  3. Test_table3

backup all, single, multiple tables

Let’s see how I can backup all tables in “test_database”. When you say all tables that means I have two options either backup database as a whole like this without mentioning the table names

Shell> mysqldump –uroot –pyourrootpwd test_database > /custompath/backup_file.sql or

Shell> mysqldump –uroot –pyourrootpwd test_database test_table1 test_table2 test_table3 > /custompath/backupfile.sql

From the command above you can choose all, single or multiple tables to backup from a single command.

To backup all available databases

use the following command

shell> mysqldump -uroot -ptmppassword –all-databases > /custompath/backupfile.sql

as you can see we have used option “–all-databases” to backup all available databases.

One of many databases

Say if you have two databases “test_database1” and “test_database2” and you just want to backup “test_database1” use the command below to do that:

shell> mysqldump -uroot -ptmppassword –-databases test_database1 > /custompath/backupfile.sql

as you can see we have used option “–databases” to backup “test_database1” out of the available databases. Including useful option while backing upYou already have seen options like –all-databases and –databases above but there are many others that you can use. It all depends on your need. Let’s check out few of them:

  •  –opt: this is a group option and is enable by default. It is logically same as –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, and –disable-keys. To disable this option use –skip-opt
  •   –compact: this gives you less verbose output. I consider it good for debugging. It disables structure comments and also the header and footer constructs. It enables options such as –skip-add-drop-table, –no-set-names, –skip-disable-keys and –skip-add-locks options.
  •   –add-drop-database: add drop database statement before each create database statement. It is useful if you are overwriting an entire data file.
  •   –add-drop-table: adds drop table before each create table statement. Useful when you are overwriting a database.
  •   –flush-privileges: Emit a FLUSH PRIVILEGES statement after dumping the mysql database. This option should be used any time the dump contains the mysql database and any other database that depends on the data in the mysql database for proper restoration. This option was added in MySQL 5.1.12. (taken from mysql website).

There are twenties of more options that you can use. Above are the most commonly used ones. If you are interested to know more please visit http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html   Restoring you database that you backed up using mysqldumpBacking up the database is half the job. Say if there is a need to restore your database how you do it. It is very simple. Above we discussed how we backup our databases. We backed up “test_database” as our database by using the following statement Shell> mysqldump –uroot –pyourrootpwd test_database > /custompath/backup_file.sql

Restoring is quite simple. Run the statement below to read the backup file back to the server

Shell> mysql –uroot –pyourrootpwd test_database < /custompath/backup_file.sql OR

Shell> mysql –uroot –pyourrootpwd –e ”source /custompath/backup_file.sql” test_database

You may have to create a blank database first, depending on which option you chosen when backing up the database. Say if you have chosen to skip create and drop database option while backing up your database you need to do this before using any of the above statement.

And then run the following statement to restore your database.

Shell> mysql –uroot –pyourrootpwd test_database < /custompath/backup_file.sql

How other programs can help for full proof backup strategy

There are many backup solutions. If you are a windows user then you can use inbuilt backup application to backup your sql file that is result of mysqldump. There are many disadvantages of using in built windows backup solution such as they don’t offer online backup, they don’t have inbuilt backup strategies. I use a solution called BackupAssist which overcomes all of this and provides me with industry standard backup strategies. To know more about this product isit www.backupassist.com

I have highlighted following backup destinations:

  1. NAS
  2. Tape Drive
  3. External HDD
  4. USB flash drive
  5. Offsite/Online (FTP or Rsync)
  6. Local hard drive.

Depending on the data importance you can choose any of the above. I use online backup, NAS and external HDD to backup my sql files.

You can work on your own mysql data backup strategy. Many will have different opinion but you are the one to decide which is best for you.

Read this article to know more about some advantages and disadvantages of you backup strategies

http://technet.microsoft.com/en-us/library/cc181319.aspx

Some authors have written 1000 page plus books about how you go with your backups. I hope this article gives you a kick start about your backup plans but all depends on your needs and how you want to address those. Please leave a comment if you have a specific question about this article or want to know more about something that I stated in the article.

 

 

 

VN:F [1.9.22_1171]
Rating: 4.5/5 (1 vote cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)
How to Backup and Restore mysql databases, 4.5 out of 5 based on 1 rating

Comments

  1. Mysql Backup says:

    Great work done by author of this blog. I never seen such a beautiful and informative blog. Also the looks of the blog is awesome. Keep posting please.

Speak Your Mind

*

CommentLuv badge