Updating MySQL on Ubuntu

Disclaimer

Before updating, backups of the panel administrator, panel users, and their databases must be configured and successfully created in the panel.

We also highly recommend that you consult the official MySQL documentation for the specified versions before updating to verify compatibility between directives, their values, and database data parameters.

Preparation

During the database server update, the control panel, websites, and applications that use this database server will be unavailable. Choose a time with minimal server load for the update, and notify your users!

Make sure you have at least 2 GB of free disk space plus enough space for a full backup of all databases:

df -h /

Make sure you have at least 2 GB of free RAM:

free -mh

Update all system packages:

apt -y update; apt upgrade

Disable monitoring of the mysql service in the Services section of the control panel using the Monitoring button.

Stop the ispmanager 6 control panel:

/usr/local/mgr5/sbin/mgrctl -m core --lock

To verify that the control panel processes have stopped, run the following command, the output must be empty:

ps aux | grep -i "bin/core ispmgr" | grep -v grep

Copy the DBMS root password from /root/.my.cnf and create a backup (dump) of all native DBMS databases:

mysqldump -u root -p<password> --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql

Check for the line «Dump completed» in the dump, it indicates successful dump creation:

tail <dump_name>

If you need to start the panel after stopping it earlier, see the Start and verification section.

Update

Download the MySQL 8.4 package and install it:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.36-1_all.deb
dpkg -i mysql-apt-config_0.8.36-1_all.deb

During the installation process, a text menu will open:

• Select MySQL Server & Cluster (Currently selected: ...);
• In the version list, enter the number with the value «mysql-8.4-lts»;
• Save your configuration by selecting «OK».

Update the package list:

apt update

Make sure the package manager detects the new version:

apt-cache policy mysql-server

Stop the MySQL service:

systemctl stop mysql

Disable the legacy authentication plugin and enable support of legacy connections:

sed -i 's/^default-authentication-plugin/mysql_native_password=ON\n#default-authentication-plugin/' /etc/mysql/mysql.conf.d/mysqld.cnf

Update your database encoding settings to modern ones if necessary:

sed -i.bak -e 's/^collation-server[[:space:]]*=[[:space:]]*utf8_general_ci$/collation-server = utf8mb4_0900_ai_ci/' -e 's/^character-set-server[[:space:]]*=[[:space:]]*utf8$/character-set-server = utf8mb4/' /etc/mysql/mysql.conf.d/mysqld.cnf

Update MySQL:

apt install mysql-server

During the update, you'll be asked whether to overwrite the /etc/mysql/mysql.conf.d/mysqld.cnf configuration file. We recommend choose the option «keep your currently installed version» to preserve the working configuration.

You may also be prompted to enter the DBMS administrator password. You can leave the field blank to keep the password unchanged.

Start and verification

Check the MySQL service status:

systemctl status mysql

Check the syntax of the configuration file:

mysqld --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --validate-config

If necessary, correct the errors, then start the service:

systemctl start mysql

If the service fails to start, make sure that the log_error directive is assigned to the log in the configuration file. By default, the log is located at /var/log/mysql/error.log. To see the latest entries, run the following command:

tail -n 50 /var/log/mysql/error.log

Enable autostart for the service:

systemctl enable mysql

Check the MySQL version:

dpkg -l | grep -iE "(mysql|mariadb)" | grep -i server

Check the version and encoding:

mysql -e "SELECT VERSION();"
mysql -e "SHOW VARIABLES LIKE 'character_set_server';"
mysql -e "SHOW VARIABLES LIKE 'collation_server';"

Check the DBMS authorization:

mysql -u root -p

Start the panel:

/usr/local/mgr5/sbin/mgrctl -m core --unlock

Go to the Notifications section and delete any notifications about resolving the mysql service issue.

Post-update steps

• Sites running PHP-FPM may display a 404 error. This is due to the lack of support for user-friendly URLs. You can enable friendly URL support in the settings for each specific site in the Sites section.
• Re-enable service monitoring in the panel