The script is placed in the/home/user/mysql_backup.sh
Crontab
# crontab-l
# m H Dom Mon Dow command
* * */home/user/mysql_backup.sh
The script is as follows
Copy Code code as follows:
#!/bin/sh
# mysql_backup.sh:backup MySQL databases and keep newest 5 days backup.
#
# Last updated:20 March 2006
# ----------------------------------------------------------------------
# This are a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2006 Sam Tang
# feedback/comment/suggestions:http://www.real-blog.com/
# ----------------------------------------------------------------------
# your MySQL login information
# Db_user is MySQL username
# db_passwd is MySQL password
# db_host is MySQL host
# -----------------------------
Db_user= "Root"
db_passwd= "Password"
db_host= "localhost"
# The directory for story your backup file.
Backup_dir= "/home/mybackup"
# Date format for backup file (dd-mm-yyyy)
Time= "$ (date +"%d-%m-%y ")"
# MySQL, mysqldump and some other bin ' s path
Mysql= "/usr/local/mysql/bin/mysql"
mysqldump= "/usr/local/mysql/bin/mysqldump"
Mkdir= "/bin/mkdir"
Rm= "/bin/rm"
mv= "/BIN/MV"
gzip= "/bin/gzip"
# Check the directory for store backup is writeable
Test! -W $backup _dir && echo "Error: $backup _dir is un-writeable." && exit 0
# The directory for story the newest backup
Test! -D "$backup _dir/backup.0/" && $MKDIR "$backup _dir/backup.0/"
# Get all databases
All_db= "$ ($MYSQL-u $db _user-h $db _host-p$db_passwd-bse ' show databases ')"
For DB in $all _db
Todo
$MYSQLDUMP-u $db _user-h $db _host-p$db_passwd $db | $GZIP-9 > "$backup _dir/backup.0/$time. $db. Gz"
Done
# Delete the oldest backup
test-d "$backup _dir/backup.5/" && $RM-rf "$backup _dir/backup.5"
# Rotate Backup Directory
for int in 4 3 2 1 0
Todo
if (test-d "$backup _dir"/backup.) $int ")
Then
Next_int= ' expr $int + 1 '
$MV "$backup _dir"/backup. " $int "" $backup _dir "/backup." $next _int "
Fi
Done
Exit 0;
Note:
MySQL is running as a MySQL user and will fail to write to/home/mybackup
chmod 777/home/mybackup problem solved.