自动备份mysql数据库，并清空log日志数据

abloz 2010-03-17
2010-03-17

周海汉 /文

2010.3.17

mysql数据库有几个表分别记录cpu等占用信息的日志，有大量记录。这些日志对于定位问题很重要，但如果没有问题，则比较多余。运维手工清除不仅麻烦，而且容易出错。因此，写一个脚本，放到crontab里，定期备份和清除日志。

  1. #!/bin/sh

  2. File: /root/bcdb.sh

  3. Author: zhouhh

  4. Date: 2010-3-17

  5. Database info

  6. DB_NAME=(“systemdb” “localdb”)
  7. DB_USER=”root”
  8. DB_PASS=”xxxxxx”

  9. Others vars

  10. BIN_DIR=”/usr/bin”
  11. BCK_DIR=”/var/lib/mysql/backup”
  12. SYSTABLES=(“history1” “history2” “cpu_history”)
  13. LOCALTABLES=(“cpulog”)
  14. DATE=date +%F
  15. #backup systemdb
  16. for db in ${DB_NAME[@]}
  17. do
  18. echo
  19. echo “backup $db to $BCK_DIR/${db}_$DATE.gz…”
  20. $BIN_DIR/mysqldump –opt -u$DB_USER -p$DB_PASS $db gzip > “$BCK_DIR/${db}_$DATE.gz”
  21. echo “backup $db finished!”
  22. done
  23. #clear systemdb tables;
  24. echo
  25. echo “clear table logs…”
  26. for tb in ${SYSTABLES[@]}
  27. do
  28. echo “clear $tb…”
  29. $BIN_DIR/mysql -u$DB_USER -p$DB_PASS ${DB_NAME[0]} -e “truncate table $tb;”
  30. done;
  31. #clear localdb tables
  32. for tb in ${LOCALTABLES[@]}
  33. do
  34. echo “clear $tb…”
  35. $BIN_DIR/mysql -u$DB_USER -p$DB_PASS ${DB_NAME[1]} -e “truncate table $tb;”
  36. done;
  37. echo
  38. echo ‘clear table logs successfully’
#!/bin/sh # File: /root/bcdb.sh # Author: zhouhh # Date: 2010-3-17 # Database info DB_NAME=(“systemdb” “localdb”) DB_USER=”root” DB_PASS=”xxxxxx” # Others vars BIN_DIR=”/usr/bin” BCK_DIR=”/var/lib/mysql/backup” SYSTABLES=(“history1” “history2” “cpu_history”) LOCALTABLES=(“cpulog”) DATE=date +%F #backup systemdb for db in ${DB_NAME[@]} do echo echo “backup $db to $BCK_DIR/${db}_$DATE.gz…” $BIN_DIR/mysqldump –opt -u$DB_USER -p$DB_PASS $db gzip > “$BCK_DIR/${db}_$DATE.gz” echo “backup $db finished!” done #clear systemdb tables; echo echo “clear table logs…” for tb in ${SYSTABLES[@]} do echo “clear $tb…” $BIN_DIR/mysql -u$DB_USER -p$DB_PASS ${DB_NAME[0]} -e “truncate table $tb;” done; #clear localdb tables for tb in ${LOCALTABLES[@]} do echo “clear $tb…” $BIN_DIR/mysql -u$DB_USER -p$DB_PASS ${DB_NAME[1]} -e “truncate table $tb;” done; echo echo ‘clear table logs successfully’

本脚本备份systemdb和localdb两个库，并清除两个库中的表。在/var/lib/mysql中建立backup目录，脚本执行时将数据库备份到/var/lib/mysql/backup/数据库名_日期.gz

也可以在脚本中检查一下：

#check backup dir
if ! [ -d ${BCK_DIR} ]
then
echo “${BCK_DIR} does not exist,create it…”
mkdir ${BCK_DIR}
fi

另：

清除mysql系统log，3天前/var/log/mysql
mysql -uroot -e ‘PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);’
#清除指定log
PURGE MASTER LOGS TO ‘mysql-bin.000930’;

定期执行脚本：

[root@server ~]# crontab -e

增加：

00 05 */3 * * /root/bcdb.sh

每3天的凌晨5点备份清空一次。

如非注明转载, 均为原创. 本站遵循知识共享CC协议,转载请注明来源

FEATURED TAGS
css vc6 http automake linux make makefile voip 乱码 awk flash vista vi vim javascript pietty putty ssh posix subversion svn windows 删除 编译 多线程 wxwidgets ie ubuntu 开源 c python bash 备份 性能 scp 汉字 log ruby 中文 bug msn nginx php shell wordpress mqueue android eclipse java mac ios html5 js mysql protobuf apache hadoop install iocp twisted centos mapreduce hbase thrift tutorial hive erlang lucene hdfs sqoop utf8 filter 草原 yarn ganglia 恢复 scrapy django fsimage flume tail flume-ng mining scala go kafka gradle cassandra baas spring postgres maven mybatis mongodb https nodejs 镜像 心理学 机器学习 Keras theano anaconda docker spark akka-http json 群论 区块链 加密 抽象代数 离散对数 同余 欧拉函数 扩展欧几里德算法 ES6 node-inspect debug win10 vscode 挖矿
FRIENDS