Database Upgrade from MySQL to MariaDB

Automatics is running on MySQL v5.6, which is at EOL as per https://www.mysql.com/support/eol-notice.html so we are had upgraded to the latest MariaDB (LTS) community server v10.11(support till February 2028 as per this). We have done the upgrade with reverse compatibility in mind so that this upgrade in the Automatics application wont affect the existing users who are currently using MySQL database. This upgrade will bring latest features & security in database tier to Automatics.

Steps to Upgrade from MySQL 5.6 to MariaDB 10.11

The below are the steps to be followed for upgrading the existing MySQL database of Automatics (for both Orchestration and Device Manager) to MariaDB.

1. Check the MariaDB Version

   sudo mysql -u <<DB_USERNAME>> -p -V

2. Backup the Database

   When performing an upgrade, creating a backup or a dump of the existing databases is important just in case if something goes wrong. Dump the database with the following command.

   sudo mysqldump -u <<DB_USERNAME>> -p <<DB_PASSWORD>> --all-databases > /tmp/database-backup.sql

   Alternatively, copy the database directory into a separate folder using this command.

   sudo cp -a /var/lib/mysql /var/lib/mysql.backup

   Backup the configuration file with this command.

   sudo cp -a /etc/my.cnf /etc/my.cnf_bk

   In case of any failures in the upgrade, you can use one of the above copies to restore your databases.

   
   

3. Uninstall the Current MariaDB Repository.You will need to uninstall the old MySQL database before installing the newer version. To do so, you must first stop the MySQL service using the below command.

   sudo systemctl stop mysql

   Check the current status of MySQL using this command.

   sudo systemctl status mysql

4. Add the New MariaDB RepositoryAll the packages need to be up-to-date. Use the following command to update your packages.

   sudo yum update

   Create a new repo file for the latest version.

   vi /etc/yum.repos.d/MariaDB.repo

   Copy and paste the following contents into the /etc/yum.repos.d/mariadb.repo file.

   [mariadb]
   name=MariaDB
   baseurl=https://rpm.mariadb.org/10.11/rhel/$releasever/$basearch
   gpgkey=https://rpm.mariadb.org/RPM-GPG-KEY-MariaDB
   gpgcheck=1

   To exit and save the Vim file, type :wq and press Enter.

5. Uninstall the Old MySQL VersionUninstall the old version of MariaDB using the below command. This command completely removes the old MariaDB version and leaves the databases.

   sudo yum remove mysql
   sudo yum remove mysql-community-common
   sudo yum clean all

6. Install the Latest Version of MariaDB

   Install 10.11 of MariaDB and the most common packages, using the command below.

   sudo yum install MariaDB-server galera-4 MariaDB-client MariaDB-shared MariaDB-backup MariaDB-common

7. Start MariaDB

   Start the MariaDB service with this command.

   sudo systemctl start mariadb

   To set MariaDB to start automatically when the VM boots up, use the following command.

   sudo systemctl enable mariadb

   Check the currently installed MariaDB version with this command.

   sudo mysql -u root -p -V

8. Upgrade MariaDB

   Run the upgrade command to upgrade MySQL database to MariaDB. This command will migrate all the existing databases with its data to latest installed MariaDB

   sudo mysql_upgrade

   
   

   MariaDB has been successfully upgraded to the latest version.

Now you can login to the MariaDB using the below command and verify whether all the existing data is available in the Database.

sudo mysql -u root -p