Whither your rollback plan?
MySQL 5.6 upgrades are in full swing these days and knowing how to safely upgrade from MySQL 5.5 to 5.6 is important. When upgrading a replication environment, it’s important that you can build a migration plan that safely allows for your upgrade with minimal risk — rollback is often a very important component to this.
For many people this means upgrading slaves first and then the master. The strategy of an older master replicating to a newer slave is well known and has been supported in MySQL replication for a very long time. To be specific: you can have a MySQL 5.6 slave of a 5.5 master and this should work fine until you upgrade your master and/or promote one of the slaves to be the master.
However, there are those of us who like to live on the edge and do unsupported things. Suppose that when you cut over to that MySQL 5.6 master your application completely breaks. What would your rollback plan be? In such a case, leaving a 5.5 slave of the new 5.6 master (or perhaps a dual-master setup with 5.5 and 5.6) would be useful to allow you to rollback to but still have the data written on the 5.6 master.
What might break?
With Statement-based replication (SBR), you are generally ok with this type of setup, provided you aren’t doing any MySQL 5.6 syntax-specific things until you don’t have any more 5.5 slaves. However, with Row-based replication (RBR), things are a bit trickier, particularly when column formats change.
Now, one nice new feature of MySQL 5.6 is the improvement of thestorage requirements forDATETIME fields as well as the addition of fractional second support for TIME, DATETIME, and TIMESTAMP. This is great, but unfortunately this is a new column format that 5.5 clearly would not understand. Does this put our 5.6 to 5.5 replication in jeopardy? The answer is, if we’re careful, NO.
Quite simply, MySQL 5.6 supports both old and new types and mysql_upgrade does not make such a conversion on existing tables. Only NEW tables or REBUILT tables in 5.6 will use the new format. Any tables from 5.5 with a simple mysql_upgrade to 5.6 will still be using the old types. For more information on how to find columns in 5.6 that are using the old format, seeIke Walker’s excellent blog post on the topic. (Thanks Ike!)
An imperfect test
To test this out, I created a simple experiment. I have a master and slave using RBR, both on 5.5, and I setuppt-heartbeatto update the master. I realized that pt-heartbeat actually uses a varchar for the timestamp field — I suspect this makes multiple database support easier. However, since pt-heartbeat’s update uses a NOW() to populate that field, I can convert it to a DATETIME:
[root@master ~]# pt-heartbeat --update --database percona --create-tableCREATE TABLE `heartbeat` (`ts` varchar(26) NOT NULL,`server_id` int(10) unsigned NOT NULL,`file` varchar(255) DEFAULT NULL,`position` bigint(20) unsigned DEFAULT NULL,`relay_master_log_file` varchar(255) DEFAULT NULL,`exec_master_log_pos` bigint(20) unsigned DEFAULT NULL,PRIMARY KEY (`server_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1master mysql> alte<p style="color:transparent">本文来源gao!%daima.com搞$代*!码网1</p>r table heartbeat drop column ts, add column ts DATETIME;slave mysql> select * from heartbeat/G *************************** 1. row *************************** server_id: 1file: master-bin.000002position: 5107583 relay_master_log_file: NULL exec_master_log_pos: NULLts: 2014-05-02 17:03:59 1 row in set (0.00 sec) CREATE TABLE `heartbeat` ( `server_id` int(10) unsigned NOT NULL, `file` varchar(255) DEFAULT NULL, `position` bigint(20) unsigned DEFAULT NULL, `relay_master_log_file` varchar(255) DEFAULT NULL, `exec_master_log_pos` bigint(20) unsigned DEFAULT NULL, `ts` datetime DEFAULT NULL, PRIMARY KEY (`server_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
[root@master~]# pt-heartbeat –update –database percona –create-table 搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权 转载请注明原文链接：Row-based replication, MySQL 5.6 upgrades and temporal data_MySQL |
