Connect to ERPNext database remotely

ERPNext
1

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'
2

on v5,

bench mysql

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

3

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

4

in frappe-bench dir,

for version 4, bench frappe --mysql

5

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]>
6

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

7

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.

3 Likes
8

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

Access denied for user root

9

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

10

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

image.png959×160 17.1 KB

11

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)

12

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.

13

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.

image
image723×47 1.18 KB

2 Likes
14

getting the same error still after running command use mysql

15

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
 :slight_smile:

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!! :sun_with_face:

1 Like
16

Thanks its worked for me.

17

I cannot access my erpnext database outside of 192.168.1.201 linux server.
I’m usign mobaxterm to access the server. I can connect db using mobaxterm console. but from my local pc I can’t connect the db.
how to solve the problem ?
please suggest me.

18

sudo nano /etc/mysql/my.cnf
change the bind-address
#bind-address = 0.0.0.0
bind-address = 192.168.1.166

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
change the bind-address
-#bind-address = 127.0.0.1
bind-address = your ip address

GRANT ALL PRIVILEGES ON . TO ‘root’@‘expected ip’ IDENTIFIED BY ‘password’;

After all restart your server.

If any query or facing any problem knock me.
thank you.

1 Like
19

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
change the bind-address
-#bind-address = 127.0.0.1
bind-address = your ip address

No need to add your ip address. only comment #bind-address = 127.0.0.1 this line. that’s enough.
Problem solved.
Thank you

1 Like