How to move users and privileges from one MySQL server to another?
I used "mysqldump -A" to copy some databases out and then "mysql" to import them back into another server. This works ok but all the user logins and privileges are lost. Is there an easy way to move the users and privileges?
Thanks!
First get latest stable version of phpMyAdmin ( http://belnet.dl.sourceforge.net/sourceforge/phpmyadmin/phpMyAdmin-2.11.1.2-all-languages-utf-8-only.tar.gz ) up and running on your server. After you configure phpMyAdmin open it in your browser ( Example http://127.0.0.1/phpMyAdmin ). After phpMyAdmin loads look in the left panel under "Database" and see if you can find a table named "information_schema". There are all the privileges stored among other mysql settings. Now, if that table appears to you means that you configured the phpMyAdmin right. Go to "Export" section on top of page. After the page loads select the table "information_schema" from the table list provided by "Export" and leave everything else as it is (unless you know what you are doing). Click "Go", there your Privileges are saved in a SQL format. Import them on your other mysql server in the same way, but this time go to "Import" section (witch is also located on top of page). Browse for your sql file and leave everything else as it is (also unless you know what you are doing). Now you have your Privileges imported and and working. Good luck
P.S.: You can also try and save "information_schema" with mysqldump but many users report problem when backup is performed using mysqldump command line utility. But if you still wish to try with mysqldump here is the right command to avoid the errors "mysqldump -u root -p –all-databases –single-transaction > all.sql".