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?
Best Answer
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:
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:
That is THE right way to move users. Your MySQL ACL is modified with pure SQL.