Good Afternoon MySQL DBAs & Gurus, Preface: I am an Oracle RAC DBA at heart. I apologize if I'm making this more difficult than it needs to be. Setup: MySQL Community General Linux version 5.6.19 with GTID Master/Slave environment. All schemas for the application are INNODB.
TL;DR question: For those of you running GTID, do you run mysql_upgrade, and if so, how? Really Long version: I am applying patch 5.6.21, specifically for the PITR bug fix, though the others are needed as well. My process is to stop the slave, apply the patch to the slave, then start slave back up to ensure there are no problems and binlogs are applied/GTID moves forward. Then, during a downtime, stop the application, stop the master, do a "Reset slave all" and "reset master" on the old slave, move the application to point to the old slave/new master, and VOILA, application is using an upgraded database. After a few minutes, reset the old master/new slave, and all changes recorded in the GTID since the RESET of the old slave/new master will be sent to the new slave as long as I set the new slave up before any of the new logs are expired. Eventually, I upgrade the new slave as well. My problem comes with the MySQL_UPGRADE. I understand the instructions are as follows at "http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-restrictions.html#replication-gtids-restrictions-mysql_upgrade"; : ". Prior to MySQL 5.6.7, mysql_upgrade could not connect to a MySQL Server that was running with --gtid-mode=ON unless mysql_upgrade was run with --write-binlog=OFF. (Otherwise, mysqld had to be restarted with --gtid-mode=OFF before running mysql_upgrade, then restarted with --gtid_mode=ON afterwards.) This is not an issue in MySQL 5.6.7 and later, where mysql_upgrade runs with --write-binlog=OFF by default. (Bug #13833710) However, it is not recommended to do so, since mysql_upgrade can make changes to system tables that use the MyISAM storage engine, which is nontransactional." The wording in this has me confused, and I'm hoping someone else can help clear it up. Are we not supposed to run mysql_upgrade at all if we are using GTID? Is the database considered upgraded if we do not run mysql_upgrade? Or are the instructions to run mysql_upgrade, but first modify the my.cnf to GTID_MODE=OFF? If that's the case, why the fix in MySQL 5.6.7, where Mysql_upgrade will go around GTID? The result would be the same - changes that don't get logged. If it is only for system tables that won't get written to the binary logs, or for modifications to tables without modifying the data, I think my process would work around this, wouldn't it? It will update the slave, the slave becomes Master, and I've got an upgraded database. Thank you so much, ~ Tyfanie Wineriter ~ Database Administrator University of Oregon 1212 University of Oregon Eugene, OR 97402-1212 (541) 346-1366
