Noupe Editorial Team March 15th, 2009

10 Ways to Automatically & Manually Backup MySQL Database

Kein Beitragsbild

Noupe Editorial Team

The jungle is alive: Be it a collaboration between two or more...

 Sponsors love

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:

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 :

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.

Noupe Editorial Team

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.


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

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

      1. Your example crontab line has the “data ‘ %m-%d-%Y'” instead of “date ‘ %m-%d-%Y'”. That’s why it doesn’t work.

  2. 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 {
    rotate 14
    create 640 root adm
    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

    1. 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!


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

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

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

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

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

      1. 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!

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

    You do need to initialize a Git repository at /path/to/dbbackups first. Then add a cron tab that calls the backup script.

    1. @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 ;-)

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

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

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

    1. 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?

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

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

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

  12. @Sarah

    Replication is a HA solution, not a backup solution.

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

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

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

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

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

  17. 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!

  18. 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!

  19. 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?

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

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

  21. 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!

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

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

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

  25. 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?

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

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

  28. 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 ( . 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.

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

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

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

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

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

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

    # Purpose: Backup all mySQL databases
    # Author: Jason TEPOORTEN (
    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;


  35. 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”

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

  37. 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 ( , and the lesser-known Xeround Cloud Database (, 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?

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

  39. We would recommend our backup tool MySql Backup And Ftp (MySqlBF, ). 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.

Sorry, Comments are closed...