Esse script realiza backup de bancos de dados MySQL e PostgreSQL. Os arquivos de backup s\u00e3o compactados e s\u00e3o preservados os “N” \u00faltimos arquivos de backup, conforme especificado na vari\u00e1vel KEEP_DAYS. Os bancos a serem salvos devem ser especificados no arquivo CONF.<\/p>\n
#!\/usr\/bin\/python\r\n# -*- coding: UTF-8 -*-\r\n# Programa: backupdb.py\r\n# Fun\u00e7\u00e3o: Realiza backup de bancos de dados\r\n# Vers\u00e3o: 2.0 (salva databases individuais)\r\n#\r\n# Layout do arquivo conf\r\n# ----------------------\r\n# SGBD SERVERNAME HOSTNAME USERNAME PASSWORD\r\n# (m|p) (srvname) (srv fqdn) (usr name) (usr pwd)\r\n#\r\n# SGBD........: m - mySql, p - PostgreSQL\r\n# SERVERNAME..: Nome do servidor (apenas para documenta\u00e7\u00e3o)\r\n# HOSTNAME....: FQDN do servidor (usado para conectar ao banco)\r\n# USERNAME....: Userid para acesso ao banco\r\n# PASSWORD....: Password para o userid\r\n# \r\n# Dependencias\r\n# ------------\r\n# - mysql\r\n# - mysqldump\r\n# - pg_dump\r\n# - psql\r\n# \r\n# OBS1: \r\n# Para o Postgre aceitar conex\u00f5es externas, editar o arquivo pg_hba.conf \r\n# no servidor, incluindo a seguinte linha abaixo de \"#IPv4 local connections\"\r\n#\r\n# host all all \/32 md5\r\n#\r\n# OBS2: \r\n# Para backup do PostgreSQL \u00e9 necess\u00e1rio criar um arquivo \"\/root\/.pgpass\",\r\n# considerando que esse script ser\u00e1 executado pelo usu\u00e1rio \"root\",\r\n# com o seguinte conte\u00fado:\r\n#\r\n# #hostname:port:database:username:password\r\n# :5432:*:postgres:\r\n#\r\n# No arquivo conf, especificar o mesmo usu\u00e1rio do arquivo .pgpass,\r\n# no exemplo acima, \"postgres\".\r\n\r\nimport commands\r\nimport os, sys, time, glob\r\n\r\n#Par\u00e2metros\r\nTEST = False # Flag teste\r\nCONF_FILE = \"backupdb.conf\" # Nome do arquivo conf\r\nBKP_DIR = \"\/mnt\/data\/backup\/backupdb\" # Dir. para arqs de backup\r\nLOG_DIR = \"\/mnt\/data\/backup\/backupdb\" # Dir. para arqs de log\r\nKEEP_DAYS = 3 # Nro. de dias a preservar\r\n#fim Par\u00e2metros\r\n\r\n#Vari\u00e1veis globais\r\nBASENAME = \"\" # Nome base p\/ arquivo.\r\n\r\n\r\ndef main():\r\n global TEST, BASENAME, KEEP_DAYS, LOG_DIR, BKP_DIR\r\n \r\n #Trata caminhos dos arquivos\r\n BKP_DIR = BKP_DIR.strip()\r\n if (BKP_DIR.endswith(\"\/\") == False):\r\n BKP_DIR += \"\/\"\r\n #endIf\r\n LOG_DIR = LOG_DIR.strip()\r\n if (LOG_DIR.endswith(\"\/\") == False):\r\n LOG_DIR += \"\/\"\r\n #endIf\r\n \r\n #monta nome base do arquivo \r\n t = time.localtime(time.time())\r\n BASENAME = \"BKP%d%02d%02d\" % (t[0], t[1], t[2])\r\n logfile = LOG_DIR+BASENAME+\".log\"\r\n \r\n if not(TEST):\r\n #captura print para arquivo de log\r\n saveout = sys.stdout\r\n fsock = open(logfile, 'w')\r\n sys.stdout = fsock\r\n #endIf\r\n \r\n #In\u00edcio\r\n printLogMsg(\"Inicio de execucao.\")\r\n \r\n #Rotina de backup\r\n doBackup()\r\n \r\n #Remove arquivos antigos\r\n cleanUpFiles(BKP_DIR, \"BKP*\", KEEP_DAYS)\r\n \r\n #Fim\r\n printLogMsg(\"Fim de execucao.\")\r\n \r\n if not(TEST):\r\n #Encerra captura do print\r\n sys.stdout = saveout\r\n fsock.close()\r\n #endIf\r\n#end main\r\n\r\n\r\ndef doBackup():\r\n \"\"\"Realiza backup dos bancos de dados\"\"\"\r\n global CONF_FILE\r\n\r\n conf = os.path.dirname(sys.argv[0]) + \"\/\" + CONF_FILE\r\n fconf = open(conf)\r\n\r\n #Processa backups a realizar\r\n for line in fconf:\r\n ln = line.split()\r\n if (len(ln) > 0):\r\n if (ln[0].lower() == \"m\"):\r\n doMySqlBkp(ln)\r\n elif (ln[0].lower() == \"p\"):\r\n doPostBkp(ln)\r\n #endIf\r\n #endIf\r\n #endFor\r\n\r\n fconf.close()\r\n#end doBackup\r\n\r\n\r\ndef doMySqlBkp(args):\r\n \"\"\"Realiza backup de banco MySQL\"\"\"\r\n global BASENAME, BPK_DIR\r\n\r\n #Nomes dos arquivos de backup e compactados\r\n bkpname = BKP_DIR+BASENAME+\"_\"+args[1]+\"_My\"\r\n tarname = BKP_DIR+BASENAME+\"_\"+args[1]+\"_My.tar.bz2\"\r\n \r\n msg = \"Iniciando backup do servidor %s (MySQL)\" % (args[1])\r\n printLogMsg(msg)\r\n \r\n #Relaciona bancos de dados\r\n cmd = \"mysql -h %s -u %s -p%s -Bse 'show databases'\" % \\\r\n (args[2], args[3], args[4])\r\n var = commands.getoutput(cmd)\r\n dbs = var.split(\"\\n\")\r\n for db in dbs:\r\n if (db.strip() != \"\"):\r\n fbkp = bkpname + \"-\" + db + \".sql\"\r\n #Realiza backup\r\n printLogMsg(\"Fazendo dump do banco %s.\" % (db))\r\n opt = db + \" --skip-opt --add-lock\"\r\n cmd = \"mysqldump -h %s -u %s -p%s %s > %s\" %\\\r\n (args[2], args[3], args[4], opt, fbkp)\r\n var = commands.getoutput(cmd)\r\n if (len(var.strip()) > 0):\r\n printLogMsg(var)\r\n #endIf\r\n #endIf\r\n #endFor\r\n \r\n #Compacta arquivo\r\n fbkp = bkpname + \"-*\"\r\n tarFile(fbkp, tarname)\r\n#end doMySqlBkp\r\n\r\n\r\ndef doPostBkp(args):\r\n \"\"\"Realiza backup de banco PostgreSQL\"\"\"\r\n global BASENAME, BKP_DIR\r\n\r\n #Nomes dos arquivos de backup e compactados\r\n bkpname = BKP_DIR+BASENAME+\"_\"+args[1]+\"_Pg\"\r\n tarname = BKP_DIR+BASENAME+\"_\"+args[1]+\"_Pg.tar.bz2\"\r\n \r\n msg = \"Iniciando backup do servidor %s (PostgreSQL)\" % (args[1])\r\n printLogMsg(msg)\r\n\r\n #Relaciona bancos de dados\r\n cmd = \"psql -h %s -U %s -q -c '\\l'\" % (args[2], args[3]) + \\\r\n \" | sed -n 4,\/\\eof\/p | grep -v rows\\) | awk {'print $1'}\"\r\n var = commands.getoutput(cmd)\r\n dbs = var.split(\"\\n\")\r\n for db in dbs:\r\n if (db.strip() != \"\"):\r\n fbkp = bkpname + \"-\" + db + \".sql\"\r\n #Realiza backup\r\n printLogMsg(\"Fazendo dump do banco %s.\" % (db))\r\n cmd = \"pg_dump %s -h %s -U %s > %s\" %\\\r\n (db, args[2], args[3], fbkp)\r\n var = commands.getoutput(cmd)\r\n if (len(var.strip()) > 0):\r\n printLogMsg(var)\r\n #endIf\r\n #endIf\r\n #endFor\r\n \r\n #Compacta arquivo\r\n fbkp = bkpname + \"-*\"\r\n tarFile(fbkp, tarname)\r\n#end doPostBkp\r\n\r\n#----- Fun\u00e7\u00f5es Genericas -----------------------------------------------------\r\n\r\ndef cleanUpFiles(fdir, mask, age):\r\n \"\"\"Remove arquivos que atendam o padr\u00e3o 'mask' com mais de 'age' dias\"\"\"\r\n\r\n printLogMsg(\"Verificando remocao arquivos antigos...\")\r\n \r\n #Data\/Hora de corte\r\n tm = time.time() - (age * 60 * 60 * 24)\r\n \r\n #Processa arquivos que atendam ao padr\u00e3o\r\n pattern = fdir + mask\r\n for fname in glob.glob(pattern):\r\n if (os.stat(fname).st_mtime < tm): fn = os.path.basename(fname) printLogMsg(\"Removendo arquivo: %s\" % (fn)) os.remove(fname) #endIf #endFor #end cleanUpFiles def tarFile(fname, tarname): \"\"\"Compacta arquivo e remove arquivo descompactado\"\"\" fn = os.path.basename(fname) printLogMsg(\"Iniciando compacta\u00e7\u00e3o dos arquivos %s\" % (fn)) #Compactar arquivo cmd = \"tar -cjpf %s %s\" % (tarname, fname) var = commands.getoutput(cmd) if (len(var.strip()) > 0):\r\n printLogMsg(var)\r\n #endIf\r\n\r\n #Remover arq. descompactado\r\n for fn in glob.glob(fname):\r\n os.remove(fn)\r\n #endFor\r\n#end tarFile\r\n\r\n\r\ndef printLogMsg(msg):\r\n \"\"\"Imprime mensagem de log com data e hora correntes.\"\"\"\r\n t = time.localtime(time.time())\r\n lmsg = \"%02d\/%02d\/%d %02d:%02d - %s\" % (t[2], t[1], t[0], t[3], t[4], msg)\r\n print lmsg\r\n sys.stdout.flush()\r\n return\r\n#end printLogMsg\r\n\r\n\r\nif __name__ == \"__main__\":\r\n main()\r\n \r\n<\/pre>\n
\nbackupdb.conf (exemplo)<\/p>\n
# SGBD SERVERNAME SERVER_IP USERNAME PASSWORD\r\n m srvcos05 10.0.0.1 root 2006\r\n p srvcos05 10.0.0.2 postgres\r\n<\/pre>\n <\/p>\n","protected":false},"excerpt":{"rendered":"
Esse script realiza backup de bancos de dados MySQL e PostgreSQL. Os arquivos de backup s\u00e3o compactados e s\u00e3o preservados os “N” \u00faltimos arquivos de backup, conforme especificado na vari\u00e1vel KEEP_DAYS. Os bancos a serem salvos devem ser especificados no arquivo CONF. C\u00d3DIGO #!\/usr\/bin\/python # -*- coding: UTF-8 -*- # \u2026 Continue lendo