This article is the second in a two-part series on upgrading MySQL.  The first article, Upgrade from 5.0 directly to 5.7 using mysqldump, discussed performing an upgrade using the mysqldump utility.  We call that a ‘Dump’ Upgrade.  In this article, we will discuss what we call an ‘In Place’ Upgrade, also known as a Binary Upgrade or a Live Upgrade.

As with the ‘Dump’ Upgrade, it is a good idea to take a backup of the MySQL server 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, as well as any versions en route to that version. 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.

The ‘In Place’ Upgrade is a quicker option than the ‘Dump’ Upgrade because you don’t need to load the databases in as part of the upgrade process.  Again, this saves load time, but a backup before upgrading is even more important, because you are performing all your upgrade steps ‘In Place’ on the original data directory.  Because you are using the same datafiles, you are unable to take advantage of some new features that require a server rebuild, such as creating undo tablespaces (introduced in 5.6.3) or rebuilding your existing innodb tables into individual tablespaces (innodb_file_per_table enabled by default in 5.6.6).

I used the following steps to perform the upgrade.

1. Start with a basic MySQL 5.0.96 server instance with the sakila schema loaded. I used –no-defaults here for simplicity.
   
   Initialize MySQL 5.0.96 and load sakila schema and data

   $ cd <mysql 5.0.96 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

   1 2 3 4 5 6

   $ cd <mysql 5.0.96 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. Shut down the MySQL server.  This would be a great time to back up your MySQL server.  Next, change your directory to the new MySQL version and start up the MySQL server.  This uses the same data directory, so all your existing data files should be located in <DATADIR>.
   
   Shut down the MySQL server

   $ cd <mysql 5.0.96 basedir> $ ./bin/mysqladmin -uroot --socket=<SOCKET> shutdown $ cd <mysql 5.7.9 basedir> $ ./bin/mysqld_safe --no-defaults --datadir=<DATADIR> --basedir=. --port=< PORT> --socket=<SOCKET>  --skip-grant-tables  &

   1 2 3 4

   $ cd <mysql 5.0.96 basedir> $ ./bin/mysqladmin -uroot --socket=<SOCKET> shutdown $ cd <mysql 5.7.9 basedir> $ ./bin/mysqld_safe --no-defaults --datadir=<DATADIR> --basedir=. --port=< PORT> --socket=<SOCKET>  --skip-grant-tables  &

   
   Please note, when starting a MySQL server 5.7.x for the first time on a data directory created by 5.0.x or 5.1.x, you must use –skip-grant-tables.
3. Run mysql_upgrade. This upgrades all the system tables and checks the user schemas.
   
   Run mysql_upgrade

   $ ./bin/mysql_upgrade -uroot --socket=<SOCKET>

   1	$ ./bin/mysql_upgrade -uroot --socket=<SOCKET>

4. Load in the help tables (optional step).
   
   Fill help tables

   $ ./bin/mysql -uroot --socket=<SOCKET> --execute="source ./share/fill_help _tables.sql" mysql

   1	$ ./bin/mysql -uroot --socket=<SOCKET> --execute="source ./share/fill_help _tables.sql" mysql

5. Shut down and restart the MySQL server.
   
   Shut down and restart the MySQL server

   $ ./bin/mysqladmin -uroot --socket=<SOCKET> shutdown $ ./bin/mysqld_safe --no-defaults --datadir=<DATADIR> --basedir=. --port=<PORT> --socket=<SOCKET> &

   1 2	$ ./bin/mysqladmin -uroot --socket=<SOCKET> shutdown $ ./bin/mysqld_safe --no-defaults --datadir=<DATADIR> --basedir=. --port=<PORT> --socket=<SOCKET> &

6. Run mysqlcheck for a status on all schema tables.
   
   Run mysqlcheck

   $ ./bin/mysqlcheck -uroot --socket=<SOCKET> --all-databases

   1	$ ./bin/mysqlcheck -uroot --socket=<SOCKET> --all-databases

If you are upgrading from 5.0.96, this method will most likely require a rebuild/upgrade of some user tables or triggers. In the case of sakila, I received the following warnings from the repair section of mysql_upgrade for the triggers (extraneous output was removed):

Reloading is required due to changes in the metadata stored for triggers. I took the following steps to recreate the triggers.

1. Extract the triggers using mysqldump.
   
   Extract triggers

   $ ./bin/mysqldump --socket=<SOCKET> -uroot --triggers --no-create-db --no-data --no-create-info --all-databases > addtriggers.sql

   1	$ ./bin/mysqldump --socket=<SOCKET> -uroot --triggers --no-create-db --no-data --no-create-info --all-databases > addtriggers.sql

2. Generate a SQL script to drop the existing triggers. Exclude ‘sys’ as that those triggers will not be extracted with the mysqldump procedure outlined above.
   
   SQL script to extract triggers

   mysql> SELECT CONCAT('DROP TRIGGER ', TRIGGER_SCHEMA, '.', TRIGGER_NAME, ';') FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema not in ('sys') INTO OUTFILE 'droptriggers.sql';

   1 2 3 4 5

   mysql> SELECT CONCAT('DROP TRIGGER ', TRIGGER_SCHEMA, '.',                  TRIGGER_NAME, ';')           FROM INFORMATION_SCHEMA.TRIGGERS           WHERE trigger_schema not in ('sys')           INTO OUTFILE 'droptriggers.sql';

3. Drop the existing triggers.
   
   Drop existing triggers

   $ ./bin/mysql --socket=<SOCKET> -uroot --execute="source droptriggers.sql"

   1	$ ./bin/mysql --socket=<SOCKET> -uroot --execute="source droptriggers.sql"

4. Reload the triggers.
   
   Reload triggers

   $ ./bin/mysql --socket=<SOCKET> -uroot --execute="source addtriggers.sql"

   1	$ ./bin/mysql --socket=<SOCKET> -uroot --execute="source addtriggers.sql"

5. Run mysqlcheck for a status on all tables.
   
   Run mysqlcheck

   $ ./bin/mysqlcheck -uroot --socket=<SOCKET> --all-databases

   1	$ ./bin/mysqlcheck -uroot --socket=<SOCKET> --all-databases

The steps noted above for performing the ‘In Place’ upgrade were successful in the upgrade from 5.0.96 or 5.1.73 or 5.5.46 or 5.6.27 to 5.7.9. Validation was done using mysqlcheck, counting tables and columns and routines for all schemas, running basic select/insert/update/delete statements on the user schema, and by executing/calling user functions and procedures.

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