分类： 便签

mysql 自动备份脚本

Posted on by admin


#!/bin/bash
#
# mysqldump -uroot -ppass test > /code/mysql_backup/test_$(date +%Y%m%d_%H%M%S).sql
# ===================== 配置项（根据你的环境修改） =====================
# MySQL 连接信息
MYSQL_USER="root" # MySQL 用户名
MYSQL_PASSWORD="root" # MySQL 密码（若为空，删除 -p 后面的内容）
DATABASE_NAME="test" # 要备份的数据库名（多个库用空格分隔，或写 --all-databases 备份所有）
MYSQL_HOST="127.0.0.1" # MySQL 主机
MYSQL_PORT="3306" # MySQL 端口

# 备份存储路径（确保目录存在，否则手动创建：mkdir -p /data/mysql_backup）
BACKUP_DIR="/code/admin/mysql_backup"

# 保留备份的天数（超过则自动删除，避免磁盘占满，0 表示不删除）
KEEP_DAYS=7

# ===================== 备份逻辑 =====================
# 获取 ISO 格式的年月（YYYY-MM），比如 2025-12
ISO_MONTH=$(date +%Y-%m)

# 生成备份文件名：数据库名_ISO年月_时间戳.sql.gz（压缩节省空间）
# BACKUP_FILENAME="${DATABASE_NAME}_${ISO_MONTH}_$(date +%Y-%m-%d_%H%M%S).sql.gz"
BACKUP_FILENAME="${DATABASE_NAME}-$(date +%Y-%m%d_%H%M%S).sql.gz"

BACKUP_PATH="${BACKUP_DIR}/${BACKUP_FILENAME}"

# 日志文件路径
LOG_FILE="${BACKUP_DIR}/mysql_backup.log"

# 记录备份开始日志
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 开始备份数据库 ${DATABASE_NAME}..." >> ${LOG_FILE}

# 执行 mysqldump 并压缩（推荐压缩，大幅减小文件体积）
mysqldump -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASSWORD} ${DATABASE_NAME} | gzip > ${BACKUP_PATH}

# 检查备份是否成功
if [ $? -eq 0 ]; then
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 备份成功！文件路径：${BACKUP_PATH}" >> ${LOG_FILE}

# 清理旧备份（可选）
if [ ${KEEP_DAYS} -gt 0 ]; then
find ${BACKUP_DIR} -name "${DATABASE_NAME}_*.sql.gz" -mtime +${KEEP_DAYS} -delete
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 已删除 ${KEEP_DAYS} 天前的旧备份" >> ${LOG_FILE}
fi
else
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 备份失败！" >> ${LOG_FILE}
exit 1
fi

exit 0

SQL-生成时间日期和车牌

Posted on by admin
WITH RECURSIVE index_loop AS (
    SELECT 1 AS idx  -- 起始索引（可改为0）
    UNION ALL
    SELECT idx + 1   -- 索引自增
    FROM index_loop
    WHERE idx < 100   -- 循环次数（此处为10次）
)
SELECT idx ,
FROM_UNIXTIME(
    FLOOR(
      UNIX_TIMESTAMP('2023-01-01 00:00:00') + 
      RAND() * (
        UNIX_TIMESTAMP('2023-12-31 23:59:59') - 
        UNIX_TIMESTAMP('2023-01-01 00:00:00')
      )
    )
  ) AS random_datetime,
CONCAT(
    ELT(FLOOR(1 + RAND() * 34), '京','津','冀','晋','蒙','辽','吉','黑','沪','苏','浙','皖','闽','赣','鲁','豫','鄂','湘','粤','桂','琼','渝','川','贵','云','藏','陕','甘','青','宁','新','港','澳','台'),
    ELT(FLOOR(1 + RAND() * 24), 'A','B','C','D','E','F','G','H','J','K','L','M','N','P','Q','R','S','T','U','V','W','X','Y','Z'),
    
    CONCAT(
        ELT(FLOOR(1 + RAND() * 34), '0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','J','K','L','M','N','P','Q','R','S','T','U','V','W','X','Y','Z'),
        ELT(FLOOR(1 + RAND() * 34), '0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','J','K','L','M','N','P','Q','R','S','T','U','V','W','X','Y','Z'),
        ELT(FLOOR(1 + RAND() * 34), '0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','J','K','L','M','N','P','Q','R','S','T','U','V','W','X','Y','Z'),
        ELT(FLOOR(1 + RAND() * 34), '0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','J','K','L','M','N','P','Q','R','S','T','U','V','W','X','Y','Z'),
        ELT(FLOOR(1 + RAND() * 34), '0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','J','K','L','M','N','P','Q','R','S','T','U','V','W','X','Y','Z')
    )
) AS license_plate
FROM index_loop;