Upgrading from MySQL 5.7 to MySQL 8

Prerequisites

  • Open a terminal window with the root user and enter the following command:
mysql --version

You will get an answer similar to the following:

mysql  Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using  EditLine wrapper

CentOS 7

  • Stop the PFMS server and the PFMS Software Agent:
/etc/init.d/pandora_server stop
/etc/init.d/pandora_agent_daemon stop
  • Stop the Apache web server:
systemctl stop httpd.service

Either:

/etc/init.d/httpd stop
  • Make a special database backup that is compatible with MySQL 8:
mysqldump -u root -p pandora \
  --skip-add-drop-table \
  --complete-insert \
  --no-create-info> backup_pandora.sql
  • Make a backup as it is done in MySQL 5.7 (an additional insurance if you have to go back to this version):
mysqldump -u root -p pandora> backup_pandora_5.7.sql
  • Back up the MySQL configuration file (my.cnf):
cp /etc/my.cnf /tmp/my.cnf.BACK
  • Delete the Percona 5 repositories:
rpm -qa | grep Percona-Server | xargs rpm -e --nodeps
  • Install the Percona 8 repositories:
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y
  • Enable the repositories from the previous step:
percona-release setup ps80
  • Install Percona 8
yum install percona-server-server
  • Restore the file my.cnf:
mv /tmp/my.cnf.BACK /etc/my.cnf
  • Modify the my.cnf file to dispense with query_cache_*:
sed -i -e "s/^query_cache_type.*/#query_cache_type/g" /etc/my.cnf
sed -i -e "s/^query_cache_size.*/#query_cache_size/g" /etc/my.cnf
sed -i -e "s/^query_cache_min_res_unit.*/#query_cache_min_res_unit/g" /etc/my.cnf
sed -i -e "s/^query_cache_limit.*/#query_cache_limit/g" /etc/my.cnf
  • Start the MySQL service:
systemctl start mysql

or:

service mysqld start
  • Verify the version installed with:
mysql --version

You will get an answer similar to the following:

mysql  Ver 8.0.28-19 for Linux on x86_64 (Percona Server (GPL), Release 19, Revision 31e88966cd3)
  • Now use MySQL's own command line and:
    • Delete the pandora database.
    • Create a new pandora database.
    • Open the pandora database and import the schema found in the PFMS Web Console.
    • Import the data that special MySQL 8 compatible backup.
# mysql -u root -p pandora> drop database pandora;> create database pandora;
> use pandora; source /var/www/html/pandora_console/pandoradb.sql;
> source backup_pandora.sql;
  • Start the PFMS server and the PFMS Software Agent:
/etc/init.d/pandora_server start
/etc/init.d/pandora_agent_daemon start
  • Stop the Apache web server:
systemctl start httpd.service

Either:

/etc/init.d/httpd start

Rocky Linux 8/AlmaLinux 8/RHEL 8

  • Stop the PFMS server and the PFMS Software Agent:
/etc/init.d/pandora_server stop
/etc/init.d/pandora_agent_daemon stop
  • Stop the Apache web server:
systemctl stop httpd.service

Either:

/etc/init.d/httpd stop
  • Make a special database backup that is compatible with MySQL 8:
mysqldump -u root -p pandora \
  --skip-add-drop-table \
  --complete-insert \
  --no-create-info> backup_pandora.sql
  • Make a backup as it is done in MySQL 5.7 (an additional insurance if you have to go back to this version):
mysqldump -u root -p pandora> backup_pandora_5.7.sql
  • Back up the MySQL configuration file (my.cnf):
cp /etc/my.cnf /tmp/my.cnf.BACK
  • Delete the Percona 5 repositories:
rpm -qa | grep Percona-Server | xargs rpm -e --nodeps
  • Install the Percona 8 repositories:
dnf install https://repo.percona.com/dnf/percona-release-latest.noarch.rpm -y
  • Enable the repositories from the previous step:
percona-release setup ps80
  • Install Percona 8
dnf install percona-server-server
  • Restore the file my.cnf:
mv /tmp/my.cnf.BACK /etc/my.cnf
  • Modify the my.cnf file to dispense with query_cache_*:
sed -i -e "s/^query_cache_type.*/#query_cache_type/g" /etc/my.cnf
sed -i -e "s/^query_cache_size.*/#query_cache_size/g" /etc/my.cnf
sed -i -e "s/^query_cache_min_res_unit.*/#query_cache_min_res_unit/g" /etc/my.cnf
sed -i -e "s/^query_cache_limit.*/#query_cache_limit/g" /etc/my.cnf
  • Start the MySQL service:
systemctl start mysql

or:

service mysqld start
  • Verify the version installed with:
mysql --version

You will get an answer similar to the following:

mysql  Ver 8.0.28-19 for Linux on x86_64 (Percona Server (GPL), Release 19, Revision 31e88966cd3)
  • Now use MySQL's own command line and:
    • Delete the pandora database.
    • Create a new pandora database.
    • Open the pandora database and import the schema found in the PFMS Web Console.
    • Import the data that special MySQL 8 compatible backup.
# mysql -u root -p pandora> drop database pandora;> create database pandora;
> use pandora; source /var/www/html/pandora_console/pandoradb.sql;
> source backup_pandora.sql;
  • Start the PFMS server and the PFMS Software Agent:
/etc/init.d/pandora_server start
/etc/init.d/pandora_agent_daemon start
  • Stop the Apache web server:
systemctl start httpd.service

Either:

/etc/init.d/httpd start

Go back to Pandora FMS documentation index