MySQL replication between different versions (4.1.20 to 5.0.22)

[riddlermarc]

Are there any problems/gotchas I need to look out for when setting this up? My mail server box is also now running my blog and it's blatantly time to set up some replication and offsite backup for safety purposes.

The mail server is running MySQL v4.1.20 under CentOS (linux) and the replication will be made with a Windows 2003 Server box on the same lan.. do I need to keep to v4.1.xx on the Windows box or can I use v5?

 

[riddlermarc]

nm, found this page that seems to suggest it's not adviseable:

Note: You cannot replicate from a master that uses a newer binary log format to a slave that uses an older format (for example, from MySQL 5.0 to MySQL 4.1.) This has significant implications for upgrading replication servers, as described in Section 6.6, “Upgrading a Replication Setup”.

http://dev.mysql.com/doc/refman/5.0/en/replication-compatibility.html

 

[Beansprout]

Replication for a blog?

First thing's first you'll need another server running the same major version of MySQL (4.0.x, 4.1.x, 5.x) because there's quite a few changes between them.

I'd just run a mysqldump and store it on another server, and on that server run the same version of MySQL so you can restore the backup with no problems if need be.

But then...if the server hosting the blog fails (assuming it's also the primary MySQL box), you're stuffed either way assuming you don't also replicate the www files.

 

[riddlermarc]

Replication for a blog?

Yep I'm setting it up as password-protected under Apache so it isn't public, that way members of my family across the country/world can blog it as a way to keep the extended family up to date with goings on.. therefore I want it to be a long-term thing and don't want a server crash to knacker the blog site completely.

I am also going to make a daily backup of the entire wwwroot directory and export it to the Windows box..

I did spend a couple of hours setting up the MySQL replication yesterday, it's working perfectly now using identical build versions and it saves me having to mess about with mysqldump etc.. part of my job is working with SQL Server (analyst programmer, lots of SQL/.NET stuff) so replication is the obvious choice for on-the-fly data duplication and protection.

 

[Beansprout]

Very nice