Mysql Data Backup and automatic backup instances in linux

Script in/home/user/mysql tutorial _ 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 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

Take a tutorial on Regularly backing up mysql databases under lix

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 zookeeper backup_name).tar.gz $ {backup_name}. SQL

Rm-f $ {backup_name}. SQL

# Backup to mnt

Cp worker backup_name1_.tar.gz worker mnt_back_path1_1_backup_name1_.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 extension into backup_name).tar.gz

Close

Bye

!

2. Add a scheduled task (vi/etc/crontab)

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

Execute at every day