Ask Your Question
1

Seeking direction re MySQL update

asked 2016-06-09 11:33:45 -0500

slnm gravatar image

updated 2016-06-11 08:39:11 -0500

I've taken over an IT job and I'm new to puppet so please forgive my ignorance.

I'm running puppet 3.7.4. The nodes that puppet manages are running MySQL 5.1.73. Management has asked me to upgrade MySQL on the nodes to 5.7.13.

I see that there is a modules/mysql directory. metadata.json has this identifying information:

"name": "puppetlabs-mysql",
"version": "2.1.0",
"summary": "Mysql module",
"author": "Puppet Labs",
"description": "Mysql module"

I've got a couple of general questions. I'm sure I'll have more over time.

  1. How can I figure out how the current version of MySQL is installed on the agents? I'm guessing it's through yum but I'd like to find evidence that puppet is doing that. I imagine I can look through /var/log/messages on an agent but I'd like to understand how puppet is picking the version and doing the MySQL install.

  2. What's a reasonable approach to upgrading MySQL and leaving a copy of the current version on the agents in case I need to revert back?

At this point I'm just hoping for some direction.

Thanks very much.

Update 6/11/16: Since posting this question I do see that yum is managing the mysql packages. I was able to update mysql as follows:

curl -OL http://dev.mysql.com/get/mysql57-community-release-el6-8.noarch.rpm
rpm -Uvh mysql57-community-release-el6-8.noarch.rpm
yum update mysql

I had to make two changes in my.cnf. Then I had to do this in one shell window:

mysqld_safe --skip-grant-tables

And, I had to do this is another shell window:

mysql_upgrade -u root -p

My remaining question is how do I puppetize this upgrade? In particular, this business of having to run mysqldsafe skipping the grant tables so that I can then run mysqlupgrade seems a little bit tricky. I have 100 VMs to update so I do need to automate this!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2016-06-10 10:55:34 -0500

ramindk gravatar image

Your current version of MySQL should be coming from the standard OS packages based on it's age. You can use rpm, dpkg, or whatever the OS supports to determine if that's true. It's possible Puppet has set external repos for Mysql, but based on the age of the version it's unlikely.

In regards to your next questions, how to upgrade, you're in for a world of hurt. The proper upgrade path from Mysql 5.1 to 5.7 is first to 5.5.x, then 5.6.x, and then 5.7.x. I would also suggest doing this upgrade path with a copy of the database while you work out the sequencing. Note a number of config values have been deprecated and may cause error in 5.7. I'd pay attention to the messages at each upgrade point.

I have done the 5.1. to 5.5 upgrade a few times,. IIRC it was relatively straightforward. 5.5 to 5.6 was more complicated. I had to dump the db and reimport to make that work. User accounts were a bit tricky as well for some reason.

Once your upgrade process is solid you'll need to look at how how much downtime you can stand. If the db is trivial, take the machine down for the day and do the upgrade. If the db needs to be up 24x7, you'll need to make a slave, upgrade the slave, make it master, cut over, test, etc. Puppet is not useful for this sort of work since it's all about the end state rather than how to do tricky updates with multiple states.

I could easily write another page on things to keep in mind when updating Mysql on a live site. I suggest you do your research thoroughly or pull in some help.

edit flag offensive delete link more

Comments

Thanks for the answer. I've figured out how to do the upgrade manually and, fortunately, didn't have major issues going straight to 5.7. I've updated my post with details and the question of how to puppetize the upgrade.

slnm gravatar imageslnm ( 2016-06-11 08:41:12 -0500 )edit

I think the best answer is to use something like mcollective to manage the 'manual' steps across your VMs and batch the commands

DarylW gravatar imageDarylW ( 2016-06-11 19:02:42 -0500 )edit

The only other thing you could do is add a one off script to do the upgrade, and have it either create a lockfile once it has run, or check some sort of state

DarylW gravatar imageDarylW ( 2016-06-11 19:03:21 -0500 )edit

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

Question Tools

1 follower

Stats

Asked: 2016-06-09 11:33:45 -0500

Seen: 42 times

Last updated: Jun 11 '16