python + crontab 实现 MySQL 数据库自动备份

野性酷女 2022-03-28 08:19 186阅读 0赞

# [python + crontab 实现 MySQL 数据库自动备份][python _ crontab _ MySQL][\[复制链接\]][python _ crontab _ MySQL] #

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <div> 电梯直达  
      <a title="跳转到指定楼层" rel="nofollow"><img src="http://www.okpython.com/static/image/common/fj_btn.png" alt="跳转到指定楼层"></a> 
     </div> 
     <strong><a href="http://www.okpython.com/thread-2612-1-1.html" title="您的朋友访问此链接后，您将获得相应的积分奖励" rel="nofollow"><em>1</em><sup>#</sup></a></strong> 
     <div> 
      <div></div> 
      <div> 
       <img src="http://www.okpython.com/static/image/common/online_admin.gif" alt=""> 
       <em>发表于 2009-2-6 23:10:10</em> 
       <span>|</span> 
       <a href="http://www.okpython.com/forum.php?mod=viewthread&amp;tid=2612&amp;page=1&amp;authorid=1" rel="nofollow">只看该作者</a> 
       <span>|</span> 
       <a href="http://www.okpython.com/forum.php?mod=viewthread&amp;tid=2612&amp;extra=page%3D14&amp;ordertype=1" rel="nofollow">倒序浏览</a> 
       <div style="float:right"> 
        <div style="line-height:12px"> 
         <img src="http://share.baidu.com/static/images/type-button-1.jpg" alt=""> 
        </div> 
       </div> 
      </div> 
     </div> 
    </div> 
    <div> 
     <div> 
      <div> 
       <table> 
        <tbody> 
         <tr> 
          <td>很早以前为了实现 MYSQL 数据库自动备份，在 FreeBSD 下用SH写了一个脚本放到 CRONTAB 中，便轻松搞定。<br> 今日突发奇想，想用 Python 做个脚本实现 MYSQL 数据库自动备份，于是折腾了一翻，效果不错，呵呵。<br> <br> 测试环境：ubuntu704 + python2.5 + mysql5<br> <br> 一、程序清单：auto_backup.py<br> (请确定当前用户对备份目录、日志目录有一定权限)<br> #**********************************************************************************************************************<br> #!/usr/bin/env python<br> # -*- coding: utf8 -*-<br> # Mysql_backup @ Python<br> # Functions: 自动创建当日备份目录、日志记录、压缩备份文件<br> # Created By Fufay on 2007-04-30,Version 0.1<br> <br> import sys,os,time<br> <br> # ----------------------------- 初始信息 --------------------------------------------<br> mysql_usr = 'root'&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;# mysql 用户<br> mysql_pwd = '780408ss'&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;# mysql 密码<br> mysql_db = 'mysql'&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;# mysql 数据库<br> mysql_charset = 'gb2312'&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; # 数据库编码<br> bk_path&nbsp; &nbsp;= '/home/fufay/Python/backup/'&nbsp; &nbsp; # 备份目录<br> cmd_path = '/usr/bin/'&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; # 命令 mysqldump 的路径<br> Logs_path = bk_path + 'Logs'&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;# 日志文件全路径<br> # ----------------------------- 初始完毕 --------------------------------------------<br> <br> # 写日志函数<br> def writeLogs(filename,contents):<br> &nbsp; &nbsp; f = file(filename,'aw')<br> &nbsp; &nbsp; f.write(contents)<br> &nbsp; &nbsp; f.close()<br> <br> # 备份目录以当日日期为名<br> today = bk_path + time.strftime('%Y-%m-%d')<br> # 数据库备份名称以备份时间为名<br> fname = today + os.sep + time.strftime('%H%M%S') + '.gz'<br> <br> # 创建当日备份目录<br> if not os.path.exists(today):<br> &nbsp; &nbsp; Msg = '-'*30 + time.strftime('%Y-%m-%d,%H:%M:%S') + '-'*30 + '\n'<br> &nbsp; &nbsp; if(os.mkdir(today)) == None:<br> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Msg += '** 成功创建备份目录： ' + today + '\n\n'<br> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;writeLogs(Logs_path,Msg)<br> &nbsp; &nbsp; else:<br> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Msg += '!! 创建备份目录： ' + today + '失败，请检查目录是否可写！\n\n'<br> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;writeLogs(Logs_path,Msg)<br> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;sys.exit()<br> <br> # 备份 Mysql 命令<br> cmd_dump = "%smysqldump -u%s -p%s --default-character-set=%s --opt %s | gzip &gt; %s" % \<br> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; (cmd_path,mysql_usr,mysql_pwd,mysql_charset,mysql_db,fname)<br> <br> # 执行备份命令<br> if os.system(cmd_dump) == 0:<br> &nbsp; &nbsp; writeLogs(Logs_path,'数据备份为： ' + fname + '\n')<br> else:<br> &nbsp; &nbsp; writeLogs(Logs_path,'数据备份失败！\n')<br> <br> # over<br> #**********************************************************************************************************************<br> <br> 二、crontab 相关。<br> 打开终端，将 auto_backup.py 拷贝到某个文件夹（如：/home/fufay/Python/），并将其权限设定为 “允许执行”：<br> &nbsp; &nbsp;&nbsp; &nbsp; chmod a+x ./auto_backup.py<br> <br> 设定 crontab:<br> &nbsp; &nbsp;&nbsp; &nbsp;crontab -e<br> 如：<br> */30&nbsp; &nbsp; *&nbsp; &nbsp;*&nbsp; &nbsp;*&nbsp; &nbsp;*&nbsp; &nbsp;/home/fufay/Python/auto_backup.py<br> 保存退出<br> ok，这样系统每隔30分钟(当然可以设置每天/每周等等)就会运行一下 auto_backup.py 这个文件做数据库备份工作了。</td> 
         </tr> 
        </tbody> 
       </table> 
      </div> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

[python _ crontab _ MySQL]: http://www.okpython.com/thread-2612-1-1.html