Connect to ERPNext database remotely

[boyan_sharic]

Hello,

Does anyone know how to connect to ERPNext database from a remote client like Navicat?

I am using VirtualBox image downloaded from: Not Found

I commented out line:

bind-address = localhost

from:
/etc/mysql/my.cnf

and
/home/erpnext/frappe-bench/sites/erpnext.erpnext-vm/site_config.json
says

"db_password": "YeIsGsnvUvQrGC2r"

so I tried to view is there any existing remote users with:

erpnext@erpnext-vm:~$ mysql --user=erpnext --password=YeIsGsnvUvQrGC2r
MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';

and I got this:

ERROR 1142 (42000): SELECT command denied to user 'erpnext'@'localhost' for table 'user'

[pdvyas]

on v5,

bench mysql

Also, to access mysql from outside the VM, you will have to configure port forwarding

[boyan_sharic]

@pdvyas

bench mysql

Where?
Could you please explain?
Also, what about v4?

[pdvyas]

in frappe-bench dir,

for version 4, bench frappe --mysql

[boyan_sharic]

Thank you but it’s still the same:

erpnext@erpnext-vm:~$ cd /home/erpnext/frappe-bench
erpnext@erpnext-vm:~/frappe-bench$ bench frappe --mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1496
Server version: 5.5.40-MariaDB-1~wheezy-log mariadb.org binary distribution

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [erpnext]> SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';
ERROR 1142 (42000): SELECT command denied to user 'erpnext'@'localhost' for table 'user'
MariaDB [erpnext]>

[pdvyas]

this is to browse the erpnext database. If you want superuser stuff, mysql -uroot -perpnext

[boyan_sharic]

Finally!
So the complete procedure for anyone looking to connect to ERPNext database from outside is:

1. edit my.conf (/etc/mysql/my.cnf)

2. uncomment line 47 (#bind-address = localhost)

3. connect as root (password “erpnext”) via putty and execute:

   cd frappe-bench
   mysql -uroot -perpnext
   GRANT ALL PRIVILEGES ON . TO ‘root’@‘%’ IDENTIFIED BY ‘erpnext’ WITH GRANT OPTION;
   service mysql restart

That’s it! Now you can connect to ERPNext database from an external PC via Navicat or other db manager of your choice.

[ferohers]

I am on ubuntu and installed all the system under user. can do sudo tho.

Access denied for user root

[DMZVUL01]

hi I have installed ERP Next on VM now i need a remote pos client to connect with it via internet and sync all day invoices with the same. is this possible to do

[aiqual]

Hi boyan. I tried your command but it failed. Please let me know if I typed anything wrong. See attached screenshot. Thanks

[clarkej]

Your’e close aiqual - after you login, you need to specify the ‘mysql’ database. For example:

frappe@erpnext:~/frappe-bench$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 100
Server version: 10.0.29-MariaDB-1~trusty mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> show tables;
±--------------------------+
| Tables_in_mysql |
±--------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
±--------------------------+
30 rows in set (0.02 sec)

[aiqual]

I resolved the problem by installing Bitvise SSH client on Windows and tunneled into the VirtualBox VM port 3306. Then the MySQL Workbench could connect to this same port.

[Agrogers]

Took me a while to get MySQL Workbench connected to the database running in the ERPnext Virtual Box VM. I had to:

1. Add a port forwarding rule into VirtualBox for my virtual machine to point to 3306
2. Change the /etc/mysql/my.cn to allow connections from all hosts (bind-address = 0.0.0.0)
3. Update the user list in MySQL. Actually, i am not certain that i need to do this step. The syntax I used is shown below. The IP address is coming from the Virtual Machine. ‘frappe’ is the default password for the VM install of ERPnext.

[nikzz]

getting the same error still after running command use mysql

[quadtree]

Just found the perfect solution!!, that requires no config changes!, is a SSL tunnel to bind your remote MYSQL to a local port, so the mysql engine connects as local but the data is forwarded using a tunnel, here is the command in terminal (i’m using MacOS):

ssh -L 3306:127.0.0.1:3306 root@<your remoteIP> -N
Be sure to have ssh access for root…

The terminal will say nothing but the port is forwarded until you press , so now you can acces your remote mysql with erpnext as local to your machine… I used MySQL Workbench smoothly, and no config changes on the remote server… So this can be used when needed quick access without spending too much time moving things around (and maybe breaking something on the way)…

Hope this is useful … it can also be used to forward remote erpnext as local, usefun when testing api’s or whatever use you can imagine… is like an reverse Ngrock service…

I found this on youtube at: 8 super heroic Linux commands that you probably aren't using - YouTube
Also useful: ssh(1) - OpenBSD manual pages

Happy coding!!

[Laxman_Tandon]

@boyan_sharic

service mysql restart

Thanks its worked for me.