定时任务备份mysql数据库，同时备份多个数据库

编写备份数据库的shell脚本

1. 创建脚本

mkdir -p /root/mysql_dump/data
cd /root/mysql_dump
vim mysql_back.sh

2. 脚本内容如下

#!/bin/sh
# File: /root/mysql_dump/mysql_back.sh
# Database info
DB_NAME1="database1"
DB_NAME2="database1"
DB_USER="username"
DB_PASS="userpassword"
# Others vars
# whereis mysqldump
# IS ` but not "
BIN_DIR="/usr/bin"
BCK_DIR="/root/mysql_dump/data"
DATE=`date +%Y%m%d_%H%M%S`
# TODO
if [ ! -d $BCK_DIR ];then
mkdir -p $BCK_DIR
fi
#back DB_NAME1
$BIN_DIR/mysqldump --opt --lock-tables=false -u$DB_USER -p$DB_PASS $DB_NAME1 | gzip 
 > $BCK_DIR/$DB_NAME1.dump_$DATE.sql.gz
#set sql file readonly
chattr +i $BCK_DIR/$DB_NAME1.dump_$DATE.sql.gz
#DB_NAME2
$BIN_DIR/mysqldump --opt --lock-tables=false -u$DB_USER -p$DB_PASS $DB_NAME2 | gzip 
 > $BCK_DIR/$DB_NAME2.dump_$DATE.sql.gz
#set sql file readonly
chattr +i $BCK_DIR/$DB_NAME2.dump_$DATE.sql.gz

3. 脚本字段详细解释

#!/bin/sh
# 要备份的数据库名字：database1
DB_NAME1="database1"
# 要备份的数据库名字：database2
DB_NAME2="database1"
# mysql的账号
DB_USER="username"
# mysql的密码
DB_PASS="userpassword"
# 命令所在文件夹
BIN_DIR="/usr/bin"
# 数据库备份文件的保存位置
BCK_DIR="/root/mysql_dump/data"
# 当前日期
DATE=`date +%Y%m%d_%H%M%S`
# 判断备份文件是否存在如果不存在，就创建
if [ ! -d $BCK_DIR ];then
mkdir -p $BCK_DIR
fi
#备份数据库database1，并压缩成gz格式，并输出保存在/root/mysql_dump/data目录
$BIN_DIR/mysqldump --opt --lock-tables=false -u$DB_USER -p$DB_PASS $DB_NAME1 | gzip 
 > $BCK_DIR/$DB_NAME1.dump_$DATE.sql.gz
#上面这句话变量替换出来是：
#/usr/bin/mysqldump --opt --lock-tables=false -uusername -puserpassword #database1 | gzip  
#> /root/mysql_dump/data/database1.dump_20181129_155629.sql.gz


#设置导出的文件只读属性，放置数据文件被误删除
> chattr +i $BCK_DIR/$DB_NAME1.dump_$DATE.sql.gz
#上面这句话的变量替换以后的意思是：
chattr +i /root/mysql_dump/data/database1.dump_20181129_155629.sql.gz$BCK_DIR/$DB_NAME1.dump_$DATE.sql.gz

#备份数据库database2，下面的语句的意思同备份数据库database1的意思一样，不再赘述
$BIN_DIR/mysqldump --opt --lock-tables=false -u$DB_USER -p$DB_PASS $DB_NAME2 | gzip 
 > $BCK_DIR/$DB_NAME2.dump_$DATE.sql.gz
#set sql file readonly
chattr +i $BCK_DIR/$DB_NAME2.dump_$DATE.sql.gz

修改脚本权限

chmod 755 /root/mysql_dump/mysql_back.sh

以root用户登录mysql客户端，创建账户username，并赋予该账户可以使用密码userpassword，在本地，查询数据库database1和数据库database2的权限

[root@localhost ~]# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 38
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type "help;" or "h" for help. Type "c" to clear the current input statement.

mysql> grant select on database1.* to username@localhost identified by "userpassword";
Query OK, 0 rows affected (0.00 sec)
mysql> grant select on database2.* to username@localhost identified by "userpassword";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;

使用crontab -e配置定时任务

[root@10-13-111-96 mysql_dump]# crontab -e
0 1 * * * /bin/bash /root/mysql_dump/mysql_back.sh > /dev/null 2>&1
# 这句意思是每天一点运行

有不清楚的，评论区留言，会及时回复