Skip to main content

How To Connect To An External Managed Database

WorkinOnIt
I want to take advantage of an external managed database (similar to AWS RDS). I understand it's possible with cPanel server. I also want to be able to use phpmyadmin to manage the external database. Can you please offer some tips / guidance on doing that?

Comments

4 comments

Date Votes
  • cPRex Jurassic Moderator
    Hey there! As far as using the remote database itself, you can use the WHM >> Manage MySQL Profiles page to configure the remote connection. Once that connection is configured, I would expect PHPMyAdmin to follow and use that server as well, so you should not have to make any other changes to the system. You may also need to adjust your server's firewall settings to ensure the systems can talk to each other and correct ports are opened, but other than that, it should be just that easy.
    0
  • WorkinOnIt
    Cheers @cPRex I've created a new profile, but having some issues trying to validate / activate in WHM I have read troubleshooting article, to no avail. This is the error message I get in WHM when trying to validate. The MySQL user "myuser" does not have the proper PRIVILEGES to act as a MySQL superuser. After adding the profile in WHM, I also manually created the user in console (via SUDO user - should it be done via root?) mysql> CREATE USER 'myuser'@'my-host.com' IDENTIFIED BY 'my-password'; Then have added: GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'my-host.com' WITH GRANT OPTION; Query OK, 0 rows affected (0.03 sec) Then I rebooted the machine and mysql> SHOW GRANTS FOR 'myuser'@'my-host.com'; +----------+ | Grants for myuser@my-host.com | +----------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO 'myuser'@'my-host.com' WITH GRANT OPTION | | GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO 'myuser'@'my-host.com' WITH GRANT OPTION | +----------+ 2 rows in set (0.00 sec) However - when I SSH to the remote DB, it connects without issue: mysql --user=myuser --password=mypass --host=my-host.com --port=123456 defaultdb What can I do to fix the issue via WHM, so that I can apply the new profile ?
    0
  • WorkinOnIt

    Hi cPRex I am coming back to this thread as still interested in how to achieve this (ps can you please update the thread title to remove the typo: it should read: "How To Connect To An External Managed Database")

    I have ascertained via CP support that it is not currently possible to connect to an external Managed Database via the WHM SQL profiles section, unless you have root control of the external database.

    In my case, this is not available as the external Managed Database is exactly that: a Managed Database and as such does not come with root access.  Indeed my service provider has advised that root access is not possible with any Managed Database provider (AWS, Google Cloud, Vultr, Digital Ocean) because none of them provide root access to their managed DB products.

    The concept, in my case, is that I need to set up a Highly Available server for a particular client, with the server using a Managed Database off-site.  We still have a need to be able to use PHPmyAdmin etc. 

    Now, we can "hack" the WHM server to use the external DB - but obviously would prefer it if we could configure it via usual methods e.g. via profiles.  

    I look forward to your thoughts on the matter.

     

    0
  • cPRex Jurassic Moderator

    Title updated!

    Have you left a comment at https://features.cpanel.net/c/78-built-in-load-balancing-replication-high-availability yet with your thoughts?  That's where we're tracking everything in regards to the new High Availability feature at this time, so it would be good to have any thoughts you have over there.

     

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post