Upgrading your MySQL Server Farm

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

There are several advantages to keeping up to date with the latest major versions ofmysql. To provide ashort example using MySQL 5.7:

Let application developers benefit from new functionality, e.g. JSON in 5.7 Use your hardware infrastructure more efficiently with parallel slaves in 5.7 Improve monitoring or management for DevOps with more performance schema instrumentation in 5.7 Improve security by using features like transparent data encryption, firewall, auditing, and sha256 authentication in 5.7 Keep within the EOL window for access to maintenance releases and support

This blog post explains general activities in an upgrade project and what to look out for prior to an upgrade between major MySQL Server releases. I use the example of ‘web companies’ because they commonly have 24x7x365 requirements, but much of this advice applies to all deployment types of MySQL.

Our goal in the MySQL development team is to make upgrades as smooth as possible, with no downtime (or only minimal downtime) required.


A typical situation for a Web company providing online services to its customers is to have a set of end user applications running on top of a backend MySQL Server farm. The server farm will typically use replication for redundancy and scale out. The Web company will want to eliminate or minimize service downtime while an upgrade is going on. Depending on size and complexity of the Server farm this can be a challenging and time consuming task.

Some Web companies will take an incremental test in production approach, perhaps start with setting up a single slave (new version) taking in the production replication stream but without an application load. In this way they can assure that the new version is stable and handles the replication stream coming from an old master. Another approach can be to upgrade a non critical part of their MySQL infrastructure and gain confidence that way. Web companies will typically also want to test the new Server version in a test environment and qualify it as much as they can before putting it into production.

In general, what you want is a number of learning exercises to build your knowledge and confidence in the next version. This helps you tweak your monitoring to handle the possible nuances in how it works compared to the previous version as a progressive exercise.

Setting up a Test Server

The easiest way to set up a test server is to start with an empty old (current) server and then restore a backup from the relevant production system. Sometimes data will first need to be obfuscated due to customer privacy concerns or rules. This will give you an old server with representative data.

The next step will be to perform the MySQL in-place upgrade procedure :

stop server (old version) change binaries (new version) adjust configuration (my.cnf for new version) start server (new version) run mysql_upgrade (new version) restart server (new version)

This will give you a ready to use new server. (There are some caveats here which will be discussed below).

An alternative is to seed the test server with data from a logical mysqldump (not covered in this blog post). Inplace upgrade is normally preferred since it is faster. Dumping the data and loading it somewhere else again takes time, an inplace upgrade has the data ondisk and so the downtime is reduced to the time to stop the server, swap out binaries and run mysql_upgrade .

The final step is to test the new server version using test load from existing user applications. Some Web companies have reported that it would be nice to have T-split functionality in routers, i.e. sending a copy of the incoming user requests both to the real production system as well as to the test server. There is ongoing work called mirroring in ProxySQL to support such functionality.

User Applications

An upgrade from an old server version to a new server version has implications for user applications as well as for the server. There will be a time period where the server farm has both old and new server versions running, for example some old masters replicating to some new slaves during a rolling upgrade. Some time during the upgrade process the user applications will switch from talking to the old server version and start talking the new server version. Typically, this means that the user applications need to be prepared for both old and new server versions .

An example of a application difference between 5.6 and 5.7 is GET_LOCK() . Simon Mudd pointed out that we improved the functionality (good) but but didn’t leave a backward compatible behavior (bad). There are two issues people can run into if they uses GET_LOCK(): First, in 5.6 only a single simultaneous lock can be acquired andGET_LOCK() releases any existing lock. Second, MySQL 5.7 enforces a maximum length on lock names of 64 characters. Previously, no limit was enforced. In both cases users might have code that depend upon the 5.6 behavior and need to adjust to the 5.7 behavior by changing application code.

As an aside, this particular incompatibility can be addressed by the query rewrite plugin . However, it still requires user applications to be aware that they rely on the old behavior.

Upgrading the Connector?

As a starting point one needs to check that the client side connector in use supports both server versions, and if not, upgrade to a connector which does support both versions. Upgrading to the latest connector is generally recommended to be able to benefit fully from added features. Note that each new server release comes with an up-to-date, forward and backward compatible C Client library ( libmysqlclient ). New versions of other connectors will typically be available at Server GA date or shortly thereafter.

There are also many non-Oracle supported drivers around which needs to be checked for compatibility with server versions like the go sql driver .

Qualifying the Applications for a new Server version

So, after possibly upgrading the connector the next step is to qualify and potentially change user applications to work with the new server version. Web companies will typically test the set of existing user applications with the new server version. In most cases they will work out-of-the box because MySQL tends to be backward compatible. But Web companies will also read the release notes to check for things they should be aware of and things they should test in more depth.

Things that might cause problems for existing user applications:

Removed syntax . In general, some syntax might be legal in the old release but illegal in the new release. Historically MySQL has been very conservative and not removed any SQL syntax to keep compatibility between versions. In the future we will likely remove some non standard SQL syntax where it makes sense, for example in MySQL 8.0 we will remove “\N” as a synonym for NULL. In MySQL 5.7 we deprecated a number ofGIS functi

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

tags: new,version,will,upgrade,MySQL
本文标题:Upgrading your MySQL Server Farm

技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(82)