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:
Copy codeThe 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 and cannot be written to/home/mybackup.
Chmod 777/home/mybackup solved