Remote Database Setup

Frappe Framework
1

Hi All,

I need some help with configuration and would appreciate if you guys could provide me some guidance or point me to the right direction.

I have been struggling these last few weeks to install ERPNext on one server and have the database on another server.

I have read through the forum in search of guidance and tried almost everything i found.

Adding the below to both the site_config.json and common site did not help also.

_“db_host”: “10.0.1.7”,
_“db_port”: “3306”,

After adding the above, i ran the below query to create the database.

bench new-site site1.local --db-name erpnext --mariadb-root-username root --mariadb-root-password Password.2 --install-app erpnext --verbose --force

With the above, i noticed that the database was created in the remote server but it stopped at this error.

Created user erpnext
Created database erpnext
Granted privileges to user erpnext and database erpnext
Starting database import…
Imported from database /home/frappe/frappe-bench/apps/frappe/frappe/data/Framework.sql
Database not installed, this can due to lack of permission, or that the database name exists.
Check your mysql root password, or use --force to reinstall

I tried the below sql to provide additional access right to the user but it did not work.

GRANT ALL PRIVILEGES ON erpnext.* TO ‘erpnext’@‘10.0.1.15’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;

The password in the query comes from the site_config.json file for the site.
10.0.1.15 is the server where i have installed ERPNext.

I would really appreciate if you could provide any help. I don’t know what else to try.

Many Thanks.

2 Likes
2

@Azrael

You’re correct in setting the site_config but it should be db_host instead of dbhost

3

Thank you for your comment.

I changed the db_host and db_port syntax but i still get the same error.

Any other idea?

4

@Azrael

Try and do this first.

  1. Try and connect remotely using something like Sequel Pro or whichever app you use to connect to a DB
  2. That connection needs to be direct one not using ssh, if you are able to login, than use those details in the site_config
  3. I am guessing you will not be able to connect as you will need to enable remote connection on MariaDB – check this guide should help https://mariadb.com/kb/en/mariadb/configuring-mariadb-for-remote-client-access/
5

Thank you for the additional info.

I installed HeidiSQL on my pc and tried to connect to the remote DB server using root and it connected.

I have noticed however that when i use the below command, it creates a user on MariaDB with the same name as the database and tries to connect to the DB using that username.

bench new-site site1.local --db-name ERPNext --mariadb-root-username root --mariadb-root-password Password.2 --install-app erpnext --verbose --force

The above command creates a ERPNext user in MariaDB and connect to the DB as follows:
ERPNext@server-ip (ERPNext@10.0.1.20)

Is the above command the right way to create a site with a DB on a remote server?

Many thanks for the guide. I tried everything in there also.

Thanks Again.

6

Steps to setup Remote Database

  1. Setup the remote DB to accept remote connections, In /etc/mysql/my.cnf or /etc/my.cnf comment out
    1.1 # bind-address=0.0.0.0
    1.2 # skip-networking (on later versions of mariadb this is not present)

  2. On the remote DB in mysql enter:
    Grant Privilegies to root as GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '[ROOT-SQL-PASSWORD]' WITH GRANT OPTION;

  3. Restart remote MySQL
    sudo service mysql restart

  4. Add remote IP or Address to the local instance
    bench set-mariadb-host [REMOTE-MySQL-IP/ADDRESS]

  5. So changes are applied do:
    bench restart

  6. Test the connection using bench mysql if everything is working fine you should be able to connect to the remote DB server.

  7. Create new site using:
    bench new-site site1.remote --install-app erpnext --verbose
    IMPORTANT Create the site only after you are able to connect to connect to the MySQL using bench mysql, otherwise the site will not be created. you should see the following:

frappe@ERPNext:~/frappe-bench$ bench new-site site1.remote --install-app erpnext --verbose MySQL root password: Created user a1947c1ffeceb24a Created database a1947c1ffeceb24a Granted privileges to user a1947c1ffeceb24a and database a1947c1ffeceb24a Starting database import... Imported from database /home/frappe/frappe-bench/apps/frappe/frappe/data/Framework.sql Installing frappe... Updating frappe : [========================================] Updating country info : [========================================] Set Administrator password: Re-enter Administrator password: Installing erpnext... Updating erpnext : [========================================] *** Scheduler is disabled ***

Reboot the server

15 Likes
7

Thank you very much for the guide.

It finally worked. I really appreciate all your effort on this.

Is it possible to use MariaDB on windows for the database instead of Ubuntu?

Many Thanks.

8

would you (in order to support the rest of the Community) mind to gather your working solution in a step-by-step howto and put that in this thread?

I’m sure others would be happy about that

9

@Azrael

From an experience perspective, I would strongly recommend staying with Ubuntu simply for the reason that you are likely to get more support because everyone is on Ubuntu.

Not saying its not possible, but my experience in installing MySQL on Windows was not exactly smooth sailing.

Also I think from a cost point of view its cheaper as well.

10

@vrms

Post six of this thread does exactly that. :slight_smile:

1 Like
11

Hello I have installled ERP NEXT ON VM now i need to install a pos
client so that it will create bill n sync with the installed VM at my
home system is this possible to do that boths ends are having normal
internet

12

Grant Privilegies to root as GRANT ALL PRIVILEGES ON . TO ‘1bd3e0294da19198’@‘localhost’ IDENTIFIED BY ‘root’ WITH GRANT OPTION;
when i give that line in mysql i get this err
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 'as GRANT ALL PRIVILEGES ON . TO ‘1bd3e0294da19198’@‘localhost’ IDENTIFIED BY ‘ro’ at line 1

13

IDENTIFIED BY ‘ro’

after IDENTIFIED BY, paste the password as mentioned in site_config.json of the correct site

14

my passwd is root i mentioned in that IDENTIFIED BY .

15

is it that user ‘1bd3e0294da19198’ 's password because you are trying to do the operation for that user

16

yeah its passwd

17

I keep getting this error :
pymysql.err.OperationalError: (2003, “Can’t connect to MySQL server on ‘localhost’ ([Errno 111] Connection refused)”)

how can i solve this?

18

Firstly, try and access mariadb console using bench like so

bench mariadb

if you get the following back, then the connection settings are correct,

**Welcome to the MariaDB monitor. Commands end with ; or \g.**

**Your MariaDB connection id is 2054**

**Server version: 10.4.6-MariaDB Homebrew**

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

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

MariaDB [_dd687aa1c92fa6dd]>

If you don’t, then you need to make sure your mariadb is connected before you do anything.

But judging by the error message, you’re still connecting to the local mariadb and not the remote one.

See the steps above to make sure you connect to the db.

19

Welcome to ERPNext coder1

To add to saidsl’s advice pointer and to learn more, a web search on this

will get you curated tips like this

20

Hello, getting this error while creating new site:

MySQL root password:
sh: 1: mysql: not found
Database not installed, this can due to lack of permission, or that the database name exists.
Check your mysql root password, or use --force to reinstall