Migrate Control Panel Database to MariaDB 10.4

Starting with 6.3, OnApp Control Panel servers databases are migrated to MariaDB 10.4. For this reason, It is required to migrate your databases to MariaDB 10.4 as well. 

  • The migration should be processed before OnApp is upgraded to 6.3 version.
  • It is required Control Panel's database is migrated to MySQL version as minimum as 5.6.4 with microseconds resolution in the TIME, TIMESTAMP, and DATETIME types.
  • No migration required if you use any of custom MySQL version >= 5.6.4.
  • MariaDB version 10.4 is default for OnApp version >= 6.3 (if no custom MySQL).
  • The migration should be run manually using onapp-db-migrate.sh utility.
  • The downtime will be longer than during regular OnApp Control Panel upgrades because of the migration. For example, 6 GB database migration will take 2 hours.

     Click here to see an example
    1. The migration will take two hours for a database with the following characteristics: 
    db# du -smh /var/lib/mysql/onapp
    5.8G	/var/lib/mysql/onapp

    Hardware characteristics:

    CPU: Intel(R) Xeon(R) CPU E5506 @ 2.13GHz

    RAM: MemTotal: 24728512 kB

    HDD (LVM on 2 disks): Hitachi HDS721050CLA362 (500 GB)

          2. The migration may take four hours (without --skipdump) or one minute (with --skipdump) for a database with the following characteristics: 

    +--------------------+----------------------+
    | Data Base Name     | Data Base Size in MB |
    +--------------------+----------------------+
    | information_schema |           0.18750000 |
    | mysql              |           2.69531250 |
    | onapp              |       32183.37500000 |
    | performance_schema |           0.00000000 |
    +--------------------+----------------------+

    The case above also implies that the host compute resource is utilizing Intel(R) Xeon(R) Gold 6248 CPU @ 2.50GHz CPUs, datastore is remote, and the Control Panel server is located on a VS with the following characteristics: 

    # free -m
                  total        used        free      shared  buff/cache   available
    Mem:         126382       30463       66991          33       28927       95035
    Swap:          8191           0        8191
  • Please pay attention to migration utility logs and output, so you can properly react if the migrate fails. The is no automated rollback in case of failure.

You can perform the following migrations depending on your databases type:

For details and instructions, refer to the sections below. 

Command line prompts

db# - run on box, where database resides with corresponded MySQL solution installed;
cp# - run on box, where Control Panel is installed;
In most cases, db# and cp# are the same boxes (physical computer or a virtual server, where the database server is installed).

If you need to check your MySQL version, run:

# mysql -u root -h MYSQLHOST --password=MYSQLPASSWD -e 'SELECT version();'
+-----------------+
| version()       |
+-----------------+
| 10.3.17-MariaDB |
+-----------------+

Where:
MYSQLHOST - IP address or FQDN where MySQL serves database;
MYSQLPASSWD - MySQL root user password.

Migration from Stock Databases



To migrate from CentOS 6.x and 7.x stock databases (MySQL 5.1 and MariaDB 5.5 respectively), proceed with the following steps. Most of the steps should be processed on the box, where the database resides.

  1. Download the OnApp YUM repository file:

    db# rpm -Uvh http://rpm.repo.onapp.com/repo/onapp-repo-6.3.noarch.rpm
  2. Update following packages, to get recent migration utility:

    db# yum -y update onapp-mysql
  3. Stop all OnApp services, If Control Panel and database are on separate boxes(CentOS 7.x example):

    cp# monit unmonitor all
    cp# systemctl stop onapp
    cp# systemctl stop httpd
    cp# systemctl stop crond
  4. To display migration utility's option full list, run:

    db# /onapp/onapp-mysql/onapp-db-migrate.sh -h

    --skipdump option could be used to speed up migration and if you are sure database backup isn't required.
    --mysqluser, --mysqlpasswd options can be useful if your database is located separately from Control Panel, and there are no /onapp/interface/config/database.yml config to read access credentials.


  5. To migrate the database to MariaDB version 10.4, run:
    • CentOS 6.x

      db# /onapp/onapp-mysql/onapp-db-migrate.sh --mariadb --skipdump
    • CentOS 7.x

      db# /onapp/onapp-mysql/onapp-db-migrate.sh --mariadb-custom --skipdump
  6. Upgrade Control Panel after the migration is completed.

Migration from Custom MySQL



To migrate from custom MySQL, proceed with the following steps. Most of the steps should be processed on box, where the database resides. MySQL Community 5.5 on CentOS 7.x example:

  1. Download the OnApp YUM repository file:

    db# rpm -Uvh http://rpm.repo.onapp.com/repo/onapp-repo-6.3.noarch.rpm
  2. Update following packages, to get recent migration utility:

    db# yum -y update onapp-mysql
  3. Stop all OnApp services, and create a recent dump of OnApp database (CentOS 7.x example):

    cp# monit unmonitor all
    cp# systemctl stop onapp
    cp# systemctl stop httpd
    cp# systemctl stop crond
  4. Create database dump if required and if you are going to restore it at the very end of the migration:

    • If Control Panel and database on the same box, dump with:

      cp# /onapp/onapp-cp-install/onapp-database-dump.sh -M -r

      Dump will be named like/onapp/interface/db/dump/xxxxxxxxxx.localhost.onapp.sql.gz, unzip it:

      cp# gunzip /onapp/interface/db/dump/xxxxxxxxxx.localhost.onapp.sql.gz

      Your DUMP_FILE is /onapp/interface/db/dump/xxxxxxxxxx.localhost.onapp.sql


    • Dump onapp database manually to the DUMP_FILE, if the database is on separate from Control Panel box
  5. Determine MySQL related RPM packages list. Those all packages must be removed (CentOS 7.x example):

    db# rpm -qa | grep -i community
    mysql-community-libs-5.5.62-2.el7.x86_64
    mysql-community-release-el7-5.1.onapp.x86_64
    mysql-community-client-5.5.62-2.el7.x86_64
    mysql-community-server-5.5.62-2.el7.x86_64
    mysql-community-common-5.5.62-2.el7.x86_64
  6. Remove all MySQL related RPM packages (on the box, where database resides):

    • If Control Panel and database on the same boxes:

      cp# rpm -e --nodeps --allmatches `rpm -qa | grep -i community` rubygem-mysql2
    • If Control Panel and database on separate boxes:

      db# rpm -e --nodeps --allmatches `rpm -qa | grep -i community`
  7. Clean YUM metadata:

    db# yum clean all
  8. To display migration utility's option full list, run:

    db# /onapp/onapp-mysql/onapp-db-migrate.sh -h

    --mysqluser and --mysqlpasswd options can be useful if your database is located separately from Control Panel, and there is no /onapp/interface/config/database.yml config to read access credentials

  9. To migrate database to MariaDB version 10.4 run:

    • CentOS 6.x:

      db# /onapp/onapp-mysql/onapp-db-migrate.sh --mariadb -f --dumpfile DUMP_FILE
    • CentOS 7.x:

      db# /onapp/onapp-mysql/onapp-db-migrate.sh --mariadb-custom -f --dumpfile DUMP_FILE
  10. Tell monit (if used) to monitor all services:

    cp# monit monitor all

Manual Migration



To migrate manually (not using the migration utility), the following steps are recommended (this case assumes MySQL database is on separate of Control Panel box):

  1. Stop all OnApp related services on Control Panel box: onapp, httpd and crond (to make sure no rake tasks are running as cron job).
  2. Dump OnApp's database.
  3. Disable innodb-fast-shutdown
  4. Stop mysql service.
  5. Remove old MySQL:
    • related RPM packages with --nodeps --allmatches options and left unsatisfied RPMs' dependencies
    • or uninstall according to MySQL's recommended procedure
  6. Backup old mysql configuration file.
  7. Remove ib_logfile (for the first time).
  8. Install mysql new version >= 5.6.4.
  9. Start mysql service (new).
  10. Check database mysqlcheck --all-databases --check-upgrade --auto-repair.
  11. Update database mysql_upgrade.
  12. Stop mysql service.
  13. Remove ib_logfile (for the second time).
  14. Check or, if necessary, configure settings in the configuration file.
  15. Start mysql service.
  16. Restore OnApp's database (dumped on step 2).
  17. Start OnApp-related services on Control Panel box.


Having migrated databases to MariaDB 10.4, you may proceed with upgrading Control Panel server and other servers to 6.3.