Question on remote MySQL

[jdhf99]

Here is my situation, I run a free hosting company and we have about 15,000 accounts spread across three servers. I was thinking about getting an additional server to handle just MySQL. Would this be a viable option for me? To have a single database server and have the other three computers connect to it?

With so many accounts I'm not sure if it will work? I really have no experience with remote services like this.

Thanks for any input!

[nottheusual1]

Tough question with two answers:

Yes - it is possible, but you would need an enterprise-type installation vs what comes on a standard *nix box to handle the sheer magnitude of base users (domain associations - 15k+) and very serious security and admin tools with possibly some custom scripting if you wanted to tie it into your existing management system.

You would not be able to manage it with stock mysql and phpMyAdmin. What a remote_hosts file that would be to maintain!

No - it creates a single-point-of-failure for 15k+ domains without substantial resource investments (RAID's, fallovers, replication schemes, etc.).

It also creates a greater security risk by having to expose mysql ports to the internet. Many would argue that there is no safe exposed mysql port.

Our solution was to offer a bigger/faster/better servers for heavy DB users who didn't want a dedicated server. The cost benefit analysis we did said that such a high percentage of users had such small mysql "footprints" that the actual resource consumption was negligible, far outweighing the necessary investment once you factored in utilization and overhead.

However, that being said, we do have customers that have dedicated database servers - notice plural server - because of their data requirements.

BTW - 15k users is impressive. Nice job!!

[jdhf99]

Thank you very much for your detailed reply!

I wouldn't mind getting an enterprise type setup for the MySQL portion as long as it WAS manageable and took the current SQL load off of all my other servers. The cost of one enterprise level MySQL setup for me would be less then putting additional servers online. Right now MySQL is using up a good portion of the servers processing power limiting the amount of accounts I can keep on one server. So my thoughts are that I can add additional accounts to each server from the processing power I'm saving by having a remote MySQL server.

So I guess now my question would be, I know WHM has a built in remote MySQL option now. By using this option do I need cPanel on the MySQL server also? When new accounts create a database\user, etc... it does create it on the remote server automatically, correct?

Thanks to everyone for their time,

-Corey

[nottheusual1]

Here are some links. The first is an actual CPANEL training presentation on what you are looking to do:

http://trainingseminar.cpanel.net/sl...e%20Server.pdf

This guy answers your question directly:

http://forums.theplanet.com/index.php?showtopic=60912

What isn't obvious is if you'll be able to do it with multiple servers feeding one mysql server and if so how is it administered.

Best of luck!!

[jdhf99]

Great!

Thank you so much for your time and research. It is GREATLY appreciated.

-Corey