Upgrading from MySQL 5.7 to MySQL 8
Prerequisites
To migrate from MySQL 5.7 to MySQL 8 you must be upgraded to at least PFMS version NG 760 MR 52.
- 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.
CentOS 7
CentOS 7 will soon reach its end-of-life (EOL). This documentation is retained for historical purposes.
- 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 withquery_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
See also “Optimization and troubleshooting” for checking the my.cnf
file.
- 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>";
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.
- Assign the native password for
mysql
:
ALTER USER "<DBUSER>"@'%' IDENTIFIED WITH mysql_native_password BY "<DBPASS>";
- Grant grants on Pandora FMS database (
pandora
):
<codes sql> GRANT ALL PRIVILEGES ON pandora.* TO “<DBUSER>”@'%';
</code>
- 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
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/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 withquery_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
See also “Optimization and troubleshooting” for checking the my.cnf
file.
- 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