Connect ERPNext to Google Data Studio

Has anyone managed to successfully connect ERPNext with google data studio for data visualization? If anyone has any information on how to do this please share. Thanks

I tried to create datasource, It had option to connect mysql.

I didn’t try anything further, my mariadb is not accessible over internet.

1 Like

I am having the exact same problem as well. I was hoping there is a workaround

Hi
Have you created a database user with access from any IP?
Also, you have to change bind_address = 0.0.0.0 in my.cnf

Thanks

1 Like

Thanks, @hiimkhaled I have already tried that but with no success. Have you had any success with this? Since we also have an instance hosted on ERPNext is there a way we can connect that instance as well without access to the backend

Server port 3306 must allow connection.
Check if any firewall blocks port.

1 Like

Yes I have connected several mariadb databases with Google Data Studio and Google Apps Scripts using JDBC.

Btw, Have you opened the port 3306?

1 Like

Thanks everyone for the help, i have finally managed to connect MySQL to Google data studio i will be writing a detailed explanation on how to achieve this to help anyone who may be having issues then I will post it here

These steps are made thanks to Upande LTD Reach out to us if you need further help.

  1. Ssh to the server where the server is located e.g

     $ ssh <username>@<host ip>
    
  2. Set the MySQL server to listen on all IP addresses on the machine. To do this you will need to edit the mysql configurations file which will be different depending on mysql version.In my case i am using mariadb which i simply open the file using the following command:

    $ sudo nano /etc/mysql/my.cnf
    
    For more information on this refer to the to the link below: https://linuxize.com/post/mysql- 
    remote-access/
    
  3. Search for a line that begins with bind-address and set it to listen to 0.0.0.0

    Ie. change

          bind-address = 127.0.0.1 to bind-address = 0.0.0.0
    
  4. Look for the line skip-networking and delete it or comment it out i.e

    # skip-networking
    
  5. Restart the MySQL service depending on your system.If using ubuntu use the following command:

    $ sudo service mysql restart
    
     Next you need to create a specific mysql/mariadb user that will be allowed to access the 
    database remotely
    
  6. Login to the mysql/mariadb shell using the following command:

    $ mysql -u root -p
    
  7. Create a mysql user that you are going to use for remote access:

    a.  Create user
    
    MariaDB [(none)]>CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
    
    b. Grant all privileges
    
    MariaDB [(none)]> GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
    
    Please note that the two asterisks represent the databases and tables that are all databases and 
    

    all tables. You can specify database or tables if you like that.eg.

    MariaDB [(none)]> GRANT ALL PRIVILEGES ON database1. * TO 'newuser'@'localhost';
    

    (c) Reload Privileges

     MariaDB [(none)]> FLUSH PRIVILEGES;
    
  8. Allow the user to connect to the database from a specific IP address:

    MariaDB [(none)]> GRANT ALL ON database_name.* TO user_name@'ip_address' IDENTIFIED BY 'user_password';
    

    i. database_name is the name of the database that the user will connect to.
    ii. user_name is the name of the MySQL user.
    iii. IP_address is the IP address from which the user will connect. Use % to allow the user to connect
    from any IP address. In this case, however use the IP addresses specified by Google data studio
    (check 8 below)
    iv. user_password is the user password.

  9. Google Data studio gives a list of IP addresses that the user should be allowed to access from as given below. You can however also get the list from this link: Connect to MySQL - Looker Studio Help

    64.18.0.0/20

    64.233.160.0/19

    66.102.0.0/20

    66.249.80.0/20

    72.14.192.0/18

    74.125.0.0/16

    108.177.8.0/21

    173.194.0.0/16

    207.126.144.0/20

    209.85.128.0/17

    216.58.192.0/19

    216.239.32.0/19

As you may have noticed the IP address is in terms of ranges the best way to give access to these
ranges therefore is to make use of the wild card which gives access to all the ip within that range for
example if the ip address is 64.18.0.0/20 you can use this as the ip address with wildcards 64.18.%.%

Using the above example therefore in order to grant the first address access use the following
command

GRANT ALL ON <database_name>.* TO user_name@'64.18.%.%' IDENTIFIED BY 'user_password';
  1. Now head back to google data studio login enter the details: hostname/ip,database, username and password and you are good to go:

3 Likes