MySQL root user is deleted

  • I upgraded a test installation from 1.4.7 to 1.5.1. After the upgrade I wanted to change the database server.
    As soon as I run /usr/local/src/imscp-1.5.1/engine/setup/imscp-reconfigure, my MySQL root user gets deleted.




    This seems a similar issue to:
    MySQL root user is deleted

  • @major


    I cannot confirm the problem, at least with git version.


    Furthermore, in version 1.5.1, there is a check to prevent deletion of SQL root user:


    https://github.com/i-MSCP/imsc…ervers/sqld/mysql.pm#L239


    Anyway please: Reporting rules - Reminder

    After the upgrade I wanted to change the database server.

    • Distribution and codename?
    • What was your previous MySQL server vendor and version?
    • What is your new MySQL server vendor and version?

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206

  • Hi,


    thanks for the fast reply!


    Old DB is MySQL 5.5 on Ubuntu 14.04
    New DB is MariaDB 10.1 on Debian 9.1


    I copied the complete /var/lib/mysql directory from the old server to the new one, then started MariaDB on the new server, and executed mysql_upgrade.
    Next, I executed /usr/local/src/imscp-1.5.1/engine/setup/imscp-reconfigure, which failed with



    Access denied for user 'imscp_user'@'193.239.xxx.xxx' (using password: YES)
    I guess, when running an external DB server, I might need to add privileges for 'imscp_user'@'%' manually? However, after the installer exited, I was no longer able to login to the MariaDB server, because user root was gone.

  • ?
    You don't say that you move to a new server.
    Install the new server with the same imscp version, plugins etc. which running on the old server.


    Serverumzug


    If all running, change the sql.


    My opinion.

  • I guess, when running an external DB server

    External server?


    OK... Please, provide us with all correct information and we will be able to help... Here the problem is not i-MSCP ;)

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206

  • Hi,


    I'm sorry, here is a little bit of context:


    I plan to migrate an ISPCP and two ISPConfig instances to i-MSCP. All three systems currently use an external database server, external nameserver and external mailserver. In the new setup, I like to keep mailserver, database server and nameserver independent. To plan/prepare the migration, I built a test environment, where I setup i-MSCP (1.4.7), which I later upgraded to 1.5.1.


    At one stage, I wanted to try to connect i-MSCP to the existing external database server. To do so:

    • I first created a clone of the MariaDB server
      • The system has about 2000 databases, a whole lot of users, and of course a root user
      • It never knew about the i-MSCP database or any i-MSCP user
    • I mysqldump'ed the i-MSCP database from the i-MSCP server and imported it into the MariaDB server.
    • I ran /usr/local/src/imscp-1.5.1/engine/setup/imscp-reconfigure to update i-MSCPs database server settings.
      (As the MariaDB server didn't even know any i-MSCP user, I thought this would be the easiest way to create the i-MSCP database user and update the i-MSCP config)
      • The script asks for db IP address, port, root-username and root-password, imscp-username, imscp-password, imscp-database-name, imscp-web-admin and web-password
      • After I enter all this data, the script fails with "Servers::sqld::mysql::installer::_setupDatabase Couldn't connect to SQL server"
      • -> From now on, the MariaDBs root user is gone.


    The i-MSCP server is Debian 9.1 with i-MSCP 1.5.1 and MariaDB 10.2 (10.2.9+maria~stretch)
    The MariaDB server to which I want to connect i-MSCP is Debian 9.1 with MariaDB 10.1 (10.1.26-0+deb9u1)


    All this is only a test setup at the moment, but I thought it might be a good idea to inform you about the issue.


    Regards, Sven.

  • -> From now on, the MariaDBs root user is gone.


    First, you should ensure that all SQL root users are really gone. There is not only one SQL root user but many with different hosts.


    i-MSCP doesn't remove the remote SQL root users. There was a bug in a past version but it has been fixed since a while. In our backend there is a check for important SQL users such as the SQL root users. Those users cannot be deleted.


    Our backend only remove SQL root users that are set with a host other than localhost, 127.0.0.1 or ::1, and only when you use a local SQL server. The behavior here is identical to the one provided by the MYSQL_SECURE_INST(1) program.


    See https://github.com/i-MSCP/imsc…d/mysql/installer.pm#L770


    Furthermore, the _setupSecureInstallation() is run late in the setup/reconfiguration process, not at the time you answer the dialog.


    The script asks for db IP address, port, root-username and root-password, imscp-username, imscp-password, imscp-database-name, imscp-web-admin and web-password


    After I enter all this data, the script fails with "Servers::sqld::mysql::installer::_setupDatabase Couldn't connect to SQL server"


    The symptom here let's me think that the SQL root user was already gone... At this stage (setup dialog), the backend cannot be in fault. There is no write operation.

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206

  • Ok, i see, there we have the issue.


    My MariaDB only has one root user (root@%). The production MariaDB is actually a galera cluster with no local access, so no root@localhost exists. When I say I cloned the MariaDB server, I actually ment I added a galera cluster node, synced all data and then split the node from the cluster. This leaves me with a single MariaDB server, that I can use for testing.


    Of course, this also leaves me with only the root@% account, which will be removed by the installer code you linked (https://github.com/i-MSCP/imsc…d/mysql/installer.pm#L770)



    Summing this up leaves me with the question, whether it is a good idea to connect i-MSCP to such a database cluster. Actually, I don't want to allow i-MSCP root access to the cluster, I only do this at the moment while testing.


    In your opinion, is this a good or a bad idea? :) Will it break many of the automatic installer or upgrade scripts?

  • Ok, i see, there we have the issue.


    My MariaDB only has one root user (root@%). The production MariaDB is actually a galera cluster with no local access, so no root@localhost exists. When I say I cloned the MariaDB server, I actually ment I added a galera cluster node, synced all data and then split the node from the cluster. This leaves me with a single MariaDB server, that I can use for testing.


    Of course, this also leaves me with only the root@% account, which will be removed by the installer code you linked (github.com/i-MSCP/imscp/blob/1…d/mysql/installer.pm#L770)

    Ok but the user is not removed if your SQL server is a remote one. It is only removed in case of local SQL server, that is, the server where i-MSCP is installed. That was your case after cloning?

    Summing this up leaves me with the question, whether it is a good idea to connect i-MSCP to such a database cluster. Actually, I don't want to allow i-MSCP root access to the cluster, I only do this at the moment while testing.


    In your opinion, is this a good or a bad idea? Will it break many of the automatic installer or upgrade scripts?

    i-MSCP make use of the SQL root user (or any user with full privileges that you provide when you're asked for the root SQL user) only during i-MSCP installation, or reconfiguration when its own user cannot access the SQL server. i-MSCP create a specific SQL user for it own usage. That user of course need full privileges too because it need to be able to create SQL users, SQL databases and so on ;)


    To summarize, once the i-MSCP setup (or reconfiguration) is done, i-MSCP will never use your SQL root user. It don't even store any data about that user. If you drop remote access for the root SQL user after i-MSCP setup, this shouldn't cause any warn for i-MSCP as long as its own user can access the SQL server.

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206