Upgrading from MySQL 5.7 to MySQL 8

• 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

• Perform a data backup procedure. See “Backup and recovery procedures” for details.

• 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

• If you do not have an HA environment, disable the binary logs with the following command in the [mysqld] section:

skip-log-bin

• 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;

• The necessary permissions (grants) must be added again; if you are not logged in to mysql as root user (replace with the corresponding password) run:

mysql -u root -p <password>

• Once logged in as root execute the following commands:
• Deactivate the password validation plugin (optional).

UNINSTALL COMPONENT 'file://component_validate_password';

• Create the login user if it does not exist:

CREATE USER "<DBUSER>"@'%' IDENTIFIED BY "<DBPASS>";

• Assign the native password for mysql:

ALTER USER "<DBUSER>"@'%' IDENTIFIED WITH mysql_native_password BY "<DBPASS>";

• Grant grants on Pandora FMS database (pandora):

GRANT ALL PRIVILEGES ON pandora.* TO "<DBUSER>"@'%';

• Save the changes and exit:

flush privileges;
exit

Once you have finished working with the database, perform the following steps:

• Start the PFMS server and the PFMS Software Agent:

/etc/init.d/pandora_server start
/etc/init.d/pandora_agent_daemon start

• Start the Apache web server:

systemctl start httpd.service

Either:

/etc/init.d/httpd start

• 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/yum/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

• If you do not have an HA environment, disable the binary logs with the following command in the [mysqld] section:

skip-log-bin

• 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

• Start the Apache web server:

systemctl start httpd.service

Either:

/etc/init.d/httpd start

Go back to Pandora FMS documentation index