{"id":209,"date":"2024-05-22T01:13:07","date_gmt":"2024-05-22T01:13:07","guid":{"rendered":"https:\/\/www.rajeshkumar.xyz\/blog\/?p=209"},"modified":"2024-05-22T04:12:13","modified_gmt":"2024-05-22T04:12:13","slug":"how-to-connect-grafana-to-a-remote-mysql-database","status":"publish","type":"post","link":"https:\/\/www.rajeshkumar.xyz\/blog\/how-to-connect-grafana-to-a-remote-mysql-database\/","title":{"rendered":"How to Connect Grafana to a Remote MySQL Database"},"content":{"rendered":"\n

Grafana is one of the most widely-used interactive data visualization tools on the market. It\u2019s open-source, powerful, highly configurable and free to use. I\u2019ve walked you through the process of installing Grafana on Ubuntu Server 20.04<\/a>, and this time around, I\u2019ll help you connect that newly installed platform to a MySQL database, so you can visualize that data.<\/p>\n\n\n\n

Grafana allows you to connect to numerous data sources, such as Google Sheets, Amazon Timestream, Elasticsearch and many databases. Because MySQL is such a popular database, I thought it would be a great data source to use as an illustration. This will also refresh your memory on how to set MySQL up for remote connections.<\/p>\n\n\n\n

What you\u2019ll need<\/h2>\n\n\n\n

To make this work, you\u2019re going to need a running instance of Grafana, a running instance of MySQL and a user with sudo privileges.<\/p>\n\n\n\n

For this tutorial, I am going to assume your instance of MySQL is on a remote Linux server.<\/p>\n\n\n\n

How to configure MySQL for remote connection<\/h2>\n\n\n\n

The first thing we must do is configure the MySQL server to allow remote connections. On top of that, we\u2019re going to create a specific user that has specific permissions for the database we\u2019ll view on Grafana.<\/p>\n\n\n\n

Configure Mariadb for remote access<\/h3>\n\n\n
$ systemctl status mysql\n$ systemctl stop mysql\n$ systemctl status mysql\n$ more my.cnf\n$ cd conf.d\/\n$ vi mysql.cnf\ncd ..\n$ grep -R bind-address .\n$ grep -R 50-server.cnf .\n$ vi .\/mariadb.conf.d\/50-server.cnf\n$ systemctl start mysql\n$ systemctl status mysql<\/code><\/span><\/pre>\n\n\n
Configure MySQL for remote access<\/h3>\n\n\n\n
Going through the process below will help ensure MySQL is indeed configured correctly. In fact, if the MySQL server is not running on the same server as Grafana, it is critical that you follow the steps below first, as a stock MySQL installation usually is not configured to allow for remote connections.<\/p>\n\n\n\n
    \n
  1. First, log into your MySQL server and open the MySQL configuration file with:<\/li>\n<\/ol>\n\n\n\n
    sudo nano \/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/pre>\n\n\n\n
      \n
    1. In that file, look for the line:<\/li>\n<\/ol>\n\n\n\n
      bind-address = 127.0.0.1<\/pre>\n\n\n\n
        \n
      1. Change that to:<\/li>\n<\/ol>\n\n\n\n
        bind-address = 0.0.0.0<\/pre>\n\n\n\n
          \n
        1. Save and close the file, then restart the MySQL service with:<\/li>\n<\/ol>\n\n\n\n
          sudo systemctl restart mysql<\/pre>\n\n\n\n
          Create a new user with specific permissions<\/h3>\n\n\n\n
          Now that we\u2019ve set up our MySQL server to allow remote connections, we need to create a new user and give it the correct permissions. To do this:<\/p>\n\n\n\n
            \n
          1. Log in to the MySQL console with:<\/li>\n<\/ol>\n\n\n\n
            sudo mysql -u root -p<\/pre>\n\n\n\n
              \n
            1. Make sure you know which database you\u2019re going to be using. You can list them out with:<\/li>\n<\/ol>\n\n\n\n
              show databases;<\/pre>\n\n\n\n
                \n
              1. Create the new user with:<\/li>\n<\/ol>\n\n\n\n
                CREATE USER 'grafanareader' IDENTIFIED BY 'PWORD';<\/pre>\n\n\n\n
                Where PWORD is a strong\/unique password for the new user.<\/p>\n\n\n\n
                  \n
                1. Now, we can grant that new user the SELECT permission for the database with:<\/li>\n<\/ol>\n\n\n\n
                  GRANT SELECT ON DB.* TO 'grafanareader';<\/pre>\n\n\n\n
                  Where DB is the name of the database to be read by Grafana.<\/p>\n\n\n\n
                    \n
                  1. Flush the privileges and exit from the MySQL console with:<\/li>\n<\/ol>\n\n\n\n
                    flush privileges;\nexit<\/pre>\n\n\n\n
                    Your database server is now ready.<\/p>\n\n\n\n
                    How to connect Grafana to MySQL<\/h2>\n\n\n\n
                    Log into your Grafana instance, and click the hamburger (3 horizontal lines) icon in the left sidebar. From the resulting pop-up, click Add new connection (Figure A<\/strong>).<\/p>\n\n\n\n
                    Figure A<\/strong><\/p>\n\n\n\n
                    \"Screencapture
                    Add a new connection.<\/figcaption><\/figure>\n\n\n\n
                    In the next window, type mysql in the search bar, and hit Enter, or scroll down, and click MySQL from the listing (Figure B<\/strong>).<\/p>\n\n\n\n
                    Figure B<\/strong><\/p>\n\n\n\n
                    \"The
                    The MySQL Grafana configuration selection page.<\/figcaption><\/figure>\n\n\n\n
                    On the next page, click on the blue Add new data source button in the upper right (Figure C<\/strong>).<\/p>\n\n\n\n
                    Figure C<\/strong><\/p>\n\n\n\n
                    \"Prelude
                    Prelude to adding a new MySQL connection.<\/figcaption><\/figure>\n\n\n\n
                    You will then be presented with the necessary configuration options for a MySQL data connection. Here\u2019s what you must fill out:<\/p>\n\n\n\n
                      \n
                    • Host:<\/strong> The IP address or domain name of the MySQL hosting server, along with the port used for the database server. Note that if you are setting up a connection to localhost:3306, which is the default value specified, you must click into the field and type it in, or else the connection will not work.<\/li>\n\n\n\n
                    • Database:<\/strong> The database to be used as a source.<\/li>\n\n\n\n
                    • User:<\/strong> grafanareader.<\/li>\n\n\n\n
                    • Password:<\/strong> PWORD used to create grafanareader in the MySQL console.<\/li>\n<\/ul>\n\n\n\n
                      You might also have to enable Skip TLS Verify. And you may want to consider specifying a name for this connection, especially if you need to have multiple MySQL connections from Grafana.<\/p>\n\n\n\n
                      Configure those options, and click Save & Test. You should eventually see Database Connection OK (Figure D<\/strong>).<\/p>\n\n\n\n
                      Figure D<\/strong><\/p>\n\n\n\n
                      \"The
                      The completed connection setup.<\/figcaption><\/figure>\n\n\n\n
                      Congratulations! You now have Grafana connected to a remote MySQL server. In our next piece in the series, we\u2019ll create a new dashboard to view some of the data from the source.<\/p>\n\n\n\n
                      Now that you have successfully connected to your MySQL data source<\/p>\n","protected":false},"excerpt":{"rendered":"
                      Grafana is one of the most widely-used interactive data visualization tools on the market. It\u2019s open-source, powerful, highly configurable and… <\/p>\n","protected":false},"author":1,"featured_media":4163814,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"series":[],"class_list":["post-209","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/posts\/209","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/comments?post=209"}],"version-history":[{"count":3,"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/posts\/209\/revisions"}],"predecessor-version":[{"id":215,"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/posts\/209\/revisions\/215"}],"wp:attachment":[{"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/media?parent=209"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/categories?post=209"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/tags?post=209"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/series?post=209"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}