Python bulk export methods for importing MySQL users _python

The database migration (A-> B) requires that the user be migrated, while the user table (Mysql.user) has hundreds of users. There are 2 ways to migrate quickly:
1, in the same version of the condition, directly back up a server MySQL database, restore to B server.
2, if the different version of the data (5.1-> 5.5), it is very likely that the MySQL database some of the table structure, or even the default value of the table data, in accordance with the 1 method of migration, although the final can also be normal access, but still some do not trust, is likely to affect the B server MySQL, so you need to use the command line to generate account, which is the most secure and reassuring. The following is a bulk export using a Python script:

Copy Code code as follows:

#!/bin/env python
#-*-Encoding:utf-8-*-
#-----------------------------------------
# Name:mysql_user_dump.py
# Purpose: Bulk 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)

Running: Python mysql_user_dump.py

Copy Code code as follows:

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

GRANT all Privileges "*.* 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 *.* to ' zzz_test ' @ ' 192.168.234.% ' identified by PASSWORD ' *2a032f7c5ba932872f0f045e0cf6b53cf702f2c5 ';
GRANT SELECT, INSERT, UPDATE, DELETE on ' zzz% '. * to ' zzz_test ' @ ' 192.168.234.% ';

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