mysql 按条件备份所有表

mysqldump -u username -p database_name --tables table1 table2 --where="condition" > backup.sql

GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON database_name.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

mysqldump -u username -p database_name --tables table1 table2 --where="condition" | gzip > backup.sql.gz

#!/bin/bash

# 数据库连接信息
DB_USER="username"
DB_PASS="password"
DB_NAME="database_name"

# 备份目录
BACKUP_DIR="/path/to/backup"

# 备份时间
DATE=$(date +%Y%m%d%H%M%S)

# 备份命令
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME --tables table1 table2 --where="created_at > '2023-01-01'" > $BACKUP_DIR/backup_$DATE.sql

# 压缩备份文件
gzip $BACKUP_DIR/backup_$DATE.sql

# 删除超过7天的备份文件
find $BACKUP_DIR -type f -name "backup_*.sql.gz" -mtime +7 -exec rm {} \;

0 0 * * * /path/to/backup.sh