2

I have to upgrade a production server from 5.1 to 5.6 version. The favourite option would be to dump the database from 5.1 and import on 5.6 but this operation has been estimated to 2 hours on my environment which will have an impact for customers (downtime during this maintenance).

I'm trying to find a solution to limit the downtime. A replication from 5.1 to 5.6 is not possible (only 1 major release between master and slave) so I wonder if the solution to setup a replication as M1 (5.1) -> S1 (5.5) -> S2 (5.6) is viable and has been already tested/validated by someone.

Thanks for your help.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
Guillaume
  • 21
  • 2

1 Answers1

1

I have a caution to share with you in your setup.

Using MIXED for all 3 servers would be OK, but I would prefer you use STATEMENT during this migration time. Why ??? On Jan 02, 2015 I wrote the answer to this post : mysql replication master 5.5 slave 5.1 error on create database

I discussed how the format of binlog events can change between versions.

In that post, I gave an example that my former manager (at the NY Times) discovered. There was a setup of MySQL 5.5 master with MySQL 5.6 slave. Replication would break very quickly and repeatedly. My then manager found an odd trace dump in the error log with the phrase unpack:

/usr/sbin/mysqld(_Z10unpack_rowPK14Relay_log_infoP5TABLEjPKhPK9st_bitmapPS5_PmS5_+0x43f)[0x898b7f]
/usr/sbin/mysqld(_ZN14Rows_log_event24do_index_scan_and_updateEPK14Relay_log_info+0x100)[0x873c50]
/usr/sbin/mysqld(_ZN14Rows_log_event14do_apply_eventEPK14Relay_log_info+0x852)[0x877c32]
/usr/sbin/mysqld(_ZN9Log_event11apply_eventEP14Relay_log_info+0x74)[0x87fb54]
/usr/sbin/mysqld(_Z26apply_event_and_update_posPP9Log_eventP3THDP14Relay_log_info+0x263)[0x8aefd3]

Please note the Z10unpack_rowPK14Relay_log_info

What was the issue ??? It turned out that DATETIME fields would break replication. How ??? MySQL 5.6 started supporting microseconds in DATETIME values. MySQL 5.5 did not. So, when replication threads attempted to unpack the binlog event from the relay log, the format for DATETIME was wrong in the eyes of MySQL 5.6.

My manager's workaround ??? Switch to STATEMENT for binlog_format, use statement-based replication. Everything went fine from there.

You said you are using MIXED and that's OK. I would just make sure you get away from the 5.1 and 5.5 server as fast as you can (Do the Migration in Haste).

Such a setup is fine. A current colleague at my current employer did MySQL 5.1 to 5.7 in stages. No issues (I assumed he used MIXED or STATEMENT).

You should try this setup in DEV/STG and make sure DATETIME value don't keel over replication.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536