1、创建备份用户
CREATE USER 'bankuser'@'%' IDENTIFIED BY 'XXXXXXXXX';
GRANT ALL PRIVILEGES ON *.* TO 'bankuser'@'%';
FLUSH PRIVILEGES;
2、备份脚本
#!/bin/bash
source /etc/profile
source ~/.bash_profile
dbuser='bankuser'
dbpasswd='XXXXXXXXX'
dbname='jxbp_bpm jxbp_form jxbp_job jxbp_nacos jxbp_portal jxbp_seata jxbp_system jxbp_user brain_data_xmgxdsj brain_data_xmgxzsfz brain_data_zstp brain_qyzhfwpt brain_qyzhfwpt_zcysq dtwhale_assets dtwhale_collection dtwhale_dispatch dtwhale_dtant dtwhale_govern dtwhale_kettle dtwhale_manage dtwhale_reptile sendmsg jx_dw jx_ods '
backtime=`date +%Y%m%d%H%M`
logpath='/data/mysqlbak/data'
datapath='/data/mysqlbak/data'
farDataPath='/yssgxpt_nsf_27/databackup25'
day=30
if [ ! -d $logpath ];
then
mkdir -p $logpath;
fi
if [ ! -d $datapath ];
then
mkdir -p $datapath;
fi
echo -e "\n备份时间:${backtime},数据库 ${dbname} 备份开始;" >> ${logpath}/mysqllog.log
for table in $dbname; do
source=`mysqldump -u ${dbuser} -p${dbpasswd} ${table} --skip-lock-tables > ${logpath}/${backtime}.sql` 2>> ${logpath}/mysqllog.log;
if [ "$?" == 0 ];then
cd $datapath
tar jcf ${table}${backtime}.tar.bz2 ${backtime}.sql > /dev/null
rm -f ${datapath}/${backtime}.sql
find $datapath -name "*.tar.bz2" -type f -mtime +$day -exec rm -rf {} \; > /dev/null 2>&1
echo "备份时间:${backtime},数据库 ${dbname} 备份成功!" >> ${logpath}/mysqllog.log
else
echo "备份时间:${backtime},数据库 ${dbname} 备份失败!" >> ${logpath}/mysqllog.log
fi
done
3、添加定时任务
vim /etc/crontab
0 1 * * * root /opt/apps/databack/mysqlbackup.sh
0 13 * * * root /opt/apps/databack/mysqlbackup.sh
*/30 * * * root /usr/local/mycommand.sh
* 3 * * * root /usr/local/mycommand.sh
0 3 * * * root /usr/local/mycommand.sh
*/10 11-13 * * * root /usr/local/mycommand.sh
10-30 * * * * root /usr/local/mycommand.sh
10,30 * * * * * root /usr/local/mycommand.sh