Pattern Goal

The goal of the Oracle MySQL Server Database Detail pattern is to obtain the list of Databases being managed by the MySQL Server, and to obtain (optionally, and disabled by default) the list of Tables within these databases.

The information is then stored within the Discovery Model as DatabaseDetail Nodes. The DatabaseDetail node has attributes that simplify mapping by the CMDB sync mechanism to the BMC_Database CIs into the target CMDB.

Setup

Setting of Database Credentials

Add the Database credentials, including Username and Password.  You will need to ensure the credential has the SHOW DATABASES privilege (see publisher's documentation).  If you wish to retrieve table details for a database the credential will need the relevant access to information_schema.tables

Setting the port

The option to set the default port no longer exists in the MySQL Database Detail pattern.
The MySQL Database Detail pattern behavior is now as follows:

• If the core TKU MySQL Server Pattern discovers the listening port, this is the port that will be used
• Alternatively, the MySQL Database Detail pattern will use the port that has been set in the database credentials by the user.
• If no port was retrieved and port DB listening port was not set by the user, the MySQL Database Detail pattern will not attempt to perform database queries.
  The reason for this change is to minimize the number of configuration steps required and prevent errors due to forgotten changes in pattern configuration.

Finding Databases

We create a Database node for each database

SQL Queries Executed

The pattern executes the following SQL Query to find databases: SHOW DATABASES

Command

The pattern executes the following command to find databases: /usr/bin/mysqlshow 

Discovery by File Query

Discovery by file query is only attempted if discovery by a database credential fails and the use_listdir configuration option is enabled.  By default the use_listdir option is enabled

The pattern obtains a list of all subdirectories of the db_path.  If any of these subdirectories contains a database file the patterns assumes this corresponds to a database with the name of the subdirectory, and creates a Database node.  They files the pattern looks for are:

• Any db.opt file (for versions prior to 8.0)
• Any file with the extension .ibd (refers to a table space)
• Any file with the extension .sdi (alternative table space file)

So, for example, if /opt/MySQL/data has a subdirectory "BigData" and file /opt/MySQL/data/BigData/db.opt exists then a Database node will be created with instance name "BigData"

Finding Tables

We only attempt to retrieve tables if the retrieve_tables configuration option is set.  By default it is false.  Enabling this option will impact performance

We attempt to find table information by running the following database query:

• SELECT table_name FROM information_schema.tables WHERE table_schema = <db_instance>

If that fails we run the following command (UNIX only).

• /usr/bin/mysqlshow <db_instance>

We create a DatabaseDetail node for each table.

Finding Database Size

We find database size using the following database query

• select round(sum(data_length+index_length)) as size from information_schema.tables where table_schema = "%db_name%"