Monthly Archives: February 2014

MySQL Percona 5.5 upgrade to 5.6

IMG_7048photo by © erin o’neill 2014 Las Vegas Int’l Airport

I like to prepare for the worst, especially with Server upgrades. I read through the gotchas, about server settings default changes, passwords being a problem and maybe having a slave 5.6 and a master 5.5. But Percona made the upgrade sound super easy. Too easy.

This was a simple setup used for stats. It wasn’t even that large of a dataset. I already wasn’t using old passwords (hey the install was a new 5.5 and I didn’t allow old passwords there). I’d tuned the server a bit, specifically using innodb_file_per_table, but I’ve been using that since I could.

I backed up the my.cnf file, the schema, the mysql data, the data of my tables. I was good to go. I also dropped my databases for good measure. AND I put the show slave status into a file for later.

Since I was already using percona 5.5 I didn’t need the repo. This was on Ubuntu, not my fav OS. In fact I find Ubuntu a pain in the rear and prefer CentOS. But this was not my choice. I just ran the following:

apt-get -y install percona-server-server-5.6 percona-server-client-5.6
apt-get -y install xtrabackup
apt-get install -y percona-toolkit

Turned out that while xtrabackup was up to date, percona-toolkit was not. Then I started mysql. the apt-get turns out to have run the mysql_upgrade so that wasn’t even needed! I checked my mysql error log and there were no serious errors.

Importing the schema was where my big worry lay because this is a data warehouse which means they use evil stored procedures and views. I get it that data warehouse setups really need stored procedures but views?? I’ve already ranted enough about that. The import went fine. That kind of worried me as I thought stored procedures and views might not upgrade well as they usually don’t. And they’d already given me a major headache in the upgrade from 5.1 to 5.5.

Then I imported the data. Again no errors. Hey I thought this upgrade was supposed to be slightly gnarly???!!

Then I ran the CHANGE MASTER which my dump got wrong. No worries I ran a show slave status to a file after stopping the slave so I had the info. I put the proper file and file position in my CHANGE MASTER command. Then it scolded me for using user names and passwords. WTF??!! Oh, I’m supposed to use an auth plugin. I ran start slave as normal and the show slave status showed that the two replication threads were running and included a WHOLE lot more info. Good thing I’m no longer using perl to grab that info as I would have had to modify my scripts! Not a fun exercise at all.

I’m testing the percona mysql 5.5 master to percona mysql 5.6 slave. Next week I’ll upgrade the master. That’ll make the devs happy as they’ll have more diagnostics for their stored procedures. I hope it’s also faster and less CPU intensive. But it is a data warehouse and one can not escape full table scans….

Oh, and MySQL is gonna scold me every time I run a CHANGE MASTER as I don’t have an auth plugin. I’ll continue to ignore it, but it’s nice to know I can add auth.