All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Database server
Fellow forum members,
I would like to setup a VPS as a database server only. I will assign the hostname being mysql.example.com.
On another VPS, I will use it particularly to host scripts, so example.com will connect to database at mysql.example.com and other sites hosted on example.com (e.g example1.com, example2.com, and so on) will connect to database at mysql.example.com as well.
Obviously on mysql.example.com I would need to setup php server as well, so people can access the database via mysql.example.com/phpmyadmin, while at example.com I would not setup a database server in order to reduce load.
I am sure that someone has did this before, so I would like to hear your comment about it. I know this is a too general situation and there will be a lot of different perspectives. Either way all these will certainly give me some lights.
Thanks, Sigit
Comments
https://www.digitalocean.com/community/tutorials/how-to-install-linux-nginx-mysql-php-lemp-stack-on-ubuntu-12-04
then
https://www.digitalocean.com/community/tutorials/how-to-install-phpmyadmin-on-a-lemp-server
:-).
@catalystium yes, I know how to do that. Sorry I did not make my point clear.
I would like to know how does this affect to server load, is it a good idea? Also there will be a lag between 'localhost' and 'mysql.example.com', because the latter will need to connect to another server while the first one doesn't have to go outside the server. But this lag, is it significant? In the past, I've read one article about the disadvantage of using database in another server (remote database), I want to know someone's experience about it, tho. But thanks for your comment.
There will be some lag difference, just depends on how far away its hosted from where you are calling it from. IE if your database server is in LA and your calling it from some server in NY there will be some load time added for it to retrieve information.
Best bet would get a central location between your servers or one server with the same host so its almost local.
If any of that makes sense.
If you don't want PHP on the mysql.example.com, you can also install PHPMyAdmin on the primary example.com then configure it to access mysql.example.com databases.
Try using servers with low latency to each other to reduce the lag.
Further question, then.
So I have these two VPS, one is:
128 MB of RAM
128 MB of vSWAP
(from cat /proc/cpuinfo: cpu MHz : 3400.023)
The other one is:
512 MB of RAM
64 MB of vSWAP
(from cat /proc/cpuinfo: cpu MHz : 2666.588)
All these have 100 GB of bandwidth each, I think this is enough for my setup, there won't be much traffic for the moment for example.com.
From between these two, which one do you think will be the better for database server, and which one will be the better for hosting php scripts? As you can see, one has better ram than the other, but according to cpuinfo the lesser RAM has the better cpu speed.
Thank you all for your advice.
Edit:
At this moment I plan to use the first VPS with 128MB RAM/128MB swap for database server, and the other VPS with 512MB RAM/64MB swap for hosting PHP scripts such as Drupal or Wordpress. I think that Drupal will require more RAM than mysql, eh?
All databases are memory hog, I could not see any benefit you host your DB in a dedicated 128 MB box, why not just get a 1G mem box and host your DB and php stuff in one box?
You are not in the bottleneck and have to scale out, in fact, you are lightyears away from there, why bother all the trouble?
Also, you need to secure you mysql <-> php link with openvpn/stunnel/tinc etc; you can not leave mysql port wide open to the internet.
That is not obvious. PMA does not need to be hosted on the same server as MySQL. :P
I am not aware of this. These are two idle boxes I have in hand, and I am exactly looking for an insight like that. So, thanks! As people always said, in my situation, there's nothing wrong with an experiment (as long as I go within TOS/AUP, of course).
The experiment might go, in another lightyear or so
with several database servers and load balancing among them. I am learning from all your inputs, which aspects I need to learn more and which perspectives I need to anticipate. So this is the reason behind all these ...
I was thinking to put phpmyadmin on root index of mysql.example.com, instead of in a subdirectory. Combined with @bootstack perspective above, that won't be appealing, obviously
If you using mysql in other box make sure mysql port is only accesable from your host ip
Deal with that when you get to it.
Run your scripts and DB server on the 512MB box and every day back everything up to the 128MB box
Hassle free and you will make use of both boxes.
Interesting. Long time since I have made use od PMA and didn't know this was possible nowadays. What would be the rationale for that? Is it possible to administrate databases on more than one DB server from one PMA, for example?
Yes, it is. Create a config file.
Pay attention to the traffic usage. For a large site I'm running, it costs terabytes of traffic every month between db and web. Besides that, not a big problem.
Yep. That's what I do. (https://wiki.phpmyadmin.net/pma/Config/Servers) Less than 20% of my fleat of servers have PHP installed on them - 90% of my servers use MySQL locally.