Importing database users after migration

Hey everyone,

I recently migrated my ispconfig to a new server. I dumped all my databases, and imported them into mariadb.
Ispconfig is running perfectly exept for the fact that my existing database users haven't been exported properly. They entries "exist" in ISPConfig (they are properly listed), but if i want to log in to lets say phpmyadmin under a certain db user that only has rights to it's own db, that user doesn't exist. Is there a way to import all the db users with privileges to my new server?

Or is this a question for a Mysql/mariadb forum since it's not actually related to ispconfig?

Thanks in advance

 

I think, you can also resync the database-settings.

 

thanks, gonna try both and i'll respond later on if it succeeded or not.

 

after again "correctly" exporting and importing the mysql user table, i can login to the db with the different users. But it's only allowed access to the information_scheme db. I'll figure it out and'll post the answer here once it succeeded

 

i ended up doing the following to export both users as privileges.
Get a List of MySQL Users

Code:

mysql -B -N -uroot -p -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != ''" mysql > mysql_all_users.txt

Obtain a List of User Privileges

Code:

while read line; do mysql -B -N -uroot -p<put_password_here> -e "SHOW GRANTS FOR $line"; done < mysql_all_users.txt > mysql_all_users_sql.sql

Now, if we insert a ‘;’, at the end of every SQL command present in that file, we will end up with executable queries. Instead of having to this manually, you can execute below command:

Code:

sed -i 's/$/;/' mysql_all_users_sql.sql

Importing the Users

Code:

mysql -u root -p < mysql_all_users_sql.sql

Works like a charm!!!

Source: https://wisdmlabs.com/blog/migrate-mysql-users-one-server-another/

 

To reply to your question, just importing the mysql database (containing the user table) won't simply be enough. you'll get working users and login, but with no privileges whatshowever (exept looking into the INFORMATION_SCHEME database). The normal solution is to export all those users, and running the SHOW GRANTS permission for every user on the old mysql database. than import those permissions in the new database. This wasn't a solution for me since i had a lot of db users and it would've simply be to much time-consuming to do it by hand for every user. The way stated in my reply above is a more elligant way of doing this process.