New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
SQL Replication on 2 VPS
Hi,
I have 2 Ramnode VPS's, NY and Atlanta.
VPS #1 (Main): Hosts a database used by an application to authenticate users
VPS #2 (Failover): In the event VPS #1 goes down, VPS #2 needs to have an up to date copy of the database on VPS 1.
What would be the best way to make sure the database on VPS #2 is in sync with VPS #1.
People make purchases every few minutes, and when a purchase is made, a username and password is added to the database on VPS #1
Comments
High end solution, Galera cluster and innodb. 1
Low end solution, master-master replication with auto increment id offset. 2
https://www.linode.com/docs/databases/mariadb/clustering-with-mariadb-and-galera
Galera needs min 3 servers to function properly
Should this be a typical master/slave setup? The OP never asks the slave to step up for write when master is down.
I assume so. OP only need one way.
I assumed that OP wants the #2 to be up and running when #1 is down. Running = Write involved.
It's a good time to grab their Seattle service.
When #1 is down, I just needs #2 to have all the data #1 had.
No sales will happen when #1 is down, so no new entries will be added to the database.
You could have 2 DB insert calls, one goes to localhost and the other goes to the 2nd VPS. This is assuming that both DBs are the same at the time you implement 2 DB insert calls. I'm also assuming that the database isn't transaction heavy (inserts / deletes / other stuff) and you're willing to deal with the latency.
Means that you can run a simple master/slave
Yeah just run Master <-> Master setup, tunnel it with autossh, all good.
Could also use the built-in SSL settings.
Would obviously require #2 to always be up and running, unless you temporarily change the app/stored proc code so it does CRUD to #1 only during maintenance, but in this case you end up with two inconsistent databases and need to re-load data into #2 once the maintenance tasks are carried out.
Personally, I would go with any existing replication solution.