Database migration (A-> B) requires migrating users, and the user table (mysql. user) has 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:
Copy codeThe 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
Copy codeThe 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.