How to import data to MySQL Users in batches using python

This article mainly introduces database migration (A-> B). users must also be migrated over, and user tables (mysql. user) have hundreds of users. There are two methods for fast migration:
1. in the same version, back up the mysql database of server A and restore it to server B.
2. if data of different versions (5.1-> 5.5) is used, it is very likely that some table structures in the mysql database, or even the default values of table data, are different, and the migration is performed according to the 1 method, although it can still be accessed normally at the end, it is still a bit uneasy and may affect MySQL on server B. In this way, you need to use the command line to generate an account, this is the most secure and reliable. The following uses the python script for batch export:

The code is 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.exe cute (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.exe cute (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 = '000000', db = 'mysql', port = 123456, charset = 'utf8 ')
Rows = get_data (conn)
Output_data (conn, rows)

Run: python mysql_user_dump.py

The code is as follows:

Grant replication slave on *. * TO 'rep '@ '192. 192. %' 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. 192.% 'identified by password' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 ';

Grant usage on *. * TO 'test' @ '192. 192.% 'identified by password' * 2A032F7C5BA932872F0F045E0CF6B53CF702F2C5 ';
Grant select, INSERT, UPDATE, delete on 'test'. * TO 'test' @ '2017. 192.% ';

Grant usage on *. * TO 'zzz _ test' @ '192. 192.% 'identified by password' * 2A032F7C5BA932872F0F045E0CF6B53CF702F2C5 ';
Grant select, INSERT, UPDATE, delete on 'zzz % '. * TO 'zzz _ test' @ '192. 192.% ';

Finally, execute these commands on B. You can also redirect the script to an SQL file, for example, user. SQL: execute source user in the database of server B. SQL completes the import.
The 2nd method is the best. you do not need to delete or recreate a table in 1, which is the safest.