WishMeLz

生活其实很有趣

shell脚本备份数据库

 #!/bin/bash

# 设置变量
BACKUP_DIR="/backsql"  # 本地备份目录
MYSQL_USER="user"  # MySQL 用户名
MYSQL_PASSWORD="5CYGfYhjEXE4STY"  # MySQL 密码
MYSQL_HOST="127.0.1"  # MySQL 服务器地址,如果是远程服务器,请修改为相应的IP地址
MYSQL_PORT="22619"  # MySQL 端口号
DATABASE_NAME="detabase"  # 要备份的数据库名称

# 备份文件名格式:数据库名_日期.sql
BACKUP_FILE="$BACKUP_DIR/$DATABASE_NAME_$(date +\%Y-\%m-\%d).sql"

# 创建备份目录(如果不存在)
mkdir -p "$BACKUP_DIR"

# 备份数据库
mysqldump -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" --set-gtid-purged=OFF  -p"$MYSQL_PASSWORD" "$DATABASE_NAME" > "$BACKUP_FILE"

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "Database backup successful: $BACKUP_FILE"
else
    echo "Database backup failed!" >&2
fi

# 可选:删除超过7天的旧备份
find "$BACKUP_DIR" -type f -name "*.sql" -mtime +7 -exec rm {} \;

日志

mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
Database backup successful: /app/backsql/2025-01-16.sql
----------------------------------------------------------------------------
★[2025-01-16 01:00:03] Successful
----------------------------------------------------------------------------
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
Database backup successful: /app/backsql/2025-01-17.sql
----------------------------------------------------------------------------
★[2025-01-17 01:00:03] Successful
----------------------------------------------------------------------------
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
Database backup successful: /app/backsql/2025-01-18.sql
----------------------------------------------------------------------------
★[2025-01-18 01:00:03] Successful
----------------------------------------------------------------------------
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
Database backup successful: /app/backsql/2025-01-19.sql
----------------------------------------------------------------------------
★[2025-01-19 01:00:02] Successful
----------------------------------------------------------------------------
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
Database backup successful: /app/backsql/2025-01-20.sql
----------------------------------------------------------------------------
★[2025-01-20 01:00:02] Successful
----------------------------------------------------------------------------
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
Database backup successful: /app/backsql/2025-01-21.sql
----------------------------------------------------------------------------
★[2025-01-21 01:00:04] Successful
----------------------------------------------------------------------------
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
Database backup successful: /app/backsql/2025-01-22.sql
----------------------------------------------------------------------------
★[2025-01-22 01:00:03] Successful
----------------------------------------------------------------------------