Connecting MariaDB on MySQL Workbench

I installed ERPNext on Amazon Web Service EC2, how can I connect the MariaDB to my local database workbench such as MySQL Workbench?

Please explain use case.

By default MariaDB only allows localhost connection,To allow remote access to MariaDB…

sudo nano /etc/mysql/my.cnf

comment out bind-address

sudo service mysql restart

login mariaDB as root user

~$ mysql -uroot -pYourMariaDBrootPassword

grant privileges

MariaDB [(none)]>GRANT ALL PRIVILEGES ON . TO ‘root’@‘%’ IDENTIFIED BY ‘YourMariaDBrootPassword’ WITH GRANT OPTION;

in mysqlbench login as root

I have tried to use MySQL Workbench with local MariaDB server.

But MySQL Workbench is incompatible with MariaDB. This is a big problem, when wanting to work with a good tool such as the Workbench.

I think during the time after the fork MySQL and MariaDB drifted away regarding interfacing.

As of MariaDB 10.1 and the most recent MySQL workbench, there are no major issues that I’ve found. There is an error message saying that it is not supported, but it works.

Hi! Thanks for your reply. I’ve installed ERPNext on Amazon Web Service EC2, however, I can’t access the database of ERPNext, do you know how to access the database from the Ubuntu instance of AWS EC2. I’m new to ERPNext and I’m having a hard time configuring it.

I installed ERP Next on Amazon Web Service EC2 by using the Ubuntu Instance. It’s currently working but we want to modify doctypes by accessing the codes and also the database structure. Hope you will give me step by step tutorial on it.

hello @Bernard_Gresola im sharing my steps for connecting MYSQL workbench to erpnext database hosted in digital ocean. what you need. Root user and password for ssh, and root user and password for MariadB usually located at root / .

Open new connection at workbench and select standar tcp / ip over ssh use the user and password for remote ssh and configure user and password for maria db under my sql hostname use 127.0.0.1 and server port 3306. it should work out of the box without changing the my.cnf file. hope it works for you as it did for me.

Hi ! Thank you very much for your reply. However, do you know how to do this on Amazon Web Service EC2? I’m having a hard time configuring it. I have the mariadb password but I can’t access using the DNS of my instance.

Please shared your steps and where you are stuck, error code…etc

Hi folks,

I have a strange senario.

I can connect no problem to the database server with mysql workbench, but when I try to run queries on a site I created to the default site that the install created I get errors.

So I can query from any table on the site1.local site database but I cannot query any table from my own site on the same server.

Any ideas how to troubleshoot why?

regards
Hemant

I am getting an error as below on giving the correct pwd, please advise:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘TO ‘root’@’%’ IDEN
TIFIED BY ‘YourMariaDBrootPassword’ WITH GRANT OPTION’ at line 1

1 Like