May
13
2015
Upgrading Directly From MySQL 5.0 to 5.6 With mysqldump
Posted by MySQL Server Dev Team on Wed 13 May 2015 14:37 UTC
Tags:

Upgrading MySQL

Upgrading MySQL is a task that is almost inevitable if you have been managing a MySQL installation for any length of time. To accomplish that task, we have provided a utility and documentation to upgrade from one version of MySQL to another. The general recommendation is to perform the upgrade by stepping from one major release to the next, without skipping an intermediate major release. For example, if you are at 5.1.73, and you want to go to 5.6.24, the safest and recommended method is to upgrade from 5.1.73 to 5.5.43 (the latest 5.5 release at the time of this writing), and then upgrade from 5.5.43 to 5.6.24 (or any version of 5.6). This allows the upgrade process to apply the changes for one major release at a time.

We test upgrading from one version to the next quite extensively during each release cycle to ensure that all user data and settings are safely and successfully upgraded. In these cases, we run through an extensive set of test cases involving users, privileges, tables, views, procedures, functions, datatypes, partitions, character sets, triggers, performance schema, the mysql system schema, and more. We also create new test cases for every release as needed. We test and validate each topic at the initial, upgraded, and downgraded stages of the process. This also includes tests involving replication between versions. Validation includes ensuring the stability of the mysql server, reviewing integrity of the data, and testing functionality at all stages.

So What Are My Upgrade Options?

There are 2 basic options available when upgrading from one mysql instance to another:

  1. Perform a ‘Dump Upgrade’
  2. Perform an ‘In-place Upgrade’

A ‘Dump Upgrade’ involves dumping the data from your existing mysql instance using mysqldump, loading it into a fresh MySQL instance running the new version (e.g. MySQL 5.7), then running mysql_upgrade. Alternatively, you can perform a purely logical upgrade by dumping only the user schema(s), and loading them into a fresh MySQL instance at the new version. In this case, mysql_upgrade is not necessary.

An ‘In-place Upgrade’ involves shutting down the existing (older) MySQL instance, upgrading the installed MySQL server package and binaries, starting up that (newer) instance using the new mysqld server binary, and then running mysql_upgrade.

It is always a good idea to take a backup of the database instance prior to making any changes. Before any upgrade process, be sure to read the related upgrade documentation for the version to which you are moving. This can include important tips and information about running the upgrade: upgrading to 5.1, upgrading to 5.5 , upgrading to 5.6, or upgrading to 5.7 .

There are potentially other methods for upgrading when using native OS packages. I will not cover those processes here. We will focus on the dump based upgrade here, and we will discuss the in-place upgrade in another follow-up article.

What If I Don’t Want to Upgrade Through Every Major Version?

We know that upgrading a MySQL installation can be a big proposition to undertake. With all the preparation, testing and dry runs required for a successful project, upgrades are not taken lightly. Because of the magnitude of this type of effort, we understand that some of our customers may not upgrade at every GA release. This leaves some customers with several major versions to hop through to get to the most current version. The recommended upgrade process can be very time-consuming, and sometimes take more resources, both human and machine, than a customer has.

With all of the interest around a faster upgrade path, we have been testing various upgrade options to see what works. I started upgrading from 5.0 to 5.6 to see what would happen, and to establish a baseline.

My starting MySQL instance was version 5.0.85 with all default settings and the sakila schema loaded.  I used Oracle Linux 7 as the platform.

I followed the following steps to perform the upgrade:

  1. Start with a basic mysql 5.0.85 server instance with the sakila schema loaded. I also used --no-defaults here for simplicity:
    $ cd <mysql 5.0.85 basedir>
$ ./scripts/mysql_install_db --no-defaults --datadir=<DATADIR> --basedir=.
$./bin/mysqld_safe --no-defaults --datadir=<DATADIR> --basedir=. --port=<PORT> --socket=<SOCKET> &
$ ./bin/mysql -uroot --socket=<SOCKET>  --execute="create database sakila;"
$ ./bin/mysql -uroot --socket=<SOCKET> --execute="source sakila-schema.sql" --database=sakila 
$ ./bin/mysql -uroot --socket=<SOCKET> --execute="source sakila-data.sql" --database=sakila
  2. Dump all databases/schemas from the existing mysql server using mysqldump:
        $ ./bin/mysqldump -uroot --socket=<SOCKET> --add-drop-table --all-databases --force > alldatabases.dmp
  3. Initialize a new MySQL 5.6.24 server instance, including any new options or parameters. Again, I used --no-defaults here for simplicity. You can use a new data directory, port, and socket or you can shutdown the 5.0.85 server, clean out the datadir, and reuse those settings. Either way the directory must be empty:
    $ cd <mysql 5.6.24 basedir>
$ ./scripts/mysql_install_db --no-defaults --datadir=<DATADIR> --basedir=.
$ ./bin/mysqld_safe --no-defaults --datadir=<DATADIR> --basedir=. --port=<PORT> --socket=<SOCKET> &
  4. Load the dump file into the new MySQL 5.6 server:
        $ ./bin/mysql -uroot --socket=<SOCKET> --execute="source alldatabase.dmp"
  5. Run mysql_upgrade (to get all the system tables upgraded):
    $ ./bin/mysql_upgrade -uroot --socket=<SOCKET>
  6. Load in the help tables (optional step):
        $ ./bin/mysql -uroot --socket=<SOCKET> --execute="source ./share/fill_help_tables.sql" mysql

With mysqldump, these are the parameters that I used, and why I include them:

  • --all-databases — this extracts data and definitions for all databases/schemas stored in the MySQL server except for performance_schema and information_schema.
  • --add-drop-table — this will force the table to be recreated with the load so that in case the existing definition (if defined) is different from the load file, it will be recreated to avoid load failures.
  • --force — this will force the dump to continue in the case that there is an error.

You may be saying to yourself, why don’t you include --routines?  And that is a great question. There are 2 ways of getting the functions and procedures included in the dump file:

  1. Include them by using the --routines parameter. The CREATE statements are then included as part of the user schema dump.
  2. Include them by including the mysql.proc table itself (the system table where the routine definitions are stored) in the dump. This will load the functions and procedures as rows of the mysql.proc table.  These are not create statements for the procedure, but insert statements into the mysql.proc table.

If you use the --routines option with mysqldump in these upgrade scenarios, you will get an error when trying to load the schema including functions and procedures because of an incompatibility of the mysql.proc table. The resulting error will look like this: 

ERROR 1547 (HY000) at line 1212 in file: '50data.dmp': Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted.
ERROR 1728 (HY000) at line 1459 in file: '51data.dmp': Cannot load from mysql.proc. The table is probably corrupted

This error is related to code added to prevent a MySQL server crash when the mysql.proc table is not in the proper format. The reason this happens is that, although the new MySQL 5.6.24 server was initialized properly, the proc table was then reverted to an earlier format via the CREATE TABLE 'proc' statement in the dump file that we subsequently loaded. Later in that same dump file were the definitions for procedures and functions in the sakila schema. The definition of the proc table then differed from what MySQL 5.6 expected to be there. This error can be avoided by not including the --routines parameter on the mysqldump command. Including this option was something that did trip me up in my testing, as I typically include --routines when I perform a dump of my user schemas.

The steps noted above for performing the ‘Dump Upgrade’ were successful in the upgrade from 5.0.85 or 5.1.73 or 5.5.43 to 5.6.43. Validation was done using mysqlcheck, running basic select/insert/update/delete statements on the user schema, and by executing/calling user functions and procedures.

What About a Logical Upgrade?

A variation of the full ‘Dump Upgrade’ method is to only pull in the user/application schema(s) into a freshly built MySQL 5.6.24 server (in our example). This allows you to skip running mysql_upgrade because all the system tables (the mysql schema) and procedures inside the MySQL server will be at the new 5.6.24 version. The steps are slightly different, and are as follows.

  1. Start with a basic MySQL 5.0.85 server instance with the sakila schema loaded. I used --no-defaults again here for simplicity:
    $ cd <mysql 5.0.85 basedir>
$ ./scripts/mysql_install_db --no-defaults --datadir=<DATADIR> --basedir=.
$./bin/mysqld_safe --no-defaults --datadir=<DATADIR> --basedir=. --port=<PORT> --socket=<SOCKET> &
$ ./bin/mysql -uroot --socket=<SOCKET>  --execute="create database sakila;"
$ ./bin/mysql -uroot --socket=<SOCKET> --execute="source sakila-schema.sql" --database=sakila 
$ ./bin/mysql -uroot --socket=<SOCKET> --execute="source sakila-data.sql" --database=sakila
  2. Dump only the user databases/schemas (skipping the mysql system schema)—which is only the Sakila schema in our example—from the existing MySQL server using mysqldump:
        $ ./bin/mysqldump -uroot --socket=<SOCKET> --add-drop-table --routines --force --databases sakila> userdatabases.dmp
  3. Initialize a new MySQL 5.6.24 server instance, including any new options or parameters. Again, I used --no-defaults here for simplicity. You can use a new data directory, port, and socket or you can shutdown the 5.0.85 MySQL server, clean out the data dir, and re-use those settings. Either way the data directory must be empty:
    $ cd <mysql 5.6.24 basedir>
$ ./scripts/mysql_install_db --no-defaults --datadir=<DATADIR> --basedir=.
$ ./bin/mysqld_safe --no-defaults --datadir=<DATADIR> --basedir=. --port=<PORT> --socket=<SOCKET> &
  4. Load the dump file into the new MySQL 5.6 server instance:
        $ ./bin/mysql -uroot --socket=<SOCKET> --execute="source alldatabase.dmp"

With mysqldump in this scenario, these are the parameters that I use, and why I include them:

  • --databases — this extracts data and definitions for all databases included in the database list
  • --add-drop-table — this will force the table to be recreated with the load so that in case the existing definition (if defined) is different from the load file, it will be recreated to avoid load failures.
  • --routines — this will include the functions and procedures in the user schema dump. We need this because we are not including them with the mysql.proc dump.
  • --force — this will force the dump to continue in the case that there is an error.

One thing to keep in mind with this type of upgrade, is that it does not load any user connection or privilege related data, nor any server settings. All of that would need to be re-created separately.

Actually, I would recommend anyone who considers skipping many major versions to use a pure logical upgrade. With a pure logical upgrade there are no assumptions made about meta-data and thus no mysql_upgrade step is needed. This increases the likelihood of success dramatically. The only downside is that it will not include users and privileges, and if you have archived data in a non pure form you will need to load it into your old version and produce the pure logical dump from there.

In my next article, I will tackle the ‘In-Place Upgrade’.

That’s it for now. THANK YOU for using MySQL!