10 Ways to Automatically & Manually Backup MySQL Database
- Posted in How To's, PHP
- 59 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.
Your example crontab line has the “data ‘ %m-%d-%Y’” instead of “date ‘ %m-%d-%Y’”. That’s why it doesn’t 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
dear sir,
i am using 2003server as domain and oracle database installed in domain if i do bdc how i can backup every secound from pdc to bdc
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.
I totally agree with you Sam. Sometimes people choose to complain more than to appreciate what has been contributed because they don’t ‘understand’ the article’s title. Anyway, keep up your good work!
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.
I agree with PS, Shane and Sarah…All of these solutions use mysqldump, which can bring your server to a halt while the script is running. For LARGE databases, this could be mean your website going down for a few minutes every day. These solutions are great for smaller databases, but not scaleable if your database has millions of records.
Also, Kingsly. Replication may be an HA solution, but it can also be used to backup the database without interrupting service to your website visitors…
For god sake people stop using FTP when SFTP is so darned easy to use and automate!
Intimately, the post is really the greatest on this notable topic. I agree with your conclusions and will thirstily look forward to your future updates. Just saying thanks will not just be enough, for the extraordinary clarity in your writing. I will right away grab your rss feed to stay informed of any updates. Admirable work and much success in your business efforts!
Comfortabl y, the article is in reality the greatest on this noteworthy topic. I concur with your conclusions and will eagerly look forward to your upcoming updates. Just saying thanks will not just be adequate, for the exceptional lucidity in your writing. I will instantly grab your rss feed to stay privy of any updates. Fabulous work and much success in your business efforts!
Fantastic your ability as a copywriter are amazing
I never knew I would be equipped to jump high so fast. Can anyone gift me anymore suggesstions?
What a great article! I actually created a script to email me a backup dump of a chosen MySQL database. See it here: http://thephpanswers.com/viewtopic.php?f=13&t=8
I really like this article as you have covered all different options! wish I found it earlier!
http://www.mysqldumper.net/
I loved your blog post.Really thank you!
i very adore your own posting choice, very remarkable.don’t quit and keep penning mainly because it simply just that is worth to read it,looking forward to looked over way more of your current well written articles, have a pleasant day!
Here is a GUI to auto backup mysql to local path or remote ftp server.
NBackupMySQL :
http://naster.net/index.php?page=scripts&id=7
Plz try and comment ;)
What is the best way to backup MYSQL so that the tables dont get locked, aka other than mysqldump on a ubuntu server runing mysql?
I agree with that.
Webmin has a scheduled back up service available which is easy to use / set up. It does not allow for rotation of files (that I can see).