Back up all of your mysql databases nightly

June 10, 2013 — 5 Comments

Have you ever wanted to make your own MySQL database backups?  You can use this script I wrote to make a backup of all databases, then save each one in a dated backup directory.  It will keep doing this and keep 10 days worth of backups.  I’d suggest you use rsync or something to automatically back them up off the server each night/morning also.

Put the following into something like /usr/local/bin/mysql_backup.sh and since it has MySQL’s root password in it, make sure that you chmod 700 to it so no one else can read it.

By the way, we’re skipping tables ‘performance_schema’ and ‘information_schema’…

Then just call it from cron by creating a root cron entry:

BTW, a simpler way to grab all of them is to use the –all-databases flag in the mysqldump command.. but it doesn’t make nice separate files for you.  It will make one large file.

Let me know if this helped you out!

Related posts from Linux Brigade!

  • Warning: Skipping the data of table mysql.event.Warning: Skipping the data of table mysql.event. Your MySQL script was running great, but then you started getting errors – If you get this warning when dumping the mysql database, it’s not a bug – MySQL did this on purpose because they […]
  • Import MySQL database from command lineImport MySQL database from command line You’ll need to import a database via command line from time to time – I’ll normally opt for this anyway. After seeing how easy it is, you’ll probably end up using the command line instead […]
  • Check, repair, optimize all databases with one commandCheck, repair, optimize all databases with one command My server had some tables marked as ‘crashed’ in one of the databases.. the server had recently gone down pretty harshly so I wanted to go through and check, repair and optimize all of the […]
  • Move MySQL tmp directoryMove MySQL tmp directory You’ll eventually run into a client who has a crashed table in their database – this table will be pretty large – maybe even larger than the system’s default temporary directory (usually […]
  • Migrate WHM/cPanel from one server to a new one (script)!Migrate WHM/cPanel from one server to a new one (script)! We’ve started a site to house a script that will migrate your full WHM/cPanel server to new hosting. This takes the new WHM/cPanel backup files and will restore them including system […]

5 responses to Back up all of your mysql databases nightly

  1. Easy peasy! This worked exactly as described. It’s really generous of you to share your code like this. I just wanted you to know that it is much appreciated. Have a goodun!

  2. Thank you for sharing your code. It is always important backup your databases at least every night so this code and guide will be very useful. I am really liking this blog so far, it has been really useful to me.

  3. Hi,
    thank you for your script. I’ve got an error on execution.
    Could you please look over…

    /usr/local/bin/mysql_backup.sh: Zeile 16: gt: Kommando nicht gefunden(command not found).
    gzip: compressed data not written to a terminal. Use -f to force compression.
    For help, type: gzip -h
    mysqldump: Got errno 32 on write

    Thank you

Trackbacks and Pingbacks:

  1. mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user ‘root’@’localhost’ for table ‘cond_instances’ when using LOCK TABLES | Linux Brigademysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user - July 24, 2014

    […] probably means you’re trying to backup all of your databases at once.. you don’t need to back up the performance_schema database (and information_schema) anyway […]

  2. Warning: Skipping the data of table mysql.event. | Linux BrigadeWarning: Skipping the data of table mysql.event. - Linux Brigade - July 24, 2014

    […] Also – if you’re interested, we have created a nice script to dump all of your MySQL databases nightly! […]

Leave a Reply