Collect from multiple databases

[ambu50]

Hi,

Is there a way to collect from multiple databases? I am using mysql data source and would like to read from two different databases within the same server.

[burningalchemist]

Hi @ambu50,

Try to apply the following snippet at sql_exporter.yml with your own adjustments:

...
jobs:
  - job_name: test1
    collectors: [mysql_standard]
    static_configs:
      - targets:
          mysql1: 'mysql://tcp(host1:3306)/'
          mysql2: 'mysql://tcp(host2:3306)/'
...

You need to replace target object from the default configuration with jobs. And collectors becomes a part of jobs as well, so no duplicates.

mysql1, mysql2 are arbitrary names, can be anything.

Let me know if it works for you. I made a quick test, but didn't look deeper. 🙂

[ambu50]

Hi,

Thanks for your reply. My question was actually about accessing two databases from the same DBMS host. My issue was that I included the database name in the DSN like this: mysql://tcp(host1:3306)/db1. The solution is to remove the database name from the DSN then include it in the mysql query like this "select count(*) from db1.table1"

I am sure your solution will help some other users with multiple DMBSs.

[burningalchemist]

Hey @ambu50, oh I'm sorry, I misunderstood that you meant logical databases, not database instances. Yes, this is the way. 🙂👍

[jdstone]

@burningalchemist, does the ability to perform a SELECT * FROM db1.table1 not work with MSSQL, as @ambu50 did with MySQL?

[burningalchemist]

Hey @jdstone, the interaction with the DB really depends on a particular driver implementation and the database. What's your current issue?

[jdstone]

@burningalchemist, I'd like to be able to connect to multiple databases without having to setup multiple sql_exporter containers.
I reviewed the drivers documentation, but didn't find anything that would help.

[jdstone]

Try to apply the following snippet at sql_exporter.yml with your own adjustments:

...
jobs:
  - job_name: test1
    collectors: [mysql_standard]
    static_configs:
      - targets:
          mysql1: 'mysql://tcp(host1:3306)/'
          mysql2: 'mysql://tcp(host2:3306)/'
...

You need to replace target object from the default configuration with jobs. And collectors becomes a part of jobs as well, so no duplicates.

mysql1, mysql2 are arbitrary names, can be anything.

Let me know if it works for you. I made a quick test, but didn't look deeper. 🙂

Hi @burningalchemist, in addition to being stuck with my previous question (from 2 days ago), I'm also confused by your response to ambu50 in the quote above. I have the following config, which specifies the Kubernetes Pod running the sql_exporter application. Are you saying that the Prometheus job configuration can be used to set to directly target a database server? Basically, I'm confused why you would want to specify a host with the mysql:// protocol (rather than http://, or simply no protocol).

- job_name: sql-exporter

  scrape_interval: 15m
  static_configs:
  - targets: ['10.224.0.4:30011']

Thank you.

[jdstone]

@burningalchemist, could you please answer my questions above?

[burningalchemist]

Hey @jdstone, this configuration snippet is not for Prometheus. In sql_exporter besides a single target scrape, we can also define jobs: we can specify multiple targets against which the exporter executes queries.