Development

10 Ways to Automatically & Manually Backup MySQL Database

March 15th, 2009

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

MySQL Backup Solution

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?

MySQL Backup Solution

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.

The jungle is alive: Be it a collaboration between two or more authors or an article by an author not contributing regularly. In these cases you find the Noupe Editorial Team as the ones who made it. Guest authors get their own little bio boxes below the article, so watch out for these.

Tags:

116 comments for „10 Ways to Automatically & Manually Backup MySQL Database
  1. xmlsamurai on March 15th, 2009 at 5:34 pm

    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 on November 6th, 2009 at 2:40 pm

      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 on July 12th, 2010 at 6:37 pm

        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 on April 28th, 2011 at 10:16 pm

        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 on June 24th, 2011 at 7:49 pm

      Many many quailty points there.

  2. Vivek on March 15th, 2009 at 6:57 pm

    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 on October 30th, 2009 at 10:27 am

      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

  3. Scott Jones on March 15th, 2009 at 9:21 pm

    I use SQLyog Enterprise. It can connect over SSH and I can schedule my backups. It is not free though :-(

  4. Binny V A on March 15th, 2009 at 10:18 pm

    This is what I use – Perl script to backup mysql databases

    • alex on March 29th, 2010 at 11:34 pm

      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

  5. insic on March 15th, 2009 at 11:05 pm

    Nice list list of tips. Number 6 and #7 is handy.

  6. Mani on March 15th, 2009 at 11:42 pm

    Thank you noupe!! Nice list of resources to be used in different applications.

  7. Willem on March 15th, 2009 at 11:43 pm

    This list is incomplete imo, i’d reather see examples of using diff for db backups. This doesnt scale at all.

  8. Matteo on March 16th, 2009 at 12:05 am

    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

  9. PS on March 16th, 2009 at 12:31 am

    Not a single one of these options will scale past a small to moderately sized database.

    • Sam on November 6th, 2009 at 3:05 pm

      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 on May 19th, 2010 at 8:38 am

        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!

  10. Farid Hadi on March 16th, 2009 at 12:34 am

    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. :)

  11. Ronaldo on March 16th, 2009 at 1:27 am

    Currently I use zmanda mysql backup. I use the comunity version

  12. Peter De Berdt on March 16th, 2009 at 1:52 am

    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.

    • Mike on March 18th, 2012 at 9:06 pm

      @Peter

      I love your ‘git’ method. I never would have thought of putting mysqldump and git together.

      I’m not sure I’ll implement it right now though because I’m spending so much time doing things in a quality way that I am wasting to much time on my business. i.e. I am using a backup system (CrashPlan) while using automysqlbackup. I was using git but the learning curve is a bit steep… however it’s not an option NOT to use a version control system!

      I’ve also got automated tests (code checker tests, doxygen documentation tests, unit tests, and functional tests via browser). A lot to take on. I know someone who didn’t bother with any quality control and made a million creating Google Adsense websites! So I’m not sure if quality equals wealth… but hey… maybe I’m a dumbass!

      Still let’s see what happens ;-)

      • Vinícius on November 19th, 2012 at 7:41 pm

        I’m pretty curious on what is your conclusion about quality/version control after a while that you wrote this.

  13. Thorsten Strusch on March 16th, 2009 at 5:54 am

    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.

  14. john woah on March 16th, 2009 at 6:12 am

    if you’re stuck using mySQL…. sorry

  15. Shane on March 16th, 2009 at 7:05 am

    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 on May 25th, 2011 at 7:26 pm

      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?

  16. ShavenLunatic on March 16th, 2009 at 7:23 am

    brilliant. Thanks for a useful list :)

    @john woah, what do you have against MySQL?

  17. Rob on March 16th, 2009 at 8:28 am

    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.

  18. Brandon Darbro on March 16th, 2009 at 11:04 am

    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.

  19. Timothy on March 16th, 2009 at 12:47 pm

    this is some good information. Thanks

  20. Sarah on March 16th, 2009 at 1:33 pm

    Wow, I was kind of expecting more than just mysqldump, mysqldump, and mysqldump. Has any tried replicating the Mysql DB as a backup solution??

  21. Kingsly on March 17th, 2009 at 12:39 am

    @Sarah

    Replication is a HA solution, not a backup solution.

    a “delete * from tablename” will wipe out your “backup” too.

  22. Sklep Zoologiczny W?dkarski on March 17th, 2009 at 2:56 pm

    Thanks! You’re saving my life again ;)

  23. Dainis Graveris on March 18th, 2009 at 1:08 pm

    I am still rookie in working with databases, very useful.

  24. ez on March 19th, 2009 at 3:36 am

    Really cool, I get asked this all the time. We will definably link to your article.

  25. ez on March 19th, 2009 at 3:37 am

    The cron for linux is great too, I think I’ll throw that together tonight.

  26. Mithun Sreedharan on March 19th, 2009 at 5:52 am

    Thank you!

  27. fail on March 21st, 2009 at 6:34 pm

    none of them are correct. fail.

  28. kissfang on March 21st, 2009 at 11:55 pm

    actually i scacely use Mysql except in CMS,but CMS sets mySQL already ,

  29. Rahul on March 22nd, 2009 at 7:07 am

    Nice one. Web developers and admin can use these to backup their online database.

  30. Geo on March 23rd, 2009 at 7:48 am

    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.

  31. strony internetowe on March 24th, 2009 at 2:30 am

    great stuff

  32. Jeff on March 29th, 2009 at 8:19 am

    Umm, how about hot backups?

  33. Roy Leonhardt on April 16th, 2009 at 8:49 pm

    Thank you so much! Excellent!

  34. Timothy on October 27th, 2009 at 5:28 pm

    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.

  35. Abhishek Dilliwal on November 5th, 2009 at 7:38 am

    nicely listed… i was not knowing all of them… thanks :)

  36. Assaf on November 11th, 2009 at 7:30 am

    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

  37. Shahriat Hossain on December 25th, 2009 at 10:19 pm

    Nice listing though some of them are old but very helpful to bookmark for the future need :)

  38. Martha on January 22nd, 2010 at 5:51 pm

    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.

  39. Daniel Errante on February 19th, 2010 at 11:09 am

    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…

  40. Eric on March 2nd, 2010 at 7:49 am

    For god sake people stop using FTP when SFTP is so darned easy to use and automate!

  41. Antone Dever on April 21st, 2010 at 7:55 am

    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!

  42. Nelson Knisely on April 21st, 2010 at 8:03 am

    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!

  43. Thewriter on April 24th, 2010 at 11:39 am

    Fantastic your ability as a copywriter are amazing

  44. Ahmed Pittsley on April 27th, 2010 at 12:27 pm

    I never knew I would be equipped to jump high so fast. Can anyone gift me anymore suggesstions?

  45. Colin Jensen on May 9th, 2010 at 4:43 pm

    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!

  46. Bind on May 19th, 2010 at 9:58 pm
  47. Naster on August 13th, 2010 at 9:28 am

    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 ;)

  48. Growl on August 19th, 2010 at 5:21 am

    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 on October 3rd, 2010 at 8:56 am

      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.

  49. linaly on August 20th, 2010 at 12:32 am

    I agree with that.

  50. John C on September 1st, 2010 at 2:15 am

    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).

  51. ???? on September 22nd, 2010 at 7:21 am

    MajorMUD Control Panel is a PHP website designed.

  52. BEHESHT on September 28th, 2010 at 11:40 am

    Thanks Bro Nice tut…

  53. rai on October 11th, 2010 at 6:27 am

    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

  54. kranthikiran on October 15th, 2010 at 1:28 pm

    How to Take incremental backup using script,please help me

  55. Poster on October 22nd, 2010 at 3:29 pm

    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.

  56. tommy on October 29th, 2010 at 2:42 pm

    I always use mysqldump, and send backup to my home server and another backup server. simple and fast.

  57. Akin on November 3rd, 2010 at 8:52 am

    Very good list of ways to achieve a mysql backup. I personally use mysqldump, will find time to try other. Thanks.

  58. marriage records on November 16th, 2010 at 4:31 pm

    Great ideas! I used to be having some porblems about what you already stated, Now I shall be higher!

  59. Logan Waston on November 28th, 2010 at 2:58 am

    Who knows what happens next… All I know is:

    Left to themselves, things tend to go from bad to worse. :)

  60. sinx on January 9th, 2011 at 1:41 pm

    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!

  61. Get Wife Back on January 18th, 2011 at 10:37 am

    I watched a system about that on tv at the weekend. With thanks for your extra in-depth explanation

  62. cybermysql on February 18th, 2011 at 3:15 pm

    There is a new alternative java web application to backup mysql.Here is the link

    http://code.google.com/p/mysql-backup-java-webapp/

  63. Krissy Decoteau on March 1st, 2011 at 3:17 pm

    A relative recommended that I check out your site. I’m happy that I’m here.

  64. OCHotline on March 9th, 2011 at 12:19 am

    Great tip, thanks! I was looking for an easy way to do some backups.

  65. John Thompson on March 28th, 2011 at 9:55 am

    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!?

  66. pandu on April 28th, 2011 at 2:56 pm

    Thanks dude ….very helpful

  67. swathi on April 29th, 2011 at 12:36 pm

    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………

  68. Vince on May 1st, 2011 at 10:08 am

    I use this one as it makes backup and restore of all my databases very simple:
    http://www.mysqlbackup.info/

  69. songeur on May 9th, 2011 at 2:51 pm

    Dont forget rsync and rsync over ssh ….
    It’s not the best way but fast and easy !

  70. tea lights on May 9th, 2011 at 8:00 pm

    great

  71. kate on May 18th, 2011 at 1:22 pm

    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

  72. Sandie Kingsley on May 19th, 2011 at 11:55 am

    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?

  73. Gabby on June 11th, 2011 at 7:16 am

    Why the publish code that now explain what in it
    1.include ‘opendb.php'; (where is this file??????????)

  74. Juan C. HORNA on August 11th, 2011 at 5:08 pm

    Good Job… your explication is great. It works fine….
    greetings.
    PD: The point 6 require more detail

  75. rets developer on September 19th, 2011 at 8:34 am

    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 !! :(

  76. marthes on October 5th, 2011 at 2:51 pm
  77. Paul G. on October 10th, 2011 at 1:59 pm

    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.

  78. Alejandro Arauz on October 10th, 2011 at 6:10 pm

    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.

  79. Kimberly on October 31st, 2011 at 4:20 pm

    Nice tips!
    I will try some of them to backup my MySQL.

  80. Polinux on November 4th, 2011 at 10:43 am

    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

  81. Benson on January 18th, 2012 at 8:59 am

    most way to backup need root privilege.
    Way 6, using php to write a file, don’t work.

  82. Zahid.Pakistan on February 3rd, 2012 at 3:21 pm

    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

  83. smithy on February 11th, 2012 at 11:35 pm

    does anyone know how to backup changes to the database say once per hour or even better whenever a certain table is updated ie an order?

  84. Rehan Anis on February 23rd, 2012 at 8:15 pm

    here, I have developed this script, which can create backup of virtually any size of database.
    Just have a look at it.
    dbcare.webcare.pk
    And the details are here
    http://www.blog.webcare.pk/2012/02/make-backup-of-large-mysql-databases.html

  85. XI on April 4th, 2012 at 4:45 pm

    There’s another solution that is useful if you have, like me, multiple dbs to backup.
    Here: http://www.mysqlsaver.com/

  86. Joe Duncan on April 8th, 2012 at 8:09 pm

    I use Automatic MySQL Database Backup Plugin. It works with Unix and windows servers and doesn’t use Cron Tabs either. The best part is that it doesn’t backup if nothing has changed which really cuts down on server load since we run a few high traffic sites.

  87. ayan on May 15th, 2012 at 8:32 am

    thanks man its really helping

  88. urdesh kumar on June 27th, 2012 at 12:39 pm

    Nice collection for db backup.thanks

  89. harsha on June 28th, 2012 at 8:46 am

    i hav a .csv file when I use the the load data infile it show me that u did syntax error this s not the correct syntax can anyone give me a clear syntax

  90. Omarxp Studio on July 2nd, 2012 at 1:16 pm

    thanks, very helpful, this is what I was looking for.

  91. Sanjay on July 10th, 2012 at 8:21 am

    I know best option use phpmyadmin to import and export .

  92. Jason TEPOORTEN on July 12th, 2012 at 4:53 am

    Hi,
    Thanks VERY much for the helpful link.
    I used #2. I changed the date format in the filename to reflect YYYYMMDD_HHMMSS as I find it easier to file sort in a directory simply using the filename.

    Here’s an example of the command I use in the root crontab:
    mysqldump -u root -pmissMantisBT –all-databases | gzip -9 > /zdbbkp_ecqmantisbt/database_`date ‘+%Y%m%d_%H%M%S’`.sql.gz

    I’ll post a FIND command (“please use with care”) that only retains the last seven days of recent files.
    Regards,
    Jason

  93. Jason TEPOORTEN on July 12th, 2012 at 5:05 am

    Hi again,
    Sorry for the second post…

    Here’s my BASH script that I use to backup all of my mySQL databases on a local Linux (Turnkey LAMP 11.3) host:

    #!/bin/bash
    # Purpose: Backup all mySQL databases
    # Author: Jason TEPOORTEN (JTepoorten@smsmt.com)
    mysqldump -u root -pmissMantisBT –all-databases | gzip -9 > /zdbbkp_ecqmantisbt/database_`date ‘+%Y%m%d_%H%M%S’`.sql.gz || exit 1;
    find /zdbbkp_ecqmantisbt/ -name ‘*.sql.gz’ -mtime +7 -exec rm -f {} \; || exit 1;
    #END

    Regards,
    Jason

  94. Sakahayang on July 22nd, 2012 at 4:49 pm

    thank you for info,
    I like to use a phpMybackup Pro,,,

    happy blogging

  95. Rekha_g on August 23rd, 2012 at 8:57 am

    thank u but i am not understand u r format, so i request u send the simple format in”how to get the mysql backup”

  96. Adam Gorge on August 30th, 2012 at 12:45 pm

    You can also use Auto Backup for MySQL Professional Edition software to schedule automatic backup which is totally free utility. The software can be used to backup MySQL database from the remote server runs under Any Operating Systems, including Unix, Linux, Mac OS, Windows and all.

  97. Paolo on September 3rd, 2012 at 5:01 pm

    Thank you for the article and for all the post that I can read.
    Grazie davvero!

  98. Eric on September 11th, 2012 at 12:15 am

    Do any of these solutions work if your data base is hosted externally? e.g. Go Daddy

  99. Liya Gerber on December 31st, 2012 at 2:48 pm

    Noupe, thanks for showing so many MySQL backup solutions! Since this post is over three years old, I’d like to make some suggestions for your next article.

    Sigh regard to the first option you mentioned, Amazon’s EC2 already has automatic backup. To this, I’d like to add the options offered by the increasingly popular Database-as-a-Solution (DBaaS) solutions, like Amazon’s RDS (http://aws.amazon.com/rds) , and the lesser-known Xeround Cloud Database (http://xeround.com), among others.

    With Amazon’s RDS, aside from automated backups, you also have what they call “Database (DB) Snapshots”. According to their Website: “DB Snapshots are user-initiated and enable you to back up your DB Instance in a known state as frequently as you wish, and then restore to that specific state at any time.”

    Xeround adds an alternative they call “Hot” Backups. You can do these “Hot” Backups online, without suffering downtime or interruptions. They let you choose between backups that either automatic or user initiated, on-demand. Does anyone here have experience with these “Hot” Backups?

  100. Faisal on January 20th, 2013 at 6:18 pm

    I have created a backup script using Java. and executes it remotely. it successfully creates a .sql file in specified folder.
    I wants a post that helps me to taking physical backup automatically. at another location.
    Thanks every one for all theses posts. Thanks SAM.

  101. luoly on April 10th, 2013 at 10:58 am

    Why it not mention the software “Automatic Backup Scheduler for MySQL”

    I think it a good software to back up and restore MySQL databases automatically.

    The website is http://www.databasethink.com

    Hope it helps you.

  102. LyZzard on May 14th, 2013 at 4:50 pm

    I have used automysqlbackup and Dropbox. It is very simple to install and configure: http://webdevnote.com/article/create-a-backup-for-your-ubuntu-server-with-automysql-backup-and-dropbox-6.html

  103. Alex Green on May 24th, 2013 at 6:16 pm

    We would recommend our backup tool MySql Backup And Ftp (MySqlBF, http://mysqlbackupftp.com/ ). MySqlBF allows to schedule backups using a Windows Task Scheduler or a custom windows service for a complex backup scenarios.

    It supports connecting securely over an SSH connection and has such a unique feature as creating a backup via phpMyAdmin. The tool allows to archive, encrypt and save backups to HDD, FTP, Network or most popular cloud storage services (Dropbox, Amazon S3, Google Drive, SkyDrive, Box) and sends email notifications on success or failure.

  104. Sergei K on September 12th, 2013 at 2:49 pm

    Number eleven and surely best for me – try dbForge Studio. Its MySQL Dump tool is very fast and works with great amount of data! Read more here: http://www.devart.com/dbforge/mysql/studio/mysql-backup.html