Configuring Database Backend
1. Using SQLite Database
| Configuration Option | Value | Description |
|---|---|---|
| database.type | sqlite3 |
Sets the database backend to SQLite |
| database.path | /var/lib/grafana/grafana.db |
Path to the SQLite database file |
| database.max_idle_conn | 2 |
Maximum number of idle database connections |
| database.max_open_conn | 100 |
Maximum number of open database connections |
Example: SQLite Configuration in grafana.ini
[database]
type = sqlite3
path = /var/lib/grafana/grafana.db
max_idle_conn = 2
max_open_conn = 100
Note: SQLite is suitable for small to medium deployments. For production environments with high concurrency, consider using PostgreSQL or MySQL.
2. Configuring MySQL Backend
Example: MySQL Configuration in Grafana
[database]
type = mysql
host = 127.0.0.1:3306
name = grafana_db
user = grafana_user
password = secure_password
ssl_mode = disable
| Property | Value | Description |
|---|---|---|
| type | mysql |
Database type to use (must be mysql) |
| host | 127.0.0.1:3306 |
MySQL server host and port |
| name | grafana_db |
Name of the database to use |
| user | grafana_user |
Database user for Grafana to connect as |
| password | secure_password |
Password for the database user |
| ssl_mode | disable |
SSL mode for database connection (disable, enable, require) |
Note: Ensure the MySQL user has appropriate permissions to read and write to the database.
Warning: Never store passwords in plain text in configuration files. Use environment variables or secure secret management.
3. Configuring PostgreSQL Backend
| Property | Value | Description |
|---|---|---|
| database.type | postgres |
Database type to use for Grafana backend |
| database.host | localhost:5432 |
PostgreSQL server host and port |
| database.name | grafana |
Name of the database to use |
| database.user | grafana_user |
Database user for Grafana to connect as |
| database.password | secret_password |
Password for the database user |
| database.sslmode | require |
SSL mode for database connection (disable, require, verify-full) |
Example: PostgreSQL Configuration in grafana.ini
[database]
type = postgres
host = localhost:5432
name = grafana
user = grafana_user
password = secret_password
sslmode = require
Note: Ensure the PostgreSQL database and user are created before starting Grafana.
Warning: The
database.password should be stored securely and not exposed in plain text.
4. Setting Database SSL Mode
| Option | Values | Description |
|---|---|---|
| database.ssl.mode | disable, require, verify-ca, verify-full |
SSL mode for database connections. NEW in Grafana 10.0 |
Note: Ensure your database server supports the specified SSL mode. Misconfiguration can lead to connection failures.
5. Configuring Connection Pool
| Property | Default Value | Description | Notes |
|---|---|---|---|
| connection.max_idle_conns | 10 |
Maximum number of idle connections in the pool | Set to 0 for unlimited idle connections |
| connection.max_open_conns | 100 |
Maximum number of open connections in the pool | Set to 0 for unlimited open connections |
| connection.conn_max_lifetime | 14400s |
Maximum lifetime of a connection in seconds | Connections older than this will be closed and reopened |
Example: Database Connection Pool Configuration
[database]
type = mysql
host = 127.0.0.1:3306
name = grafana_db
user = grafana_user
password = secret_password
# Connection pool settings
max_idle_conns = 10
max_open_conns = 100
conn_max_lifetime = 144006. Setting Database Log Queries
| Option | Values | Notes |
|---|---|---|
| log_queries | false |
Enable logging of SQL queries to the Grafana log file |
| log_queries_duration_threshold | 1000 |
Log queries that take longer than this duration (in milliseconds) |
Example: Enable query logging in Grafana configuration
[database]
log_queries = true
log_queries_duration_threshold = 500
Note: Enabling query logging can significantly impact performance in high-traffic environments. It is recommended to enable this setting temporarily for debugging purposes.
7. Running Database Migrations
| Command | Description | Notes |
|---|---|---|
| grafana-cli migrations up | Run all pending database migrations | Ensures database schema is up to date with current Grafana version |
| grafana-cli migrations down | Revert to previous migration version | Use with caution; may cause data loss or inconsistencies |
| grafana-cli migrations status | Check current migration status | Shows which migrations have been applied and pending |
Note: Always backup your database before running migrations in a production environment.
Warning: Migrations are irreversible in some cases. Ensure Grafana is stopped before running migrations.
Migration Process Overview
- Start Grafana server or CLI tool
- Run
grafana-cli migrations statusto check state - Execute
grafana-cli migrations upto apply pending changes - Verify migration success in logs or via status command
8. Backing Up Database
Note: Grafana stores its configuration and data in a database backend (SQLite, MySQL, or PostgreSQL). Regular backups are essential for disaster recovery.
| Backup Type | Description | Command/Method | Notes |
|---|---|---|---|
| Database Dump | Full database export using SQL dump tools | mysqldump -u <user> -p <database> > backup.sql |
For MySQL/PostgreSQL. Ensure proper permissions and compression. |
| File System Backup | Copy Grafana's data directory and configuration files | tar -czf grafana-backup.tar.gz /var/lib/grafana |
Includes dashboards, datasources, users, and plugins. |
| Incremental Backup | Only backup changed data since last backup | Use database-specific tools or third-party solutions | Requires scripting or external tools like pg_dump for PostgreSQL. |
Example: MySQL Backup Script
#!/bin/bash
DB_USER="grafana_user"
DB_PASS="secure_password"
DB_NAME="grafana_db"
BACKUP_DIR="/backups/grafana"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/grafana_backup_$DATE.sql"
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME > $BACKUP_FILE
gzip $BACKUP_FILE
echo "Backup completed: $BACKUP_FILE"
Backup Process Workflow
- Stop Grafana service to prevent data changes during backup
- Run database dump or file system copy
- Verify integrity of backup files (e.g., restore to test DB)
- Schedule automated backups using cron or similar
- Store backups securely offsite or in cloud storage (e.g., S3, GCS)
Backup Best Practices
- Automate backups using cron jobs or Grafana's built-in backup features (if available)
- Test restore procedures regularly
- Keep at least 2-3 recent backups to allow rollback in case of corruption
- Encrypt sensitive backup files before storing them
- Monitor disk space to avoid backup failures due to insufficient storage
9. Restoring Database Backups
Example: Restore from SQL dump
# Export current database (example for PostgreSQL)
pg_dump -h localhost -p 5432 -U grafana_user grafana_db > backup.sql
# Stop Grafana service
sudo systemctl stop grafana
# Restore database (example for PostgreSQL)
psql -h localhost -p 5432 -U grafana_user grafana_db < backup.sql
# Start Grafana service
sudo systemctl start grafana| Step | Description | Notes |
|---|---|---|
| 1. Backup Current Data | Export the current database to a file using native tools (e.g., pg_dump for PostgreSQL, mysqldump for MySQL). | Ensure backup includes all tables and schema required by Grafana. |
| 2. Stop Grafana Service | Stop the Grafana server to prevent data changes during restore. | Use appropriate OS commands (e.g., systemctl stop grafana). |
| 3. Restore Database | Import the backup file into the target database using native tools. | Ensure permissions and user roles match between backup and restore environment. |
| 4. Start Grafana Service | Restart the Grafana server after restore completes. | Verify that all dashboards and data sources are accessible post-restore. |
Note: Always test the restore process in a non-production environment before applying to production systems.
Warning: Restoring a database backup can overwrite existing data. Ensure backups are recent and verified before proceeding.
10. Optimizing Database Performance
| Configuration Option | Description | Default Value | Notes |
|---|---|---|---|
| database.max_idle_conns | Maximum number of idle database connections | 2 |
Reduce for low-memory environments |
| database.max_open_conns | Maximum number of open database connections | 0 |
0 means unlimited; set to prevent connection exhaustion |
| database.conn_max_lifetime | Maximum lifetime of a database connection | 14400s |
Set to prevent long-lived connections from becoming stale |
| database.cache_ttl | Time to live for query result cache | 30s |
Adjust based on data volatility and query frequency |
Example: Database Connection Pool Configuration
[database]
max_idle_conns = 5
max_open_conns = 10
conn_max_lifetime = 30s
Note: Adjust
max_open_conns based on your database's capacity and Grafana's expected load.