Python bulk export method for importing MySQL users

Database Migrations (A-B) require users to migrate to the past, while the user table (Mysql.user) has hundreds of users. There are 2 ways to quickly migrate:
1, under the same version of the conditions, directly back up a server MySQL database, restore to B server.
2, if different version of the data (5.1-5.5), it is likely that the MySQL database below some of the table structure, even the default values of table data are not the same, according to the 1 method of migration, although the final is also able to access, but there is some less confidence, It's likely to affect MySQL on the B server, which requires the command line to generate the account number, which is the safest and most reassuring. The following is a Python script for batch export:

Copy the Code code as follows:

#!/bin/env python
#-*-Encoding:utf-8-*-
#-----------------------------------------
# Name:mysql_user_dump.py
# Purpose: Batch export users
# AUTHOR:ZHOUJY
# created:2013-05-28
#-----------------------------------------
Import MySQLdb

DEF get_data (conn):
query = ' Select User,host from Mysql.user order by user '
cursor = Conn.cursor ()
Cursor.execute (query)
lines = Cursor.fetchall ()
Return lines

def output_data (conn,rows):
For user,host in rows:
query = "Show grants for '%s ' @ '%s '"% (user,host)
cursor = Conn.cursor ()
Cursor.execute (query)
Show_pri = Cursor.fetchall ()
For Grants_command in Show_pri:
print '. Join (Grants_command) + '; '
print '

if __name__ = = ' __main__ ':
conn = MySQLdb.connect (host= ' localhost ', user= ' root ', passwd= ' 123456 ', db= ' MySQL ', port=3306,charset= ' UTF8 ')
rows = Get_data (conn)
Output_data (Conn,rows)

Run: Python mysql_user_dump.py

Copy the Code code as follows:

GRANT REPLICATION SLAVE On * = ' rep ' @ ' 192.168.234.% ' identified by PASSWORD ' * 6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 ';

GRANT all privileges on * * to ' root ' @ ' localhost ' identified by PASSWORD ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 ' with GRANT OPTION;

GRANT all privileges on * * to ' root ' @ ' 192.168.234.% ' identified by PASSWORD ' *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 ' ;

GRANT USAGE on * * to ' test ' @ ' 192.168.234.% ' identified by PASSWORD ' *2a032f7c5ba932872f0f045e0cf6b53cf702f2c5 ';
GRANT SELECT, INSERT, UPDATE, DELETE on ' test '. * to ' test ' @ ' 192.168.234.% ';

GRANT USAGE on *. zzz_test ' @ ' 192.168.234.% ' identified by PASSWORD ' *2a032f7c5ba932872f0f045e0cf6b53cf702f2c5 ';
GRANT SELECT, INSERT, UPDATE, DELETE on ' zzz% '. * to ' zzz_test ' @ ' 192.168.234.% ';

Finally, you can execute these commands on B and redirect them to a SQL file when you execute the script: User.sql, the import work is done by executing the source User.sql in the database of the B server.
The 2nd method is best, do not need 1 inside of the delete table and rebuild the table operation, the safest.

