10 Ways to Automatically & Manually Backup MySQL Database
- By Editorial
- Posted in How To's, PHP
- 93 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.
xmlsamurai, 15 March 2009
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
Sam, 06 November 2009
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.
Matt, 12 July 2010
Your example crontab line has the “data ‘ %m-%d-%Y’” instead of “date ‘ %m-%d-%Y’”. That’s why it doesn’t work.
James W, 28 April 2011
The formatting is a little off. I use the following, which I got from http://www.fortasse.com/tutorials/mysqldump-mysql-database-backup/ :
`date +%m%d%y`
Cherlin, 24 June 2011
Many many quailty points there.
Vivek, 15 March 2009
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
}
fan of Vivek, 30 October 2009
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
Scott Jones, 15 March 2009
I use SQLyog Enterprise. It can connect over SSH and I can schedule my backups. It is not free though :-(
Web Dev Hobo, 15 March 2009
Personnaly, I just have PhpMyAdmin do the backup for me.
Binny V A, 15 March 2009
This is what I use – Perl script to backup mysql databases
alex, 29 March 2010
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
insic, 15 March 2009
Nice list list of tips. Number 6 and #7 is handy.
Mani, 15 March 2009
Thank you noupe!! Nice list of resources to be used in different applications.
Willem, 15 March 2009
This list is incomplete imo, i’d reather see examples of using diff for db backups. This doesnt scale at all.
Matteo, 16 March 2009
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
PS, 16 March 2009
Not a single one of these options will scale past a small to moderately sized database.
Sam, 06 November 2009
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.
hans, 19 May 2010
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!
Farid Hadi, 16 March 2009
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. :)
Ronaldo, 16 March 2009
Currently I use zmanda mysql backup. I use the comunity version
Peter De Berdt, 16 March 2009
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.
Thorsten Strusch, 16 March 2009
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.
john woah, 16 March 2009
if you’re stuck using mySQL…. sorry
Shane, 16 March 2009
Wow, I was kind of expecting more than just mysqldump, mysqldump, and mysqldump. Has any tried replicating the Mysql DB as a backup solution?
Michael, 25 May 2011
Hey Shane,
The biggest issue with MySQLDump is that it locks the database. “For each dumped database, lock all tables to be dumped before dumping them,” from the documentation. The issue with replication is that it’s a mirrored 1:1 copy of your data. If you lose data integrity on your master, your slave is corrupt within seconds. A backup is a snapshot of your data, thus if you replicate snap shots you can not compress and this could become another big issue (and that you’ve now got to find a way to replicate to a destination with multiple copies over time).
Overall, replication is a balancing technique (to me). Have you tried 1:many replications as a backup solution?
ShavenLunatic, 16 March 2009
brilliant. Thanks for a useful list :)
@john woah, what do you have against MySQL?
Rob, 16 March 2009
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.
Brandon Darbro, 16 March 2009
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.
Timothy, 16 March 2009
this is some good information. Thanks
Sarah, 16 March 2009
Wow, I was kind of expecting more than just mysqldump, mysqldump, and mysqldump. Has any tried replicating the Mysql DB as a backup solution??
Kingsly, 17 March 2009
@Sarah
Replication is a HA solution, not a backup solution.
a “delete * from tablename” will wipe out your “backup” too.
Sklep Zoologiczny W?dkarski, 17 March 2009
Thanks! You’re saving my life again ;)
Dainis Graveris, 18 March 2009
I am still rookie in working with databases, very useful.
ez, 19 March 2009
Really cool, I get asked this all the time. We will definably link to your article.
ez, 19 March 2009
The cron for linux is great too, I think I’ll throw that together tonight.
Mithun Sreedharan, 19 March 2009
Thank you!
fail, 21 March 2009
none of them are correct. fail.
kissfang, 21 March 2009
actually i scacely use Mysql except in CMS,but CMS sets mySQL already ,
Rahul, 22 March 2009
Nice one. Web developers and admin can use these to backup their online database.
Geo, 23 March 2009
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.
strony internetowe, 24 March 2009
great stuff
Jeff, 29 March 2009
Umm, how about hot backups?
Roy Leonhardt, 16 April 2009
Thank you so much! Excellent!
Timothy, 27 October 2009
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.
Abhishek Dilliwal, 05 November 2009
nicely listed… i was not knowing all of them… thanks :)
Assaf, 11 November 2009
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
Shahriat Hossain, 25 December 2009
Nice listing though some of them are old but very helpful to bookmark for the future need :)
Martha, 22 January 2010
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.
Daniel Errante, 19 February 2010
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…
Eric, 02 March 2010
For god sake people stop using FTP when SFTP is so darned easy to use and automate!
Antone Dever, 21 April 2010
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!
Nelson Knisely, 21 April 2010
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!
Thewriter, 24 April 2010
Fantastic your ability as a copywriter are amazing
Ahmed Pittsley, 27 April 2010
I never knew I would be equipped to jump high so fast. Can anyone gift me anymore suggesstions?
Colin Jensen, 09 May 2010
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!
Bind, 19 May 2010
http://www.mysqldumper.net/
Naster, 13 August 2010
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 ;)
Growl, 19 August 2010
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?
Drew Q, 03 October 2010
I have been using mysqldump for years now with rather a huge mysql installation. The best improvement I made to avoid downtime during backups due to table locking was to set up a replication server and perform the dump on that. Setting up replication is a whole different animal, but the mysql documentation is good.
linaly, 20 August 2010
I agree with that.
John C, 01 September 2010
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).
????, 22 September 2010
MajorMUD Control Panel is a PHP website designed.
BEHESHT, 28 September 2010
Thanks Bro Nice tut…
rai, 11 October 2010
here is a shell script that i’ve created a while ago. It creates a backup daily(needs cron) where you can specify the file’s lifespan. It also includes a monthly archiving every first of the month.
http://bostsip.blogspot.com/2010/10/automated-mysql-backup.html
kranthikiran, 15 October 2010
How to Take incremental backup using script,please help me
Poster, 22 October 2010
Well I just discovered this site on Google for the first time today. I really think your site is good with excellent articles. Many thanks for the good read. Will add to my Bookmarks.
tommy, 29 October 2010
I always use mysqldump, and send backup to my home server and another backup server. simple and fast.
Akin, 03 November 2010
Very good list of ways to achieve a mysql backup. I personally use mysqldump, will find time to try other. Thanks.
marriage records, 16 November 2010
Great ideas! I used to be having some porblems about what you already stated, Now I shall be higher!
Logan Waston, 28 November 2010
Who knows what happens next… All I know is:
Left to themselves, things tend to go from bad to worse. :)
sinx, 09 January 2011
Hello, I have read your article with interest and most tools described there is using mysqldump+gzip. Also many of this tools doesn’t check return code of mysqldump, which may be very dangerous (as a sysadmin you think you have perfect backup, but it is not true – your backup is corrupted), recently I have written small bash script which do necessary checks, here is the source: MySQL Quick Backup with bash script.
btw: Thanks for info about Auto MySQL Backup tool!
Get Wife Back, 18 January 2011
I watched a system about that on tv at the weekend. With thanks for your extra in-depth explanation
cybermysql, 18 February 2011
There is a new alternative java web application to backup mysql.Here is the link
http://code.google.com/p/mysql-backup-java-webapp/
Krissy Decoteau, 01 March 2011
A relative recommended that I check out your site. I’m happy that I’m here.
OCHotline, 09 March 2011
Great tip, thanks! I was looking for an easy way to do some backups.
John Thompson, 28 March 2011
Some good stuff here, but the author, in #6, doesn’t seem to understand the difference between a database and a table. The title says “Backup a Database” but the technique backs up a table!?
pandu, 28 April 2011
Thanks dude ….very helpful
swathi, 29 April 2011
Hi,
I am trying to create mysqldump from java program
i write code like this
Runtime.getRuntime().exec(“cmd /c C:/wamp/mysql/bin/mysqldump -u root -p march >C:/backup1.sql”);
file is creating but with zero bytes..
can u give solution for this………
Vince, 01 May 2011
I use this one as it makes backup and restore of all my databases very simple:
http://www.mysqlbackup.info/
songeur, 09 May 2011
Dont forget rsync and rsync over ssh ….
It’s not the best way but fast and easy !
tea lights, 09 May 2011
great
kate, 18 May 2011
Morning. This is enchanting article, guy. Danke. However, Im experiencing weird situation with ur Really Simple Syndication. I have no idea I cannot subscribe to it. Is there anybody having similar rsserror? Cheers
Sandie Kingsley, 19 May 2011
Hello! I just wanted to ask if you ever have any issues with hackers? My last blog (wordpress) was hacked and I ended up losing months of hard work due to no data backup. Do you have any solutions to prevent hackers?
Gabby, 11 June 2011
Why the publish code that now explain what in it
1.include ‘opendb.php’; (where is this file??????????)
Juan C. HORNA, 11 August 2011
Good Job… your explication is great. It works fine….
greetings.
PD: The point 6 require more detail
rets developer, 19 September 2011
Brilliant Post. I was actually looking for any desktop application that would save FTP details and schedule to run daily, make backup and store at my computer. Any idea? I once found one, but lost !! :(
marthes, 05 October 2011
Good job.
I found another 100% PHP solution : http://blog.evolya.fr/index.php?post/05/10/2011/mysqldump-en-php-%3A-export-database
Paul G., 10 October 2011
Hey,
You can automate the backup of your MySQL databases if you’re running cPanel. It finds all your databases and downloads them. The script doesn’t FTP yet, but we’ll release it shortly so that it does.
You can grab it here: http://www.hostliketoast.com/2011/10/cpanel-hosting-full-database-backup-script-free-download/
Cheers!
Paul.
Alejandro Arauz, 10 October 2011
The article has some good information but it would be better if you mention some tools too, running scripts and creating scheduled tasks manually can be complicated when managing several databases.
I would recommend MySqlBackupFTP (http://mysqlbackupftp.com/) . It is very simple to use and it has a free version that allows you to create scheduled backups.
This tool also has the option to export the backup to an FTP server so you don’t need to run a separate script.
Kimberly, 31 October 2011
Nice tips!
I will try some of them to backup my MySQL.
Polinux, 04 November 2011
1 one should be like that
15 2 * * * root mysqldump -u root -pPASSWORD –all-databases | gzip > /mnt/disk2/database_`date +%m-%d-%Y`.sql.gz
Benson, 18 January 2012
most way to backup need root privilege.
Way 6, using php to write a file, don’t work.
Zahid.Pakistan, 03 February 2012
I needed a little variant for mysql data dump with single line inserts and no comments and no db create statement. Finally, the following command worked well:
root mysqldump –skip-extended-insert –skip-comments –no-create-info –no-create-db -u root -pgeoware123 iom3_00_04 > /mnt/pcpi/iom3_00_04_data_`date +%m%d%y`.sql
for mysql 5.1 and higher. Hope it helps