Scripts for user rights export in MySQL

MySQL database backup and migration is a frequently needed task in the DBA's daily work. Backup generally by physical backup and logical backup two, for the physical backup with xtrabackup, table data and user rights are all back up and restore, if it is a logical backup, user data and authorization information needs to be imported separately, user data directly with mysqldump Tool Export import can, but MySQL user rights can not directly export import, a permission to handle a permission, there is more trouble.

Therefore, it is necessary to write a MySQL user rights script to export the user authorization information in the source database. As long as the authorized SQL with user rights, whether it is a normal backup, or authorization when migrating, will be useful.

MySQL user access, the whole can be obtained from the Mysql.user table to obtain the user's relevant permissions to generate the corresponding SQL statement, and then execute the generated SQL statement on the target server.

1. Create a script to export user rights

On the source server, create a exp_grants.sh script that is used to export the user's permissions, with the following script:

#!/bin/bash #Function Export user Privileges source/etc/profilepwd=123456 expgrants () {mysql-b-U ' root '-p${pwd} -n-s/data/mysql_3306/tmp/mysql.sock [email protected]-E "select CONCAT (' SHOW GRANTS for ', user, ' @ ', host, '" ;‘ )   As query from Mysql.user "|   Mysql-u ' root '-p${pwd}-s/data/mysql_3306/tmp/mysql.sock [email protected] |  Sed ' s/\ (GRANT *\)/\1;/;s/^\ (Grants for. *\)/--\1/;/--/{x;p;x;} ' } expgrants >./grants.sql

The overall idea of this export script is that it is divided into three steps, connected with a pipe, and the result of the previous operation, as input to the following operation:

The first step is to use the CONCAT function, query mysql.user in the user authorization, and connect to a show grants for command, when executing the command, plus "-b-n" option, so that the output does not have column name and box, but the command;

The second step is to execute the show grants for command in the previous step again, to derive the specific grant authorization command corresponding to each authorization in MySQL;

The third step uses SED and regular expressions to process the results of the previous step, add a comment prefix and a blank line to the show grant for line, and a semicolon at the end of the specific authorized grant line to form the SQL Authorization command that can be executed.

Where the regular expression is added and modified as follows, red for the selection of the row condition, purple for adding and modifying the content:

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

2. Execute the above script on the source server and generate the SQL script to grant permissions

#./exp_grants.sh

# Cat Grants.sql

The above two steps in my test environment are executed in the following procedure:

[[email protected] tmp]# > grants.sql [[email protected] tmp]# [[ email protected] tmp]# [[email protected] tmp]# cat exp_grants.sh #!/ bin/bash   #Function  export user privileges source /etc/profilepwd=123456   expgrants ()   {    mysql -b -u ' root '  -p${pwd} -N  -S /data/mysql_3306/tmp/mysql.sock [email protected] -e  "Select concat (    ' show grants for  ', user,  ' @ ', host,  '; '  )  as query from mysql.user " |   mysql -u ' root '  -p${pwd } -s /data/mysql_3306/tmp/mysql.sock [email protected] |   sed  ' S /\ (grant .*\)/\1;/;s/^\ (grants for .*\)/-- \1 /;/--/{x;p;x;} '   } expgrants > ./grants.sql  [[email protected] tmp]# [[email protected] tmp]# [[email  protected] tmp]# ./exp_grants.sh [[email protected] tmp]# [[email protected ] tmp]# [[email protected] tmp]# cat grants.sql-- grants for [ email protected] grant all privileges on *.* to  ' root ' @ ' 127.0.0.1 '  IDENTIFIED BY PASSWORD  ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 '  with grant  option;-- grants for [email protected]%.% grant usage on *.*  TO  ' wgphp ' @ ' 192.168.%.% '; grant select, insert, update, execute on  ' test ' .* to  ' wgphp ' @ ' 192.168. %.% ';-- grants for [email protected] grant all privileges on *.*  TO  ' root ' @ ' 192.168.226.163 '  IDENTIFIED BY PASSWORD  ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 ';-- grants for [email protected] grant usage on *.*  TO  ' Test ' @ ' 192.168.226.163 '  IDENTIFIED BY PASSWORD  ' * 6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 '; grant select, insert, update, delete, create, drop, index, alter  on  ' test04 ' .* to  ' Test ' @ ' 192.168.226.163 '; grant select, insert, update, delete, create, drop, index, alter  on  ' test03 ' .* to  ' Test ' @ ' 192.168.226.163 '; grant select, insert, update, delete, create, drop, index, alter  on  ' test01 ' .* to  ' Test ' @ ' 192.168.226.163 '; grant select, insert, update, delete, create, drop, index, alter  on  ' test02 ' .* to  ' Test ' @ ' 192.168.226.163 ';-- grants for [email protected]: :1 grant all privileges on *.* to  ' root ' @ ':: 1 '  IDENTIFIED BY PASSWORD  ' * 6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 '  with grant option;-- grants for [email  protected] GRANT ALL PRIVILEGES ON *.* TO  ' root ' @ ' localhost '   identified by password  ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 '  WITH GRANT  OPTION; grant proxy on  ' @ '  TO  ' root ' @ ' localhost '  WITH GRANT OPTION;--  grants for [email protected] grant usage on *.* to  ' Test ' @ ' localhost '  IDENTIFIED BY PASSWORD  ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 '; grant select, insert, update, delete, create, drop, index, alter  on  ' test04 ' .* to  ' test ' @ ' localhost '; grant select, insert, update, delete, create, drop, index, alter  on  ' test01 ' .* to  ' test ' @ ' localhost '; grant select, insert, update, delete, create, drop, index, alter  on  ' test02 ' .* to  ' test ' @ ' localhost '; grant select, insert, update, delete, create, drop, index, alter  on  ' test03 ' .* to  ' test ' @ ' localhost '; [[email protected] tmp]#

3. Perform authorization on the target server by executing the SQL script granting permissions

Execute the generated script on the target server:

Mysql-uname-ppwd < Grants.sql

or log in to the MySQL client first, and execute it with the source command:

Mysql-uname-ppwd-s/data/mysql_3306/tmp/mysql.sock

Mysql> Source/tmp/grants.sql

mysql> flush Privileges;

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 '.

C, because the test environment MySQL port, socket, user password and so on is not necessarily the default configuration, MySQL connection needs to be modified according to circumstances.

D, step three after you execute the SQL script that grants permissions on the target server, remember to execute the flush privileges before the authorization can take effect.

The third step of the test process is:

[[email protected] tmp]# [[email protected] tmp]# [[email protected]  Tmp]# mysql -b -u ' Root '  -p123456 -n -s /data/mysql_3306/tmp/mysql.sock  -e  "select user,host from mysql.user;" root    127.0.0.1wgphp   192.168.%.%root     192.168.226.163test    192.168.226.163root    ::1root     localhosttest    localhost[[email protected] tmp]# [[email  protected] tmp]# mysql -u ' Root '  -p123456  -s /data/mysql_3306/tmp/ mysql.sock -e  "select user,host from mysql.user;"      +-------+-----------------+| user  | host             |+-------+-----------------+| root  |  127.0.0.1       | |  wgphp | 192.168.%.%     | |  root  | 192.168.226.163 | |  test  | 192.168.226.163 | |  root  | ::1              ||  root  | localhost       | |  test  | localhost       |+-------+-----------------+[[ Email protected] tmp]# [[email protected] tmp]# mysql -u ' Root '  - p123456  -s /data/mysql_3306/tmp/mysql.sock -e  "Delete from mysql.user  where user<> ' Root ';select user,host from mysql.user;       +------+-----------------+| user | host             |+------+-----------------+| root | 127.0.0.1       | |  root | 192.168.226.163 | |  root | ::1             | |  root | localhost       |+------+-----------------+[[email  protected] tmp]# [[email protected] tmp]# [[email protected] tmp]#  mysql -u ' Root '  -p123456  -s /data/mysql_3306/tmp/mysql.sock < /tmp /grants.sql[[email protected] tmp]# [[email protected] tmp]# mysql -u ' Root '  -p123456  -S /data/mysql_3306/tmp/mysql.sock -e  "Flush privileges;select  user,host from mysql.user; "                                           +-------+-----------------+| user  |  host            |+-------+-----------------+ | root  | 127.0.0.1       | |  wgphp | 192.168.%.%     | |  root  | 192.168.226.163 | |  test  | 192.168.226.163 | |  root  | ::1              ||  root  | localhost       | |  test  | localhost       |+-------+-----------------+

This article is from the "Yumushui column" blog, be sure to keep this source http://yumushui.blog.51cto.com/6300893/1722051

Scripts for user rights export in MySQL