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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
#!/bin/bash DB_BACKUP="/backups/mysql_backup/`date +%Y-%m-%d`" DB_USER="root" DB_PASSWD="secretttt" HN=`hostname | awk -F. '{print $1}'` # Create the backup directory mkdir -p $DB_BACKUP # Remove backups older than 10 days find /backups/mysql_backup/ -maxdepth 1 -type d -mtime +10 -exec rm -rf {} \; # Backup each database on the system for db in $(mysql --user=$DB_USER --password=$DB_PASSWD -e 'show databases' -s --skip-column-names|grep -viE '(staging|performance_schema|information_schema)'); do mysqldump --user=$DB_USER --password=$DB_PASSWD --events --opt --single-transaction $db | gzip > "$DB_BACKUP/mysqldump-$HN-$db-$(date +%Y-%m-%d).gz"; done |
By the way, we’re skipping tables ‘performance_schema’ and ‘information_schema’…
Then just call it from cron by creating a root cron entry:
|
1 |
30 3 * * * /usr/local/bin/mysql_backup.sh |
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!
Move 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 […]- Import 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 […]
- 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 […]
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 […]- Check, 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 […]
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!
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.
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
Your code has an error. On line 16, the “greater than” sign is showing as the HTML entity (>).