• 4

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?

One of the easiest ways I've found to export users is using Percona's tool pt-show-grants. The Percona tool kit is free, easy to install, and easy to use, with lots of documentation. It's an easy way to show all users, or specific users. It lists all of their grants and outputs in SQL format. I'll give an example of how I would show all grants for test_user:

shell> pt-show-grants --only test_user

Example output of that command:

GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASSWORD '*06406C868B12689643D7E55E8EB2FE82B4A6F5F4';

I usually rederict the output into a file so I can edit what I need, or load it into mysql.

Alternatively, if you don't want to use the Percona tool and want to do a dump of all users, you could use mysqldump in this fashion:

shell> mysqldump mysql --tables user db > users.sql

Note: --flush-privileges won't work with this, as the entire db isn't being dumped. this means you need to run it manually.

shell> mysql -e "FLUSH PRIVILEGES"
  • 0
Reply Report

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.

  • 0
Reply Report

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:

  mysql -B -N [email protected] -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR '', user, ''@'', host, '';'
    ) AS query FROM mysql.user" | 
  mysql [email protected] | 
  sed 's/(GRANT .*)/;/;s/^(Grants for .*)/##  ##/;/##/{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 [email protected] 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.

  • 4
Reply Report