This is what I tend to use for a simple MySQL database backup script… I wanted to post this so I can look it up when I need it. There are probably better ways to do this (tell me about them!) but this works for me.
#!/bin/bash DT=`date +"%Y%m%d%H%M%S"` mysqldump -u [USERNAME] -p[PASSWORD] [DATABASENAME] > /home/backups/[DATABASENAME]-$DT.dump gzip /home/backups/[DATABASENAME]-$DT.dump
Substitute your MySQL user for [USERNAME]. (There should be a space between the ‘-u’ and the [USERNAME])
Substitute your MySQL user’s password for [PASSWORD]. (There should not be a space between the -p and the [PASSWORD])
Substitute your MySQL user’s database for p[DATABASENAME].
Each time you run it, it will get the date with the year, month, day, hours, minutes, seconds, and use it in the name. So %Y%m%d%H%M%S would produce something like 20100711090854. If you are running one backup per day, you could shorten it to %Y%m%d.
This would put the files in the /home/backups directory. Set this to wherever you want the files to go.
The gzip command compresses the dumped database file. If you don’t want to compress it (and save disk space) then don’t use it.
(BTW, you don’t type the [ brackets ]. They are just there to highlight the words you need to fill in.)
5 replies on “mysql backup shell script”
You can save a ton of IO (at the expense of CPU) by gzipping as you go. You can also convert this to a one-liner by specifying the date format as part of the file name:
mysqldump -u [USERNAME] -p[PASSWORD] [DATABASENAME] | gzip > `date +”/home/backups/[DATABASENAME]-%Y%m%d%H%M%S.dump.gz”`
Note that the way you specify the password on the command line is a bad idea if this is a multi-user box: it’ll show up in the processlist. Instead, just create a .my.cnf file that’s readable only by the user running backups that contains the password as shown here:
http://dev.mysql.com/doc/refman/5.1/en/option-files.html
Thanks Eamon! I’ll dig into using the my.cnf file for this.
This should probably scare you, but this is almost exactly the same script I came up with. I also do not type the brackets.
I also tend to forget that I often want to use the:
—-skip-extended-insert
option when I use mysqldump.
This script is good if you want to keep it simple. Just wanted to add to this. Send an email after the backup just like below
# list of e-mails to send out message
@emails = (‘user1@domain.org’,’user2@domain.org’);
foreach $email (@emails) {
sendmail($email, $msg);
}
#————————————————————————————-
# SUB: sendmail(email-address, message)
#
# DESC: uses SENDMAIL to send out e-mail
#————————————————————————————-
sub sendmail {
my $email = $_[0];
my $msg = $_[1];
open (SENDMAIL, “|/usr/sbin/sendmail -oi -t -odq”) || die (“No Sendmail: $!\n”);
print SENDMAIL “From: Backup-script\@domain.com\n”;
print SENDMAIL “To: $email\n”;
print SENDMAIL “Subject: Database backup update \n\n”;
print SENDMAIL “$msg”;
close(SENDMAIL);
}