Skip to main content

How-To Upgrading the Included MySQL Database Version to 8.0

Table of Contents​

Introduction

Windows

Linux

Introduction​

The priint:publishing server Windows base packages until version 4.1.8 contain a MySQL installation of version 5.5. For Linux either MySQL version 5.5 or version 5.7 is included. priint:publishing server version 4.1.9 now supports MySQL 8.0. The base package of 4.1.9 contains MySQL 8.0. If you update an existing installation (based on an older base package) to priint:publishing server version 4.1.9, we recommend to update the included MySQL installation (if used) for security reasons to MySQL 8.0.

!IMPORTANT! Before modifying the database, a database backup (dump) and configuration backup should be created. The below information is only a set of hints that should help you migrate from MySQL 5.5 to MySQL 8.0. Every action is taken on your own risk.

The migration must be conducted in steps 5.5 -> 5.6 -> 5.7 -> 8.0. There is no option to directly migrate from the version 5.5. to 8.0.

You can either update your pubserver to 4.1.9 before or after upgrading MySQL to 8.0. Just note that you should stop the pubserver domain before starting the MySQL upgrade process, and only start it again after the MySQL upgrade is successfully finished.

Further information can be found in the documentation on the MySQL website.

Migration from 5.5 -> 5.6 https://dev.mysql.com/doc/refman/5.6/en/windows-upgrading.html Migration from 5.6 -> 5.7 https://dev.mysql.com/doc/refman/5.7/en/windows-upgrading.html Migration from 5.7 -> 8.0 https://dev.mysql.com/doc/refman/8.0/en/windows-upgrading.html

 

Windows​

Upgrading MySQL Using the Windows ZIP Distribution

To perform an upgrade using the Windows ZIP archive distribution, follow the described steps.

 

1. Download the latest Windows ZIP Archive distribution (5.6, 5.7, 8.0) of MySQL from https://downloads.mysql.com/archives/community/

 

2. If the mysql demon (mysqld) is running, stop it.

If it is installed as a service, stop the service with the following command from a command prompt: C:\> SC STOP _mysqld_service_name_

Alternatively, use NET STOP _mysqld_service_name_ or the Services application.

If you are not running the MySQL server/demon as service, but as user application, use mysqladmin to stop it. C:\<path to your MySQL>\bin\mysqladmin -u root -p shutdown

For example: C:\devstack\database\MySQL\bin\mysqladmin -u root -p shutdown

Enter the password of the root account when prompted.

It is important to shutdown MySQL correctly, since the upgrade process will not work with improper closed database transactions, or after a crashed process. Check error.log in the MySQL\data folder for any error message. Double check in task manager, that it is not running anymore.

 

3. Extract the ZIP archive. Install it into a different directory, such as C:\devstack\database\MySQL56. Overwriting the existing installation is NOT recommended, only do this after a successful upgrade. Follow these steps:

a) Unzip the ZIP archive in some location other than your current MySQL installation.  

b) Remove the data directory from unzipped MySQL installation, if there is one.  

c) Copy the data directory and my.ini file from the current MySQL installation to the location of the just-removed data directory.  

d) Edit my.ini file in the new MySQL folder. For 5.5 > 5.6 remove entry: table_cache=256. For 5.6 > 5.7 please remove entry innodb_additional_mem_pool_size=8M. For 5.7 > 8.0 please remove the following entries:

  • query_cache_type=1
  • query_cache_limit=256K
  • query_cache_min_res_unit = 2k
  • query_cache_size = 20M
  • sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

 

4. Restart MySQL. a) Open command line as administrator b) Go to your MySQLnn\bin location c) Enter command using the correct path of the just edited ini file: mysqld --defaults-file=c:\devstack\database\MySQLnn\my.ini --standalone d) Press enter

The demon should run in the command window now. Double check with task manager. If there are problems, you might find messages in error.log in bin or data folder.

 

5. Upgrade MySQL (skip this step for 5.7 > 8.0) a) Open command line as administrator b) Go to your MySQLnn\bin location c) Paste command: mysql_upgrade -u root -p d) Press enter

This only shows part of the long output. Check for any error messages. 6. For 5.5 > 5.6 edit once again my.ini file and add entry: table_cache=256 below entry query_cache_size = 20M.

 

After this operation, the migration process is finished, and MySQL version is 5.6. To upgrade MySQL to version 5.7 and then to 8.0, please repeat all the steps.

The mysql_upgrade client is now deprecated in MySQL 8.0. The actions executed by the upgrade client are now done by the server. To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade. The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand. It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.

When you successfully created a running MySQL 8.0 with the data of your pubserver, stop MySQL. Rename the old MySQL folder to MySQL55. Then rename the folder MySQL80 to MySQL. Now you can either start the 4.1.9 updater, or if that update was done already, start the 4.1.9 pubserver and you are all set.

Linux​

!IMPORTANT!​

Currently the default version of MySQL for Ubuntu 18.04 is MySQL 5.7. If you happen to have a lower version of MySQL, please consult the official MySQL documentation:

https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/#repo-qg-apt-upgrading

 

  1. To take the backup use the mysqldump command and backup all the databases with the Master user of the databases.

    $ mysqldump --lock-all-tables --all-databases -uroot -proot > all_databases.sql

    and backup of the configuration file:

    $ sudo cp /etc/my.cnf /etc/my.cnf_bkp

  2. Stop MySQL

    $ sudo systemctl stop mysql

  3. Download the repository

    $ wget https://repo.mysql.com/mysql-apt-config\_0.8.14-1\_all.deb

  4. Install the MySQL 8.0 package

    $ sudo dpkg -i mysql-apt-config_0.8.14-1_all.deb

  5. Select "MySQL Server and Cluster". Press Enter

  6. Select "mysql-8.0" and press Enter

  7. Choose OK and press Enter

  8. Update the repository

    $ sudo apt-get update

  9. Start the upgrade process

    $ sudo apt-get install mysql-server

  10. Choose "Use Strong Password Encryption" authentication plugin and click OK

  11. Choose "keep your currently-installed version" when asked about the Configuration File

  12. Go to the configuration file mysqld.cnf and comment out the following entries

    #query_cache_limit = 1M

    #query_cache_size = 16M

    #innodb-support-xa=0

  13. Check the status

    $ sudo systemctl status mysql

  14. Log in to the server to check the version

    $ mysql -uroot -proot