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.
MySQL Replication Master - Master
Hey guys, I have 2 VPS from different hosts.
I want to have sync'ed mysql databases so if one vsp goes offline, the other can still write to the database. And when it comes back online, it will sync automatically.
Anyone knows an easy way for me to set this up? Thanks!
Comments
Replication via phpmyadmin perhaps?
http://santoshkumarsarkar.blogspot.com/2012/06/mysql-replication-with-phpmyadmin.html
@Fliphost Awesome, thanks. Months ago I wanted to do that
@DestroyeRCo No problem, glad to help
I would do this only if the databases are very close (e.g. different data centers nearby), especially being master master. Long distance / high latency can introduce issues with data being out of sync if in async mode.
@Fliphost But that is for Master - Slave Replication.
@concerto49 the database can be out of sync it does really matter as long as it resyncs once the vps comes back online.
You can loose data this way.
You want MySQL Cluster
I thought MySQL cluster uses a lot more resources than MySQL Master-Master right?
If you have one more vps, you can use galera or percona xtradb cluster for synchronized multi-master that works over WAN.
Basically I have 1 database that is read/queried more than written to... MySQL Cluster or 3 VPS requirement maybe be over the top for my setup.
Currently that database is on 1 VPS and when it goes offline because of any reason... I want to be able to access the database on another VSP. If I do make any changes on that it will be passed on to the offline VPS as soon as it appears online.
Is this even possible?
this
MySQL master-master replication as others have said. Make sure you keep enough bin logs to recover from. If your VPS is down for a few days and you only keep a days worth of bin logs on the other master, once it comes back up it won't be able to resync. You'll need to dump and copy over the whole database if that happens.
Another thing you'll need to change is the auto increment increment and auto increment offset for auto increment fields if you are inserting new data on both masters at the same time. You'll want different offsets otherwise inserts on one master will clobber inserts on the other.
@t3k9 Thanks for nice info!
You might also want to look into Percona XtraDB Cluster. It's based on the InnoDB storage engine, and does multi-master replication with minimal configuration required.
Have a look at this guide, one of the best I have found so far: http://brendanschwartz.com/post/12702901390/mysql-master-master-replication
He does it using SSH tunnels. So you don't have to worry about making your database listen on everything.
@NickM I think it requires 3 VPS? I only have 2.
It works with just 2, but can lead to problems if the nodes are both online, but unable to talk to each other. To get around that issue, you could run two instances on one VPS.
@Centaur Thanks, I'll try that
@NickM even numbers lead to brain deadness. You'd need garbd running elsewhere (very light) to make quorum.