MySQL生成整个数据库下的所有表的单独备份语句

employeestables.sh

#!/bin/bash

#backup employees of titles delete databasesbackup before 20

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:~/bin

#数据库用户名

user="root"

#数据库密码

passwd="aaa12345"

#备份文件存放目录

backupdir="/data/backup/"

#备份数据库名

dbname="employees"

#备份表名

#table="departments  dept_emp dept_manager employees salaries titles"

table=$(mysql -u$user -p$passwd -ss -e "use $dbname;show tables;")

# table=$(mysql -uroot -paaa12345 -ss -e "select table_name from information_schema.tables where table_schema='employees';")

#当前时间

date=$(date +%Y%m%d%H%M%S)

#过期时间

outtime=20

#判断备份目录是否存在

if [ ! -d $backupdir ];then

mkdir -p $backupdir

fi

for tablename in $table

do

#备份出来的文件名

backfile=$tablename'_'$date.sql

#压缩后的文件名

tarfile=$backfile.tar.bz2

mysqldump -u$user -p$passwd $dbname $tablename > $backupdir$backfile

#tar

if [ $backfile ];then

tar -jcvf $tarfile $backupdir$backfile

rm -f $backfile

fi

done

#delete before 20

#find $backupdir -name *.tar.bz2 -mtime +$outtime |xargs rm -rf

find $backupdir -name *.tar.bz2 -mtime +$outtime -exec rm -f {} \;

###自动每天备份

[root@gyf backup]# crontab -e

01 12 * * * sh /data/backup/employeestables.sh

重启生效

[root@gyf backup]# /etc/init.d/crond restart

原文链接：https://blog.csdn.net/weixin_34044889/article/details/113207651