I can sell you two server computers, each one set up as a MySQL database server. You will use one of them just as a normal MySQL server, as you use your existing MySQL database; this one we'll call the "master". The other one, the "slave", attaches to the master and any change that is made to the master, the slave does soon after (within seconds).
If the main server fails, due to bad harddrive or any other reason, you can switch your websites or other applications to use the IP address of the slave server. Your infrastructure will be unavailable for only as long as it takes to switch; switching can be done manually, or set up to happen automatically. (Once you start writing data to the "slave" server, you should not move back to the "master", as the data does not go from slave to master automatically.)
When the first server is repaired or replaced, it can be brought back as the "new slave" to the one you now using (the old slave). This can mean the database has to be unavailable while you sync the new databases, but this unavailability can be scheduled and planned, unlike the first failure.
I can set up and deliver the pair of servers to you. You will have to load your data on the master and make your website and applications start using the master. I will provide you with instructions for switching to the slave on failure, or a script to do it automatically if necessary. I will also provide you with instructions for bringing back a new slave, so you can do it yourself or hire any technical person to do it (I cannot promise to be available, as that may happen years in the future).
Depending on the hardware you buy, and any special configuration or programming out of the ordinary, the price will be from $5,000 upwards. I will give you a custom quote for your situation.
The master server is configured to have a "binary log", which is a file which records all the SQL actions that change the database. The slave server follows this log, running each action the master server runs. There is a way of marking a position in the log, so that a slave can pick up from a particular point.
To keep from doubling all database traffic on your network, each of the two servers has an extra ethernet card, and the replication traffic flows over these.
A secondary advantage is that database access that only reads the database, and does not change it, can be done on the slave server. (The data may be a few seconds or more behind the master.) This removes load from the master, speeding up the overall system. Backup dumps can be done from the slave.
Especially in the case that backups are being done from the slave, you should have some sort of automated alert should the replication stop happening and need to be restarted.
Here are some web pages describing the setup in various levels of detail:
MySQL Replication Documentation - you should read all of this if you will set up the replication yourself, and you should probably look it over even if you are paying me or someone else to do it.
Simple Setup of Master-Slave - a good document, does not cover forcing replication traffic to go through a second ethernet card.
How To Repair MySQL Replication - this document tells you how to restart replication if for some reason an SQL command on the master gave an error on the client, by skipping that command.
Fighting MySQL Replication Lag - this short article will give you an idea of some of the issues in dealing with replication.
For most people I would advise buying two Dell Poweredge 2950 servers, with large harddisks and a minimum of 4 GB of RAM, or similar hardware from another vendor or used.
If your primary goal is simply to be able to painlessly handle a hardware failure, and your database is not under extreme load, you probably don't need high end or expensive servers. The primary goal in that case is to simply have two servers always available. For simplicity I would have both servers have identical hardware, but that is not necessary. There are many businesses for which two old desktops set up as master-slave replication are all that is needed. I can provide a MySQL master-slave setup installed on very cheap computers if that is what you need.
Consider the amount of time your database would be unavailable if the server quit working. This time would be the time it took someone to realize what happened, added to the time it would take to obtain and configure a replacement computer (you may have a spare handy, so this might be no time at all), added to the time it would take to read the backups into the new server.
The time it takes to read backups back into a new server can be quite long if your database is large, and may be longer if the backup is stored over a slow network or on slow media.
In addition you will lose any data written since the last backup.
You can estimate the probability of these events happening from how often your servers fail.
Consider also, that over time the amount of data in a database and it's importance to business operations may slowly and imperceptibly grow, so that while not having it for 24 hours may seem acceptable when you are first setting it up, by the time it does fail all business comes to a halt without it.
Note that database replication is not a substitute for backups. Database replication protects you from failing hardware, but it does not protect you from doing something like accidently deleting data, or dropping a key table or database; in that case the deletion will be replicated to the slave faster than you can stop it. You still need to do backups, so you can undo changes. Database replication only saves you from server failures.
Most businesses can accept the risk, that once two years or so, their database and any website or application that depends on it will be down until they attend to re-creating from backup, and they will lose any data written that day.
If you dump your database every night, and restoring from that dump takes an hour or less, and losing any data written during the day is a cost you can ingest, then you should probably not set up replication. Instead, you should focus on having a second server ready to go (maybe not actually powered up), and making sure your backups are complete and happen reliably and automatically. When you have a failure, restore your backups to the spare server and accept a few hours of data lost, and you will be ok. (Note that your spare server does not have to be as powerful as your regular one, so long as it is sufficient for you to operate.)
Here are some alternatives you might consider:
MySQL does have a more complete clustering setup, called "NDBCLUSTER". NDBCLUSTER should allow you to write to any of the servers, and more communication between the servers will keep all the servers synchronized continuously.
However, it requires more computers, at least three, to operate. Some versions of it require that all the data fit in RAM (the newest code apparently has a way around that requirement). The cluster is generally more complex than the master-slave replication, seems like it would require more customized setup and maintenance. There are also some features of normal SQL that might not work for NDBCLUSTER.
For further research, I would start here: MySQL Cluster Documentation, known limitations, and MySQL Clustering FAQ.
To do a MySQL Cluster, I would advise purchasing four of Dell's newer line of Poweredge computers that have four ethernet ports per computer. This would allow each of the four servers to be directly connected to the three others. However such a setup would require research and experimentation and I cannot sell it pre-configured.
"Master - Master Replication" is the name given to a master-slave replication such as I provide, except the master also follows the slave's log. Thus either server can be written to. MySQL is smart enough not to send the same command from one to another and back again in a loop.
This can have issues with data being briefly out of sync. With master-slave replication usually the applications or web sites only use the master, so
One server is set to create only even auto-indexed fields and the other creates only odd, so that if both servers INSERT to a table at the same time, they don't end up conflicting. This can cause problems if you have code that presumes every auto-incremented field is one higher than the previous one, instead of correctly retrieving the new auto-incremented field value.
The possibility of having to check and fix code that uses the database is why I advise against this. In this situation you should not simply attempt to use the Master-Master configuration and see if it works; the bugs introduced may be subtle and hard to test for, without care forethought.
A trigger is a bit of code that runs when a certain event happens in the database. You could set a trigger, that when a table had an "INSERT" or "UPDATE" SQL command run on it, it would also run that same command on another database server. This would amount to a sort of hand-configured replication.
I don't deal with trigger based replication because it is not simple to setup and maintain, and the users of the database have to make sure that new tables have the correct triggers. However there are programs which attempt to do all of that automatically, and one of them might be best for you. There is a good list of such programs in the Firebird Database FAQ: third-party trigger based replication.
Postgres is another Free Software database, that is fast and of high quality. It does not have replication in the officially supported code, however there are modifications that have replication. The Postgres project generally has high standards for code, and these replication projects may be useable even though they are not official.
The closest thing Postgres has to the MySQL master-slave replication is warm standby. The "slave" is not available for reads in that configuration.
Other popular Postgres replication schemes are Slony-I and PGCluster. There are also number of other projects listed on the Postgres website.
I have not offered Postgres replication servers to anyone purely on the basis that most of my customers are already using MySQL. Doing a Postgres replicated server would require a bit of research, and you would have to be prepared to provide more of your own support; the official Postgres project might pick a different way to do replication, and everyone else might abandon the code you are using.
Research - it would be a good idea to look through and try to understand the various links above, even if you are not a technical person
Estimate your needs - you should have an idea of how big your database is, how many requests per hour it handles, and what the costs of downtime or data loss would be
Contact Me - send me email at rgr@sdf.lonestar.org if you think you might like to buy MySQL replication servers from me