It seems so simple: I have two servers running MySQL, one has a nice big database of important stuff, the other is an empty shell ready to be used as a replication slave. These are the steps I should need to take to set up replication:
- Add a user to the master with the relevent replication & host privileges
- Run a statement from the slave telling it to 'replicate this db from this server'
- Done. That's it.
Instead I have to mess around with my.cnf settings on both servers, mess about with binary logs and positions and snapshots. Yes I know some of these things are optimisations for large databases but is this the best way they could think of doing this? I just want to replicate a database, this is a very common practice. I still have not managed it.
Where can I find a decent how-to for this? Everything I've found so-far is either missing some important step, is written for an older version of MySQL or is just plain too complex for me or requires a degree of commitment I'm clearly not prepared to take, and that includes the official documentation.
Give this a try:
http://www.howtoforge.com/mysql_database_replication
I bought the book titled "High Performance MySQL" by Schwartz, Zaitsev, Tkachenko, et al.
I have also used Slony on PostgreSQL. Wasn't straightforward, either.
Master-master replication seems a good idea.