[Python] 데이터베이스 백업스크립트(Oracle, PgSQL, MySQL)

자료출처: https://madlife.co.kr

#!/usr/bin/env python
# frenzy27 at passmail.to
# frenzy at madlife.co.kr

import os
import commands
DATE = commands.getoutput('date "+%Y%m%d"')
BACK_DIR = "/home/DUMP"
MYSQLD = "/usr/local/mysql/"
POSTGRES = "postgres"

# mysql 백업 (root 패스워드를 알경우)
def frenzyMysql(dbhost, dbpass, term):
        global DATE, BACK_DIR, MYSQLD
        if(os.path.exists(BACK_DIR) == 0):
                os.mkdir(BACK_DIR)
        os.system("chmod 777 %s" %BACK_DIR)
        SHELL = "%s/bin/mysqldump --all-databases -h'%s' -uroot -p'%s' > %s/mysql_%s.dmp" %(MYSQLD, dbhost, dbpass, BACK_DIR, DATE)
        os.system(SHELL)
        os.system("find %s -ctime +%s -exec rm -f {} \;" %(BACK_DIR,
term))

# mysql 백업 (root 패스워드를 모를 경우)
def frenzyMysqlskipGrant(dbhost, term):
        global DATE, BACK_DIR, MYSQLD
        if(os.path.exists(BACK_DIR) == 0):
                os.mkdir(BACK_DIR)
        os.system("chmod 777 %s" %BACK_DIR)
        killPid = commands.getoutput("ps ax|grep mysqld|awk -F\" \" '{print $1}'")
        killpidList = killPid.split("\n")
        for killid in killpidList:
                os.system("kill -9 %s" % killid)
        os.system("sleep 3")
        result = os.system("cd %s;./bin/safe_mysqld --skip-grant &" %MYSQLD)
        os.system("sleep 4")
        if(result == 0):
                SHELL = "%s/bin/mysqldump --all-databases -h'%s' -uroot -p' ' > %s/mysql_%s.dmp" %(MYSQLD, dbhost, BACK_DIR, DATE)
                os.system(SHELL)
        os.system("%s/bin/mysqladmin -uroot -p' ' flush-privileges" %MYSQLD)
        os.system("find %s -ctime +%s -exec rm -f {} \;" %(BACK_DIR, term))

# postgreSQL 백업스크립트
def frenzyPostgreSQL(term):
        global DATE, BACK_DIR
        if(os.path.exists(BACK_DIR) == 0):
                os.mkdir(BACK_DIR)
        os.system("chmod 777 %s" %BACK_DIR)

        SHELL = "su -l %s -c \"pg_dumpall > pgsql_%s.dmp\"" %(POSTGRES, DATE)
        os.system(SHELL)
        os.system("mv /usr/local/pgsql/pgsql_%s.dmp %s" %(DATE, BACK_DIR))
        os.system("find %s -ctime +%s -exec rm -f {} \;" %(BACK_DIR, term))

# oracle 백업스크립트
def frenzyOracle(dbuser, dbpass, term):
        global DATE, BACK_DIR
        if(os.path.exists(BACK_DIR) == 0):
                os.mkdir(BACK_DIR)
        os.system("chmod 777 %s" %BACK_DIR)
        SHELL = "su -l oracle -c \"exp %s/%s grants=y file=%s/oracle_%s.dmp\"" %(dbuser, dbpass, BACK_DIR, DATE)
        os.system(SHELL)
        os.system("find %s -ctime +%s -exec rm -f {} \;" %(BACK_DIR, term))

if __name__ == "__main__":
        # mysql dbhost = "localhost", dbpass = "dbpassword", term = "14"
        # term 은 백업주기이다. 숫자로 기재한다. 입력할때는 " 로 입력문자열을 감싼다.
        #frenzyMysql("localhost", "passwordinput", "6")
        #frenzyMysqlskipGrant("localhost", "6")
        #frenzyPostgreSQL(term)
        #frenzyOracle(dbuser, dbpass, term)