Remote Mysql as localhos

[osnanderson]

Hi!


I work with shared server hosting with hundreds of accounts and databases.

Mysql has been a problem as it is consuming a lot of CPU and RAM, so I thought about moving Mysql to an external dedicated server for Mysql.

But we have hundreds of client applications running, in addition to clients that migrate to our hosting and it is unfeasible to change the host of all applications to the Mysql server address.

I've read articles about using SSH tunneling and other proxying methods to redirect localhost:3306 connections to an external IP. Would it be possible?

Using Mysql tunnel seems to be relatively easy, but would that work well? Can you suggest me a reliable method of doing this redirection?

I understand that redirecting all localhost to another IP would be a problem for the server, but with Mysql tunnel it seems possible to specifically redirect connections on port 3306.

Could you share your knowledge about this?

Thank you very much

 

[cPRex]

Hey there! I believe you're looking for this:

Manage MySQL® Profiles | cPanel & WHM Documentation

This interface allows you to manage multiple MySQL® profiles.

docs.cpanel.net

which explains how you can setup all your databases on a remote server, without the need to edit any connection strings in the code. Let me know if that helps!

 

[osnanderson]

I thought this option would also force me to change the host in the users' scripts, changing "localhost" to the hostname of the remote mysql server.

So if I create a remote Mysql profile in this option, I won't need to change anything in the applications that connect with localhost?

could you tell me how cpanel works in this redirection? Is there any article talking more about this? because the link you gave me has the instruction to create the profile, but I don't see anything saying that the mysql localhost would be automatically redirected to the remote mysql server.

thank you!

 

[cPRex]

Let me do some more testing with this and I'll get back with you soon.

 

[cPRex]

When you set up the remote profile, two things happen. The first, and most important, is that this gets added to /root/.my.cnf:

host=IPOFREMOTEMYSQLSERVER
port=3306

so any connections made from existing applications will get routed to that remote system.

The second thing, is that any NEW applications that get created, such as a WordPress installation from WordPress Toolkit, have the following configured for the connection string:

/** MySQL hostname */
define( 'DB_HOST', 'IPOFREMOTEMYSQLSERVER:3306' );

so any new sites will have that directly in the code as well.

I'm going to submit a documentation update for this as well so it will be more clear in the future exactly how this happens.

 

[osnanderson]

I understand, but wouldn't there be a way for new accounts to continue keeping their applications with Host “localhost”? This is the biggest issue, not having to update applications because, as there are many sites (90% WordPress) it would be a inconvenience, for example in the case of a migration of several applications.

 

[cPRex]

All sites on the server would use the remote database server unless they were manually configured not to by the admin.

 

[osnanderson]

All sites on the server would use the remote database server unless they were manually configured not to by the admin.

Yes, all sites using remote database server. But use ‘localhost’ as Host in website scripts like a wordpress? (Old sites and new sites)

 

[cPRex]

That's fine too.

 

[osnanderson]

Amazing! Thank you very much!

 

[osnanderson]

Hello great cPRex!

I was confused by some information and some doubts.

Come on:

1) Does the remote MySQL server need to be a server with cPanel or can it be a dedicated server with MySQL only (without any panel like cPanel)?

2) Does the remote MySQL server (With/ without cPanel) need to be Ubuntu with MySQL 8? Remote MySQL can't be on version 5.7 and OS can't be CentOS 7?
I'm a little confused by cPanel's documentation page on remote MySQL server requirements.

3) Can the web server with cPanel/WHM (where the applications, websites and other services will be running) also have MySQL 5.7?
This is the current standard we use, and if possible I would like to keep MySQL 5.7 on the web and remote MySQL servers.

4) Does the web server with cPanel/WHM (where the applications, websites and other services will be running) need to have a local MySQL running? If yes, does it somehow interact with the MySQL that is on the remote server?
Or after migrating the entire database to the remote MySQL server, can we disable MySQL on the web server?

5) Do you know if the web server (where the applications, sites and other services will be running) makes a local cache of the remote MySQL? Or will each query always be performed directly on the remote MySQL, without the possibility of a local cache?

6) Now it's just some information that differs from what you told me in the messages above, where I could continue using the MySQL host in applications/scripts as 'localhost' (no need to manually configure each site/application with the hostname of the server Remote MySQL).

On the MySQL profiles documentation page, I read:

After you configure the remote server, you must manually transfer your existing MySQL data to that server and configure your accounts’ applications to use that server. Any new databases that you create will exist on the remote MySQL server.

For example, If you move an existing WordPress® database to the remote server, you must also update the WordPress server’s configuration files to use the new server.

On the WHM > Manage MySQL® Profiles, I read:

After you configure the remote server, you must manually transfer your existing MySQL data and configure your accounts’ applications to use the remote database server.

Before proceeding with initial tests with a remote MySQL server, I would like to be sure and clear about this information to keep using 'localhost' in the configurations of existing applications/sites and the new ones that we will migrate or create.

Thank you very much for your attention.

 

[cPRex]

1 - No, the remote system does not have to be cPanel. It just has to use a compatible MySQL version that our tools can work with.
2 - It doesn't have to be Ubuntu, but I wouldn't use CentOS 7 at this point if you're building a new system. AlmaLinux is the way to go if you're more familiar with CentOS systems than Ubuntu systems.
3 - I suppose you could, but if you're going to put in all this work, I wouldn't choose MySQL 5.7 at this point. I'd go with MySQL 8.
4 - No - once you make the switch you don't need a local MySQL server running
5 - No - since no local service is running or handling data, there isn't any way for caching to happen on the webserver.
6 - My testing yesterday determined you do not need to update the configuration files in existing applications. That's part of the documentation that's going to get updated.

I would strongly recommend testing this as much as you can outside of a production environment if that is possible. Although, with the MySQL Profile Manager, switching back to a local instance is just clicking a button, in the event things don't work as you expect.

 

[osnanderson]

1 - No, the remote system does not have to be cPanel. It just has to use a compatible MySQL version that our tools can work with.
2 - It doesn't have to be Ubuntu, but I wouldn't use CentOS 7 at this point if you're building a new system. AlmaLinux is the way to go if you're more familiar with CentOS systems than Ubuntu systems.
3 - I suppose you could, but if you're going to put in all this work, I wouldn't choose MySQL 5.7 at this point. I'd go with MySQL 8.
4 - No - once you make the switch you don't need a local MySQL server running
5 - No - since no local service is running or handling data, there isn't any way for caching to happen on the webserver.
6 - My testing yesterday determined you do not need to update the configuration files in existing applications. That's part of the documentation that's going to get updated.

I would strongly recommend testing this as much as you can outside of a production environment if that is possible. Although, with the MySQL Profile Manager, switching back to a local instance is just clicking a button, in the event things don't work as you expect.

Thank you very much. You are really incredible!

 

[cPRex]

You're very welcome!

 

[osnanderson]

Hello cPRex!

Sorry for bothering you again with more questions. I believe this forum topic will be very useful for other people.

I'm starting a test environment to run the remote MySQL server, mainly to test the possibility of using 'localhost' in applications even for MySQL running on an external server. I'm really excited about it!

1) Would I be able to use AlmaLinux 8/9 and Oracle Linux 8/9 on the remote MySQL server? It will be a server running exclusively MySQL 8.

2) And this MySQL server can be built with ARM Ampere processor?

I already looked up information and AlmaLinux OS has AArch64 compatible image (ARM Ampere) and MySQL 8 would also be supported in this environment.

I would prefer Oracle Linux even more, to have better compatibility with the environment that will be Oracle Cloud.

But my main question is: would cPanel have problems connecting to this remote server with Oracle Linux or AlmaLinux running ARM Ampere processor?

Remembering that the WEB server, where cPanel/WHM and all applications/sites will be running, has CloudLinux. The above questions about OS and processor I refer exclusively to the remote server with MySQL 8.

Thank you very much!

 

[cPRex]

As far as cPanel is concerned, as long as you can connect to the remote MySQL system, we don't really care what it runs, as long as the MySQL version is compatible with our tools.

 

[osnanderson]

So I'm going to start a test environment with Oracle Linux on the server dedicated to MySQL 8. I'll post the results and details here to share with the community.

1) Could you suggest me an article that I can follow, for Linux, to install MySQL correctly on the remote server? It doesn't have to be a perfect article or tutorial for this scenario, but some generic article or tutorial to build on for me.

2) About the firewall of the remote MySQL server, I wanted to ensure security to prevent attacks directly on the MySQL server. Does port 3306 need to be open publicly so that users can connect to the database and applications can also connect (even using localhost)?

Or is all the connection made on the web server with cPanel which internally redirects to the remote MySQL server? That is, do I release the SSH port and port 3306 only to the cPanel server from where the connections will come from?

Remembering that our users and clients can connect remotely to MySQL, so I am in doubt if they will connect with the IP/hostname of the web server (like localhost), or should the remote connection be made on the hostname of the dedicated server with MySQL?

What if everything works as 'localhost' in the above situations. Can I close all ports on the remote MySQL server and create a connection permission to SSH ports and 3306 starting only from the web server with cPanel?

Thank you very much!

 

[cPRex]

For issue 1, I can't recommend anything as I can only officially talk about things that cPanel tests. It *should* be as simple as installing the MySQL software on that machine.

For #2, you will want 3306 open for traffic.

Most services just listen for incoming connections, so they don't care if it's the hostname or the IP - if it hits the right port, it will respond.

 

[osnanderson]

Hi


I created cPanel server and external mysql server, created mysql profile and disabled local mysql. So I created the first database running on the mysql server and installed a Wordpress for testing. in the wordpress configuration I adjusted the mysql host of the application comp 'localhost'. But that didn't work, the site has a connection error with the database.

Do I need to make any adjustments so that the remote mysql connects using localhost in application?

Thanks

 

[cPRex]

From my testing, you shouldn't need to do anything else. Could you create a ticket with our team so we can take a look?