mysq备份脚本5.7.30
创建备份用户
grant select,lock tables on *.* to backup@localhost identified by "123456";
flush privileges;
备份脚本
#!/bin/bash
MYSQL_USER="backup"
MYSQL_PASSWD="123456"
BACKUP_TIME=`date +%F`
BACKUP_DIR="/data/mysql_backup/$BACKUP_TIME"
BACKUP_CMD="/usr/bin/mysqldump"
SQL_CMD="/usr/bin/mysql"
FLAG=0
mysql_backup (){
$BACKUP_CMD -u$MYSQL_USER -p$MYSQL_PASSWD --databases $database > $BACKUP_DIR/${database}.sql 2>/dev/null
if [ $? -eq 0 ];then
echo -e "\033[32mbcakup $database success!\033[0m"
ls -l $BACKUP_DIR
else
FLAG=1
echo -e "\033[31mbackup $database failed!\033[0m"
rm -rf $BACKUP_DIR/${database}.sql
fi
}
if [ ! $UID -eq 0 ];then
echo -e "\033[31mfailed:execute user must be root!\033[0m"
exit 1
fi
if [ $
echo -e "\033[32m--------------------------------Usage help-----------------------------\033[0m"
echo -e "\033[32mUsage1:backup one database;example:$0 db\033[0m"
echo -e "\033[32mUsage2:backup more databases;example:$0 db1 db2 db3\033[0m"
echo -e "\033[32mUsage3:backup all databases;example $0 all\033[0m"
exit 1
fi
if [ ! -d $BACKUP_DIR ];then
mkdir -p $BACKUP_DIR
fi
if [ "$*" == "all" ];then
for database in `$SQL_CMD -u$MYSQL_USER -p$MYSQL_PASSWD -e "show databases" 2>/dev/null|grep -viE "database|information_schema|performance_schema|sys"`
do
mysql_backup
done
exit $FLAG
fi
for database in $*
do
mysql_backup
done
exit $FLAG