Example of MySQL data backup and automatic backup under Linux

Scripts are placed in the/home/user/mysql tutorial _backup.sh

Crontab

# crontab-l

# m H Dom Mon Dow command

* * */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 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.

Look at a regular backup of MySQL database under Lix tutorial

Add backupmysqleveryday.sh (vi/data/shell/backupmysqleveryday.sh)

#!/bin/sh

#this Shell is user for backup MySQL data everyday

#author: www.ieliwb.com

#path-config

base_mysql_path=/data/webserver/mysql/

mysql_dump_path=/data/mysqlbackup/

mnt_back_path=/mnt/web/mysqlbackup/

ftp_back_path=mysql_data_backup/

#mysql-config

Mysql_user= "Root"

Mysql_pass= "*******"

#ftp-config

ftp_host= "IP"

Ftp_user= "********"

Ftp_pass= "********"

Backup_name= ' Date +%y%m%d%h%m%s '

CD ${mysql_dump_path}

#mysqldump

${base_mysql_path}bin/mysqldump-u${mysql_user}-p${mysql_pass}-all-database > ${backup_name}.sql

#pack

Tar zcf ${backup_name}.tar.gz ${backup_name}.sql

Rm-f ${backup_name}.sql

#backup to MNT

CP ${backup_name}.tar.gz ${mnt_back_path}${backup_name}.tar.gz

#ftp to other host

ftp-n<

Open ${ftp_host}

User ${ftp_user} ${ftp_pass}

Bin

Prompt off

CD ${ftp_back_path}

Passive

Put ${backup_name}.tar.gz

Close

Bye

!

2. Add timing Plan (VI/ETC/CRONTAB)

0 * * * root/data/shell/backupmysqleveryday.sh

0:01 every day.