3

I am building a site to be used by clients it would store there basic information and projects or services there paying for to my company. The entire login + panel would run under SSL/HTTPS but my main concern comes down to Database Replication to prevent any events where something is lost.

Because some of the projects are hosted by me for the clients I need a way to assure there data is safe and sound. At the moment I am using Media Temple GS service but will move to DV service ones more customers start to pickup.

Based on my personal knowledge I was thinking of doing something like you would do with Hard Drives. Where there is a Master and then Slave. In SQL terms there would be a Master (Index) Database and there would be few Slaves (Cache) Databases.

But the question is, what would be the best way to replicate or to backup the Master onto Slave(s) and should I have additional GS or DV servers or is using the same server but with different DB name good enough?

Edit I did some looking around MT and came accross there MySQL GridContainer which seems to do the same as owing 2nd server. Would this be a good alternative to actuall 2nd server?

Improve this question
7
  • There is no value in replicating a MySQL database running on a server onto another MySQL instance running on the same server. Configuring normal MySQL replication to replicate databases onto other servers is fairly straightforward - you just setup MySQL replication users that the servers can use remotely. Commented Mar 9, 2013 at 19:46
  • 1
    @PhilipWhitehouse you don`t understand replication used for backup. Replication for backup allows you to stop database (slave) and make complete backup without stopping master database. You can use same machine for that, because slave database is just a "snapshot" that you may move/copy to other server. Commented Mar 9, 2013 at 20:16
  • @Kamil I specifically said "running". Snapshot is totally different. The user is talking about live caches. Commented Mar 9, 2013 at 20:40
  • @PhilipWhitehouse OK, I thought you didn't know about replication+backup idea. And I used quotation marks intentionally where i wrote "snapshot". Commented Mar 9, 2013 at 20:54
  • @PhilipWhitehouse sorry probably made my self bit un-clear by Cache I was referring to a Cache/Snapshot of the Index record. Commented Mar 9, 2013 at 21:36

1 Answer 1

Reset to default
3

Idea of replication for backup is replicating database to another database that you can stop and create full backup of that stopped database, while your production database is running.

You can use same server for creating backup files, but don't forget, that backup can ruin server performance (hard disk load). Additionally - when database is big, and you need historical backup files - you may need to compress backup files, and compression opertion will ruin your server performance totally.

You can't avoid second server, because you have to copy backup to another machine anyway (backup on same machine makes no sense).

So in general - it's better to replicate to another machine, which can be used also in crisis situations, when master server is down.

I found nice article about many solutions for high availability MySQL: link to mysql.com.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Well MT does provide a powerful SQL pool. And as I said it will get the the point where secondary DV will be needed to provide data availability. But I am not sure how to do it in the present event.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.