1

Short Version: Is replication from 5.5 master to a 5.1 slave possible?

Long Version: We did a large scale upgrade from 5.0 to 5.5. It was a long process if dumping the 400gb dbs and importing them into 5.5.

5.5 replication seems to be completely broken. The masters consistently hang, the slaves keep dis/reconnecting and leaving stale binlog_dump connections (visible from show processlist). The master hangs on shutdown until I have to kill when shutdown "gives up" after an hour. Lastly it gets this type error daily "110423 13:55:48 [ERROR] Slave SQL: Could not execute Delete_rows event on table prod.site_iplist; Can't find record in 'site_iplist', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000385, end_log_pos 65644796, Error_code: 1032" which is a bug that is will be fixed in 5.5.12.

This has been very disappointing, as our 5.0 setup ran fine for 3 years.

Anyhow, I am looking to move to 5.1.56 (which has at least 56 updates to a stable product). The problem is that all my databases are 5.5 now.

Is it possible to have a 5.5 master and a 5.1 slave? The migration process being to import the db into 5.1 and then enable replication, fail everyone over to 5.1 once it is synced up, and then downgrade all the other 5.5 servers while everyone is on the 5.1 db.

Will 5.5 master --> 5.1 slave work at all? If so, will it work with the current MIXED mode replication? Would I have to change that to be statement only?

Thanks!

aspitzer
  • 967
  • 5
  • 14

2 Answers2

5

What you are trying to do is not generally supported. Quote from the Replication Compatibility Between MySQL Versions docs:

MySQL supports replication from one major version to the next higher major version. For example, you can replicate from a master running MySQL 4.1 to a slave running MySQL 5.0, from a master running MySQL 5.0 to a slave running MySQL 5.1, and so on.

and

Replication from newer masters to older slaves may be possible, but is generally not supported.

It might work for those versions, but it's far from a recommended setup. My opinion: don't do it.

Also read the Upgrading a Replication Setup

In other words, when upgrading to MySQL 5.5, the slaves must be MySQL 5.5 before you can upgrade the master to 5.5.

(and maybe this snippet although I don't think it's directly relevant if you re-import to 5.1)

Downgrading a replication setup to a previous version cannot be done once you have switched from statement-based to row-based replication, and after the first row-based statement has been written to the binlog.

Mat
  • 1,536
  • 1
  • 17
  • 21
3

Another thing to consider is this: The size of an empty binary log

MySQL 5.5 : 107 bytes
MySQL 5.1 : 106 bytes
MySQL 5.0/4.x/3.x: 98 bytes

To replicate from a higher version to a lower version is not possible because the header information is offset past what the lower version can read.

To replicate from a lower version to a higher version is possible because the header information is offset behind what the high version can read.

Community
  • 1
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80