Export MySQL user rights

When migrating a MySQL database, it is sometimes necessary to migrate users and permissions within the source database. For this migration we can obtain the relevant permissions of the user from the Mysql.user table to generate the corresponding SQL statement, and then execute the generated SQL statement on the target server. This article provides a script that generates extract user rights and gives a demonstration.

1. Script to generate user rights

[Email protected] ~]# more exp_grant.sh#!/bin/bash#function Export User privilegespwd=123456expgrants () {  mysql-b -U ' root '-p${pwd}-n [email protected]-E "select CONCAT (    ' SHOW GRANTS for ', user, ' @ ', host, '; '    ) As query from Mysql.user "|   Mysql-u ' root '-p${pwd} [email protected] |   Sed ' s/\ (GRANT *\)/\1;/;s/^\ (Grants for. *\)/--\1/;/--/{x;p;x;} '} Expgrants >./grants.sql

2. Generate Permissions SQL script

[[email  protected] ~]#/exp_grant.sh [[email protected] ~]# head grants.sql--grants for [email protected]  GRANT all privileges on * * to ' root ' @ ' 127.0.0.1 ' identified by PASSWORD ' *EB3EA446C759C9DA93F84FCB56430DBEF051A9DD ' with GRANT OPTION; Grant all Privileges "CNBO0815". * to ' root ' @ ' 127.0.0.1 ' with GRANT option;--Grants for [email protected]% GRANT A LL Privileges On * * to ' root ' @ ' 172.16.10.% ' identified by PASSWORD ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 ';-- Grants for [email protected]% GRANT USAGE on * * to ' Cnbo ' @ ' 192.168.1.% ' identified by PASSWORD ' *abd91bad4a344842856         3952e281015b237310ea8 '; ....--author:leshami--blog:http://blog.csdn.net/leshami 
. .....????????..

3. Execute the script on the target server
Execute the generated script on the target server. Mysql-uname-ppwd <grants.sql
Need to note:
A, the target service is a non-null server, there are already some accounts and permissions should be considered to cover the issue.
b, if you only need to migrate non-root users, you can add a filter in the original script, that is, where user<> ' root '.

Reference:
Http://serverfault.com/questions/8860/how-can-i-export-the-privileges-from-mysql-and-then-import-to-a-new-server

Export MySQL user rights