linux mysql备份脚本

发布时间：2016-05-13 09:55:24编辑：丝画阁阅读（736）

对于linux系统下的mysql DB的数据备份，以下两种方法，第一种方法是只备份数据，将数据保存在文本文件中；第二种方法是将整个DB备份，生成SQL文件，在DB恢复时较方便，当然，如果需要，也可以对个别表进行备份。

一．mysql.sh对所有数据库备份

#!/bin/bash


#DB服务器IP
DB_HOST="127.0.0.1"
#database name
DB_NAME="mysql"
#database username
DB_USER="USER"
#database password
DB_PASS="password"
#当前日期时间字符串 例：2010-12-20-10-12-30 （年月日时分秒）
DATE=`date +%Y-%m-%d-%H-%M-%S`
#bakup file absolute path
BAKUP_FILE=/tmp/bakfile/$DB_NAME-$DATE.sql

#执行mysqldump命令的参数
SQL_OPT="-u$DB_USER -p$DB_PASS -h $DB_HOST $DB_NAME"

#执行备份，整个DB所有表的备份，SQL语句的形式
/usr/bin/mysqldump --opt $SQL_OPT > $BAKUP_FILE

echo 'Backup success。'

二．tablename.sh

#!/bin/bash

#DB服务器IP
DB_HOST="127.0.0.1"
#database name
DB_NAME="mysql"
#database username
DB_USER="USER"
#database password
DB_PASS="password"
#需要备份的表名
TBL_NAME_1=user
#需要备份的表名
TBL_NAME_2=db
#当前日期时间字符串 例：2010-12-20-10-12-30 （年月日时分秒）
DATE=`date +%Y-%m-%d-%H-%M-%S`
#执行mysql命令的参数
SQL_OPT="-u$DB_USER -p$DB_PASS -h $DB_HOST $DB_NAME"

/usr/bin/mysql $SQL_OPT << EOF
#将单个表的数据导出到文件中，
#FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' 这些选项是把数据用逗号分隔，双引号引起来，换行用\n;如果不用，可以将这些选项删除。
select * from $TBL_NAME_1 into outfile "/tmp/bakfile/$TBL_NAME_1.$DATE.txt" FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
select * from $TBL_NAME_2 into outfile "/tmp/bakfile/$TBL_NAME_2.$DATE.txt" FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

quit
EOF

echo 'Backup success'

添加一个定时任务计划

00 00 * * * sh /usr/mysql.sh

每天的０点开始．

三．

ql data backup script
#
# use mysqldump --help,get more detail.
#
BakDir=/var/lib/mysqlbackup
LogFile=/var/lib/mysqlbackup/mysqlbak.log
DATE=`date +%Y%m%d%H%M%S`
echo " " >> $LogFile
echo " " >> $LogFile
echo "-------------------------------------------" >> $LogFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $LogFile
echo "--------------------------" >> $LogFile
cd $BakDir
DumpFile=$DATE.sql
GZDumpFile=$DATE.sql.tar.gz
/usr/mysql/bin/mysqldump -urood -pDBpassword --database  dbname> $DumpFile
echo "Dump Done" >> $LogFile
tar czvf $GZDumpFile $DumpFile >> $LogFile 2>&1
echo "[$GZDumpFile]Backup Success!" >> $LogFile
rm -f $DumpFile
#cd $BakDir/daily
cd $BakDir  
echo "Backup Done!"
echo "please Check $BakDir Directory!"
echo "copy it to your local disk or ftp to somewhere !!!"
find $BakDir -ctime +30 -exec rm -f "{} \;"
echo "delete file over 30 days"