当前位置：首页 > news >正文

shell脚本获得所有数据库备份（整库备份，表级备份）

news 2025/7/17 10:09:33

数据库备份到天翼云对象存储OBS
https://blog.csdn.net/qq_34631220/article/details/135755894

1、获得所有数据库

#!/bin/sh
HOSTNAME="ip"  #数据库信息
PORT="3306"
USERNAME="root"
PASSWORD=""
DBNAME="yusuan"  #数据库名称
# select_sql="select * from dade"
# 获得所有数据库
select_sql="show databases"
data=$(mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}")
for list in ${data}
doif [ "$list" != "Database" ] && [ "$list" != "information_schema" ] && [ "$list" != "mysql" ] && [ "$list" != "performance_schema" ] && [ "$list" != "sys" ];thenif [ "$list" == "yusuan" ]; thenecho "数据库66666----${list}"elseecho "数据库----${list}"fifi
done

2.单库表级备份

#!/bin/sh# 获得时间date "+%Y-%m-%d %H:%M:%S"
mkdirYmd=$(date +%Y%m%d)
ymd=$(date +%Y%m%d-%H:%M:%S)
# 判断目录是否存在，不存在创建，压缩存放目录
[ -d /obsfs/obsfs_CentOS7.6_amd64/zip ] || mkdir -p /obsfs/obsfs_CentOS7.6_amd64/zipHOSTNAME="ip"  #数据库信息
PORT="3306"
USERNAME="root"
PASSWORD=""
DBNAME="yusuan"  #数据库名称
# select_sql="select * from dade"
# 获得所有数据库
select_sql="show databases"
data=$(mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}")
for list in ${data}
doif [ "$list" != "Database" ] && [ "$list" != "information_schema" ] && [ "$list" != "mysql" ] && [ "$list" != "performance_schema" ] && [ "$list" != "sys" ];thenif [ "$list" == "yusuan" ]; thenecho "数据库66666----${list}"# 判断目录是否存在，不存在创建,日期+库名创建目录[ -d /obsfs/obsfs_CentOS7.6_amd64/cdj-file/mysql/${mkdirYmd}/${list} ] || mkdir -p /obsfs/obsfs_CentOS7.6_amd64/cdj-file/mysql/${mkdirYmd}/${list}# 整库备份# mysqldump -h${HOSTNAME} -u${USERNAME} -p${PASSWORD} --databases $list >/obsfs/obsfs_CentOS7.6_amd64/sql/${list}.sql# 按库单表备份tables_sql="show tables"Tab=$(mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${list} -e "${tables_sql}")for it in ${Tab}do  if [ "$it" != "Tables_in_yusuan" ];thenecho "表-----${it}"# 单表备份mysqldump -h${HOSTNAME} -u${USERNAME} -p${PASSWORD} --databases ${list} --tables ${it} > /obsfs/obsfs_CentOS7.6_amd64/sql/${it}_${ymd}.sql# 单表压缩zip -r /obsfs/obsfs_CentOS7.6_amd64/zip/${it}_${ymd}.zip /obsfs/obsfs_CentOS7.6_amd64/sql/${it}_${ymd}.sql# 复制到天翼云对象存储目录OBS(并行文件系统)，备份cp /obsfs/obsfs_CentOS7.6_amd64/zip/${it}_${ymd}.zip /obsfs/obsfs_CentOS7.6_amd64/cdj-file/mysql/${mkdirYmd}/${list}# 删除两个原文件rm -rf /obsfs/obsfs_CentOS7.6_amd64/sql/${it}_${ymd}.sql;rm -rf /obsfs/obsfs_CentOS7.6_amd64/zip/${it}_${ymd}.zip;fidoneelseecho "数据库----${list}"fifi
done

3、备份所有库（表级）

#!/bin/sh# 获得时间date "+%Y-%m-%d %H:%M:%S"
mkdirYmd=$(date +%Y%m%d)
ymd=$(date +%Y%m%d-%H:%M:%S)
# 判断目录是否存在，不存在创建，压缩存放目录
[ -d /obsfs/obsfs_CentOS7.6_amd64/zip ] || mkdir -p /obsfs/obsfs_CentOS7.6_amd64/zipHOSTNAME="ip"  #数据库信息
PORT="3306"
USERNAME="root"
PASSWORD=""
DBNAME="yusuan"  #数据库名称
# select_sql="select * from dade"
# 获得所有数据库
select_sql="show databases"
data=$(mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}")
for list in ${data}
doif [ "$list" != "Database" ] && [ "$list" != "information_schema" ] && [ "$list" != "mysql" ] && [ "$list" != "performance_schema" ] && [ "$list" != "sys" ];thenecho "数据库----${list}"# 判断目录是否存在，不存在创建,日期+库名创建目录[ -d /obsfs/obsfs_CentOS7.6_amd64/cdj-file/mysql/${mkdirYmd}/${list} ] || mkdir -p /obsfs/obsfs_CentOS7.6_amd64/cdj-file/mysql/${mkdirYmd}/${list}# 整库备份# mysqldump -h${HOSTNAME} -u${USERNAME} -p${PASSWORD} --databases $list >/obsfs/obsfs_CentOS7.6_amd64/sql/${list}.sql# 按库单表备份tables_sql="show tables"Tab=$(mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${list} -e "${tables_sql}")for it in ${Tab}do  if [ "$it" != "Tables_in_yusuan" ];thenecho "库：${list}---表：${it}"# 单表备份mysqldump -h${HOSTNAME} -u${USERNAME} -p${PASSWORD} --databases ${list} --tables ${it} > /obsfs/obsfs_CentOS7.6_amd64/sql/${it}_${ymd}.sql# 单表压缩zip -r /obsfs/obsfs_CentOS7.6_amd64/zip/${it}_${ymd}.zip /obsfs/obsfs_CentOS7.6_amd64/sql/${it}_${ymd}.sql# 复制到天翼云对象存储目录OBS(并行文件系统)，备份cp /obsfs/obsfs_CentOS7.6_amd64/zip/${it}_${ymd}.zip /obsfs/obsfs_CentOS7.6_amd64/cdj-file/mysql/${mkdirYmd}/${list}# 删除两个原文件rm -rf /obsfs/obsfs_CentOS7.6_amd64/sql/${it}_${ymd}.sql;rm -rf /obsfs/obsfs_CentOS7.6_amd64/zip/${it}_${ymd}.zip;fidonefi
done