Different server but same database

February 22, 2014 3.3k views
What if I want to create a new server on different datacenter? How do I create different server but have same database?
6 Answers
Thank you. I saw your post. And it was great. But it still doesn't suffice my question. Let me elaborate a little. It will be a community site. Where users can upload and share their thoughts. So, what will be the best practices in terms of horizontal scalling? I was hoping to create more droplets/server for that, but use have the same db. Can you please give some advice on it. Thank you once again!
Depends a bit on what you want to do.
Is the problem scaling or disaster recovery?
* If you just want to scale up the application and add more performance / a bit more redundancy on just the application server you are going to need a new application server and some way to load balance between different application servers (round robin dns, loadbalancer, geodns, logic in your app, etc.)
See https://www.digitalocean.com/community/articles/how-to-set-up-nginx-load-balancing or https://www.digitalocean.com/community/articles/how-to-use-haproxy-to-set-up-http-load-balancing-on-an-ubuntu-vps
You probably won't gain much by putting the new application server in another data center (if the database server or the network connection goes down between your app server and database server, your site is down anyway).

* If you have scaled up the application servers and need to scale up the database (mysql or postgresql) you are looking at clustering, replication, log shipping, and other heavy stuff (if you need this you probably already have 4-5 application servers already)
See https://www.digitalocean.com/community/articles/how-to-set-up-master-slave-replication-in-mysql or https://www.digitalocean.com/community/articles/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps
If you are using some of the nosql databases(cassandra, mongodb, etc) this can be reasonably simple to do by "just" adding new servers.
If you do some of these scale ups correctly you are also going to add a bit of redundancy, if you have 3 applicationserver and a decent loadbalancer one of the application servers can crash and none of the users will notice.

* If you want to add disaster recovery to your system you are basically going to add both app servers and database servers in another data center. You will have to deal with all the above issues (with at least 2-3 times the complexity) and also think about how to deal with different split-brain scenarios (what happens if the only thing going down is the network between the data centers, what to do if the same data is changed differently in both data centers at the same time, etc)
by Etel Sverdlov
MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons including facilating a backup for the data,a way to analyze it without using the main database, or simply as a means to scale out. This tutorial will cover a very simple example of mysql replication—one master will send information to a single slave.
Thank you for your detailed answer. But I think my question didn't give the correct context of what I am trying to ask. What I want to do is, create two servers for the application load balancing (please correct me if its not what I have to do). How wil I manage the database across these without database integrity? How do you guyz have multiple instance and manage database? I would really appreciate if you could answer me this. Thank you!
Oh, I misunderstood what you meant. You would need to set up master-master replication as Joachim said (https://www.digitalocean.com/community/articles/how-to-set-up-mysql-master-master-replication) and load balance between them using HAProxy (https://www.digitalocean.com/community/articles/how-to-use-haproxy-to-set-up-http-load-balancing-on-an-ubuntu-vps ).
by Jason Kurtz
This second installment of "Scaling Web Applications" will list out the steps necessary for scaling a mysql deployment over two VPS.
Have another answer? Share your knowledge.