1

I'm running a server which is connected to an SQL host. I have an another server and I decided to run it as an SQL backup. So, I have 3 of them. Srv A is the SQL host, srv B is the backup.

I know there's mysql replication, but it's simply not for what I like (correct me if I'm wrong). I'd like something distributed, so if the srv A comes back, it won't overwrite the database built during the downtime on srv B. I only have 3 servers, so setting up a cluster is not an option.

I'd be glad if anybody could help me with that.

Marsel Baker
  • 13
  • 2
  • Its probably worth mentioning that if the reason for downtime is some statement like `DROP database XXX` which has also replicated to the slave, then you would want to restore a good backup of slaveB to masterA. Hence typically you want to aggressively backup server B using `mysqldump` and cron, or some other mysql backup manager tool. You would want to research point-in-time recovery, which is a feature of some of the enterprise mysql backup tools. – Tom May 27 '12 at 17:18

2 Answers2

5

Using a master-slave configuration, and taking backups off the slave is a fairly standard strategy with MySQL database. The process of protecting the data from over-writing is addressed during the recovery phase of failover and failback.

Typically you would use the slave server B to do a full or incremental backup of the database (mysqldump -h serverB --all-databases --lock-tables --other-options), in a consistent fashion without effecting the master database with locks during dumps. This is useful because the slave is an identical replica of the master.

First the master A is configured with the mysql bin-log directive to make replication available to slaves B .. and potentially C,D etc.

But also slave B is configured to keep a bin-log of transactions. (which would normally be empty, as it shouldn't log replication updates, unless you are chaining slaves)

Once serverA has failed, the master role is moved to serverB, and B now starts to log to its own bin-log file. At this point of the failover operation, you would manually disable replication from A to B, (mysql -h serverB -e 'stop slave' ) because as you mention, you want to protect B from the failed server A.

What I mean by the "master role is moved from server A to server B" is that you would change your application to write CRUD operations (create, replace, updates, deletes) to the server B address. e.g. mysql -h serverB -e 'INSERT INTO table X'. In a 2-node setup, you would also migrate the SELECT queries as well, because you have no clustered read-only role distinct from the master-role.

Now is the sys-admin task to bring A back online as a slave of B.

If it was a clean failure, A is now some number of events behind B, but the binlog on B contains a record of those events. Hence you can replay the masterB binlog onto slaveA (it contains basic SQL statements)

If server A was completely destroyed you may opt to restore the mysql to A using an full-backup taken from B using either a recent dump, or using a tool like percona innobackex script from the xtrabackup package.

You should now configure replication in the opposite direction, to allow slaveA to replicate from masterB.

Now A and B should be identical. If you have a good reason, such as that slaveA is a much higher spec machine, then you can now switch about the replication direction to restore the masterA-slaveB configuration.

Other strategies to deal with this scenario (of failover, and failback) include MMM, multi-master replication, or the percona replication manager tool (which I have not tried in production)

Tom
  • 11,176
  • 5
  • 41
  • 63
1

I only have 3 servers, so setting up a cluster is not an option.

If you are passing data between them then they are by definition a cluster. And a cluster is exactly what you describe as your objective. In MyQL speak there is a very specific type of configuration refered to as an NDB cluster - this is probably not the right solution for you.

so if the srv A comes back, it won't overwrite the database built during the downtime on srv B

It's only going to do this if you are using auto-increment columns or other values generated from a sequence - and mysql has specific functionality to avoid this.

I'm running a server which is connected to an SQL host. I have an another server and I decided to run it as an SQL backup. So, I have 3 of them. Srv A is the SQL host, srv B is the backup.

I don't follow - do you have three database servers? Or 2?

Regardless you need to set these up as a master-master pair with asynch replication (NOT master slave). If you want to add a 3rd node then add it as a slave only. This avoids have to worry about promoting slaves in the event of a failure - you just need to route the traffic to the other node (it's also handy for backups and schema updates). There are many ways of achieving this - but the most sensible approaches are fencing at the client or using a virtual address.

I'm not going to describe the process here, because space is limited and you need to understand exactly what you're doing. There's also lots of guides on the internet - but you might want to go buy a good book (just noticed that O'Reilly have brought out this one which is even more apposite). and stick to the methods described there.

symcbean
  • 21,009
  • 1
  • 31
  • 52