10 Ways to Automatically & Manually Backup MySQL Database
- Posted in How To's, PHP
- 56 Comments »
MySQL is one of the most popular open source database management system for the development of interactive Websites.
If your site stores its sensitive data in a MySQL database, you will most definitely want to backup that information so that it can be restored in case of any disaster (we all have been there).
There are several ways to backup MySQL data. In this article we’ll look at how to backup your databases using different methods, we will also learn how to achieve an automatic backup solution to make the process easier. Starting with the mysqldump utility that comes with MySQL, we will review several examples using mysqldump, including the backup of your database to a file, another server, and even a compressed gzip file and send it to your email.
1. Automatically backup mysql database to Amazon S3
Many of users use Amazon S3 to backup their mysql databases. Here is an automated script which does this task of taking the backup of a mysql database and then moving it to the Amazon S3.
2. How to Backup MySQL Database automatically (for Linux users)
15 2 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`data ' %m-%d-%Y'`.sql.gz
This post will show you how to backup MySQL Database automatically if you are a linux user. You can use cron to backup your MySQL database automatically.”cron” is a time-based scheduling utility in Unix/Linux
operating system.
3. Backup your MySQL databases automatically with AutoMySQLBackup
AutoMySQLBackup has some great features to: backup a single database, multiple databases, or all the databases on the server; each database is saved in a separate file that can be compressed (with gzip or bzip2); it will rotate the backups and not keep them filling your hard drive (as normal in the daily backup you will have only the last 7 days of backups, the weekly if enabled will have one for each week, etc.).
4. Backing Up With MySQLDump
mysqldump ---user [user name] ---password=[password] [database name] > [dump file]
In this article we’ll look at how to backup our databases using the mysqldump utility that comes with MySQL. Several examples will be reviewed using mysqldump, including the backup of your database to a file,
another server, and even a compressed gzip file.
5. Backup Your Database into an XML File Using PHP
mysqldump ---user [user name] ---password=[password] [database name] > [dump file]
Here’s a PHP snippet that outputs your database as XML. XML isn’t the easiest format to restore a table but it can be easier to read.
6. How to – Using PHP To Backup MySQL Database
Execute a database backup query from PHP file. Below is an example of using SELECT INTO OUTFILE query for creating table backup:
<?php include 'config.php'; include 'opendb.php'; $tableName = 'mypet'; $backupFile = 'backup/mypet.sql'; $query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName"; $result = mysql_query($query); include 'closedb.php'; ?>
To restore the backup you just need to run LOAD DATA INFILE query like this :
<?php include 'config.php'; include 'opendb.php'; $tableName = 'mypet'; $backupFile = 'mypet.sql'; $query = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName"; $result = mysql_query($query); include 'closedb.php'; ?>
7. Backup MySQL Database Via SSH
A simple solution to backup your large MySQL databases through SSH. You will need to enable shell access inside your Plesk control panel and use a utility such as PuTTY to log into your server via SSH.
8. How to e-mail yourself an automatic backup of your MySQL database table with PHP
This script will send an e-mail to you with an .sql file attached, thus enabling you to back up specific tables easily. You could even set up an e-mail account just to receive these backups…
9. Ubuntu Linux Backup MySQL server Shell Script
If you have a dedicated VPS server running Ubuntu Linux. Here is how to backup all your mysql server databases to your ftp server
10. How to backup MySQL databases, web server files to a FTP server automatically
This is a simple backup solution for people who run their own web server and MySQL server on a dedicated box or VPS. The main advantage of using FTP or NAS backup is a protection from data loss.First you will need to backup each database with mysqldump command, Automating tasks of backup with tar, Setup a cron job and generate FTP backup script.
$ mysqldump -u root -h localhost -pmypassword faqs | gzip -9 > faqs-db.sql.gz
11. MySQL Export: How to backup your MySQL database?
You can easily create a dump file(export/backup) of a database used by your account. In order to do so you should access the phpMyAdmin tool available in your cPanel.
Worth Reading
- 10 things you need to know about backup solutions for MySQL
Are you using someone else’s backup solution for your MySQL data? Do you care a lot about your data? Are you sure you’re getting a reliable, recoverable backup that’ll work for your business and your application, and won’t impact your critical processes while it runs? Here are ten questions you need to be able to answer.
Your automatic backup script for Linux will not work, use this instead in the crontab:
15 2 * * * /usr/bin/mysqldump –opt –all-databases -u root -pPASSWORD > /foo/bar/db-`date +%Y-%m-%d`.sql
xmlsamurai As posted here it works fine for me.. Your system must be different or you are doing something wrong. Next time you want to post try using terms like.. It didn’t work for me.. This way you don’t openly say that the instructions is wrong or not working that someone so kindly posted on the web .
I have tested it and it does work.
How about using logrotate. Its the best way to keep backup only for X days. Also use rsync to transfer the file to a remote server just for another backup.
/var/backups/db.sql.gz {
daily
rotate 14
nocompress
create 640 root adm
postrotate
mysqldump db -u user -psecret >/var/backups/db.sql
rsync -az /var/backups/db.sql –password-file /root/rsync.password remote@server::backup
gzip -9f /var/backups/db.sql
endscript
}
hey Vivek -
I like your method the best. I found /usr/sbin/logrotate is installed by default in OpenSolaris (SXCE b123) so I have found me a “export & retain the last 14″ method!
thanks!
C-YA
I use SQLyog Enterprise. It can connect over SSH and I can schedule my backups. It is not free though :-(
Personnaly, I just have PhpMyAdmin do the backup for me.
This is what I use – Perl script to backup mysql databases
Nice list list of tips. Number 6 and #7 is handy.
Thank you noupe!! Nice list of resources to be used in different applications.
This list is incomplete imo, i’d reather see examples of using diff for db backups. This doesnt scale at all.
Dump a remote db locally in one easy line:
ssh user@server “/usr/bin/mysqldump -u user -p password database_name” | dd of=/where/you/want/the/dump.sql
Not a single one of these options will scale past a small to moderately sized database.
PS.
I guess people that doesn’t post their names like to make stupid comments.
The title on this page says “10 Ways to Automatically & Manually Backup MySQL Database”
Not 10 ways to scale.
And you can use some of the methods discussed here to back up very large databases.
These instructions were very good and helpful.
Currently I just use #11 but I’m going to look into #1 and #8.
Thanks for reminding me that I need to figure out a nice way to do this. :)
Currently I use zmanda mysql backup. I use the comunity version
We use version control to backup our database, saving us from having to rotate the dumps. This means we can go back in time as much as we like.
We use Git right now, but any SCM would do actually.
http://pastie.org/417454
You do need to initialize a Git repository at /path/to/dbbackups first. Then add a cron tab that calls the backup script.
easier:
15 2 * * * /usr/bin/mysqldump –opt –all-databases -u root -pPASSWORD > /foo/bar/db-`date -I`.sql
and with an my.cnf under ~ you dont’t have to write mysqlroots password in your crontab.
if you’re stuck using mySQL…. sorry
Wow, I was kind of expecting more than just mysqldump, mysqldump, and mysqldump. Has any tried replicating the Mysql DB as a backup solution?
brilliant. Thanks for a useful list :)
@john woah, what do you have against MySQL?
In many circumstances you would want to force the MYSQL dump to continue past any error with ‘-f’ so that you get the full database even if it contains oddness. Otherwise there is a chance it will crash out and give you only a fraction of your data.
My MySql databases are on a hosting provider, here’s the tricks I use.
1) Via ssh, connect to host, issue mysqldump or your dump script(s), have them dump to a non-quota’d location, like /tmp, or /dev/shm (if there’s enough ram on the remote box). My scripts use:
-a -Q -q -l –add-drop-table –add-locks –complete-insert -uusername -ppassword database_name > database_name.sql
2) Use rsync over ssh to pull down the dump(s) to the local machine. If you already have an older version of the dump(s), rsync will only transfer what it needs to update your copy. Use compression, too (-z) if you like. Remove these temporary dumps from the remote server.
3) Now that your local copy of the dump(s) are up to date, here’s the real magic. Why keep multiple copies of backups? Similar to rsync, what you want to do is only store the delta of these backups since the last copy. For this, use the simple old code revisioning utility, RCS. Check in your latest backup using the ‘ci’. Use the -m option to automatically add a comment to this revision’s check in, so that way it doesn’t prompt you for it. RCS will now diff the current backup against the previous version, and then record just the diff info, or the delta.
Works great. Just remember to retire older revisions to keep it from growing out of hand some day.
What’s the benefits of this?
Using RCS’s ‘co’ command, I can check out any revision still kept in the repository. RCS uses all the diffs to re-construct the backup that was checked in at that specific revision. So I can jump back 30 days if I need to find a table’s previously known good condition.
this is some good information. Thanks
Wow, I was kind of expecting more than just mysqldump, mysqldump, and mysqldump. Has any tried replicating the Mysql DB as a backup solution??
@Sarah
Replication is a HA solution, not a backup solution.
a “delete * from tablename” will wipe out your “backup” too.
Thanks! You’re saving my life again ;)
I am still rookie in working with databases, very useful.
Really cool, I get asked this all the time. We will definably link to your article.
The cron for linux is great too, I think I’ll throw that together tonight.
Thank you!
none of them are correct. fail.
actually i scacely use Mysql except in CMS,but CMS sets mySQL already ,
Nice one. Web developers and admin can use these to backup their online database.
Very useful tips. Automating this process is most ideal. Setting up a cron job would be very useful. Make sure you have proper permissions in performing some of these mysql commands.
SQLyog as mentioned above is a very useful tool but its not free unfortunately.
great stuff
Umm, how about hot backups?
Thank you so much! Excellent!
You can simply use MySQL Administrator to schedule daily backups of all your remote databases. It’s a MySQL product and is free and simple to use.
nicely listed… i was not knowing all of them… thanks :)
Backing up is so important but even more important is to save the backup OUTSIDE the server.
Email can be a good solution for small db but for db larger than 10MB it is better to use third party services
Nice listing though some of them are old but very helpful to bookmark for the future need :)
I used this info in combo with a script at http://www.fortasse.com/2010/01/tutorials/tutorial-automatic-backup-script/ . It seems to work. After you make the shell script, you can also easily execute it manually or automatically using php shell_exec. Works great.