1

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.

| improve this question | |
  • Is this a 24/7 business? Can't you afford to do the upgrade - with downtime - on a maintenance window, say a Tuesday evening or a Saturday morning? – ypercubeᵀᴹ May 23 '18 at 20:33
  • Yes this is a 24/7 business env serving worldwide customers so the maintenance window is limited to sunday night, I can define a maintenance window but I'm trying to limit it and if there's a solution to avoid 2 hours of upgrade that would be very helpful. – Guillaume May 23 '18 at 20:43
  • I have one very critical question: What do you have binlog_format set to on each server ??? – RolandoMySQLDBA May 23 '18 at 20:56
  • M1 server binlog_format is set to "mixed". S1 will be a new instance so I can set to something else if required by S2 – Guillaume May 23 '18 at 21:00
  • If you don't already have replication set up for M1->S1, how will you achieve that without 2 hours of downtime?? – Rick James Jun 2 '18 at 3:33
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.

| improve this answer | |
  • Thank you very much for your answer and interesting inputs, I will implement this on our STG environment to make sure that there's no issue with DATETIME fields as explained and I will come back with results. – Guillaume May 23 '18 at 21:27
  • @Guillaume you said you'll come back with results :/ – hpaknia Aug 1 '19 at 5:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.