I have a MySQL installation with many databases and users that I need to migrate to a new MySQL installation. I can use phpMyAdmin to export then import the databases/tables, but I don't know of anything to move the users and permissions. How can I do this easily?
mysql permissions – How to Copy User Privileges in MySQL
MySQLpermissions
Related Topic
- Mysql – How should I copy the “thesql” database to the new server using PHPMyAdmin
- MySQL privileges – DROP tables, not databases
- MySQL export tables with prefix from Unix command line
- Linux Web Server – Proper Permissions for Website Files and Folders
- Mysql – thesql: import multiple databases from sql dump with prefix
- MySQL – How to Migrate Database from MySQL 5.7 to MySQL 8.0
Best Answer
A script like this will use the mysql cli client to print out a series of grant statements you would need to use to recreate the user accounts. This command will work best if you have your database credentials stored in you .my.cnf
If you are jumping from a one version of mysql to another you may want to use this instead of a simply dump of the mysql database. The schema of the mysql database does occasionally get updated.
This will also allow you to pick and choose accounts you want to recreate, if there is some cruft you would like to eliminate.
I was recently using this on a user which included spaces in names, which confused
read
, since IFS by default includes the space character as a separator. My new and improved command, that seemed to be work better on systems weird usernames.