mysql导出数据脚本

一个脚本工具供日常运维帮业务导出数据用。

可以指定数据库，库表，库表匹配，指定SQL语句。导出CSV格式，或者SQL文件。导出完毕指定rsync服务器，推送到指定rsync服务器目录。

./mysqlexport.py  --help  

工具配置文件模板 

# 配置文件模板
[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语句 例如以下
sqltext1=select * from db1.sbtest50 limit 10
sqltext2=select * from db2.sbtest50 limit 10

#执行命令

mysqlexport.py -c configfile

 

python 源代码

# 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 = "# 执行命令
 # %s -c configfile
" % 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)

更多内容关注

#!/data1/Python-2.7.4/bin/python
# 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 = "# 执行命令
 # %s -c configfile
" % 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)