请选择 进入手机版 | 继续访问电脑版
查看: 515|回复: 0

[HTML代码] mysql user用grant语句导出

686

主题

686

帖子

2071

积分

猿er

Rank: 1

积分
2071
发表于 2016-8-13 15:42:49
mysqldump 备份用户时默认是insert语句,导致在各个版本间不兼容

使用 python dba_dump_grants.py run -sroot/passwd@ip:port
  1. #!/usr/bin/env python
  2. # encoding: utf-8
  3. # File:dba_dump_grants.py
  4. import os
  5. import sys
  6. import signal
  7. import argparse
  8. import traceback
  9. import MySQLdb
  10. def getMysqlConn(host, port, user, passwd, db="mysql"):
  11. try:
  12. conn = MySQLdb.connect(host=host,user=user,passwd=passwd,port=int(port), charset="utf8", connect_timeout=2)
  13. conn.select_db(db)
  14. return conn
  15. except MySQLdb.Error, e:
  16. traceback.print_exc()
  17. finally:
  18. pass
  19. return None
  20. def doQuerySql(conn, sql):
  21. try:
  22. cursor = conn.cursor()
  23. cursor.execute(sql)
  24. lines = cursor.fetchall()
  25. cursor.close()
  26. return lines
  27. except MySQLdb.Error, e:
  28. traceback.print_exc()
  29. finally:
  30. pass
  31. return None
  32. def dumpGrants(conn):
  33. uhosts = {}
  34. qSql = "select host, user, password from mysql.user order by user"
  35. lines = doQuerySql(conn, qSql)
  36. for line in lines:
  37. (host, user, password) = line
  38. uhost = "'%s'@'%s'" % (user, host)
  39. if not cmp(password, ''):
  40. print "Warn empty password : %s" % (uhost)
  41. tqSql = "show grants for %s" % (uhost)
  42. #print "### %s" % (tqSql)
  43. ugrants = doQuerySql(conn, tqSql)
  44. ugrants2 = []
  45. for ugrant in ugrants:
  46. ugrants2.append("%s;" % (ugrant[0]))
  47. uhosts[uhost] = ugrants2
  48. return uhosts
  49. def sub_run(args, conn):
  50. outFile = args.outfile
  51. if outFile == None:
  52. outFile = "%s/dump_grants.sql" % (pathAbs)
  53. print "outfile : %s" % (outFile)
  54. uhosts = dumpGrants(conn)
  55. print "User count :%s" %(len(uhosts))
  56. f = open(outFile,"w")
  57. for uhost, ugrants in uhosts.iteritems():
  58. #print uhost
  59. for ugrant in ugrants:
  60. f.write("%s\n" % (ugrant))
  61. f.close()
  62. def sigint_handler(signum, frame):
  63. exit(0)
  64. if __name__ == '__main__':
  65. reload(sys)
  66. #sys.setdefaultencoding("utf-8")
  67. #
  68. signal.signal(signal.SIGINT, sigint_handler)
  69. #signal.signal(signal.SIGHUP, sigint_handler)
  70. signal.signal(signal.SIGTERM, sigint_handler)
  71. (pathAbs, scName) = os.path.split(os.path.abspath(sys.argv[0]))
  72. #
  73. parents_parser = argparse.ArgumentParser(add_help=False)
  74. parents_parser.add_argument("-s", "--dsn", dest="dsn", required=True, help="format: user/password@host:port")
  75. parents_parser.add_argument("-o","--outfile", dest="outfile", help="default curDir/dump_grants.sql")
  76. parser = argparse.ArgumentParser(description="dump mysql user with grant")
  77. subparsers = parser.add_subparsers()
  78. # run
  79. run_parser = subparsers.add_parser('run', parents=[parents_parser], help='')
  80. run_parser.set_defaults(func=sub_run)
  81. args = parser.parse_args()
  82. # check
  83. uphps = args.dsn.split("@")
  84. if len(uphps) != 2:
  85. print "Error dsn : %s" %(args.dsn)
  86. exit(1)
  87. ups = uphps[0].split("/")
  88. hps = uphps[1].split(":")
  89. if len(ups) != 2 or len(hps) != 2:
  90. print "Error dsn : %s" %(args.dsn)
  91. exit(1)
  92. #
  93. (user, password) = ups
  94. (host, port) = hps
  95. conn = getMysqlConn(host, port, user, password)
  96. if conn != None:
  97. args.func(args, conn)
  98. conn.close()
  99. else:
  100. print "Error connect"
  101. exit(1)
  102. print "### Done"
  103. exit(0)
复制代码


回复

使用道具 举报