Search code examples
mysqlsql-updatesql-insertgrafana

Update\Insert data from grafana to mysql

Is is possible to update data or insert data from grafana to mysql. I have a requirement to insert/update information in mysql using a UI. Now I am already using grafana so wanted to know if there is any way we can use grafana to update or insert information

Solution

  • There is no input panel for taking user input and inserting that data to MySQL or any other. (Grafana v7.0) For very minimalist input data you can use grafana variables as a hack.

    Create example table for storing input in MySQL

    CREATE TABLE `grafana_variable` (`variable` VARCHAR(64)) ENGINE = InnoDb;

    In Grafana dashboard click settings icon:

    enter image description here

    then click:

    enter image description here

    add variable:

    enter image description here

    return to dashboard and you should see at the top:

    enter image description here

    create a new visualization panel or add another query in any already existing panel and add SQL query:

    INSERT INTO `grafana_variable` VALUES ('${myvar}');

    now every time you type into that field

    enter image description here

    and click away from it or use ie tab key the data will be inserted to the MySQL database.

    mysql> select * from grafana_variable;
+---------------------+
| variable            |
+---------------------+
| this is just a test |
+---------------------+
1 row in set (0.08 sec)

    The downside of this hack is that the visualization tile will have in its upper left corner error icon:

    enter image description here

    with information (when you hover over it):

    Found no column named time or time_sec

    because the sql query of the visualization tile is inserting data instead of selecting it from database therefore there is no time or time_sec data. Grafana also does not handle multiple MySQL statements so you cannot repair that by adding before or after the INSERT INTO ... statement second one with SELECT FROM. Maybe this could be somehow patched by using sub queries or something similar but I did not investigate that further.

    Because the error as above can be due to multiple reasons not related to the hack it will be better to use the hack query in separate visualization tile - the one that you will remember (or even give descriptive panel tile) so you know that the error there is nothing unusual.

    See grafana varialbes for more info about variables. There is also useful list of already existing variables you can use same way as myvar I created. See section MACROS here For eg:

    $__timeFrom()

    and

    $__timeTo()

    have the start and the end of displayed time range.

    Permissions and Security
    The whole hack works because the MySQL user that grafana uses is allowed to execute INSERT statement, but if grafana is able to execute ANY statement then make sure that MySQL user that grafana uses is not allowed for example to execute statements like DROP ... or any other that is not related to INSERT data into the grafana_variable table as in example.

    If you use MySQL as datasource for displaying data the grafana user should also be able to execute SELECT statements.

    But nothing else than that.

    Read about MySQL user premissions