OCI MySQL database service (with Observium example)

• Intro
• Create MySQL DB service
• Observium host
• Verify connection to MySQL service
• Connect with MySQL Shell
• Connect with MySQL client
• Observium configuration to use MySQL DB service
• Backup
• Restore

Intro

Create MySQL DB service

• Database - MySQL -DB Systems - Create MySQL DB System (chose compartment, provide DB name, AD, FD, configuration (a shapes supported by MySQL DB Service), size, maintenance window).
• Chose login credentials, network, hostname (it's service endpoint) and port (leave 3306 as default)
• Select backup, retention in days and backup window

Observium host

Verify connection to MySQL service

Connect with MySQL Shell

• Install mysql-shell on OCI compute

• $ yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm $ yum install mysql-shell

• Connect to the service endpoint (hostname)

• [root@oci-observium ~]# mysqlsh root@my-mysql-demo.your_subnet.your_tenancy.oraclevcn.com Please provide the password for 'root@my-mysql-demo.your_subnet.your_tenancy.oraclevcn.com': Welcome.1 (for this demo) MySQL Shell 8.0.21 Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type '\help' or '\?' for help; '\quit' to exit. Creating a session to 'root@my-mysql-demo.your_subnet.your_tenancy.oraclevcn.com' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 9 (X protocol) Server version: 8.0.21-u1-cloud MySQL Enterprise - Cloud No default schema selected; type \use to set one. MySQL my-mysql-demo.your_subnet.your_tenancy.oraclevcn.com:33060+ ssl JS >

Connect with MySQL client

• Install mysql client and connect to the endpoint

• $ yum install mysql [root@oci-observium ~]# mysql --host my-mysql-demo.your_subnet.your_tenancy.oraclevcn.com -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.21-u1-cloud MySQL Enterprise - Cloud Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)

Observium configuration to use MySQL DB service

• Connect to OCI MySQL service and create 'observium' database, create user observium@10x.10x.11x.17x (IP of observium host) and grant him privileges.

• mysql> CREATE DATABASE observium DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected, 2 warnings (0.01 sec) mysql> CREATE USER 'observium'@'10x.10x.11x.17x' IDENTIFIED WITH mysql_native_password BY 'my-passwd'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON observium.* TO 'observium'@'100.102.116.173'; Query OK, 0 rows affected (0.01 sec)

• Add MySQL database info in /opt/observium/config.php

• $config['db_extension'] = 'mysqli'; $config['db_host'] = 'my-mysql-demo.your_subnet.your_tenancy.oraclevcn.com'; # OCI mysql service endpoint $config['db_user'] = 'observium'; $config['db_pass'] = 'my-passwd'; $config['db_name'] = 'observium';

• Create the initial MySQL schema

• [root@oci-observium ~]# /opt/observium/discovery.php -u ___ _ _ / _ \ | |__ ___ ___ _ __ __ __(_) _ _ _ __ ___ | | | || '_ \ / __| / _ \| '__|\ \ / /| || | | || '_ ` _ \ | |_| || |_) |\__ \| __/| | \ V / | || |_| || | | | | | \___/ |_.__/ |___/ \___||_| \_/ |_| \__,_||_| |_| |_| Observium Community Edition 19.8.10000 http://www.observium.org Install initial database schema ... done. -- Database is up to date.

• Check

• mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | observium | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)

• Add admin user with command: /opt/observium/adduser.php admin admin 10
• Login as admin/admin to your Observium machine http://oci-observium.your-subet.your-tenancy.oraclevcn.com
• Add a device with command: /opt/observium/add_device.php some-hostname public v2c 161 udp

Backup

• Automatic daily backup can be enabled for MySQL service. Currently it has limited features, retention period in days and backup window start time.
• Backup can be also managed via some script, using command "mysqldump". See: github.com/etc-fstab/backup/tree/master/mysql

Restore

• The automatic backup can be restored to new MySQL database system: Restore to new DB system - select VM shape, login credentials, etc.
• The script backup can be restored via mysql command. First you need to fix dump file, by commenting out "SET @@SESSION" and "SET @@GLOBAL".

  # diff 2020-08-25_23:52-mysql-observium 2020-08-25_23:52-mysql-observium~ 18c18 < -- SET @@SESSION.SQL_LOG_BIN= 0; --- > SET @@SESSION.SQL_LOG_BIN= 0; 24c24 < -- SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '4e0a0efb-e71e-11ea-a765-0200170174d1:1-365'; --- > SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '4e0a0efb-e71e-11ea-a765-0200170174d1:1-365'; 3872c3872 < -- SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN; --- > SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

  
  You can simulate incident by droping the database observium, and restore it from mysqldump file (but first, create new one with same name). In this example, mysql password for root is "mypassword".

  mysql> drop database observium; Query OK, 110 rows affected (1.06 sec) mysql> CREATE DATABASE observium DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected, 2 warnings (0.00 sec) $ mysql -u observium -p"my-passwd" -h OCI-mysql-service-endpoint observium < 2020-08-25_23:52-mysql-observium