Importing database users after migration

Discussion in 'General' started by Lars Behrens, Sep 29, 2015.

  1. Lars Behrens

    Lars Behrens New Member

    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
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    You can export the users on the old server with e.g. phpmyadmin (see tables user and db in the "mysql" database) and then import them on the new server.
     
  3. florian030

    florian030 Well-Known Member HowtoForge Supporter

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

    Lars Behrens New Member

    thanks, gonna try both and i'll respond later on if it succeeded or not.
     
    Last edited: Sep 29, 2015
  5. Lars Behrens

    Lars Behrens New Member

    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
     
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    did yiu export the users from mysql "db" table as well? User records in mysql are splitted into several tables.
     
  7. Lars Behrens

    Lars Behrens New Member

    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/
     
  8. Lars Behrens

    Lars Behrens New Member

    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.
     
  9. till

    till Super Moderator Staff Member ISPConfig Developer

    You can simply export the ispconfig users from mysql.user and the mysql.db table, I used that on dozens of servers. Takes just a minute with phpmyadmin.
     

Share This Page