Script sharing for exporting user permission Settings in MySQL

When migrating an 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 ES2en.user table to generate the corresponding SQL statements, and then execute the generated SQL statements on the target server. This article provides and demonstrates a script to generate and extract user permissions.

1. Generate a script for user permissions

[root@HKBO ~]# more exp_grant.sh  

#!/bin/bash  

#Function export user privileges  

  

pwd=123456  

expgrants()  

{  

  mysql -B -u'root' -p${pwd} -N $@ -e "SELECT CONCAT(  

    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'  

    ) AS query FROM mysql.user" | \  

  mysql -u'root' -p${pwd} $@ | \  

  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'  

}  

expgrants > ./grants.sql  

2. Generate permission SQL script

[root@HKBO ~]# ./exp_grant.sh   

  

[root@HKBO ~]# head grants.sql  

-- Grants for root@127.0.0.1   

GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*EB3EA446C759C9DA93F84FCB56430DBEF051A9DD' WITH GRANT OPTION;  

GRANT ALL PRIVILEGES ON `CNBO0815`.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;  

  

-- Grants for root@172.16.10.%   

GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.10.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';  

  

-- Grants for CNBO@192.168.1.%   

GRANT USAGE ON *.* TO 'CNBO'@'192.168.1.%' IDENTIFIED BY PASSWORD '*ABD91BAD4A3448428563952E281015B237310EA8';  

         ...........................  

  

3. Execute the script on the target server

Simply execute the generated script on the target server. mysql uname -- ppwd < grants.sql

Note:

a, target service is not empty server, there are already 1 account and permissions should be considered to overwrite the problem.
If you only need to migrate non-ES33en users, you can add a filter condition in the original script, namely where user < > 'root'.