Mysql automatic backup script Linux

Script on / 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

#! / bin / sh

# mysql_backup.sh: backup mysql databases and keep newest 5 days backup.

#

# Last updated: 20 March 2006

# ------------------------------------------------- ---------------------

# 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 / $ 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

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 is running as mysql user and can not be written to / home / mybackup will fail

Chmod 777 / home / mybackup problem has been solved