mysql自动备份脚本

MySQL使用mysqldump备份数据库脚本手工执行成功 crontab定时却失败的解决方法

用crontab自动备份MySQL的时候导出总是为空，原因是corn的环境变量中没有mysqldump，所以用crontab执行mysqldump的时候要加上绝对路径，如：/yourMySQLPath/bin/mysqldump xxxxxxx这样就可以了！
如果不知道绝对路径可以用ps -ef|grep mysql或者whereis mysqldump查询

例如我本地的就是

#!/bin/bash
time=$(date '+%Y%m%d%H%M%S')
/usr/local/mysql/bin/mysqldump -uUSERNAME -pPASSWORD DBNAME |gzip > /data/bak/DB_$time.sql.gz
find /data/bak/ -mtime +5 -name "*.gz" -exec rm -rf {} \;

mysql自动备份脚本

#!/bin/bash

DATE=`date +%Y%m%d%H%M%S`
DATE1=`date +%Y%m%d`
DB_user=用户
DB_password=密码

start_time=`date +'%Y-%m-%d %H:%M:%S'`
echo "$DATE数据库全备开始。。。。。。" >> /home/eqics/mysql_bak/logs/Full_bak_$DATE1.log
/home/eqics/mysql/bin/mysqldump --socket=/home/eqics/mysql/mysql.sockt --all-databases --default-character-set=utf8 --flush-logs -u$DB_user -p$DB_password > /home/eqics/mysql_bak/all_data/Full_Bak_$DATE.sql
if [ $? -eq 0 ]
then
	echo "$DATE数据库备份成功......" >> /home/eqics/mysql_bak/logs/Full_Bak_$DATE1.log
cd /home/eqics/mysql_bak/all_data/
tar -zcvf Full_Bak_$DATE.sql.tar.gz Full_Bak_$DATE.sql > /dev/null
	if [ $? -eq 0 ]
	then
		echo "$DATE数据库备份打包成功......" >> /home/eqics/mysql_bak/logs/Full_Bak_$DATE1.log
		find /home/eqics/mysql_bak/all_data/ -name "Full_Bak_*.*" -mtime +7 -exec rm -rf {} \;
		find /home/eqics/mysql_bak/logs/ -name "Full_Bak_*.log" -mtime +7 -exec rm -rf {} \;
	else
		echo "$DATE数据库备份打包失败......" >> /home/eqics/mysql_bak/logs/Full_Bak_$DATE1.log
		exit 0
	fi
else
	echo "$DATE数据库备份失败......" >> /home/eqics/mysql_bak/logs/Full_Bak_$DATE1.log
	cd /home/eqics/mysql_bak/all_data/
	rm -rf Full_Bak_$DATE.sql
	exit 0
fi

end_time=`date +'%Y-%m-%d %H:%M:%S'`
start_seconds=$(date --date="$start_time" +%s);
end_seconds=$(date --date="$end_time" +%s);
echo "本次备份运行时间： "$((end_seconds-start_seconds))"s" >> /home/eqics/mysql_bak/logs/Full_Bak_$DATE1.log

辛辛苦苦，忙忙碌碌。加油