How can I export the privileges from MySQL and then import to a new server?

Question

I know how to export/import the databases using mysqldump & that's fine but how do I get the privileges into the new server.

For extra points, there are a couple of existing databases on the new one already, how do I import the old servers privileges without nuking the couple existing of ones.

Old server: 5.0.67-community

New server: 5.0.51a-24+lenny1

EDIT: I've got a dump of the db 'mysql' from the Old Server & now want to know the proper way to merge with the 'mysql' db on the New Server.

I tried a straight 'Import' using phpMyAdmin and ended up with an error regarding a duplicate (one that I've already migrated manually).

Anyone got an elegant way of merging the two 'mysql' databases?

As I mentioned below, I think Richard's mygrants script is a good way to get grant info. However, you can also try editing the dump file to comment out INSERTs to the user table for users who already exist. Privileges for the dbs restored from the old server will then be copied. If you've already assigned privileges manually for some of the dbs you restored to the new box, look for these table names in the privilege files and comment these out, too. Don't forget a flush_privileges afterwards. Good description of the mysql db at: http://www.grahamwideman.com/gw/tech/mysql/perms/index.htm — nedm, Jun 13 '09 at 10:37

score 178 · Accepted Answer · edited Jun 13 '13 at 15:20

178

Do not mess with the mysql db. There is a lot more going on there than just the users table. Your best bet is the "SHOW GRANTS FOR" command. I have a lot of CLI maintenance aliases and functions in my .bashrc (actually my .bash_aliases that I source in my .bashrc). This function:

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

The first mysql command uses SQL to generate valid SQL which is piped to the second mysql command. The output is then piped through sed to add pretty comments.

The $@ in the command will allow you to call it as: mygrants --host=prod-db1 --user=admin --password=secret

You can use your full unix tool kit on this like so:

mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret

That is THE right way to move users. Your MySQL ACL is modified with pure SQL.

edited Jun 13 '13 at 15:20

Totor

2,996

answered May 27 '09 at 15:51

Bruno Bronosky

4,539

1

This is actually a nice bash helper function that works great. Take the output from that and be able to run on the new server and the privileges would be entered properly and accurately. – Jeremy Bouse Jun 11 '09 at 14:12
1

I love your function, today it saved me a lot of work. Thank you, thank you, thank you... – Fabio Feb 15 '11 at 22:32
1

Love it. This has saved me at least an hour of work! – wolfgangsz Jul 01 '11 at 12:50
3

This is great but it has one big flaw. An extra "" is added to underscores in database names, so if you have for example a user with specific privileges on a database called foo_bar, it will be rendered as foo\_bar instead of foo_bar, so that won't be imported correctly. At least, this happens if you save the output from you script into a SQL file and then import it into the new server. I haven't tried the direct piping of export and import in a single line. – matteo Oct 15 '13 at 09:41
1

Isn't _ a special character, a wildcard character, so it has to be escaped? But I think it has to be \_ and not \\_. And I am getting \\_ when I run the command. See this question. – Mitar Jun 26 '15 at 04:36
1

Be careful with this command. If you have in user table an user with host %, but then in db table you have entries where host is an explicit value, those entries in db table are not retrieved using this method. – Mitar Jun 26 '15 at 07:27
1

@matteo Add -r to the 2nd mysql command in the function and the double escapes won't be output by mysql. eg: mysql -r $@ – lifo Sep 12 '16 at 12:08
1

This answer not only gave me the mechanism i was looking for, but also gave me several leads for learning quite a bit. Thank you much! – ilasno Jan 20 '20 at 19:47

score 61 · Answer 2 · answered Jun 18 '12 at 18:24

61

There are two methods for extracting SQL Grants from a MySQL Instance

METHOD #1

You can use pt-show-grants from Percona Toolkit

MYSQL_CONN="-uroot -ppassword"
pt-show-grants ${MYSQL_CONN} > MySQLUserGrants.sql

METHOD #2

You can emulate pt-show-grants with the following

MYSQL_CONN="-uroot -ppassword"
mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Either method will produce a pure SQL dump of the MySQL grants. All there is left to do is to execute the script on a new server:

mysql -uroot -p -A < MySQLUserGrants.sql

Give it a Try !!!

answered Jun 18 '12 at 18:24

RolandoMySQLDBA

16,694

3

pt-show-grants is exactly what you want for this, it works great. – Glenn Plas Dec 07 '14 at 22:14
Percona is just pure awesomeness. – sjas Oct 14 '15 at 13:02
8

But answer #2 is just as good and will work without additional software! – sjas Oct 14 '15 at 13:05
If you are moving to 8.0, the GRANTs and PASSWORD stuff need changing. – Rick James Feb 26 '21 at 22:23

score 15 · Answer 3 · answered Sep 02 '11 at 20:27

Richard Bronosky's answer was extremely useful for me. Many thanks!!!

Here is a small variation which was useful for me. It is helpful for transfering users e.g. between two Ubuntu installations running phpmyadmin. Just dump privileges for all users apart from root, phpmyadmin and debian-sys-maint. The code is then

mygrants()
{
mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
) AS query FROM mysql.user WHERE user NOT IN ('root','phpmyadmin','debian-sys-maint')"  | \
mysql $@ | \
sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

If you look at my example where I grep rails_admin you can infer how to do this without making a special function for every edge case. Use grep's "invert-match" option like so: mygrants --host=prod-db1 --user=admin --password=secret | grep -Ev 'root|phpmyadmin|debian-sys-maint' | mysql --host=staging-db1 --user=admin --password=secret — Bruno Bronosky, Nov 08 '17 at 01:25

score 7 · Answer 4 · edited Aug 13 '14 at 11:36

7

Or, utilize percona-toolkit (former maatkit) and use pt-show-grants (or mk-show-grants) for that purpose. No need for cumbersome scripts and/or stored procedures.

edited Aug 13 '14 at 11:36

Martijn Heemels

7,878

answered May 01 '12 at 12:08

MrkiMile

71

nedm · Answer 5 · 2009-05-16T06:41:43.343

6

You can mysqldump the 'mysql' database and import to the new one; a flush_privileges or restart will be required and you'll definitely want to back up the existing mysq db first.

To avoid removing your existing privileges, make sure to append rather than replace rows in the privilege tables (db, columns_priv, host, func, etc.).

edited May 16 '09 at 06:41

answered May 16 '09 at 06:24

nedm

5,660
5
32
52

Thanks @nedm. Seems like the annoying cPanel server I'm trying to get the dbs off doesn't show the db 'mysql'. Otherwise I'd test and affirm your answer. Once I figure that out I'll check back. Thanks. – Gareth May 16 '09 at 08:49
That is unfortunate but understandable, you are probably prevented from accessing any database but the ones directly owned by your user on a shared db. – Dave Cheney May 17 '09 at 06:05
Ouch, yeah, without access to 'mysql' it will be difficult to do anything related to users or permissions. Do you have command line access? Can you run mysqldump from the terminal or command line (NOT from the mysql shell)?
mysqldump -u username -ppassword mysql > mysqldump.sql
– nedm May 18 '09 at 17:08
The db 'mysql' was accessible from the Cpanel WHM if I logged in as 'root'. From there I can access a version of phpmyadmin which has the 'mysql' database containing permissions – Gareth Jun 10 '09 at 07:53
Merging into a existing mysql schema, data extracted from a mysql schema via mysqldump is almost certain to be problematic. You are manipulating a complex schema with enforced relationships, etc. This schema is so complex, in fact, that they created dedicated SQL syntax (GRANT, REVOKE, DROP USER, etc.) for dealing with it. Your extract, however, consists of only INSERT statements. I'm running out of characters here. Need I continue? – Bruno Bronosky Jun 13 '09 at 03:52
No, you don't need to continue--it's clear you don't like messing with the mysql db. However, it's not so mysterious as you make it sound, and it's even described as a recommended way to restore user and privilege data in the mysql documentation at: http://dev.mysql.com/doc/mysql-security-excerpt/6.0/en/backup-database-restore-quick-guide.html – nedm Jun 13 '09 at 09:56
I think your mygrants script is actually a pretty elegant way of copying permissions on a Linux box. However, for small-medium number of users, I've had good luck copying and importing the mysql database, editing the dump file as necessary -- if users are duplicated on the new box, simply edit the dump file and comment out those INSERTs in the user table. Privileges for the restored dbs from the old box will then be copied. – nedm Jun 13 '09 at 09:57

score 5 · Answer 6 · answered May 09 '11 at 22:05

You could also do it as a stored procedure:

CREATE PROCEDURE spShowGrants()
    READS SQL DATA
    COMMENT 'Show GRANT statements for users'
BEGIN
    DECLARE v VARCHAR(64) CHARACTER SET utf8;
    DECLARE c CURSOR FOR
    SELECT DISTINCT CONCAT(
        'SHOW GRANTS FOR ', user, '@', host, ';'
    ) AS query FROM mysql.user;
    DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;  
    OPEN c;
    WHILE TRUE DO
        FETCH c INTO v;
        SET @v = v;
        PREPARE stmt FROM @v;
        EXECUTE stmt;
    END WHILE;
    CLOSE c;
END

and call it with

$ mysql -p -e "CALL spShowGrants" mysql

then pipe the output through Richards sed command to get a backup of the privileges.

Ibrahim Lawal · Answer 7 · 2014-08-13T11:29:19.800

How about a PHP script? :)

View source on this script and you will have all the privileges listed:

//connect
mysql_select_db("mysql", mysql_connect("localhost","root",""));

//create grants select statements
$rs = mysql_query("SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query FROM user");

//iterate through grants
while ($row=mysql_fetch_array($rs)) {
    //run grant query
    $rs2 = mysql_query($row['query']);
    //iterate through results
    while($row2 = mysql_fetch_array($rs2)){
        //print results
        echo $row2[0] . ";\n\n";
    }
}

score 3 · Answer 8 · answered Sep 03 '11 at 01:16

3

While it appears @Richard Bronosky's answer is the correct one, I came across this question after trying to migrate a set of databases from one server to another and the solution was much simpler:

server1$ mysqldump -u root -p --all-databases > dbdump.sql

server2$ mysql -u root -p < dbdump.sql

At this point, all my data was visible if I logged in as root, the mysql.user table had everything in it I was expecting, but I couldn't log in as any of the other users and found this question assuming I'd have to re-issue the GRANT statements.

However, it turns out that the mysql server simply needed to be restarted for the updated privileges in the mysql.* tables to take effect:

server2$ sudo restart mysql

Hopefully that helps someone else achieve what should be a simple task!

answered Sep 03 '11 at 01:16

Tom

2,662

Oh, and my situation was slightly different to the OP's, in that I wasn't trying to merge a dump into a server with existing databases/users. – Tom Sep 03 '11 at 01:17
2

You don't actually have to restart MySQLd to 'update the privileges'. It can be done with the MySQL command 'flush privileges;' – CloudWeavers May 03 '12 at 18:16
The issue with this approach is that it works only when both source and destination MySQL version are the same. Otherwise you might have issues because source mysql.user table has different columns than destination mysql.user table, for example. – Mitar Jun 26 '15 at 05:30

score 2 · Answer 9 · answered Sep 06 '10 at 23:03

create a shell script file with the following code:

##############################################3

echo "SELECT DISTINCT CONCAT (\"show grants for '\", user, \"'@'\", host, \"';\") AS query FROM mysql.user; " >   script.sql    
echo "*** You will be asked to enter the root password twice ******"    
mysql -u root -p  < script.sql > output.sql ;    
cat output.sql | grep show > output1.sql  ; rm output.sql -f ; 
mysql -u root -p  < output1.sql > output.sql ;
clear
echo "-----Exported Grants-----"    
cat  output.sql ; rm  output.sql   output1.sql -f    
echo "-------------------------"
rm  script.sql -f

****then run it on the shell like this: you will be asked to enter the root password twice and then the GRANTS SQL will be displayed on the screen.****

score 0 · Answer 10 · answered Jan 18 '21 at 23:01

A challenge often encountered with some of the shell-based and command-line-based answers here is trying to execute them on a remote server -- the level of escaping of all the ticks and double-quotes and backslashes gets insane. Here is a way this can be done, from the command-line on a remote server:

cat  << EOQ | ssh $REMOTE_SERVER 'mysql -B -N  mysql | mysql -B -N ' | mysql 
 SELECT CONCAT('show grants for \'', user,'\'@\'', host, '\'\;') FROM user WHERE user != 'root' AND user != ''
EOQ

This command logs into the remote server remotely (here, we assume passwords are not needed with ssh keys and mysql/my.cnf/client configurations), executes the query which generates the show grants commands, a second mysql execution runs the generated commands, and the final mysql in the pipeline is executed on the local server which applies those show grants output on the local server.

The "root" user is not copied for security reasons.

score 0 · Answer 11 · answered Apr 13 '16 at 18:33

0

One-liner doing pretty much the same as the awesome pt-show-grants:

mysql --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql --skip-column-names -A | sed 's/$/;/g'

Based on unix tools only, no additional software needed.

Execute from within a bash shell, assumed is you have a working .my.cnf where the password of your mysql root user can be read.

answered Apr 13 '16 at 18:33

sjas

324

i duplicated half of @rolandomysqldba 's post after coming back half a year later, i just realized. – sjas Apr 13 '16 at 18:36

How can I export the privileges from MySQL and then import to a new server?

11 Answers11

Linked

Related