mysql 脚本导出_mysql导出数据脚本

# coding=utf-8

import ConfigParser

import argparse

import json

import logging

import logging.handlers

import os

import random

import re

import sys

import time

import MySQLdb.cursors

reload(sys)

sys.setdefaultencoding('utf-8')

def logger(Level="debug", LOG_FILE=None):

"""

定义日志格式

:param Level: 输入日志级别

:param LOG_FILE: 输入日志文件，None表示打印到终端

:return: 返回logger句柄

"""

Loglevel = {"debug": logging.DEBUG, "info": logging.INFO, "error": logging.ERROR, "warning": logging.WARNING, "critical": logging.CRITICAL}

logger = logging.getLogger()

if LOG_FILE is None:

hdlr = logging.StreamHandler(sys.stderr)

else:

hdlr = logging.handlers.RotatingFileHandler(LOG_FILE, maxBytes=33554432, backupCount=2)

formatter = logging.Formatter('%(asctime)s %(lineno)5d %(levelname)s %(message)s', '%Y-%m-%d %H:%M:%S')

hdlr.setFormatter(formatter)

logger.addHandler(hdlr)

logger.setLevel(Loglevel[Level])

return logger

def toJson(msg, simple=True):

"""

转化为json格式

:param msg: 输入 dict 或者 list

:param simple: 指定转换json的可读性。默认不换行

:return: 返回一个json字符串

"""

if simple:

msg = json.dumps(msg, ensure_ascii=False)

else:

msg = json.dumps(msg, ensure_ascii=False, indent=2, separators=(",", ":"))

return msg

logdir = "/tmp"

os.popen("mkdir -p %s" % logdir)

logfile = "%s/%s_logfile.log" % (logdir, os.path.basename(__file__).rstrip(".py"))

log = logger("info", logfile)

def lg(msg, level="info"):

"""

记录日志

:param msg: 日志信息，可以是任意基本类型的数据

:param level: 日志级别，默认info

"""

if isinstance(msg, dict) or isinstance(msg, list):

if level == "error":

log.error(toJson(msg))

else:

log.info(toJson(msg))

else:

if level == "error":

log.error(msg)

else:

log.info(msg)

# Case sensitive

class ConfigParserExtend(ConfigParser.ConfigParser):

def __init__(self, defaults=None):

ConfigParser.ConfigParser.__init__(self, defaults=defaults)

def optionxform(self, optionstr):

return optionstr

def getconfig(configfile):

"""

读取配置文件，配置文件格式为ini格式

:param configfile: 配置文件全路径

:return: 字典[section][option]

"""

result = {}

try:

conf = ConfigParserExtend()

conf.read(configfile)

for section in conf.sections():

result[section] = {}

for option in conf.options(section):

result[section][option] = conf.get(section, option)

except Exception as error:

lg("get config error: %s " % error)

os._exit(1)

return result

## mysql connect

class Dbconn:

def __init__(self, host, port, user, passwd, dbname='information_schema'):

self.db_host = host

self.db_port = port

self.db_user = user

self.db_passwd = passwd

self.db_dbname = dbname

try:

self.conn = self.getConnection()

self.conn.select_db(self.db_dbname)

self.succ = True

except Exception as error:

self.succ = False

msg = {"message": "instance %s:%s connect error: %s" % (self.db_host, self.db_port, error), "code": 999}

lg(msg)

def getConnection(self):

return MySQLdb.connect(host=self.db_host, user=self.db_user, passwd=self.db_passwd, port=int(self.db_port), connect_timeout=5, charset='utf8', cursorclass=MySQLdb.cursors.DictCursor)

def myquery(self, sql):

try:

cursor = self.conn.cursor()

cursor.execute(sql)

data = cursor.fetchall()

cursor.close()

self.conn.commit()

return data

except Exception as error:

msg = {"message": "instance %s:%s execute sql %s error: %s" % (self.db_host, self.db_port, sql, error), "code": 999}

lg(msg)

return False

def rsync(source_file, dest_file, export_log):

msg = {"code": 1, "source_file": source_file, "dest_file": dest_file, "step": sys._getframe().f_code.co_name}

try:

switch = str(cf.get("rsync", {}).get("switch", "false")).lower()

if switch == "true":

rsyncport = cf["rsync"]["port"]

rsyncip = cf["rsync"]["ip"]

rsyncuser = cf["rsync"]["user"]

rsyncpwd = cf["rsync"]["password"]

rsyncmodule = cf["rsync"]["module"]

rsyncdir = cf["rsync"]["dir"]

chmodcmd = "chmod 777 %s" % source_file

pipe = os.popen(chmodcmd)

pipe.close()

suiji = "".join(random.sample('zyxwvutsrqponmlkjihgfedcbaABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 10))

passwordfile = "/tmp/syncpasswd_%s" % suiji

with open(passwordfile, 'w') as ofile:

ofile.write(rsyncpwd)

ofile.close()

chmodcmd = "chmod 600 %s" % passwordfile

pipe = os.popen(chmodcmd)

pipe.close()

rsyccmd = "rsync -zrvlptD --bwlimit=20000 --port=%s --password-file=%s %s %s@%s::%s%s/%s >>%s 2>&1 " % (

rsyncport, passwordfile, source_file, rsyncuser, rsyncip, rsyncmodule, rsyncdir, dest_file, export_log)

msg["message"] = rsyccmd

lg(msg)

pipe = os.popen(rsyccmd)

state = pipe.close()

if state is None:

msg["code"] = 0

msg["message"] = "rsync success"

else:

msg["message"] = "rsync fail"

os.remove(passwordfile)

else:

msg["message"] = "rsync switch not true"

except Exception as error:

msg["message"] = error

return msg

def mainpro():

"""

数据导出 CSV SQL

"""

msg = {"code": 1, "configfile": configfile, "step": sys._getframe().f_code.co_name}

try:

exportformat = cf.get("base", {}).get("format", "csv").lower()

if exportformat not in ("csv", "sql"):

msg["message"] = "指定导出格式错误"

return msg

count = 0

user = cf["mysql"]["user"]

password = cf["mysql"]["password"]

port = cf["mysql"]["port"]

host = cf["mysql"]["ip"]

myconn = Dbconn(host, port, user, password)

if not myconn.succ:

msg["message"] = "mysql connect error"

return msg

cfdb = cf.get("tables", {}).get("db", "").split(",")

cfdb.append("")

cfdb = tuple(cfdb)

cftable = cf.get("tables", {}).get("table", "").split(",")

cftable.append("")

cftable = tuple(cftable)

cftableregular = cf.get("tables", {}).get("tablelike", "").split(",")

dbsql = 'select concat(TABLE_SCHEMA,".",TABLE_NAME) as tab from tables where TABLE_SCHEMA in %s;' % (str(cfdb))

cftablesql = 'select concat(TABLE_SCHEMA,".",TABLE_NAME) as tab from tables where concat(TABLE_SCHEMA,".",TABLE_NAME) in %s' % (str(cftable))

filter = "1=2"

for one in cftableregular:

filter = '%s or concat(TABLE_SCHEMA,".",TABLE_NAME) like "%s" ' % (filter, one)

cftableregularsql = 'select concat(TABLE_SCHEMA,".",TABLE_NAME) as tab from tables where %s ' % filter

table1 = myconn.myquery(dbsql)

table2 = myconn.myquery(cftablesql)

table3 = myconn.myquery(cftableregularsql)

tablelist = []

for one in table1:

tablelist.append(one["tab"])

for one in table2:

tablelist.append(one["tab"])

for one in table3:

tablelist.append(one["tab"])

tablelist = set(tablelist)

tablelist = tuple(tablelist)

datetime = time.strftime('%Y%m%d%H%M%S', time.localtime(time.time()))

# datetime2 = time.strftime('%Y%m%d', time.localtime(time.time()))

export_path = cf["base"]["exportdir"]

pipe = os.popen("mkdir -p %s" % export_path)

pipe.close()

# 根据SQL语句导出

sqltext_keys = cf.get("tables", {}).keys()

for onekey in sqltext_keys:

if str(onekey).startswith("sqltext"):

export_sql = cf["tables"][onekey]

export_file = "%s/%s_%s.%s" % (export_path, onekey, datetime, exportformat)

dest_file = "%s_%s.%s" % (onekey, datetime, exportformat)

export_log = "%s/%s_%s.log" % (export_path, onekey, datetime)

if exportformat == "csv":

exec_sql = "mysql -h%s -P%s -u%s -p'%s' -nse \"%s \" > %s 2>%s" % (host, port, user, password, export_sql, export_file, export_log)

elif exportformat == "sql":

db_name, table_name = re.split(" *select .* from *| *where.*| *limit.*", export_sql, flags=re.IGNORECASE)[1].split(".")

pm = re.compile(" *select .* from.*%s\.%s *" % (db_name, table_name), flags=re.IGNORECASE)

where = pm.split(export_sql)[1]

repm = re.compile(re.escape('where'), re.IGNORECASE)

mywhere = repm.sub("", where).strip()

if mywhere.lower().startswith("limit"):

mywhere = " 1=1 %s " % mywhere

elif mywhere == "":

mywhere = " 1=1"

exec_sql = "mysqldump -h%s -P%s -u%s -p'%s' -t %s --tables %s --single-transaction --skip-opt --no-autocommit --master-data=2 --where=\" %s\" > %s 2>%s" % (host, port, user, password, db_name, table_name, mywhere, export_file, export_log)

else:

msg["message"] = "指定导出格式错误"

return msg

lg(exec_sql)

msg["message"] = export_sql

msg["file"] = export_file

lg(msg)

pipe = os.popen(exec_sql)

state = pipe.close()

if state is None:

msg["code"] = 0

msg["message"] = "export data success"

count = count + 1

else:

msg["message"] = "export data fail"

continue

lg(msg)

rsyncmsg = rsync(export_file, dest_file, export_log)

lg(rsyncmsg)

# 根据指定的匹配表导出

for onetable in tablelist:

msg["table"] = onetable

export_file = "%s/%s_%s.%s" % (export_path, onetable, datetime, exportformat)

dest_file = "%s_%s.%s" % (onetable, datetime, exportformat)

export_log = "%s/%s_%s.log" % (export_path, onetable, datetime)

export_sql = "select * from %s " % onetable

if exportformat == "csv":

exec_sql = "mysql -h%s -P%s -u%s -p'%s' -nse '%s' > %s 2>%s" % (host, port, user, password, export_sql, export_file, export_log)

elif exportformat == "sql":

db_name, table_name = str(onetable).strip().split(".")

exec_sql = "mysqldump -h%s -P%s -u%s -p'%s' -t %s --tables %s --single-transaction --skip-opt --no-autocommit --master-data=2 > %s 2>%s" % (host, port, user, password, db_name, table_name, export_file, export_log)

else:

msg["message"] = "指定格式错误"

return msg

lg(exec_sql)

msg["message"] = export_sql

msg["file"] = export_file

lg(msg)

pipe = os.popen(exec_sql)

state = pipe.close()

if state is None:

msg["code"] = 0

msg["message"] = "export data success"

count = count + 1

else:

msg["message"] = "export data fail"

continue

lg(msg)

rsyncmsg = rsync(export_file, dest_file, export_log)

lg(rsyncmsg)

msg["code"] = 0

msg["message"] = "all tables: %s ,fail: %s" % (len(tablelist), count)

except Exception as error:

msg["message"] = error

return msg

def configInfo():

config = """# 配置文件模板

[base]

# 指定导出格式,可以是CSV表格格式 SQL语句

format=CSV

# 指定导出目

exportdir=/data/data_export/data

[rsync]

# 指定导出后rsync到哪个机器去。switch 为true表示需要rsync 默认false。以下是rsync的配置

switch=false

port=

ip=

user=

password=

# rsync的module配置

module=

# rsync目标端目录 /path

dir=

[mysql]

# mysql 账号密码配置

ip=

port=

user=

password=

[tables]

# 匹配表配置。 db指定整个库,例如db1,db2 。table 指定库表，例如db1.table1,db1.table2 。

# table_regular 按照like匹配库表。例如:db1.tab\_%,db2.%tab%

db=

table=sbtest50

tablelike=

# 指定SQL语句：以sqltext打头的key都是SQL语句 例如以下，SQL语句避免用双引号

sqltext1=select * from db1.sbtest50 limit 10

sqltext2=select * from db2.sbtest50 where col1='xxxx'

"""

execcmd = "# 执行命令\n # %s -c configfile\n" % os.path.basename(__file__)

print config

print execcmd

if __name__ == "__main__":

os.environ["PATH"] = "/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin"

parser = argparse.ArgumentParser(description='mysql data to export ', epilog='by van 2019', add_help=False)

parser.add_argument('-c', '--configfile', type=str, required=False, help="configfile")

parser.add_argument("-h", "--help", action="store_true", default=False, help="帮助信息")

args = parser.parse_args()

helpinfo = args.help

configfile = args.configfile

if helpinfo or configfile is None:

configInfo()

os._exit(0)

cf = getconfig(configfile)

mainproMsg = mainpro()

lg(mainproMsg)

print toJson(mainproMsg)

更多内容关注