先授权一个用户对所要备份的库权限

grant select,lock tables on zabbix.*to 'backup'@'localhost' identified by '123456'

 

#!/bin/bash

#grant select,lock tables on zabbix.*to 'backup'@'localhost' identified by '123456'

#1.定义数据库链接,目标库信息

MY_user="backup"

MY_pass="123456"

MY_host="localhost"

MY_conn="-u $MY_user -p$MY_pass -h $MY_host"

MY_db1="db1"

MY_db2="db2"

#2.定义备份目录，工具，时间，文件名

BF_dir="/opt/mysql_bak"

BF_cmd="/usr/bin/mysqldump"

BF_time=`date +%Y%m%d-%H%M`

db_name_1="$MY_db1-$BF_time"

db_name_2="$MY_db2-$BF_time"

#3先导出为.sql脚本，然后再进行压缩（打包后删除源文件）

cd $BF_dir

$BF_cmd $MY_conn --databases $MY_db1 > $name_1.sql

$BF_cmd $MY_conn --databases $MY_db2 > $name_2.sql

/bin/tar zcf $db_name_1.tar.gz $db_name_1.sql --remove &> /dev/null

/bin/tar zcf $db_name_2.tar.gz $db_name_2.sql --remove &> /dev/null