Back to Whitepapers

Migrating to MySQL 5.7 - The Database Upgrade Guide

Upgrading to a new major version involves risk, and it is important to plan the whole process carefully. In this whitepaper, we look at the important new changes in MySQL 5.7 and show you how to plan the test process. We then look at how to do a live system upgrade without downtime. For those who want to avoid connection failures during slave restarts and switchover, this document goes even further and shows you how to leverage ProxySQL to achieve a graceful upgrade process.

Download now

Table of contents

  • 1. Introduction
  • 2. Changes between MySQL 5.6 and MySQL 5.7
    • 2.1. Information schema changes
    • 2.2. Systemd introduction to RPM-based distros
    • 2.3. SQL modes
    • 2.4. Authentication changes
    • 2.5. Changes in InnoDB
    • 2.6. Other changes introduced in MySQL 5.7
  • 3. Overview of test environment
  • 4. Pre-upgrade testing
    • 4.1. First step - build a test environment
    • 4.2. Collect data for regression tests
    • 4.3. Regression tests using pt-upgrade
    • 4.4. Regression tests of application
    • 4.5. Bring back the node into replication
  • 5. Upgrade
    • 5.1. Slave upgrade process
    • 5.2. Switchover process and upgrade of the old master
  • 6. Graceful upgrade process using ProxySQL
    • 6.1. Installation of ProxySQL
    • 6.2. Configuring ProxySQL for graceful switchover with the ClusterControl

1. Introduction

MySQL 5.7 has been GA since October 2015. At the time of writing, it is still a very new release. But more and more companies are looking into upgrading, as it has a list of great new features. Schema changes can be performed with less downtime, with more online configuration options. Multi-source and parallel replication improvements make replication more flexible and scalable. Native support for JSON data type allows for storage, search and manipulation of schema-less data.

An upgrade, however, is a complex process - no matter which major MySQL version you are upgrading to. There are a few things you need to keep in mind when planning this, such as important changes between versions 5.6 and 5.7 as well as detailed testing that needs to precede any upgrade process. This is especially important if you would like to maintain availability for the duration of the upgrade.

Upgrading to a new major version involves risk, and it is important to plan the whole process carefully. In this document, we’ll look at the important new changes in 5.7 and show you how to plan the test process. Then, we’ll look at how to do a live system upgrade without downtime. For those who want to avoid connection failures during slave restarts and switchover, we’ll go even further and show you how to leverage ProxySQL to achieve a graceful upgrade process.

Want to read the rest?

Download the full whitepaper for free
Download whitepaper