Skip to main content
gloureiro
Bronze ⭐️⭐️⭐️
gloureiro
Bronze ⭐️⭐️⭐️
May 15, 2016

How to set up Grafana to use MySQL database rather than the default sqlite

  • October 5, 2026 at 9:10 AM
  • 3 replies
  • 88586 views
  • Translate All

Assume you have a MySQL database server running on mysqlserver.example.com.

Summary of the steps

  1. MySQL set up
    • Set up a grafana database on mysqlserver.example.com
    • Create a MySQL user called grafana, assign and flush privileges
  2. Edit the grafana.ini configuration file to enable use of MySQL (Edit the Advanced ams-grafana-ini section in the Ambari Metrics Config tab in Ambari)
  3. Start the Grafana
    • Starting Grafana creates the grafana tables. The session table however does not get created and the grafana docs say to create it for MySQL and postgres - so create the session table as per the grafana docs http://docs.grafana.org/installation/configuration/
    • Start Grafana - and all should be well

Detailed steps

On MySQL server (be sure to change the password):

mysql> create database grafana;
Query OK, 1 row affected (0.01 sec)
mysql> GRANT USAGE ON `grafana`.* to 'grafana'@'mysqlserver.example.com' identified by 'grafanamysqlpasswd';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON `grafana`.* to 'grafana'@'mysqlserver.example.com' with grant option;Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> use grafana;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> create table `session` (
-> `key` char(16) not null,
-> `data` blob,
-> `expiry` int(11) unsigned not null,
-> primary key (`key`)
-> ) ENGINE=MyISAM default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

Edit the grafana.ini configuration file (in Ambari, edit the Advanced ams-grafana-ini section in the Ambari Metrics Config tab) - see snippet from the config file below

<snipped>
#################################### Database ####################################
[database]
# Either "mysql", "postgres" or "sqlite3", it's your choice
;type = sqlite3
;host = 127.0.0.1:3306
;name = grafana
;user = root
;password =
type = mysql
host = mysqlserver.example.com:3306
name = grafana
user = grafana
password = grafanamysqluserpasswd
# For "postgres" only, either "disable", "require" or "verify-full"
;ssl_mode = disable
# For "sqlite3" only, path relative to data_path setting
;path = grafana.db
#################################### Session ####################################
[session]
# Either "memory", "file", "redis", "mysql", "postgres", default is "file"
;provider = file
provider = mysql
# Provider config options
# memory: not have any config yet
# file: session dir path, is relative to grafana data_path
# redis: config like redis server e.g. `addr=127.0.0.1:6379,pool_size=100,db=grafana`
# mysql: go-sql-driver/mysql dsn config string, e.g. `user:password@tcp(127.0.0.1:3306)/database_name`
# postgres: user=a password=b host=localhost port=5432 dbname=c sslmode=disable
;provider_config = sessions
provider_config = `grafana:grafanamysqluserpasswd@tcp(mysqlserver.example.com:3306)/grafana`
provider = mysql
<snipped>

    3 replies

    christopher_w_m
    christopher_w_m
    Gold ⭐️⭐️
    August 12, 2016

    For postgres, I needed slightly different steps.

    psql
    create database grafana;
    create user grafana with password 'grafana';
    GRANT ALL PRIVILEGES ON DATABASE grafana to grafana;

    connect to grafana db
    \c grafana

    create session table
    CREATE TABLE session (
    key CHAR(16) NOT NULL,
    data bytea,
    expiry INT NOT NULL,
    PRIMARY KEY (key)
    );

    Edited /var/lib/pgsql/data/pg_hba.conf to add the following lines:

    host all grafana 0.0.0.0/0 trust
    local all grafana trust

    In Ambari, under “Advanced ams-grafana-in” the content was changed to use postgres:

    #################################### Database ####################################
    [database]
    # Either "mysql", "postgres" or "sqlite3", it's your choice
    type = postgres
    host = YOURSERVER.EXAMPLE.COM:5432
    name = grafana
    user = grafana
    password = grafana

    # For "postgres" only, either "disable", "require" or "verify-full"
    ssl_mode = disable

    # For "sqlite3" only, path relative to data_path setting
    ;path = grafana.db

    Hope this helps someone!

    Translate
    agrawalreetika
    agrawalreetika
    Silver ⭐️⭐️
    May 2, 2018

    Getting below error if I am using "key" for column name.How do I resolve this?

    mysql> create table session('key' char(16) not null,'data' blob,'expiry' int(11) unsigned not null,primarykey (key)) ENGINE=MyISAM default charset=utf8; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''key' char(16) not null,'data' blob,'expiry' int(11) unsigned not null,primaryke' at line 1

    Translate
    agrawalreetika
    agrawalreetika
    Silver ⭐️⭐️
    May 3, 2018

    I am getting below error,if I am using "key" as column.

    mysql> create table session(key char(16) not null,data blob,expiry int(11) unsigned not null,primary key (key)) ENGINE=MyISAM default charset=utf8; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'char(16) not null,data blob,expiry int(11) unsigned not null,primary key (key)) ' at line 1

    By replacing "key" to "key1" it works fine.

    mysql> create table session(key1 char(16) not null,data blob,expiry int(11) unsigned not null,constraint pk_example primary key (key1)) ENGINE=MyISAM default charset=utf8; Query OK, 0 rows affected (0.00 sec)

    Translate
    Terms & ConditionsAccessibility statement