Home Assistant - Setup MariaDB Database Size Sensor

Here I describe how to create a sensor for Home Assistant that will monitor the size of the MariaDB database with a scan interval of one hour.

Dependencies

Make sure you have MariaDB running and MariaDB configured for use with Home Assistant. The secrets.yaml must be created with a db_url for use with MariaDB.

Configuration

I run the following on the CLI of my Ubuntu VM.

Go to the config directory of Home Assistant and edit configuration.yaml with the Nano text editor:

sudo nano configuration.yaml

Make sure the following is added to the configuration.yaml:

sensor: !include sensors.yaml

Exit Nano (CTRL-X) and save the changes.

Sensor

Edit sensors.yaml:

sudo nano sensors.yaml

Add the following:

- platform: sql
  db_url: !secret db_url
  scan_interval: 3600
  queries:
    - name: MariaDB size
      query: 'SELECT table_schema AS "database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;'
      column: "value"
      unit_of_measurement: MB

If needed replace the following:

scan_interval: 3600
The scan interval in seconds. In this example the scan interval is one hour

table_schema=”homeassistant”
Replace homeassistant with the name of your database if needed

Exit Nano (CTRL-X) and save the changes.

Using the Sensor

Now go to the Home Assistant UI and check your configuration via Configuration > Settings > Server Controls > Check Configuration (the button). When the configuration is valid restart the server and you can use the sensor.

The sensor is also added to the Overview dashboard if it is still maintained by Home Assistant (which is default).

Read other notes