mysql 每个表一个文件_Shell脚本来备份所有MySQL数据库，每个表在单个文件和上传到远程FTP...

Shell脚本备份所有MySQL数据库，单个文件中的每个表，并上传到远程FTP

该脚本将为每个数据库(每个表中的一个文件)创建每个表的备份，将其压缩并将其上传到远程ftp。

首先使用select和lock表特权创建一个mysql用户(或使用root)。

然后每个小时在你的crontab中使用这个脚本：#!/bin/sh

# System + MySQL backup script

# Copyright (c) 2008 Marchost

# This script is licensed under GNU GPL version 2.0 or above

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

#########################

######TO BE MODIFIED#####

### System Setup ###

BACKUP=YOUR_LOCAL_BACKUP_DIR

### MySQL Setup ###

MUSER="MYSQL_USER"

MPASS="MYSQL_USER_PASSWORD"

MHOST="localhost"

### FTP server Setup ###

FTPD="YOUR_FTP_BACKUP_DIR"

FTPU="YOUR_FTP_USER"

FTPP="YOUR_FTP_USER_PASSWORD"

FTPS="YOUR_FTP_SERVER_ADDRESS"

######DO NOT MAKE MODIFICATION BELOW#####

#########################################

### Binaries ###

TAR="$(which tar)"

GZIP="$(which gzip)"

FTP="$(which ftp)"

MYSQL="$(which mysql)"

MYSQLDUMP="$(which mysqldump)"

### Today + hour in 24h format ###

NOW=$(date +"%d%H")

### Create hourly dir ###

mkdir $BACKUP/$NOW

### Get all databases name ###

DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"

for db in $DBS

do

### Create dir for each databases, backup tables in individual files ###

mkdir $BACKUP/$NOW/$db

for i in `echo "show tables" | $MYSQL -u $MUSER -h $MHOST -p$MPASS $db|grep -v Tables_in_`;

do

FILE=$BACKUP/$NOW/$db/$i.sql.gz

echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u $MUSER -h $MHOST -p$MPASS $db $i | $GZIP -9 > $FILE

done

done

### Compress all tables in one nice file to upload ###

ARCHIVE=$BACKUP/$NOW.tar.gz

ARCHIVED=$BACKUP/$NOW

$TAR -cvf $ARCHIVE $ARCHIVED

### Dump backup using FTP ###

cd $BACKUP

DUMPFILE=$NOW.tar.gz

$FTP -n $FTPS <

quote USER $FTPU

quote PASS $FTPP

cd $FTPD

mput $DUMPFILE

quit

END_SCRIPT

### Delete the backup dir and keep archive ###

rm -rf $ARCHIVED