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:

Migration from Stock Databases: CentOS 6.x and 7.x stock databases (MySQL 5.1 and MariaDB 5.5 respectively)
Migration from Custom MySQL
Manual migration

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.

Download the OnApp YUM repository file:

db# rpm -Uvh http://rpm.repo.onapp.com/repo/onapp-repo-6.3.noarch.rpm

Update following packages, to get recent migration utility:
```
db# yum -y update onapp-mysql
```

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

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.

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

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:

Download the OnApp YUM repository file:

db# rpm -Uvh http://rpm.repo.onapp.com/repo/onapp-repo-6.3.noarch.rpm

Update following packages, to get recent migration utility:
```
db# yum -y update onapp-mysql
```

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

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

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

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`
```
Clean YUM metadata:
```
db# yum clean all
```
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

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

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

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