How Often Should MySQL (Stock, Percona, etc.) Be Upgraded?

MySQLpercona-serverupgrade

My organization runs several data-driven web applications backed by various flavors of MySQL on RHEL (i.e. we're just like everyone else!) Some of our web apps use the standard build of MySQL, and we use yum update periodically to update all yum-managed software on those systems. Other webapps on other hosts use the Percona build, which we installed manually. We use yum update periodically on those systems, too, but as our installation of Percona on those builds is not managed by yum, it isn't upgraded by that process.

Is there a rule of thumb for when to upgrade a component like MySQL (e.g. monthly, quarterly, or after each release)? We want to find the right balance between staying up-to-date and spending too much time on administration. What factors affect the decision? Regarding security, we use hardware firewalls and iptables to restrict access, so MySQL/Percona is not exposed to the outermost attack surface. The systems in question are dedicated, single-tenant hosts (i.e. there are no other users on the host).

Best Answer

Personally, I cringe when I hear about upgrades gone wrong due to human error. Most have success stories when upgrading MySQL in succession (5.0, 5.1, 5.5, 5.6) Some create their own disaster movies by moving datadir, uninstalling old MySQL, installing new MySQL, putting back the datadir and think everything's OK ... NOT !!!

MySQL Upgrade

MySQL created mysql_upgrade for an inventoried check of everything that could be different from the currently installed MySQL Binaries. When you used responsibly, this is the all-out solution. It is the only to go with very large database installations. There are certain rules you must follow. Although quite rare, even running mysql_upgrade right, strange things still can happen : See In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table and my answer to it.

Alternative

For smaller database installations, I have a much more simplistic approach: mysqldump (but with caveats). The most overlooked database in a MySQL Instance is always the mysql schema. People use mysqldump --all-databases and try to load the mysqldump from an older version into a newer version thinking that's all to it. Not so. Here is why:

With each major release of MySQL, table mysql.user has a different number of columns.

The script mysql_upgrade attempts to fix those columns among the other compatibility issues to adjust. My approach total removes concerns about compatibility issues.

My Approach

STEP 01: mysqldump all databases EXCEPT the mysql schema to a text file (/root/MySQLData.sql)

MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQLSTMT="${SQLSTMT} FROM information_schema.schemata WHERE schema_name"
SQLSTMT="${SQLSTMT} NOT IN ('information_schema','mysql')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --databases ${DBLIST}"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > /root/MySQLData.sql

STEP 02: Dump the MySQL Grants as an SQL file

There are two techniques to do that

TECHNIQUE #1: Use mk-show-grants

This dumps out all the MySQL Grants as SQL statements, which is completely portable to any version MySQL/Percona 5.x

TECHNIQUE #2: Emulate mk-show-grants

cd
MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ',"
SQLSTMT="${SQLSTMT} QUOTE(user),'@',QUOTE(host),';') "
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > GetGrants.sql
echo "SET sql_log_bin = 0;" > MySQLUserGrants.sql
mysql ${MYSQL_CONN} -AN < GetGrants.sql | sed 's/$/;/g' >> MySQLUserGrants.sql
rm -f GetGrants.sql

I have recommended these techniques before

STEP 03: Shutdown mysql

service mysql stop

STEP 04 : Get old datadir and my.cnf out of the way

mv /var/lib/mysql /var/lib/mysql_old
mv /etc/my.cnf /etc/my.cnf_old

STEP 05 : Uninstall Old MySQL/Percona Server

STEP 06 : Install New MySQL/Percona Server

STEP 07 : Login to MySQL

STEP 08 : Run the two scripts

mysql> source /root/MySQLData.sql
mysql> source /root/MySQLUserGrants.sql

That's it !!!

Don't worry, I have done this before : MySQL upgrade 5.0.88 to latest

I trust my alternative approach because a mysqldump is a logical representation of the data that can be moved from installation to installation with the except of storage engine specifics. If such is the case, I would mysqldump the data without the schema in one file and mysqldump the schema only in another file. Then, I would edit the specifics in the schema-only file.

I'll leave the gory details of software installation in your capable hands...

Give it a Try !!!

EPILOGUE

There are many old sayings

  • If it ain't broke, don't fix it.
  • Fortune Favors the Bold (From an Episode of Star Trek DS9)

In light of these saying, use common sense. If you are satisfied with MySQL's performance heretodate, upgrading would just be needless downtime. Stick with what you have. Don't just upgrade because you can. I have personally seen an upgrade to MySQL 5.5 which was slower than the original MySQL 5.1 database. here are my past answers on this phenomenon:

If you want MySQL's new features, such as

  • InnoDB Multiple Core Engagement (MySQL 5.1's InnoDB Plugin, MySQL 5.5/5.6)
  • Semisynchronous Replication (MySQL 5.5)
  • Parallel Slave Threads (MySQL 5.6)

you will have to boldly go where no man has gone before.

Learn the technology you want in your database before engaging in upgrades.

Related Question