Backup all your MySQL databases with one line of cron photo

Backup your MySQL databases with a one-liner crontab

by · Lecture: 2 minutes

As I’ve lost some data in my MySQL database recently, I’ve decided to make backups more regularly and I’ve been playing with Cron in Cpanel in order to set up automatic backups of all my databases.

Here’s a short how-to which might help some people out :

Go to Cpanel > Cron Jobs

Select between Standard or Advanced, that’s up to you !

In the command field, type this :

Kinsta: Premium Managed WordPress hosting 
date=`date -I`; mysqldump -u yourusername -p yourpassword --all-databases > /home/LOGIN/backups/xbackup_$date.sql; gzip /home/LOGIN/backups/xbackup_$date.sqlCode language: JavaScript (javascript)

Now let’s see what this all means : the date line formats the date so that we can append it to our backup filename.

We then ask mysql to dump all databases into /home/LOGIN/public_html/backups/xbackup_$date.sql, where :

  • LOGIN is your Cpanel name, using “yourusername” as user (-u) and “yourpassword” as the password (-p).

Notice the date variable inserted in the filename. Finally our SQL file is gzipped using the gzip directive.

You now have to set your backup frequency. And relax !

Notes :

1. I’ve created a user “backup” with all privileges in Cpanel > MySQL databases and associated him with all my databases so that I can backup everything in one single file with just one line of cron.

2. Protect your backup directory so that people cannot download the whole of you SQL data. You can enable directory protection with Cpanel > Password protect directories > *select your backup directory* > *check the box and enter a username/password* > *validate*.

That’s it ! Have fun, you should never have to worry about your databases’ backups again ;-)

Vous avez un projet WordPress ou WooCommerce en tête? Transformez votre vision en réalité avec mon expertise reconnue.

Parlons de votre projet dès aujourd'hui »

Shares2PinLinkedIn
Matt

Matt Biscay est développeur WordPress et WooCommerce certifié chez Codeable, ainsi que sysadmin qualifié et enseignant-chercheur. Passionné par le code performant et les solutions sécurisées, je m'efforce d'offrir une expérience utilisateur exceptionnelle sur chaque projet.

Vous avez aimé cet article ? Vous avez un projet en tête, n'hésitez pas à me contacter, je serai ravi d'en discuter avec vous !

👔 Portfolio 📨 Me Contacter

19 pensées sur “Backup your MySQL databases with a one-liner crontab”

  1. It’s working better. Except I get “date: invalid option — I
    BusyBox v0.60.5 (2003.01.24-22:44+0000) multi-call binary

    Usage: date [OPTION]… [+FORMAT]” Let me check the manual for MySQL. I might figure it out.

    Reply

  2. I don’t think that -I is an option on my Redhat linux box.

    I’ve got the file created now. The file isn’t named by date though.

    Thanks for your help.

    Reply

  5. Hi Mike,

    Mike,

    According to the Unix date manual, this should be something like : date %D %M %S (date formatted mm/dd/yy with minutes and seconds).

    Not tested but should be along these lines :-)

    -Matt

    Reply

  6. Hi there,
    Been trying to add backup TIME to:
    date=`date -I`; mysqldump -uyourusername -pyourpassword –all-databases >
    /home/LOGIN/public_html/backups/xbackup_$date.sql; gzip /home/LOGIN/public_html/backups/xbackup_$date.sql
    for multiple backups in one day for eg: WordPress.
    Any ideas please?
    Thank you.

    Reply

  7. Matt, you friggin genius! This is exactly what i needed. Thanks a lot =) Here is the mysqldump syntax reference:
    http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

    This is how my working version of my cron script looks:
    date=`date -I`; mysqldump -u my_username -pmyPassword my_databaseName >
    /home/myAccountUserName/public_html/dbbackup_$date.sql;
    gzip /home/myAccountUserName/public_html/dbbackup_$date.sql

    Note the space between -u and the username.
    My webhost uses cpanel so my_username and my_databaseName can easily be found in the MySQL Databases section of the cpanel home.

    Reply

  9. This works on my cpanel (note the space after “-u” and you have to have two “––” before the “all-databases”) also you should re-type everything yourself – DO NOT COPY AND PASTE!!!

    date=`date -I` ; mysqldump -u USERNAME -pPASSWORD ––all-databases | gzip > /home/LOGIN/public_html/backups/xbackup_$date.sql.gz

    Reply

  12. Hello Mieke,

    You can check your backup on a test install via PHPMyadmin : simply upload your backup and see how it goes. Or you can open the SQL file and check its data.
    I’ve been using this backup strategy for years now (one daily backup of this site every night). Works fine :)

    Reply

  13. you realize, your password is available to anyone able to do a ps -aux on the server right? really bad for people with shared hosting

    Reply

  17. Hi – I keep getting error 1045, access denied :(

    date=`date -I`; mysqldump -u username -p password ——all-databases > /home/path/public_html/db/dbbackup_$date.sql; gzip /home/path/public_html/db/dbbackup_$date.sql

    I have set up a user and granted all permissions… any ideas

    Reply

  18. Hello Tye,

    Make sure you have granted access to your user and your host. Run this command line with mysql:

    mysql> GRANT ALL ON *.* TO 'your_mysql_name'@'your_client_host';
mysql>flush privileges;

    And restart your server. It also depends on whether you’re hosted or using a sandbox.

    Reply

  19. Worked like a charm!

    I didn’t see that the username is -u yourusername,
    and password is -pyourpassword… (note the space between -u and the username and that there is not space between the -p and your password)

    Thanks!

    Reply

Opinions