Possible Duplicate:
How to copy user priviledges with MySQL?
I recently migrated a bunch of under utilized physical servers to one powerful server running Xenserver. I then turned one physical server into a database server.
Now, the issue is each physical server had its own MySQL instance, with users and databases. I know I can export each DB and import them into the database server, however what I need now is a way to import the users and permissions.
How can I do this?
You will find all permissions bits, etc in the database named mysql. Yet, I don't think you simply will want to merge all that different data into the mysql database on the new consolidated MySQL server. Except that you might have overlaps in usernames, etc there might also be the issues of users previously connecting from localhost now connecting from an external address.
Given a limited number of different users and databases I would probably create the users and the permissions from scratch manually with a series of GRANT queries. Assuming the amount of users and/or databases are less limited you will most likely have to inspect the content of each mysql database and based on that perform some intelligent scripting.
Permissions and user accounts are defined in the mysql database. As long as the version did not change between the original servers and the new server, you should be able to export the contents of this database for importing to the new server.