超简洁shell解决mysql按表备份需求

databases='test1 test2 test3'

Date=$(date "+%Y-%m-%d")
Time=$(date "+%H%M%S")

for db in $databases
	do mkdir -p `echo /home/backup/mysql/$db/$Date`
done

for db in $databases
	do tables=`mysql -u root -p123456 $db -e "show tables" | grep -v "Tables"`
	for table in $tables
		do mysqldump -u root -p123456 $db $table | gzip > /home/backup/mysql/$db/$Date/${table}_$Time.sql.gz
	done
done

 

改进代码，实现需求：少于2000行则不压缩

databases='test1 test2 test3'

Date=$(date "+%Y-%m-%d")
Time=$(date "+%H%M%S")

for db in $databases
	do mkdir -p `echo /home/backup/mysql/$db/$Date`
done

for db in $databases
    do tables=`mysql -u root -p123456 $db -e "select GROUP_CONCAT(TABLE_NAME,',', TABLE_ROWS)  FROM information_schema.TABLES where TABLE_SCHEMA='$db' group by TABLE_NAME;" | grep -v "TABLE_NAME" | grep -v "_copy"`
    for table_row in $tables
	do table=`echo $table_row | awk -F, '{print $1}'`
        rows=`echo $table_row | awk -F, '{print $2}'`
        if [ $rows -le 2000 ]
        then
            mysqldump -uroot -p123456 $db $table > /home/backup/mysql/$db/$Date/$Time/${table}.sql
        else
            mysqldump -uroot -p123456 $db $table | gzip > /home/backup/mysql/$db/$Date/$Time/${table}.sql.gz
        fi
    done
done

 

建议把备份出来的sql文件实时复制到其他硬盘或其他主机上，防止硬盘损坏导致数据丢失，备份结果也应实时使用wehook发送到钉钉、企业微信或飞书，并生成报表发到邮箱