Put the script in/home/user/mysql_backup.sh
Crontab
# Crontab-l
# M h Dom mon Dow command
28 16 ***/home/user/mysql_backup.sh
The script is as follows:
CopyCode The Code is as follows :#! /Bin/sh
# Mysql_backup.sh: Backup MySQL Databases and keep newest 5 days backup.
#
# Last Updated: 20 march2006
#----------------------------------------------------------------------
# This is 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
Do
$ Mysqldump-U $ db_user-h $ db_host-p $ db_passwd $ db | $ gzip-9> "$ backup_dir/backup.0/snapshot"
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
Do
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 runs as a MySQL user, failed to write/home/mybackup
chmod 777/home/mybackup solved