Percona Server for MySQL In-Place Upgrading Guide: From 5.7 to 8.0 Percona Server for MySQL In-Place Upgrading Guide: From 5.7 to 8.0¶
In-place upgrades are those which are done using the existing data in the server. Generally speaking, this is stopping the server, installing the new server and starting it with the same data files. While they may not be suitable for high-complexity environments, they may be adequate for many scenarios.
The following is a summary of the more relevant changes in the 8.0 series. It is strongly recommended that you read the following guides as they contain the list of incompatible changes that could cause automatic upgrade to fail:
- changed_in_8.0
- Upgrading MySQL
- Upgrading from MySQL 5.7 to 8.0
- Upgrade Paths
- Preparing your Installation for Upgrade
Warning
Do not upgrade from 5.7 to 8.0 on a crashed instance. If the server instance has crashed, crash recovery should be run before proceeding with the upgrade.
Note that in Percona Server for MySQL 8.0, the ROW FORMAT clause is not supported
in CREATE TABLE and ALTER TABLE statements. Instead, use the
tokudb_row_format variable to set the default compression
algorithm.
With partitioned tables that use the TokuDB or MyRocks storage engine, the upgrade only works with native partitioning.
Upgrading using the Percona repositories¶
The easiest and recommended way of installing - where possible - is by using the Percona repositories.
Instructions for enabling the repositories in a system can be found in:
DEB-based distributions¶
Run the following commands as root or by using the sudo command
Having done the full backup (or dump if possible), stop the server running and proceed to do the modifications needed in your configuration file, as explained at the beginning of this guide.
Note
If you are running Debian/Ubuntu system with systemd as the default system and service manager you can invoke the above command with systemctl instead of service. Currently both are supported.
Then install the new server with:
Enable the repository:
$ percona-release enable ps-80 release
$ apt-get update
$ apt-get install percona-server-server
If you used or TokuDB or MyRocks storage engines
The TokuDB and MyRocks storage engines are installed separately. The percona-server-tokudb package installs both of them.
$ apt-get install percona-server-tokudb
If you only used the MyRocks storage engine in Percona Server for MySQL 5.7, install the
percona-server-rocksdb package.
$ apt-get install percona-server-rocksdb
The installation script will NOT run automatically mysql_upgrade as it was the case in previous versions. You’ll need to run the command manually and restart the service after it’s finished.
$ mysql_upgrade
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
...
Upgrade process completed successfully.
Checking if update is needed.
$ service mysql restart
RPM-based distributions¶
Run the following commands as root or by using the sudo command
Having done the full backup (and dump if possible), stop the server:
service mysql stop and check your installed packages with rpm -qa | grep Percona-Server
Note
If you’re running RHEL/CentOS system with systemd as the default system and service manager you can invoke the above command with systemctl instead of service. Currently both are supported.
Output of rpm -qa | grep Percona-Server
Percona-Server-57-debuginfo-5.7.10-3.1.el7.x86_64
Percona-Server-client-57-5.7.10-3.1.el7.x86_64
Percona-Server-devel-57-5.7.10-3.1.el7.x86_64
Percona-Server-server-57-5.7.10-3.1.el7.x86_64
Percona-Server-shared-57-5.7.10-3.1.el7.x86_64
Percona-Server-shared-compat-57-5.7.10-3.1.el7.x86_64
Percona-Server-test-57-5.7.10-3.1.el7.x86_64
Percona-Server-tokudb-57-5.7.10-3.1.el7.x86_64
After checking, proceed to remove them without dependencies:
$ rpm -qa | grep Percona-Server | xargs rpm -e --nodeps
It is important that you remove them without dependencies as many packages may
depend on these (as they replace mysql) and will be removed if omitted.
Important
/etc/my.cnf Backed Up in CentOS 7
In CentOS 7, the /etc/my.cnf configuration file is backed up when you
uninstall the Percona Server for MySQL packages with the rpm -e --nodeps command.
The backup file is stored in the same directory with the _backup suffix
followed by a timestamp: etc/my.cnf_backup-20181201-1802.
Substitute grep '^mysql-' for grep 'Percona-Server' in the previous command and
remove the listed packages.
You will have to install the percona-server-server package:
$ yum install percona-server-server
The TokuDB and MyRocks storage engines are installed separately.
If you used TokuDB in Percona Server for MySQL 5.7, install the
percona-server-tokudb package when doing the upgrade. This command installs
both
$ yum install percona-server-tokudb
If you used the MyRocks storage engine in Percona Server for MySQL 5.7, install the
percona-server-rocksdb package:
$ yum install percona-server-rocksdb
Once installed, proceed to modify your configuration file - my.cnf - and
reinstall the plugins if necessary.
Note
If you are using TokuDB storage engine you’ll need to comment out all the
TokuDB specific variables in your configuration file(s) before starting the
server, otherwise the server won’t be able to start. RHEL/CentOS 7
automatically backs up the previous configuration file to
/etc/my.cnf.rpmsave and installs the default my.cnf. After
upgrade/install process completes you can move the old configuration file
back (after you remove all the unsupported system variables).
You can now start the mysql service using service mysql start and
using mysql_upgrade to migrate to the new grant tables, it will rebuild the
indexes needed and do the modifications needed:
Note
If you’re using TokuDB storage engine you’ll need re-enable the storage
engine plugin by running the: ps-admin --enable-tokudb before running
mysql_upgrade otherwise you’ll get errors.
$ mysql_upgrade
Output
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
...
pgrade process completed successfully.
Checking if update is needed.
Once this is done, just restart the server as usual: service mysql restart
After the service has been successfully restarted you can use the new Percona Server for MySQL 8.0.
Upgrading using Standalone Packages¶
DEB-based distributions¶
Having done the full backup (and dump if possible), stop the
server. Run this command as root or by using the sudo command: /etc/init.d/mysql stop and remove the
installed packages with their dependencies: apt-get autoremove percona-server percona-client
Once removed, proceed to do the modifications needed in your configuration file, as explained at the beginning of this guide.
Then, download the following packages for your architecture:
percona-server-serverpercona-server-clientpercona-server-commonlibperconaserverclient21
The following example will download Percona Server for MySQL 8.0.13-3 release
packages for Debian 9.0:
$ wget https://www.percona.com/downloads/Percona-Server-8.9/Percona-Server-8.0.13-3/binary/debian/stretch/x86_64/percona-server-8.0.13-3-r63dafaf-stretch-x86_64-bundle.tar
You should then unpack the bundle to get the packages: tar xvf Percona-Server-8.0.13-3-r63dafaf-stretch-x86_64-bundle.tar
After you unpack the bundle you should see the following packages:
$ ls *.deb
libperconaserverclient21-dev_8.0.13-3-1.stretch_amd64.deb
libperconaserverclient21_8.0.13-3-1.stretch_amd64.deb
percona-server-dbg_8.0.13-3-1.stretch_amd64.deb
percona-server-client_8.0.13-3-1.stretch_amd64.deb
percona-server-common_8.0.13-3-1.stretch_amd64.deb
percona-server-server_8.0.13-3-1.stretch_amd64.deb
percona-server-source_8.0.13-3-1.stretch_amd64.deb
percona-server-test_8.0.13-3-1.stretch_amd64.deb
percona-server-tokudb_8.0.13-3-1.stretch_amd64.deb
Now you can install Percona Server for MySQL by running:
$ sudo dpkg -i *.deb
This will install all the packages from the bundle. Another option is to
download/specify only the packages you need for running Percona Server for MySQL
installation (libperconaserverclient21_8.0.13-3.stretch_amd64.deb,
percona-server-client-8.0.13-3.stretch_amd64.deb,
percona-server-common-8.0.13-3.stretch_amd64.deb, and
percona-server-server-8.0.13-3.stretch_amd64.deb. Optionally you can
install percona-server-tokudb-8.0.13-3.stretch_amd64.deb if you want
TokuDB storage engine).
Note
Percona Server for MySQL 8.0 comes with the TokuDB storage engine. You can find more information on how to install and enable the TokuDB storage in the TokuDB Installation guide.
Warning
When installing packages manually like this, you’ll need to make sure to
resolve all the dependencies and install missing packages yourself. At least
the following packages should be installed before installing Percona Server for MySQL
8.0: libmecab2, libjemalloc1, zlib1g-dev, and libaio1.
The installation script will not run automatically mysql_upgrade, so you’ll need to run it yourself and restart the service afterwards.
RPM-based distributions¶
Having done the full backup (and dump if possible), stop the server (command:
service mysql stop) and check your installed packages:
$ rpm -qa | grep Percona-Server
Percona-Server-57-debuginfo-5.7.10-3.1.el7.x86_64
Percona-Server-client-57-5.7.10-3.1.el7.x86_64
Percona-Server-devel-57-5.7.10-3.1.el7.x86_64
Percona-Server-server-57-5.7.10-3.1.el7.x86_64
Percona-Server-shared-57-5.7.10-3.1.el7.x86_64
Percona-Server-shared-compat-57-5.7.10-3.1.el7.x86_64
Percona-Server-test-57-5.7.10-3.1.el7.x86_64
Percona-Server-tokudb-57-5.7.10-3.1.el7.x86_64
You may have the shared-compat package, which is for compatibility purposes.
After checked that, proceed to remove them without dependencies: rpm -qa | grep percona-server | xargs rpm -e --nodeps
It is important that you remove it without dependencies as many packages may
depend on these (as they replace mysql) and will be removed if ommited.
Note that this procedure is the same for upgrading from MySQL 5.7 to Percona Server for MySQL 8.0, just grep '^mysql-' instead of Percona-Server and remove
them.
Download the packages of the desired series for your architecture from the download page. The easiest way is to download bundle which contains all the packages. The following example will download Percona Server for MySQL 8.0.13-3 release packages for CentOS 7:
$ wget https://www.percona.com/downloads/Percona-Server-8.0/Percona-Server-8.0.13-3/binary/redhat/7/x86_64/Percona-Server-8.0.13-3-r63dafaf-el7-x86_64-bundle.tar
You should then unpack the bundle to get the packages: tar xvf Percona-Server-8.0.13-3-r63dafaf-el7-x86_64-bundle.tar
After you unpack the bundle you should see the following packages: ls *.rpm
Output
percona-server-debuginfo-8.0.13-3.1.el7.x86_64.rpm
percona-server-client-8.0.13-3.1.el7.x86_64.rpm
percona-server-devel-8.0.13-3.1.el7.x86_64.rpm
percona-server-server-8.0.13-3.1.el7.x86_64.rpm
percona-server-shared-8.0.13-3.1.el7.x86_64.rpm
percona-server-shared-compat-8.0.13-3.1.el7.x86_64.rpm
percona-server-test-8.0.13-3.1.el7.x86_64.rpm
percona-server-tokudb-8.0.13-3.1.el7.x86_64.rpm
Now, you can install Percona Server for MySQL 8.0 by running:
rpm -ivh percona-server-server_8.0.13-3.el7.x86_64.rpm \
percona-server-client_8.0.13-3.el7.x86_64.rpm \
percona-server-shared_8.0.13-3.el7.x86_64.rpm
This will install only packages required to run the Percona Server for MySQL
8.0. Optionally you can install TokuDB storage engine by
adding the percona-server-tokudb-8.0.13-3.el7.x86_64.rpm to the command
above. You can find more information on how to install and enable the TokuDB
storage in the TokuDB Installation guide.
To install all the packages (for debugging, testing, etc.) you should run:
rpm -ivh *.rpm
Note
When installing packages manually like this, you’ll need to make sure to resolve all the dependencies and install missing packages yourself.
Once installed, proceed to modify your configuration file - my.cnf - and
install the plugins if necessary. If you’re using TokuDB storage engine you’ll
need to comment out all the TokuDB specific variables in your configuration
file(s) before starting the server, otherwise server won’t be able to
start. RHEL/CentOS 7 automatically backs up the previous configuration file
to /etc/my.cnf.rpmsave and installs the default my.cnf. After
upgrade/install process completes you can move the old configuration file back
(after you remove all the unsupported system variables).
As the schema of the grant table has changed, the server must be started without
reading them: service mysql start
Then, use mysql_upgrade to migrate to the new grant tables. It will
rebuild the indexes needed and do the modifications needed:
mysql_upgrade
Note
If you’re using TokuDB storage engine you’ll need re-enable the storage
engine plugin by running the: ps-admin --enable-tokudb before running
mysql_upgrade otherwise you’ll get errors.
After this is done, just restart the server as usual: service mysql restart
Upgrading from Systems that Use the TokuDB or MyRocks Storage Engine and Partitioned Tables¶
Due to the limitation imposed by MySQL, it is the storage engine that must provide support for partitioning. MySQL 8.0 only provides support for partitioned table for the InnoDB storage engine.
If you use partitioned tables with the TokuDB or MyRocks storage engine, the upgrade may fail if the native partitioning (i.e. provided by the storage engine itself) is not enabled.
Before you attempt the upgrade, check whether or not you have any tables that do not use the native partitioning.
$ mysqlcheck -u root --all-databases --check-upgrade
If such tables are found mysqlcheck issues a warning:
Output of mysqlcheck detecting a table that do not use the native partitioning
| comp_test.t1_RocksDB_lz4 OK
| warning : The partition engine, used by table 'comp_test.t1_RocksDB_lz4',
| is deprecated and will be removed in a future release. Please use native partitioning instead.
In this case comp_test.t1_RocksDB_lz4 is not using native partitions. To
switch, enable either rocksdb_enable_native_partition or
tokudb_enable_native_partition variable depending on the storage
engine that you are using. Then restart the server. Your next step is to alter
the tables that are not using the native partitioning with the
UPGRADE PARTITIONING clause:
ALTER TABLE comp_test.t1_RocksDB_lz4 UPGRADE PARTITIONING
In this example, the table comp_test.t1_RocksDB_lz4 to native
partitioning. Unless you complete these steps for each table that mysqlcheck
complained about, the upgrade to MySQL 8.0 will fail and your error log
will contain messages like:
2018-12-17T18:34:14.152660Z 2 [ERROR] [MY-013140] [Server] The 'partitioning' feature is not available; you need to remove '--skip-partition' or use MySQL built with '-DWITH_PARTITION_STORAGE_ENGINE=1'
2018-12-17T18:34:14.152679Z 2 [ERROR] [MY-013140] [Server] Can't find file: './comp_test/t1_RocksDB_lz4.frm' (errno: 0 - Success)
2018-12-17T18:34:14.152691Z 2 [ERROR] [MY-013137] [Server] Can't find file: './comp_test/t1_RocksDB_lz4.frm' (OS errno: 0 - Success)
See also
- MySQL Documentation: Partitioning Limitations Relating to Storage Engines
- https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations-storage-engines.html
