未加星标

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.

Context

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 管理工具

主题: SQLMySQLSQL ServerGIS
分页:12
转载请注明
本文标题:Upgrading your MySQL Server Farm
本站链接:http://www.codesec.net/view/531712.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(14)