GuidesREST APISlack CommunitySupport PortalOpsview 6.8+ Documentation

# Overview

This document describes the process for migrating your databases so that they are fully supported for strict SQL mode on MySQL/MariaDB.



The manual schema upgrade _may_ take many hours to run and you need to ensure your database server has adequate disk space available.

The schema must be run for upgrades to 6.7 or greater. Future upgrades may be blocked until this manual schema process is performed. See [MySQL 8 / MariaDB 10 Support](🔗) for more details.

Has the migration already been performed?

The migration steps may have already been run and therefore they do not need to be performed again.

  • You may check your database schema tables to confirm if this is so.

As root:

for i in opsview runtime odw; do echo "$i database: Checking for UTF8 Migration"; /opt/opsview/coreutils/utils/cx $i "select created_at,major_release from $i.schema_version where major_release='utf8_migration';"; echo; done

**Expected output:** opsview database: Checking for UTF8 Migration +---------------------+----------------+ | created_at | major_release | +---------------------+----------------+ | 2022-03-07 18:04:13 | utf8_migration | +---------------------+----------------+

runtime database: Checking for UTF8 Migration +---------------------+----------------+ | created_at | major_release | +---------------------+----------------+ | 2022-03-07 18:49:27 | utf8_migration | +---------------------+----------------+

odw database: Checking for UTF8 Migration +---------------------+----------------+ | created_at | major_release | +---------------------+----------------+ | 2022-03-08 20:42:09 | utf8_migration | +---------------------+----------------+

If you do receive these lines, then please proceed.

  • if you do not receive output for each database check, then the migration needs to be performed against that database

  • this is achieved by following the process notes through to that particular database

# Prerequisites

The migration needs either:

  • A second database server of the same size or larger, or

  • The same instance but with enough disk space (as a copy will be created before dropping the original on success). You will need enough space to hold a second copy of your largest database.

If a second database server is used, there will need to be an SSH connection from the second (target) database server back to the first (source) database server.

The migration will estimate the database size and attempt to check the disk space available, but this should always be manually confirmed.

If you use replication servers, they must use the same sql_mode setting.

The migration of the databases will be in the following order: `opsview`, `runtime`, `odw`.

The migration will cause an outage. The duration is based on the size of your database. After `opsview` and `runtime` are migrated, Opsview can be restarted while ODW is migrated in the background.

Take a backup of your databases before you begin. Store safely.

Although there are many system variables that affect the duration of the migration, we estimate a migration takes about 1 hour for every 10GB of data in the database.

To reduce time taken for imports, you can reduce the amount of data in the `runtime.nagios_servicechecks` table. If ODW imports are up to date, drop partitions older than the current day. See the instructions below in [Pruning nagios_servicechecks](🔗). You can also disable the `innodb_doublewrite` parameter within MySQL or MariaDB during the migration if you have it set.

You will need root access to update the deploy configuration. Deploy must be run on the orchestrator.

# Migration Steps

Use `tmux` or `screen` so that connection failures do not interrupt or break the migration.

## Terminology

  • **Source database server** - this is the original location of the databases

  • **Target database server** - this is the target location of the migrated databases. It can be on the same server as source server. If a different server, this can be a newer version of MySQL or MariaDB

  • **DBNAME **- database to migrate

## Local migration (same DBHOST)

These steps are to migrate a database locally within the same database host:

  • If MySQL replication is configured to another database server, stop replication on the replica:


  • Take a backup of the Opsview Monitor configuration files into `/opt/opsview/var/backups/`

  • Disable ODW imports using flag file: 

  • Set current working directory to deploy working area: 

  • Stop Opsview on all servers

  • Start loadbalancer on DB server and orchestrator: 

  • Edit `etc/user_vars.yml` and add in:

  • If you want to enable bin logging (for replication), edit `etc/user_vars.yml` and add in:

  • Run `bin/opsview-deploy lib/playbooks/database-install.yml` to reconfigure database
  • Run migration script for opsview: `bin/opsview-deploy lib/playbooks/database-migrate-opsview.yml`
  • Run migration script for runtime: `bin/opsview-deploy lib/playbooks/database-migrate-runtime.yml`
  • Run migration script for odw: `bin/opsview-deploy lib/playbooks/database-migrate-odw.yml` _Note:_ this step may be performed immediately after the `Start Opsview on all servers` step if required, but it must be completed before `Re-enable ODW imports`. This step is unnecessary if you do not have or use ODW (please check your license).
  • Start Opsview on all servers

  • When ODW has finished, re-enable ODW imports: 

  • If you use MySQL replication, you should reinitialise replication again
## Remote migration (to new DBHOST)
These steps are to migrate from a source database to a new remote target database:
  • Confirm SSH connectivity for root user to new db server
    • Confirm root user on deploy server is authorised to SSH into target DB server
    • Confirm root user on target DB server is authorised to SSH into source DB server
  • If MySQL replication is configured, stop replication on the replica:

  • Take a backup of the Opsview Monitor configuration files into `/opt/opsview/var/backups/`

  • Disable ODW imports using flag file: 

  • Set current working directory to deploy working area: 

  • Stop Opsview on all servers

  • Update `etc/opsview_deploy.yml` to configure your new database server

  • Add the following to `etc/user_vars.yml` to set sql mode

  • If you want to enable bin logging (for replication), edit `etc/user_vars.yml` and add in:

  • Note: Optional: If you are planning to migrate to MySQL 8.x or MariaDB 10.x at this point, you can also set a specific SQL version in  `etc/user_vars.yml` for the target server, i.e.

  • _If you are moving to MySQL 8.x or MariaDB 10.x_ you will need to set more secure passwords by following the next 3 steps: 
  • Backup and edit `user_secrets.yml`
  • Copy `opsview_database_root_password` to `opsview_database_migrate_source_root_password`
  • Update all database passwords in user secrets to be more secure (must have at least 2 digits and at least 2 special characters from `+ - _ . , ^`)
    
  • Install new database server (WARNING: it is important to limit the playbooks to new database server only).  **Note: ** `new database host` should be the name of the instance, not the IP address

  • Start loadbalancer on orchestrator host, source DB server and target DB server:

  • Run migration/move playbooks. Only run for the databases that are being used:

  • Remove or comment out database_hosts_source config section from opsview_deploy.yml
  • Run deploy to reconfigure components

  • Run deploy to add monitoring of the new DB server

  • Start Opsview on all servers

  • Delete the old database server host from the monitoring servers via the UI if you no longer want to monitor it.
  • Within the Opsview UI 'Apply Changes', the new database server should now be seen.
  • Re-enable ODW imports

  • If you use MySQL replication, you should reinitialise replication again
  • You can now remove the old databases
# Hints and Tips
## Improve write performance
You can improve write I/O performance by setting `innodb_doublewrite=0` in the MySQL/MariaDB database configuration before running. See the database documentation for more information. Remember to set it back after the migration.
## Pruning nagios_servicechecks
To reduce the data in nagios_servicechecks, the quickest way is drop partitions. To do this in the database, run:

If this table is partitioned, this will show you output like:

Each partition represents a day. As it is safe to delete all days except the latest when ODW is up to date, run, for instance:

Alternatively, if the `runtime.nagios_servicechecks` table is not partitioned, you can reduce rows by removing older data with:

## Database Connections Issues
The migration steps for the remote databases assume a working remote database. If you have trouble connecting, which can happen if `setup-infrastructure.yml` is run against the source remote database, then you need to revert the `opsview_deploy.yml` to previous, re-run `setup-infrastructure.yml` to get back to a previous state and then repeat the instructions above.