====== Upgrading from MySQL 5.7 to MySQL 8 ======

{{indexmenu_n>19}}



===== Prerequisites =====

<WRAP center round important 60%>

To migrate from MySQL 5.7 to MySQL 8 you must be upgraded to at least PFMS **version NG 760 MR 52**.

</WRAP>

  * Open a terminal window with the **root**  user and enter the following command:
<code>

mysql --version

</code>

You will get an answer similar to the following:

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

</code>

  * Perform a data backup procedure. See "[[:en:documentation:pandorafms:technical_annexes:07_backup_and_restore_procedures|Backup and recovery procedures]]" for details.

===== CentOS 7 =====

  * Stop the PFMS server and the PFMS Software Agent:
<code>

/etc/init.d/pandora_server stop
/etc/init.d/pandora_agent_daemon stop

</code>

  * Stop the Apache web server:

<code>
systemctl stop httpd.service

</code>

Either:

<code>
/etc/init.d/httpd stop

</code>

  * Make a special database backup that is compatible with MySQL 8:

<code>
mysqldump -u root -p pandora \
  --skip-add-drop-table \
  --complete-insert \
  --no-create-info> backup_pandora.sql

</code>

  * Make a backup as it is done in MySQL 5.7 (an additional insurance if you have to go back to this version):

<code>
mysqldump -u root -p pandora> backup_pandora_5.7.sql

</code>

  * Back up the MySQL configuration file (''my.cnf''):

<code>
cp /etc/my.cnf /tmp/my.cnf.BACK

</code>

  * Delete the Percona 5 repositories:

<code>
rpm -qa | grep Percona-Server | xargs rpm -e --nodeps

</code>

  * Install the Percona 8 repositories:
<code>

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y

</code>

  * Enable the repositories from the previous step:

<code>
percona-release setup ps80

</code>

  * Install Percona 8

<code>
yum install percona-server-server

</code>

  * Restore the file ''my.cnf'':

<code>
mv /tmp/my.cnf.BACK /etc/my.cnf

</code>

  * Modify the ''my.cnf''  file to dispense with ''query_cache_*'':

<code>
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

</code>

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

<code>
skip-log-bin

</code>

<WRAP center round tip 60%>

See also "[[:en:documentation:pandorafms:complex_environments_and_optimization:08_optimization#check_mycnf_settings|Optimization and troubleshooting]]" for checking the ''my.cnf''  file.

</WRAP>

  * Start the MySQL service:

<code>
systemctl start mysql

</code>

or:

<code>
service mysqld start

</code>

  * Verify the version installed with:

<code>
mysql --version

</code>

You will get an answer similar to the following:

<code>
mysql  Ver 8.0.28-19 for Linux on x86_64 (Percona Server (GPL), Release 19, Revision 31e88966cd3)

</code>

  * 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.

<code>
mysql -u root -p pandora

</code>

<code>
drop database pandora;
create database pandora;
use pandora;
source /var/www/html/pandora_console/pandoradb.sql;
source backup_pandora.sql;

</code>

  * 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:

<code>
mysql -u root -p <password>

</code>

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

<code>
UNINSTALL COMPONENT 'file://component_validate_password';

</code>

  * Create the login user if it does not exist:

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

</code>

<WRAP center round tip 60%>

//**Note**//  : if you want to restrict the IP address of connection to the database, you must change the ''%''  to the IP address of the connection source.

</WRAP>

  * Assign the native password for ''mysql'':

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

</code>

  * Grant grants on Pandora FMS database (''pandora''):

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

</code>

  * Save the changes and exit:

<code>
flush privileges;
exit

</code>

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

  * Start the PFMS server and the PFMS Software Agent:
<code>

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

</code>

  * Start the Apache web server:

<code>
systemctl start httpd.service

</code>

Either:

<code>
/etc/init.d/httpd start

</code>

===== Rocky Linux 8/AlmaLinux 8/RHEL 8 =====

  * Stop the PFMS server and the PFMS Software Agent:

<code>
/etc/init.d/pandora_server stop
/etc/init.d/pandora_agent_daemon stop

</code>

  * Stop the Apache web server:

<code>
systemctl stop httpd.service

</code>

Either:

<code>
/etc/init.d/httpd stop

</code>

  * Make a special database backup that is compatible with MySQL 8:

<code>
mysqldump -u root -p pandora \
  --skip-add-drop-table \
  --complete-insert \
  --no-create-info> backup_pandora.sql

</code>

  * Make a backup as it is done in MySQL 5.7 (an additional insurance if you have to go back to this version):

<code>
mysqldump -u root -p pandora> backup_pandora_5.7.sql

</code>

  * Back up the MySQL configuration file (''my.cnf''):

<code>
cp /etc/my.cnf /tmp/my.cnf.BACK

</code>

  * Delete the Percona 5 repositories:

<code>
rpm -qa | grep Percona-Server | xargs rpm -e --nodeps

</code>

  * Install the Percona 8 repositories:
<code>

dnf install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y

</code>

  * Enable the repositories from the previous step:

<code>
percona-release setup ps80

</code>

  * Install Percona 8

<code>
dnf install percona-server-server

</code>

  * Restore the file ''my.cnf'':

<code>
mv /tmp/my.cnf.BACK /etc/my.cnf

</code>

  * Modify the ''my.cnf''  file to dispense with ''query_cache_*'':

<code>
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

</code>

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

<code>
skip-log-bin

</code>

<WRAP center round tip 60%>

See also "[[:en:documentation:pandorafms:complex_environments_and_optimization:08_optimization#check_mycnf_settings|Optimization and troubleshooting]]" for checking the ''my.cnf''  file.

</WRAP>

  * Start the MySQL service:

<code>
systemctl start mysql

</code>

or:

<code>
service mysqld start

</code>

  * Verify the version installed with:

<code>
mysql --version

</code>

You will get an answer similar to the following:

<code>
mysql  Ver 8.0.28-19 for Linux on x86_64 (Percona Server (GPL), Release 19, Revision 31e88966cd3)

</code>

  * 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.

<code>
mysql -u root -p pandora

</code>

<code>
drop database pandora;
create database pandora;
use pandora;
source /var/www/html/pandora_console/pandoradb.sql;
source backup_pandora.sql;

</code>

  * Start the PFMS server and the PFMS Software Agent:
<code>

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

</code>

  * Start the Apache web server:

<code>
systemctl start httpd.service

</code>

Either:

<code>
/etc/init.d/httpd start

</code>

[[:en:documentation:start|Go back to Pandora FMS documentation index]]