背景：
生产环境发版前需要备份数据库数据，书写脚本用于手动执行备份
环境信息如下：
| 容器名 | 使用的镜像 | 端口 | 版本 | 数据库名 | 账号 | 密码 |
|---|---|---|---|---|---|---|
| bim-myql | mysql:8.0.36-debian | 3306 | 8.0.36 | lowcode-bi | root | bim%2018 |
| bim-postgres | timescale/timescaledb-postgis:latest-pg13 | 5432 | 13 | bim3dv2 | postgres | bim%2018 |
备份：
mkdir -p /data/scripts
#192.168.99.56
#mysql数据库备份脚本位置：/data/scripts/mysql_backup.sh
#mysql数据库备份存放位置：/data/data_bak/mysql/
#postgresql数据库备份脚本位置：/data/scripts/postgresql_backup.sh
#postgresql数据库备份存放位置：/data/data_bak/postgresql/
备份脚本创建（mysql）：
cat > /data/scripts/mysql_backup.sh << 'EOF'
#!/bin/bash
# MySQL数据库备份脚本
# 自动备份MySQL(lowcode-bi)数据库
# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color
# 配置信息
MYSQL_CONTAINER="bim-myql"
MYSQL_BACKUP_DIR="/data/data_bak/mysql"
# MySQL配置
MYSQL_PORT=3306
MYSQL_DB="lowcode-bi"
MYSQL_USERNAME="root"
MYSQL_PASSWORD="bim%2018"
# 生成时间戳
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
# 打印醒目的标题
print_header() {
echo
echo -e "${BLUE}================================================${NC}"
echo -e "${BLUE} MySQL数据库手动备份脚本 ${NC}"
echo -e "${BLUE}================================================${NC}"
echo
}
# 打印成功消息
print_success() {
echo
echo -e "${GREEN}************************************************${NC}"
echo -e "${GREEN}$1${NC}"
echo -e "${GREEN}************************************************${NC}"
echo
}
# 打印错误消息
print_error() {
echo
echo -e "${RED}************************************************${NC}"
echo -e "${RED}错误: $1${NC}"
echo -e "${RED}************************************************${NC}"
echo
}
# 打印警告消息
print_warning() {
echo -e "${YELLOW}警告: $1${NC}"
}
# 检查容器是否运行
check_container() {
local container_name=$1
if ! docker ps | grep -q "$container_name"; then
print_error "容器 $container_name 未运行或不存在！"
return 1
fi
return 0
}
# 创建备份目录
create_backup_dir() {
local dir=$1
if [ ! -d "$dir" ]; then
mkdir -p "$dir"
echo -e "创建备份目录: ${YELLOW}$dir${NC}"
fi
}
# MySQL备份函数
backup_mysql() {
echo -e "${BLUE}开始备份MySQL数据库...${NC}"
# 检查MySQL容器
if ! check_container "$MYSQL_CONTAINER"; then
return 1
fi
# 创建备份目录
create_backup_dir "$MYSQL_BACKUP_DIR"
# 生成备份文件名
local backup_file="${MYSQL_DB}_${TIMESTAMP}.sql"
local backup_path="${MYSQL_BACKUP_DIR}/${backup_file}"
echo "正在备份MySQL数据库: $MYSQL_DB"
# 执行MySQL备份
if docker exec "$MYSQL_CONTAINER" mysqldump -u"$MYSQL_USERNAME" -p"$MYSQL_PASSWORD" "$MYSQL_DB" > "$backup_path" 2>/dev/null; then
# 检查备份文件是否生成且不为空
if [ -s "$backup_path" ]; then
local file_size=$(ls -lh "$backup_path" | awk '{print $5}')
print_success "✅ MySQL备份成功完成！
📁 备份位置: $backup_path
📄 备份文件: $backup_file
📊 文件大小: $file_size"
return 0
else
print_error "MySQL备份文件为空，备份可能失败！"
rm -f "$backup_path" 2>/dev/null
return 1
fi
else
print_error "MySQL备份失败！请检查数据库连接和权限。"
rm -f "$backup_path" 2>/dev/null
return 1
fi
}
# 主函数
main() {
print_header
echo -e "${BLUE}开始备份MySQL数据库...${NC}"
echo "📋 备份清单:"
echo " • MySQL数据库: $MYSQL_DB"
echo
# 备份MySQL
backup_mysql
local mysql_result=$?
# 总结备份结果
echo -e "${BLUE}================================================${NC}"
echo -e "${BLUE} 备份任务完成总结 ${NC}"
echo -e "${BLUE}================================================${NC}"
if [ $mysql_result -eq 0 ]; then
print_success "🎉 MySQL数据库备份完成！
✅ MySQL (lowcode-bi): 备份成功
📁 MySQL备份目录: $MYSQL_BACKUP_DIR
📅 备份时间戳: $TIMESTAMP"
else
print_error "❌ MySQL数据库备份失败！
请检查：
1. 容器是否正常运行
2. 数据库连接信息是否正确
3. 备份目录权限是否正确"
fi
echo -e "${BLUE}备份脚本执行完毕！${NC}"
return $mysql_result
}
# 检查是否以root用户运行
if [ "$EUID" -ne 0 ]; then
print_error "请以root用户身份运行此脚本！"
exit 1
fi
# 检查docker是否安装并运行
if ! command -v docker &> /dev/null; then
print_error "Docker未安装或不在PATH中！"
exit 1
fi
if ! docker info &> /dev/null; then
print_error "Docker服务未运行！"
exit 1
fi
# 运行主函数
main
EOF
chmod +x /data/scripts/mysql_backup.sh
备份脚本创建（postgresql）：
cat > /data/scripts/postgresql_backup.sh << 'EOF'
#!/bin/bash
# PostgreSQL数据库备份脚本
# 自动备份PostgreSQL(bim3dv2)数据库
# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color
# 配置信息
POSTGRES_CONTAINER="bim-postgres"
POSTGRES_BACKUP_DIR="/data/data_bak/postgresql"
# PostgreSQL配置
POSTGRESQL_PORT=5432
POSTGRESQL_USERNAME="postgres"
POSTGRESQL_DATABASE="bim3dv2"
POSTGRESQL_PASSWORD="bim%2018"
# 生成时间戳
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
# 打印醒目的标题
print_header() {
echo
echo -e "${BLUE}================================================${NC}"
echo -e "${BLUE} PostgreSQL数据库手动备份脚本 ${NC}"
echo -e "${BLUE}================================================${NC}"
echo
}
# 打印成功消息
print_success() {
echo
echo -e "${GREEN}************************************************${NC}"
echo -e "${GREEN}$1${NC}"
echo -e "${GREEN}************************************************${NC}"
echo
}
# 打印错误消息
print_error() {
echo
echo -e "${RED}************************************************${NC}"
echo -e "${RED}错误: $1${NC}"
echo -e "${RED}************************************************${NC}"
echo
}
# 打印警告消息
print_warning() {
echo -e "${YELLOW}警告: $1${NC}"
}
# 检查容器是否运行
check_container() {
local container_name=$1
if ! docker ps | grep -q "$container_name"; then
print_error "容器 $container_name 未运行或不存在！"
return 1
fi
return 0
}
# 创建备份目录
create_backup_dir() {
local dir=$1
if [ ! -d "$dir" ]; then
mkdir -p "$dir"
echo -e "创建备份目录: ${YELLOW}$dir${NC}"
fi
}
# PostgreSQL备份函数
backup_postgresql() {
echo -e "${BLUE}开始备份PostgreSQL数据库...${NC}"
# 检查PostgreSQL容器
if ! check_container "$POSTGRES_CONTAINER"; then
return 1
fi
# 创建备份目录
create_backup_dir "$POSTGRES_BACKUP_DIR"
# 生成备份文件名
local backup_file="${POSTGRESQL_DATABASE}_${TIMESTAMP}.sql"
local backup_path="${POSTGRES_BACKUP_DIR}/${backup_file}"
echo "正在备份PostgreSQL数据库: $POSTGRESQL_DATABASE"
echo "PostgreSQL数据库较大，大约30分钟左右备份完成"
# 执行PostgreSQL备份
if docker exec -e PGPASSWORD="$POSTGRESQL_PASSWORD" "$POSTGRES_CONTAINER" pg_dump -h localhost -p "$POSTGRESQL_PORT" -U "$POSTGRESQL_USERNAME" -d "$POSTGRESQL_DATABASE" > "$backup_path" 2>/dev/null; then
# 检查备份文件是否生成且不为空
if [ -s "$backup_path" ]; then
local file_size=$(ls -lh "$backup_path" | awk '{print $5}')
print_success "✅ PostgreSQL备份成功完成！
📁 备份位置: $backup_path
📄 备份文件: $backup_file
📊 文件大小: $file_size"
return 0
else
print_error "PostgreSQL备份文件为空，备份可能失败！"
rm -f "$backup_path" 2>/dev/null
return 1
fi
else
print_error "PostgreSQL备份失败！请检查数据库连接和权限。"
rm -f "$backup_path" 2>/dev/null
return 1
fi
}
# 主函数
main() {
print_header
echo -e "${BLUE}开始备份PostgreSQL数据库...${NC}"
echo "📋 备份清单:"
echo " • PostgreSQL数据库: $POSTGRESQL_DATABASE"
echo
# 备份PostgreSQL
backup_postgresql
local postgres_result=$?
# 总结备份结果
echo -e "${BLUE}================================================${NC}"
echo -e "${BLUE} 备份任务完成总结 ${NC}"
echo -e "${BLUE}================================================${NC}"
if [ $postgres_result -eq 0 ]; then
print_success "🎉 PostgreSQL数据库备份完成！
✅ PostgreSQL (bim3dv2): 备份成功
📁 PostgreSQL备份目录: $POSTGRES_BACKUP_DIR
📅 备份时间戳: $TIMESTAMP"
else
print_error "❌ PostgreSQL数据库备份失败！
请检查：
1. 容器是否正常运行
2. 数据库连接信息是否正确
3. 备份目录权限是否正确"
fi
echo -e "${BLUE}备份脚本执行完毕！${NC}"
return $postgres_result
}
# 检查是否以root用户运行
if [ "$EUID" -ne 0 ]; then
print_error "请以root用户身份运行此脚本！"
exit 1
fi
# 检查docker是否安装并运行
if ! command -v docker &> /dev/null; then
print_error "Docker未安装或不在PATH中！"
exit 1
fi
if ! docker info &> /dev/null; then
print_error "Docker服务未运行！"
exit 1
fi
# 运行主函数
main
EOF
chmod +x /data/scripts/postgresql_backup.sh
运行备份脚本
#备份mysql
bash /data/scripts/mysql_backup.sh
#备份postgresql
bash /data/scripts/postgresql_backup.sh
还原：
需要在另一台虚拟机中创建相同环境的docker部署mysql+postgresql测试能否使用备份出来的sql文件进行还原
使用docker-compose部署db环境
目录结构：
[root@localhost db_test_zp]# tree
.
├── docker-compose.yml
├── mysql
│ ├── conf
│ │ ├── conf.d
│ │ │ ├── docker.conf
│ │ │ ├── my.cnf
│ │ │ └── mysql.conf
│ │ └── my.cnf
│ ├── logs
│ │ ├── error.log
│ │ └── slow.log
│ └── my.cnf
└── postgres
└── conf
docker-compose.yml
version: '3.8'
services:
mysql:
image: mysql:8.0.36-debian
container_name: bim-myql
restart: unless-stopped
ports:
- "3306:3306"
environment:
MYSQL_ROOT_PASSWORD: "bim%2018"
MYSQL_DATABASE: "lowcode-bi"
volumes:
- mysql_data:/var/lib/mysql
- ./mysql:/etc/mysql
- ./mysql/logs:/var/log/mysql # 日志目录
# 如果需要导入文件，可以挂载
# - /data/data_bak:/data/backup:ro
networks:
- bim-network
healthcheck:
test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "root", "-pbim%2018"]
interval: 30s
timeout: 10s
retries: 5
start_period: 60s
postgres:
image: timescale/timescaledb-postgis:latest-pg13
container_name: bim-postgres
restart: unless-stopped
ports:
- "5432:5432"
environment:
POSTGRES_DB: lowcode-bi
POSTGRES_USER: bim
POSTGRES_PASSWORD: "bim%2018"
# PostgreSQL内存优化
POSTGRES_SHARED_BUFFERS: 2GB
POSTGRES_EFFECTIVE_CACHE_SIZE: 6GB
POSTGRES_WORK_MEM: 64MB
volumes:
- postgres_data:/var/lib/postgresql/data
- ./postgres/conf:/etc/postgresql/conf.d
networks:
- bim-network
# PostgreSQL资源限制
deploy:
resources:
limits:
memory: 4G
cpus: '2.0'
reservations:
memory: 1G
cpus: '1.0'
healthcheck:
test: ["CMD-SHELL", "pg_isready -U bim -d lowcode-bi"]
interval: 30s
timeout: 10s
retries: 5
volumes:
mysql_data:
driver: local
driver_opts:
type: none
o: bind
device: /data/soft/docker/volumes/db_test_zp_mysql_data/_data
postgres_data:
driver: local
driver_opts:
type: none
o: bind
device: /data/soft/docker/volumes/db_test_zp_postgres_data/_data
networks:
bim-network:
driver: bridge
driver_opts:
com.docker.network.driver.mtu: 1500
mysql配置文件：my.cnf
[root@localhost db_test_zp]# cat mysql/conf/conf.d/docker.conf
[mysqld]
host_cache_size=0
skip-name-resolve
[root@localhost db_test_zp]# cat mysql/conf/conf.d/my.cnf
[mysqld]
bind-address = 0.0.0.0
[root@localhost db_test_zp]# cat mysql/conf/conf.d/mysql.conf
[mysql]
[root@localhost db_test_zp]# cat mysql/conf/my.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Custom config should go here
!includedir /etc/mysql/conf.d/
[root@localhost db_test_zp]#
启动mysql+postgresql容器
docker compose up -d
还原操作：
# 1. 首先检查SQL文件
echo "=== 检查SQL文件 ==="
ls -lh /tmp/lowcode-bi_20260106_163829.sql
head -20 /tmp/lowcode-bi_20260106_163829.sql
# 2. 重新创建数据库
echo "=== 重新创建数据库 ==="
mysql -u root -pbim%2018 -e "
DROP DATABASE IF EXISTS \`lowcode-bi\`;
CREATE DATABASE \`lowcode-bi\`
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;
SHOW DATABASES LIKE 'lowcode-bi';
"
# 3. 设置会话变量（在导入前）
echo "=== 设置优化参数 ==="
mysql -u root -pbim%2018 -e "
SET GLOBAL foreign_key_checks = 0;
SET GLOBAL unique_checks = 0;
SET GLOBAL autocommit = 0;
SET GLOBAL innodb_flush_log_at_trx_commit = 0;
SET GLOBAL sync_binlog = 0;
SET GLOBAL local_infile = 1;
SHOW VARIABLES WHERE Variable_name IN ('foreign_key_checks', 'autocommit', 'local_infile');
"
# 4. 使用最简单的导入方法
echo "=== 开始导入 ($(date)) ==="
time mysql -u root -pbim%2018 lowcode-bi < /tmp/lowcode-bi_20260106_163829.sql
还原后检查：
查询数据库大小
docker exec bim-myql mysql -u root -pbim%2018 -e "SELECT
table_schema as '数据库',
COUNT(*) as '表数量',
SUM(table_rows) as '总行数',
ROUND(SUM(data_length)/1024/1024/1024, 2) as '数据大小GB',
ROUND(SUM(index_length)/1024/1024/1024, 2) as '索引大小GB',
ROUND(SUM(data_length + index_length)/1024/1024/1024, 2) as '总大小GB'
FROM information_schema.tables
WHERE table_schema = 'lowcode-bi'
GROUP BY table_schema;
"
检查各表大小：
mysql -u root -pbim%2018 -e "
USE lowcode-bi;
SELECT
table_name as '表名',
table_rows as '行数',
ROUND(data_length/1024/1024, 2) as 'MB',
table_comment as '备注'
FROM information_schema.tables
WHERE table_schema = 'lowcode-bi'
ORDER BY data_length DESC
LIMIT 20;
"
检查字符集配置：
docker exec bim-myql mysql -u root -pbim%2018 -e "
SHOW VARIABLES LIKE '%character%';
SHOW VARIABLES LIKE '%collation%';
"
检查索引情况
docker exec bim-myql mysql -u root -pbim%2018 -e "
SELECT
table_name,
ROUND(data_length/1024/1024, 2) as 'Data_MB',
ROUND(index_length/1024/1024, 2) as 'Index_MB',
ROUND((data_length + index_length)/1024/1024, 2) as 'Total_MB'
FROM information_schema.tables
WHERE table_schema = 'lowcode-bi'
AND table_name = 'b_screen_data_bak';
"
附：导入过程中排查命令：
检查导入进程:
docker exec bim-myql ps aux | grep mysql
查看连接状态:
docker exec bim-myql mysql -u root -pbim%2018 -e "SHOW PROCESSLIST;"
检查表大小:
docker exec bim-myql mysql -u root -pbim%2018 -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='lowcode-bi' ORDER BY table_rows DESC LIMIT 5;"
cat > /tmp/mysql_monitor_final.sh << 'EOF'
#!/bin/bash
# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
CYAN='\033[0;36m'
MAGENTA='\033[0;35m'
WHITE='\033[1;37m'
NC='\033[0m'
# 获取数据库实时信息函数
get_db_info() {
local timeout=3
timeout $timeout docker exec bim-myql mysql -u root -pbim%2018 -N -e "
SELECT
COALESCE(COUNT(*), 0) as table_count,
COALESCE(SUM(table_rows), 0) as total_rows,
COALESCE(ROUND(SUM(data_length)/1024/1024/1024, 3), 0) as data_gb,
COALESCE(ROUND(SUM(index_length)/1024/1024/1024, 3), 0) as index_gb,
COALESCE(ROUND(SUM(data_length + index_length)/1024/1024/1024, 3), 0) as total_gb
FROM information_schema.tables
WHERE table_schema = 'lowcode-bi';
" 2>/dev/null
}
# 获取当前执行的SQL
get_current_sql() {
local timeout=2
timeout $timeout docker exec bim-myql mysql -u root -pbim%2018 -N -e "
SELECT SUBSTRING(Info, 1, 100)
FROM information_schema.PROCESSLIST
WHERE db = 'lowcode-bi' AND Command = 'Query' AND Info IS NOT NULL
LIMIT 1;" 2>/dev/null || echo "Idle"
}
# 检测导入进程状态 - 简化版本
check_import_status() {
# 检查是否有活跃的导入连接
local import_connections=$(timeout 2 docker exec bim-myql mysql -u root -pbim%2018 -N -e "
SELECT COUNT(*) FROM information_schema.PROCESSLIST
WHERE db = 'lowcode-bi' AND Command IN ('Query', 'Execute');" 2>/dev/null)
# 清理输出，移除换行符和空格
import_connections=$(echo "$import_connections" | tr -d '\n\r ' || echo "0")
# 确保是数字
if ! [[ "$import_connections" =~ ^[0-9]+$ ]]; then
import_connections=0
fi
echo "$import_connections"
}
# 检查容器内的导入进程状态
check_container_import_processes() {
echo "=== 导入状态检测 ==="
# 获取导入状态
local import_connections=$(check_import_status)
# 检查SQL文件状态
local file_exists=$(timeout 1 docker exec bim-myql test -f /tmp/lowcode-bi_20260106_163829.sql && echo "存在" || echo "不存在")
echo " 📁 SQL文件状态: $file_exists (25GB)"
echo " 🔗 活跃数据库连接: $import_connections 个"
if [ "$import_connections" -gt 0 ]; then
echo " 📊 状态: IMPORTING"
return 0
else
echo " 📊 状态: NO_ACTIVE_CONNECTIONS"
return 1
fi
}
# 获取MySQL详细状态
get_mysql_detailed_status() {
timeout 3 docker exec bim-myql mysql -u root -pbim%2018 -N -e "
SELECT
Id, User, Command, Time, State,
CASE
WHEN Info IS NULL THEN 'NULL'
WHEN LENGTH(Info) > 80 THEN CONCAT(SUBSTRING(Info, 1, 80), '...')
ELSE Info
END as Query_Info
FROM information_schema.PROCESSLIST
WHERE (db = 'lowcode-bi' OR Command != 'Sleep') AND Command != 'Binlog Dump'
ORDER BY Time DESC
LIMIT 5;
" 2>/dev/null
}
# 获取最大的几个表信息
get_largest_tables() {
timeout 3 docker exec bim-myql mysql -u root -pbim%2018 -N -e "
SELECT
table_name,
table_rows,
ROUND((data_length + index_length)/1024/1024, 1) as size_mb
FROM information_schema.tables
WHERE table_schema = 'lowcode-bi'
ORDER BY (data_length + index_length) DESC
LIMIT 5;
" 2>/dev/null
}
# 主循环变量
LAST_TOTAL_GB=0
LAST_TOTAL_ROWS=0
LAST_UPDATE_TIME=$(date +%s)
UPDATE_SPEED_GB=0
UPDATE_SPEED_ROWS=0
NO_UPDATE_COUNT=0
COMPLETION_CHECK_COUNT=0
while true; do
clear
CURRENT_TIME=$(date '+%Y-%m-%d %H:%M:%S')
CURRENT_TIMESTAMP=$(date +%s)
# 获取数据库信息
DB_INFO=$(get_db_info)
if [ -z "$DB_INFO" ]; then
printf "${RED}❌ MySQL 连接失败，请检查容器状态${NC}\n"
sleep 5
continue
fi
# 解析数据，使用awk处理，确保数值有效
TABLE_COUNT=$(echo "$DB_INFO" | awk '{print int($1)}')
TOTAL_ROWS=$(echo "$DB_INFO" | awk '{print int($2)}')
DATA_GB=$(echo "$DB_INFO" | awk '{print $3}')
INDEX_GB=$(echo "$DB_INFO" | awk '{print $4}')
TOTAL_GB=$(echo "$DB_INFO" | awk '{print $5}')
# 确保数值有效
TABLE_COUNT=${TABLE_COUNT:-0}
TOTAL_ROWS=${TOTAL_ROWS:-0}
DATA_GB=${DATA_GB:-0.000}
INDEX_GB=${INDEX_GB:-0.000}
TOTAL_GB=${TOTAL_GB:-0.000}
# 计算变化速度
TIME_DIFF=$((CURRENT_TIMESTAMP - LAST_UPDATE_TIME))
if [ $TIME_DIFF -gt 0 ] && [ $TIME_DIFF -lt 300 ]; then
SIZE_DIFF=$(awk "BEGIN {printf \"%.3f\", $TOTAL_GB - $LAST_TOTAL_GB}")
ROWS_DIFF=$((TOTAL_ROWS - LAST_TOTAL_ROWS))
UPDATE_SPEED_GB=$(awk "BEGIN {printf \"%.3f\", $SIZE_DIFF / $TIME_DIFF * 60}")
if [ "$ROWS_DIFF" -gt 0 ] && [ $TIME_DIFF -gt 0 ]; then
UPDATE_SPEED_ROWS=$(awk "BEGIN {printf \"%.0f\", $ROWS_DIFF / $TIME_DIFF}")
else
UPDATE_SPEED_ROWS=0
fi
# 判断是否有增长
if (( $(awk "BEGIN {print ($SIZE_DIFF > 0.001) ? 1 : 0}") )) || [ "$ROWS_DIFF" -gt 5 ]; then
NO_UPDATE_COUNT=0
else
NO_UPDATE_COUNT=$((NO_UPDATE_COUNT + 1))
fi
fi
LAST_TOTAL_GB=$TOTAL_GB
LAST_TOTAL_ROWS=$TOTAL_ROWS
LAST_UPDATE_TIME=$CURRENT_TIMESTAMP
# 检查导入状态
if check_container_import_processes > /dev/null 2>&1; then
IMPORT_ACTIVE=1
else
IMPORT_ACTIVE=0
fi
CURRENT_SQL=$(get_current_sql)
# 智能状态判断
if [ "$TABLE_COUNT" -eq 0 ]; then
STATUS_ICON="🟡"
STATUS_TEXT="初始化中"
STATUS_COLOR=$YELLOW
elif [ "$IMPORT_ACTIVE" -eq 1 ]; then
if (( $(awk "BEGIN {print ($UPDATE_SPEED_GB > 0.001) ? 1 : 0}") )) || [ "$UPDATE_SPEED_ROWS" -gt 0 ]; then
STATUS_ICON="🟢"
STATUS_TEXT="导入活跃 (数据增长中)"
STATUS_COLOR=$GREEN
else
STATUS_ICON="🔵"
STATUS_TEXT="导入进行中 (可能在处理大表或索引)"
STATUS_COLOR=$CYAN
fi
COMPLETION_CHECK_COUNT=0
else
# 没有活跃导入进程，检查是否已完成
if [ "$TABLE_COUNT" -gt 0 ] && [ "$TOTAL_ROWS" -gt 0 ]; then
COMPLETION_CHECK_COUNT=$((COMPLETION_CHECK_COUNT + 1))
if [ "$COMPLETION_CHECK_COUNT" -ge 2 ]; then
STATUS_ICON="✅"
STATUS_TEXT="导入完成"
STATUS_COLOR=$GREEN
else
STATUS_ICON="🔵"
STATUS_TEXT="确认完成中..."
STATUS_COLOR=$CYAN
fi
else
STATUS_ICON="🟠"
STATUS_TEXT="等待中"
STATUS_COLOR=$YELLOW
fi
fi
# ═══════════════════════════════════════════════════════════
# 绘制面板
# ═══════════════════════════════════════════════════════════
printf "${CYAN}╔═══════════════════════════════════════════════════════════╗${NC}\n"
printf "${CYAN}║${NC} ${WHITE}MySQL 容器导入监控面板 v9.2 (最终版)${NC}${CYAN} ║${NC}\n"
printf "${CYAN}╚═══════════════════════════════════════════════════════════╝${NC}\n\n"
# 时间信息
printf "${BLUE}【时间】${NC}%s (每20秒刷新)\n\n" "$CURRENT_TIME"
# ═══ 导入状态 ═══
printf "${BLUE}【导入状态】${NC}\n"
printf " ${STATUS_ICON} 状态: ${STATUS_COLOR}%s${NC}\n" "$STATUS_TEXT"
if [ "$TABLE_COUNT" -gt 0 ]; then
printf " 🎯 表数量: ${GREEN}%s${NC} 张 | 数据行: ${GREEN}%s${NC} 条\n" "$TABLE_COUNT" "$(echo $TOTAL_ROWS | sed ':a;s/\B[0-9]\{3\}\>/,&/;ta')"
printf " 💾 数据大小: 数据 ${YELLOW}%.3f GB${NC} + 索引 ${YELLOW}%.3f GB${NC} = ${WHITE}总计 %.3f GB${NC}\n" "$DATA_GB" "$INDEX_GB" "$TOTAL_GB"
printf " 🔄 当前操作: ${MAGENTA}%s${NC}\n" "$CURRENT_SQL"
# 显示导入速度（如果有）
if [ "$UPDATE_SPEED_ROWS" -gt 0 ] || (( $(awk "BEGIN {print ($UPDATE_SPEED_GB > 0.001) ? 1 : 0}") )); then
printf " 📈 导入速度: "
if [ "$UPDATE_SPEED_ROWS" -gt 0 ]; then
printf "${GREEN}%s 行/秒${NC}" "$UPDATE_SPEED_ROWS"
fi
if (( $(awk "BEGIN {print ($UPDATE_SPEED_GB > 0.001) ? 1 : 0}") )); then
[ "$UPDATE_SPEED_ROWS" -gt 0 ] && printf " | "
printf "${GREEN}%.3f GB/分钟${NC}" "$UPDATE_SPEED_GB"
fi
printf "\n"
fi
else
printf " ${YELLOW}⏳ 初始化中，正在创建数据库结构...${NC}\n"
fi
# ═══ 导入进程详情 ═══
printf "\n${BLUE}【导入进程详情】${NC}\n"
check_container_import_processes | head -n -1
# ═══ 最大表信息 ═══
if [ "$TABLE_COUNT" -gt 0 ]; then
printf "\n${BLUE}【数据表信息 TOP5】${NC}\n"
LARGEST_TABLES=$(get_largest_tables)
if [ -n "$LARGEST_TABLES" ]; then
echo "$LARGEST_TABLES" | while IFS=$'\t' read -r table_name table_rows size_mb; do
printf " • %-30s %10s 行 %8s MB\n" "$table_name" "$(echo $table_rows | sed ':a;s/\B[0-9]\{3\}\>/,&/;ta')" "$size_mb"
done
fi
fi
# ═══ MySQL连接详情 ═══
printf "\n${BLUE}【MySQL连接详情】${NC}\n"
MYSQL_PROCESSES=$(get_mysql_detailed_status)
if [ -n "$MYSQL_PROCESSES" ]; then
echo "$MYSQL_PROCESSES" | while IFS=$'\t' read -r id user command time state query; do
printf " 🔗 ID:%s | 用户:%s | 命令:%s | 时间:%ss\n" "$id" "$user" "$command" "$time"
if [ "$state" != "NULL" ] && [ -n "$state" ]; then
printf " 状态: %s\n" "$state"
fi
if [ "$query" != "NULL" ] && [ -n "$query" ]; then
printf " 查询: ${CYAN}%s${NC}\n" "$query"
fi
done
else
printf " ${YELLOW}无活跃连接${NC}\n"
fi
# ═══ 系统资源 ═══
printf "\n${BLUE}【系统资源】${NC}\n"
# 负载和内存
LOAD=$(uptime | awk -F'load average:' '{print $2}' | xargs)
MEMORY_INFO=$(free -h | awk 'NR==2{printf "%s / %s", $3, $2}')
MEMORY_PERCENT=$(free -h | awk 'NR==2{printf "%.1f", ($3/$2)*100}')
IFS=',' read -ra LOADS <<< "$LOAD"
LOAD1=$(printf "%.2f" "${LOADS[0]}" 2>/dev/null || echo "0")
LOAD5=$(printf "%.2f" "${LOADS[1]}" 2>/dev/null || echo "0")
LOAD15=$(printf "%.2f" "${LOADS[2]}" 2>/dev/null || echo "0")
printf " 🔥 CPU负载: ${CYAN}%s${NC} (1min) | ${CYAN}%s${NC} (5min) | ${CYAN}%s${NC} (15min)\n" "$LOAD1" "$LOAD5" "$LOAD15"
if (( $(awk "BEGIN {print (${MEMORY_PERCENT} > 80) ? 1 : 0}") )); then
MEM_COLOR=$RED
elif (( $(awk "BEGIN {print (${MEMORY_PERCENT} > 60) ? 1 : 0}") )); then
MEM_COLOR=$YELLOW
else
MEM_COLOR=$GREEN
fi
printf " 💾 内存使用: ${MEM_COLOR}%s${NC} (${MEM_COLOR}%.1f%%${NC})\n" "$MEMORY_INFO" "$MEMORY_PERCENT"
# 容器资源
CONTAINER_STATS=$(timeout 3 docker stats bim-myql --no-stream --format "{{.CPUPerc}} {{.MemUsage}} {{.MemPerc}} {{.BlockIO}}" 2>/dev/null)
if [ -n "$CONTAINER_STATS" ]; then
CPU_PERC=$(echo "$CONTAINER_STATS" | awk '{print $1}')
MEM_USAGE=$(echo "$CONTAINER_STATS" | awk '{print $2}')
MEM_PERC=$(echo "$CONTAINER_STATS" | awk '{print $3}')
BLOCK_IO=$(echo "$CONTAINER_STATS" | awk '{print $4}')
printf " 🐳 容器资源: CPU ${GREEN}%s${NC} | 内存 ${GREEN}%s${NC} (%s) | 磁盘IO ${CYAN}%s${NC}\n" "$CPU_PERC" "$MEM_USAGE" "$MEM_PERC" "$BLOCK_IO"
fi
# ═══ 提示信息 ═══
printf "\n${CYAN}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}\n"
if [ "$STATUS_TEXT" = "导入完成" ]; then
printf "${GREEN}🎉 导入已完成！最终数据：%.3f GB，%s 行记录${NC}\n" "$TOTAL_GB" "$(echo $TOTAL_ROWS | sed ':a;s/\B[0-9]\{3\}\>/,&/;ta')"
printf "${GREEN}✅ 所有 %d 张表已成功导入${NC}\n" "$TABLE_COUNT"
else
printf "${GREEN}📊 智能监控中 | 已导入: %.3f GB | 按 Ctrl+C 停止${NC}\n" "$TOTAL_GB"
fi
printf "${CYAN}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}\n"
sleep 20
done
EOF
chmod +x /tmp/mysql_monitor_final.sh
bash /tmp/mysql_monitor_final.sh
附：远程执行脚本
原脚本在192.168.99.56上执行，备份保存在本地，现在需要将脚本放在192.168.99.57上远程执行，将备份数据保存在192.168.99.57的/data/data_bak/目录中
mysql_backup.sh
#!/bin/bash
# MySQL数据库备份脚本 - 远程执行版本
# 自动在远程服务器上备份MySQL(lowcode-bi)数据库，保存到本地
# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
CYAN='\033[0;36m'
NC='\033[0m' # No Color
# 远程服务器配置
REMOTE_HOST="192.168.99.56"
REMOTE_USER="root"
REMOTE_PASSWORD="Txpt_4app"
REMOTE_PORT="22"
# 配置信息
MYSQL_CONTAINER="bim-myql"
# MySQL配置
MYSQL_PORT=3306
MYSQL_DB="lowcode-bi"
MYSQL_USERNAME="root"
MYSQL_PASSWORD="bim%2018"
# 本地备份存储目录
LOCAL_BACKUP_DIR="/data/data_bak/mysql"
# 生成时间戳
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
# 打印醒目的标题
print_header() {
echo
echo -e "${BLUE}================================================${NC}"
echo -e "${BLUE} MySQL数据库远程备份脚本 ${NC}"
echo -e "${BLUE} 目标服务器: $REMOTE_HOST${NC}"
echo -e "${BLUE}================================================${NC}"
echo
}
# 打印成功消息
print_success() {
echo
echo -e "${GREEN}************************************************${NC}"
echo -e "${GREEN}$1${NC}"
echo -e "${GREEN}************************************************${NC}"
echo
}
# 打印错误消息
print_error() {
echo
echo -e "${RED}************************************************${NC}"
echo -e "${RED}错误: $1${NC}"
echo -e "${RED}************************************************${NC}"
echo
}
# 打印警告消息
print_warning() {
echo -e "${YELLOW}⚠️ 警告: $1${NC}"
}
# 打印信息消息
print_info() {
echo -e "${BLUE}ℹ️ $1${NC}"
}
# 检查sshpass是否安装
check_sshpass() {
if ! command -v sshpass &> /dev/null; then
print_error "sshpass未安装！
请先运行以下命令安装sshpass:
• 在CentOS/RHEL上: yum install -y sshpass
• 在Debian/Ubuntu上: apt-get install -y sshpass"
return 1
fi
return 0
}
# 检查远程SSH连接
check_remote_connection() {
print_info "正在检查远程服务器连接..."
if sshpass -p "$REMOTE_PASSWORD" ssh -o ConnectTimeout=5 -o StrictHostKeyChecking=no \
-p "$REMOTE_PORT" "$REMOTE_USER@$REMOTE_HOST" "echo 'SSH连接成功'" &>/dev/null; then
print_info "远程服务器连接成功 ✓"
return 0
else
print_error "无法连接到远程服务器 $REMOTE_HOST
请检查：
1. 远程服务器是否在线
2. SSH服务是否启动
3. 用户名密码是否正确
4. 防火墙是否开放22端口"
return 1
fi
}
# 远程执行命令
remote_exec() {
local command="$1"
sshpass -p "$REMOTE_PASSWORD" ssh -o StrictHostKeyChecking=no \
-p "$REMOTE_PORT" "$REMOTE_USER@$REMOTE_HOST" "$command" 2>/dev/null
}
# 检查远程容器是否运行
check_remote_container() {
local container_name=$1
print_info "正在检查远程容器: $container_name"
if remote_exec "docker ps | grep -q '$container_name'"; then
print_info "远程容器检查成功 ✓"
return 0
else
print_error "远程容器 $container_name 未运行或不存在！"
return 1
fi
}
# MySQL远程备份函数
backup_mysql_remote() {
echo
echo -e "${BLUE}═══════════════════════════════════════════════════${NC}"
echo -e "${BLUE} 开始远程备份MySQL数据库${NC}"
echo -e "${BLUE}═══════════════════════════════════════════════════${NC}"
echo
# 检查MySQL容器
if ! check_remote_container "$MYSQL_CONTAINER"; then
return 1
fi
# 创建本地备份目录
if [ ! -d "$LOCAL_BACKUP_DIR" ]; then
mkdir -p "$LOCAL_BACKUP_DIR"
print_info "创建本地备份目录: $LOCAL_BACKUP_DIR"
fi
# 生成备份文件名
local backup_file="${MYSQL_DB}_${TIMESTAMP}.sql"
local local_backup_path="${LOCAL_BACKUP_DIR}/${backup_file}"
print_info "开始备份远程MySQL数据库: $MYSQL_DB"
echo " • 远程服务器: $REMOTE_HOST"
echo " • 备份数据库: $MYSQL_DB"
echo " • 备份文件: $backup_file"
echo
print_warning "正在远程服务器上执行备份，请勿中断..."
echo
# 直接从远程服务器备份到本地，不在远程服务器保存文件
if sshpass -p "$REMOTE_PASSWORD" ssh -o StrictHostKeyChecking=no \
-p "$REMOTE_PORT" "$REMOTE_USER@$REMOTE_HOST" \
"docker exec $MYSQL_CONTAINER mysqldump -u'$MYSQL_USERNAME' -p'$MYSQL_PASSWORD' '$MYSQL_DB' 2>/dev/null" \
> "$local_backup_path"; then
# 检查备份文件大小
if [ -s "$local_backup_path" ]; then
local file_size=$(ls -lh "$local_backup_path" | awk '{print $5}')
print_success "✅ MySQL备份成功完成！
📁 本地备份位置: $local_backup_path
📄 备份文件: $backup_file
📊 文件大小: $file_size
⏱️ 备份时间戳: $TIMESTAMP"
return 0
else
print_error "MySQL备份文件为空，备份可能失败！"
rm -f "$local_backup_path" 2>/dev/null
return 1
fi
else
print_error "远程MySQL备份失败！请检查：
1. 远程容器是否正常运行
2. 远程数据库连接信息是否正确
3. 网络连接是否稳定"
rm -f "$local_backup_path" 2>/dev/null
return 1
fi
}
# 显示备份统计信息
show_backup_statistics() {
echo
echo -e "${BLUE}════════════════════════════════════════════════════${NC}"
echo -e "${BLUE} 备份文件统计信息 ${NC}"
echo -e "${BLUE}════════════════════════════════════════════════════${NC}"
echo
if [ -d "$LOCAL_BACKUP_DIR" ]; then
echo -e "${CYAN}本地备份目录: $LOCAL_BACKUP_DIR${NC}"
echo -e "${CYAN}最近备份文件:${NC}"
ls -lh "$LOCAL_BACKUP_DIR" | tail -5 | awk '{if(NR>1) printf " • %-40s %6s\n", $9, $5}'
fi
echo
}
# 主函数
main() {
print_header
echo -e "${BLUE}准备执行远程MySQL备份任务...${NC}"
echo "📋 备份配置:"
echo " • 本地服务器: $(hostname -I | awk '{print $1}')"
echo " • 远程服务器: $REMOTE_HOST"
echo " • 远程数据库: $MYSQL_DB"
echo " • 本地备份目录: $LOCAL_BACKUP_DIR"
echo
# 检查sshpass
if ! check_sshpass; then
return 1
fi
# 检查远程连接
if ! check_remote_connection; then
return 1
fi
echo
# 执行远程备份
backup_mysql_remote
local backup_result=$?
# 总结备份结果
echo
echo -e "${BLUE}════════════════════════════════════════════════════${NC}"
echo -e "${BLUE} 备份任务完成总结 ${NC}"
echo -e "${BLUE}════════════════════════════════════════════════════${NC}"
if [ $backup_result -eq 0 ]; then
print_success "🎉 MySQL数据库远程备份完成！
✅ 远程备份: 成功 ($REMOTE_HOST)
✅ 本地保存: 成功
📁 本地备份目录: $LOCAL_BACKUP_DIR
📅 备份时间戳: $TIMESTAMP"
# 显示备份统计
show_backup_statistics
else
print_error "❌ MySQL数据库远程备份失败！
请检查：
1. 远程服务器是否在线和SSH可达
2. 远程容器是否正常运行
3. 远程数据库连接信息是否正确
4. 本地备份目录权限是否正确"
fi
echo -e "${BLUE}远程备份脚本执行完毕！${NC}"
echo
return $backup_result
}
# 检查是否以root用户运行
if [ "$EUID" -ne 0 ]; then
print_error "请以root用户身份运行此脚本！"
exit 1
fi
# 运行主函数
main
postgresql_backup.sh
#!/bin/bash
# PostgreSQL数据库备份脚本 - 远程执行版本
# 自动在远程服务器上备份PostgreSQL(bim3dv2)数据库，保存到本地
# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
CYAN='\033[0;36m'
NC='\033[0m' # No Color
# 远程服务器配置
REMOTE_HOST="192.168.99.56"
REMOTE_USER="root"
REMOTE_PASSWORD="Txpt_4app"
REMOTE_PORT="22"
# 配置信息
POSTGRES_CONTAINER="bim-postgres"
# PostgreSQL配置
POSTGRESQL_PORT=5432
POSTGRESQL_USERNAME="postgres"
POSTGRESQL_DATABASE="bim3dv2"
POSTGRESQL_PASSWORD="bim%2018"
# 本地备份存储目录
LOCAL_BACKUP_DIR="/data/data_bak/postgresql"
# 生成时间戳
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
# 打印醒目的标题
print_header() {
echo
echo -e "${BLUE}================================================${NC}"
echo -e "${BLUE} PostgreSQL数据库远程备份脚本 ${NC}"
echo -e "${BLUE} 目标服务器: $REMOTE_HOST${NC}"
echo -e "${BLUE}================================================${NC}"
echo
}
# 打印成功消息
print_success() {
echo
echo -e "${GREEN}************************************************${NC}"
echo -e "${GREEN}$1${NC}"
echo -e "${GREEN}************************************************${NC}"
echo
}
# 打印错误消息
print_error() {
echo
echo -e "${RED}************************************************${NC}"
echo -e "${RED}错误: $1${NC}"
echo -e "${RED}************************************************${NC}"
echo
}
# 打印警告消息
print_warning() {
echo -e "${YELLOW}⚠️ 警告: $1${NC}"
}
# 打印信息消息
print_info() {
echo -e "${BLUE}ℹ️ $1${NC}"
}
# 检查sshpass是否安装
check_sshpass() {
if ! command -v sshpass &> /dev/null; then
print_error "sshpass未安装！
请先运行以下命令安装sshpass:
• 在CentOS/RHEL上: yum install -y sshpass
• 在Debian/Ubuntu上: apt-get install -y sshpass"
return 1
fi
return 0
}
# 检查远程SSH连接
check_remote_connection() {
print_info "正在检查远程服务器连接..."
if sshpass -p "$REMOTE_PASSWORD" ssh -o ConnectTimeout=5 -o StrictHostKeyChecking=no \
-p "$REMOTE_PORT" "$REMOTE_USER@$REMOTE_HOST" "echo 'SSH连接成功'" &>/dev/null; then
print_info "远程服务器连接成功 ✓"
return 0
else
print_error "无法连接到远程服务器 $REMOTE_HOST
请检查：
1. 远程服务器是否在线
2. SSH服务是否启动
3. 用户名密码是否正确
4. 防火墙是否开放22端口"
return 1
fi
}
# 远程执行命令
remote_exec() {
local command="$1"
sshpass -p "$REMOTE_PASSWORD" ssh -o StrictHostKeyChecking=no \
-p "$REMOTE_PORT" "$REMOTE_USER@$REMOTE_HOST" "$command" 2>/dev/null
}
# 检查远程容器是否运行
check_remote_container() {
local container_name=$1
print_info "正在检查远程容器: $container_name"
if remote_exec "docker ps | grep -q '$container_name'"; then
print_info "远程容器检查成功 ✓"
return 0
else
print_error "远程容器 $container_name 未运行或不存在！"
return 1
fi
}
# PostgreSQL远程备份函数
backup_postgresql_remote() {
echo
echo -e "${BLUE}═══════════════════════════════════════════════════${NC}"
echo -e "${BLUE} 开始远程备份PostgreSQL数据库${NC}"
echo -e "${BLUE}═══════════════════════════════════════════════════${NC}"
echo
# 检查PostgreSQL容器
if ! check_remote_container "$POSTGRES_CONTAINER"; then
return 1
fi
# 创建本地备份目录
if [ ! -d "$LOCAL_BACKUP_DIR" ]; then
mkdir -p "$LOCAL_BACKUP_DIR"
print_info "创建本地备份目录: $LOCAL_BACKUP_DIR"
fi
# 生成备份文件名
local backup_file="${POSTGRESQL_DATABASE}_${TIMESTAMP}.sql"
local local_backup_path="${LOCAL_BACKUP_DIR}/${backup_file}"
print_info "开始备份远程PostgreSQL数据库: $POSTGRESQL_DATABASE"
echo " • 远程服务器: $REMOTE_HOST"
echo " • 备份数据库: $POSTGRESQL_DATABASE"
echo " • 备份文件: $backup_file"
echo
print_warning "PostgreSQL数据库较大，大约30分钟左右备份完成，请勿中断..."
echo
# 直接从远程服务器备份到本地，不在远程服务器保存文件
if sshpass -p "$REMOTE_PASSWORD" ssh -o StrictHostKeyChecking=no \
-p "$REMOTE_PORT" "$REMOTE_USER@$REMOTE_HOST" \
"docker exec -e PGPASSWORD='$POSTGRESQL_PASSWORD' '$POSTGRES_CONTAINER' pg_dump -h localhost -p '$POSTGRESQL_PORT' -U '$POSTGRESQL_USERNAME' -d '$POSTGRESQL_DATABASE' 2>/dev/null" \
> "$local_backup_path"; then
# 检查备份文件大小
if [ -s "$local_backup_path" ]; then
local file_size=$(ls -lh "$local_backup_path" | awk '{print $5}')
print_success "✅ PostgreSQL备份成功完成！
📁 本地备份位置: $local_backup_path
📄 备份文件: $backup_file
📊 文件大小: $file_size
⏱️ 备份时间戳: $TIMESTAMP"
return 0
else
print_error "PostgreSQL备份文件为空，备份可能失败！"
rm -f "$local_backup_path" 2>/dev/null
return 1
fi
else
print_error "远程PostgreSQL备份失败！请检查：
1. 远程容器是否正常运行
2. 远程数据库连接信息是否正确
3. 网络连接是否稳定"
rm -f "$local_backup_path" 2>/dev/null
return 1
fi
}
# 显示备份统计信息
show_backup_statistics() {
echo
echo -e "${BLUE}════════════════════════════════════════════════════${NC}"
echo -e "${BLUE} 备份文件统计信息 ${NC}"
echo -e "${BLUE}════════════════════════════════════════════════════${NC}"
echo
if [ -d "$LOCAL_BACKUP_DIR" ]; then
echo -e "${CYAN}本地备份目录: $LOCAL_BACKUP_DIR${NC}"
echo -e "${CYAN}最近备份文件:${NC}"
ls -lh "$LOCAL_BACKUP_DIR" | tail -5 | awk '{if(NR>1) printf " • %-40s %6s\n", $9, $5}'
fi
echo
}
# 主函数
main() {
print_header
echo -e "${BLUE}准备执行远程PostgreSQL备份任务...${NC}"
echo "📋 备份配置:"
echo " • 本地服务器: $(hostname -I | awk '{print $1}')"
echo " • 远程服务器: $REMOTE_HOST"
echo " • 远程数据库: $POSTGRESQL_DATABASE"
echo " • 本地备份目录: $LOCAL_BACKUP_DIR"
echo
# 检查sshpass
if ! check_sshpass; then
return 1
fi
# 检查远程连接
if ! check_remote_connection; then
return 1
fi
echo
# 执行远程备份
backup_postgresql_remote
local backup_result=$?
# 总结备份结果
echo
echo -e "${BLUE}════════════════════════════════════════════════════${NC}"
echo -e "${BLUE} 备份任务完成总结 ${NC}"
echo -e "${BLUE}════════════════════════════════════════════════════${NC}"
if [ $backup_result -eq 0 ]; then
print_success "🎉 PostgreSQL数据库远程备份完成！
✅ 远程备份: 成功 ($REMOTE_HOST)
✅ 本地保存: 成功
📁 本地备份目录: $LOCAL_BACKUP_DIR
📅 备份时间戳: $TIMESTAMP"
# 显示备份统计
show_backup_statistics
else
print_error "❌ PostgreSQL数据库远程备份失败！
请检查：
1. 远程服务器是否在线和SSH可达
2. 远程容器是否正常运行
3. 远程数据库连接信息是否正确
4. 本地备份目录权限是否正确"
fi
echo -e "${BLUE}远程备份脚本执行完毕！${NC}"
echo
return $backup_result
}
# 检查是否以root用户运行
if [ "$EUID" -ne 0 ]; then
print_error "请以root用户身份运行此脚本！"
exit 1
fi
# 运行主函数
main
执行效果：
远程执行备份脚本V2.0(postgresql)：
postgresql_remote_backup_client.sh
#!/bin/bash
# PostgreSQL数据库备份脚本 - 本地pg客户端版本
# 自动下载PostgreSQL客户端并连接远程PostgreSQL数据库进行备份
# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
CYAN='\033[0;36m'
NC='\033[0m' # No Color
# PostgreSQL远程数据库配置
PG_HOST="192.168.99.56"
PG_PORT="5432"
PG_USER="postgres"
PG_DATABASE="bim3dv2"
PG_PASSWORD="bim%2018"
# 本地备份存储目录
LOCAL_BACKUP_DIR="/data/data_bak/postgresql"
# PostgreSQL客户端配置
PG_CLIENT_DIR="/data/resources"
#依赖库配置
export LD_LIBRARY_PATH=/data/resources/pgsql/lib:$LD_LIBRARY_PATH
#此下载地址linux二进制包最高为10.23,使用备用下载源
#PG_DOWNLOAD_URL="https://get.enterprisedb.com/postgresql/postgresql-10.23-1-linux-x64-binaries.tar.gz"
PG_VERSION="13.3"
PG_DOWNLOAD_URL="https://repo.linuxjk.cn/%E8%BD%AF%E4%BB%B6%E5%8C%85/%E6%9C%8D%E5%8A%A1%E8%BD%AF%E4%BB%B6%E5%8C%85/postgresql/pg13%E7%BC%96%E8%AF%91%E5%AE%89%E8%A3%85%E7%9A%84%E4%BA%8C%E8%BF%9B%E5%88%B6%E5%8C%85/postgresql13.3_erjinzhi.tar.gz"
# 生成时间戳
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
# 打印醒目的标题
print_header() {
echo
echo -e "${BLUE}================================================${NC}"
echo -e "${BLUE} PostgreSQL数据库备份脚本 ${NC}"
echo -e "${BLUE} 目标服务器: $PG_HOST${NC}"
echo -e "${BLUE}================================================${NC}"
echo
}
# 打印成功消息
print_success() {
echo
echo -e "${GREEN}************************************************${NC}"
echo -e "${GREEN}$1${NC}"
echo -e "${GREEN}************************************************${NC}"
echo
}
# 打印错误消息
print_error() {
echo
echo -e "${RED}************************************************${NC}"
echo -e "${RED}错误: $1${NC}"
echo -e "${RED}************************************************${NC}"
echo
}
# 打印警告消息
print_warning() {
echo -e "${YELLOW}⚠️ 警告: $1${NC}"
}
# 打印信息消息
print_info() {
echo -e "${BLUE}ℹ️ $1${NC}"
}
# 下载并安装PostgreSQL客户端工具
download_pg_client() {
print_info "检查PostgreSQL客户端工具..."
# 检查是否已经安装
if [ -f "$PG_CLIENT_DIR/pgsql/bin/pg_dump" ]; then
print_info "PostgreSQL客户端已存在，跳过下载"
return 0
fi
print_info "开始下载PostgreSQL $PG_VERSION 客户端工具..."
# 创建客户端目录
mkdir -p "$PG_CLIENT_DIR"
cd "$PG_CLIENT_DIR" || {
print_error "无法创建目录: $PG_CLIENT_DIR"
return 1
}
# 下载PostgreSQL二进制包
print_info "正在下载: $PG_DOWNLOAD_URL"
if wget -q --show-progress "$PG_DOWNLOAD_URL" -O postgresql-client.tar.gz; then
print_info "下载完成 ✓"
else
print_error "下载失败！请检查网络连接或URL是否有效"
return 1
fi
# 解压文件
print_info "正在解压PostgreSQL客户端..."
if tar -xzf postgresql-client.tar.gz; then
print_info "解压完成 ✓"
# 清理下载的压缩包
rm -f postgresql-client.tar.gz
else
print_error "解压失败！"
return 1
fi
# 检查关键文件是否存在
if [ -f "$PG_CLIENT_DIR/pgsql/bin/pg_dump" ] && [ -f "$PG_CLIENT_DIR/pgsql/bin/psql" ]; then
print_success "PostgreSQL客户端工具安装成功！
📁 安装路径: $PG_CLIENT_DIR/pgsql/bin/
🛠️ 可用工具: pg_dump, psql, pg_restore 等"
# 显示版本信息
local pg_dump_version=$("$PG_CLIENT_DIR/pgsql/bin/pg_dump" --version)
print_info "pg_dump版本: $pg_dump_version"
return 0
else
print_error "PostgreSQL客户端工具安装失败！关键文件不存在"
return 1
fi
}
# 设置PostgreSQL客户端环境
setup_pg_environment() {
# 设置PostgreSQL客户端路径
export PATH="$PG_CLIENT_DIR/pgsql/bin:$PATH"
export LD_LIBRARY_PATH="$PG_CLIENT_DIR/pgsql/lib:$LD_LIBRARY_PATH"
# 验证pg_dump命令
if command -v pg_dump &> /dev/null; then
print_info "PostgreSQL客户端环境设置成功 ✓"
return 0
else
print_error "PostgreSQL客户端环境设置失败"
return 1
fi
}
# 检查远程PostgreSQL连接
check_pg_connection() {
print_info "正在检查远程PostgreSQL数据库连接..."
# 设置密码环境变量
export PGPASSWORD="$PG_PASSWORD"
# 测试连接（使用绝对路径确保使用正确的psql）
if "$PG_CLIENT_DIR/pgsql/bin/psql" -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -d "$PG_DATABASE" -c "\q" 2>/dev/null; then
print_info "远程PostgreSQL连接成功 ✓"
return 0
else
print_error "无法连接到远程PostgreSQL数据库！
请检查：
1. 远程服务器 $PG_HOST 是否在线
2. PostgreSQL服务是否启动 (端口:$PG_PORT)
3. 数据库用户名密码是否正确
4. 防火墙是否开放5432端口
5. pg_hba.conf是否允许远程连接"
return 1
fi
}
# 创建备份目录
create_backup_dir() {
if [ ! -d "$LOCAL_BACKUP_DIR" ]; then
mkdir -p "$LOCAL_BACKUP_DIR"
if [ $? -eq 0 ]; then
print_info "创建本地备份目录: $LOCAL_BACKUP_DIR"
else
print_error "无法创建备份目录: $LOCAL_BACKUP_DIR"
return 1
fi
else
print_info "备份目录已存在: $LOCAL_BACKUP_DIR"
fi
# 检查目录权限
if [ ! -w "$LOCAL_BACKUP_DIR" ]; then
print_error "备份目录无写权限: $LOCAL_BACKUP_DIR"
return 1
fi
return 0
}
# PostgreSQL备份函数
backup_postgresql() {
echo
echo -e "${BLUE}═══════════════════════════════════════════════════${NC}"
echo -e "${BLUE} 开始备份PostgreSQL数据库${NC}"
echo -e "${BLUE}═══════════════════════════════════════════════════${NC}"
echo
# 生成备份文件名
local backup_file="${PG_DATABASE}_${TIMESTAMP}.sql"
local backup_path="${LOCAL_BACKUP_DIR}/${backup_file}"
print_info "开始备份PostgreSQL数据库: $PG_DATABASE"
echo " • 远程服务器: $PG_HOST:$PG_PORT"
echo " • 备份数据库: $PG_DATABASE"
echo " • 备份文件: $backup_file"
echo " • 保存路径: $backup_path"
echo
print_warning "PostgreSQL数据库较大，大约30分钟左右备份完成，请勿中断..."
echo
# 设置密码环境变量
export PGPASSWORD="$PG_PASSWORD"
# 显示进度
print_info "正在执行备份，请耐心等待..."
# 执行pg_dump备份（使用绝对路径）
if "$PG_CLIENT_DIR/pgsql/bin/pg_dump" -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -d "$PG_DATABASE" \
--verbose --clean --if-exists --create --compress=0 \
--format=plain --no-owner --no-privileges \
> "$backup_path" 2>/dev/null; then
# 检查备份文件大小
if [ -s "$backup_path" ]; then
local file_size=$(du -h "$backup_path" | cut -f1)
print_success "✅ PostgreSQL备份成功完成！
📁 备份位置: $backup_path
📄 备份文件: $backup_file
📊 文件大小: $file_size
⏱️ 备份时间戳: $TIMESTAMP"
# 压缩备份文件以节省空间
print_info "正在压缩备份文件..."
if gzip "$backup_path"; then
local compressed_size=$(du -h "${backup_path}.gz" | cut -f1)
print_info "压缩完成！压缩后大小: $compressed_size"
fi
return 0
else
print_error "备份文件为空，备份可能失败！"
rm -f "$backup_path" 2>/dev/null
return 1
fi
else
print_error "PostgreSQL备份失败！请检查：
1. 远程数据库服务是否正常
2. 网络连接是否稳定
3. 数据库权限是否足够
4. 磁盘空间是否充足"
rm -f "$backup_path" 2>/dev/null
return 1
fi
}
# 清理旧备份文件（保留最近7天）
cleanup_old_backups() {
print_info "清理7天前的旧备份文件..."
if [ -d "$LOCAL_BACKUP_DIR" ]; then
local deleted_files=$(find "$LOCAL_BACKUP_DIR" -name "${PG_DATABASE}_*.sql.gz" -mtime +7 -delete -print | wc -l)
if [ "$deleted_files" -gt 0 ]; then
print_info "清理了 $deleted_files 个旧备份文件"
else
print_info "没有需要清理的旧备份文件"
fi
fi
}
# 显示备份统计信息
show_backup_statistics() {
echo
echo -e "${BLUE}════════════════════════════════════════════════════${NC}"
echo -e "${BLUE} 备份文件统计信息 ${NC}"
echo -e "${BLUE}════════════════════════════════════════════════════${NC}"
echo
if [ -d "$LOCAL_BACKUP_DIR" ]; then
echo -e "${CYAN}备份目录: $LOCAL_BACKUP_DIR${NC}"
echo -e "${CYAN}最近5个备份文件:${NC}"
ls -lht "$LOCAL_BACKUP_DIR"/${PG_DATABASE}_*.sql.gz 2>/dev/null | head -5 | while read line; do
local filename=$(echo "$line" | awk '{print $9}')
local filesize=$(echo "$line" | awk '{print $5}')
local filedate=$(echo "$line" | awk '{print $6, $7, $8}')
[ -n "$filename" ] && echo " • $(basename "$filename") - $filesize ($filedate)"
done
echo
local total_files=$(ls -1 "$LOCAL_BACKUP_DIR"/${PG_DATABASE}_*.sql.gz 2>/dev/null | wc -l)
local total_size=$(du -sh "$LOCAL_BACKUP_DIR" 2>/dev/null | cut -f1)
echo -e "${CYAN}总计: $total_files 个备份文件，占用空间: $total_size${NC}"
fi
echo
}
# 主函数
main() {
print_header
echo -e "${BLUE}准备执行PostgreSQL备份任务...${NC}"
echo "📋 备份配置:"
echo " • 本地服务器: $(hostname -I | awk '{print $1}' || echo 'localhost')"
echo " • 远程数据库: $PG_HOST:$PG_PORT"
echo " • 数据库名: $PG_DATABASE"
echo " • 用户名: $PG_USER"
echo " • 备份目录: $LOCAL_BACKUP_DIR"
echo " • 客户端安装目录: $PG_CLIENT_DIR"
echo
# 下载并安装PostgreSQL客户端
if ! download_pg_client; then
exit 1
fi
# 设置PostgreSQL环境
if ! setup_pg_environment; then
exit 1
fi
# 创建备份目录
if ! create_backup_dir; then
exit 1
fi
# 检查PostgreSQL连接
if ! check_pg_connection; then
exit 1
fi
echo
# 执行备份
if backup_postgresql; then
# 清理旧备份
cleanup_old_backups
# 显示统计信息
show_backup_statistics
print_success "🎉 PostgreSQL数据库备份完成！
✅ 客户端安装: 成功 (PostgreSQL $PG_VERSION)
✅ 远程连接: 成功 ($PG_HOST:$PG_PORT)
✅ 数据备份: 成功
✅ 文件压缩: 成功
📁 备份目录: $LOCAL_BACKUP_DIR
📅 备份时间: $TIMESTAMP"
echo -e "${BLUE}备份脚本执行完毕！${NC}"
exit 0
else
print_error "❌ PostgreSQL数据库备份失败！"
exit 1
fi
}
# 检查必要的系统命令
check_requirements() {
local missing_commands=()
for cmd in wget tar gzip; do
if ! command -v "$cmd" &> /dev/null; then
missing_commands+=("$cmd")
fi
done
if [ ${#missing_commands[@]} -gt 0 ]; then
print_error "缺少必要的系统命令: ${missing_commands[*]}
请先安装这些工具：
• 在CentOS/RHEL上: yum install -y wget tar gzip
• 在Debian/Ubuntu上: apt-get install -y wget tar gzip"
exit 1
fi
}
# 检查是否以root用户运行
if [ "$EUID" -ne 0 ]; then
print_error "请以root用户身份运行此脚本！"
exit 1
fi
# 检查系统要求
check_requirements
# 运行主函数
main
执行效果：
