How To Remotely Host ERPNext Database

Good software design will emphasize the importance of separating the database from the software application. The server where the database is hosted needs an extra layer of protection as it contains all the data. This is sometimes known as remote database setup. This can also be achieved in ERPNext and the Frappe Framework.

In this article I will demonstrate to you how this can be achieved with simple steps.

Setup the remote DB to accept remote connections

After you have installed your database in the remote server (You may use the script provided on my GitHub repository), you will need to set it up to allow remote connections by doing the following:

Edit MariaDB configuration file to accept remote connections by changing its bind-address from 127.0.0.1 to 0.0.0.0

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Login to your MySQL and create a database user that Frappe will use to authenticate.

CREATE USER 'root'@'[IP ADDRESS FOR ERPNext HOST]' IDENTIFIED BY '[YOUR PASSWORD OF CHOICE]';

Grant the created root user permissions to make changes to the created databases.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'[IP ADDRESS FOR ERPNext HOST]' IDENTIFIED BY 'YOUR PASSWORD OF CHOICE' WITH GRANT OPTION;

Flush the privileges to effect changes to the database and log out from MySQL.

FLUSH PRIVILEGES;

EXIT;

Restart MySQL server to effect all the made changes.

sudo service mysql restart

Test the connection using the below command:

mysql -u root -h [IP ADDRESS FOR DATABASE HOST] -p

If the request doesn’t go through, you may need to ensure that there is no firewall that blocks connections to TCP port 3306 of the MySQL server. We use the below command to allow an IP address on Ubuntu servers:

ufw allow from [IP ADDRESS FOR ERPNext HOST] to any port 3306

Setup your ERPNext or Frappe Framework Installation to connect to your remote Database

bench set-mariadb-host [IP ADDRESS FOR DATABASE HOST]

Restart supervisor if your instance is in production mode

supervisorctl restart all

If your instance is in development mode, restart bench

bench restart

At this point you may decide to test the connection or dive in and create a new site. The database that Frappe will create will be in the server you just set up 🙂

bench new-site erp.upeosoft.com --mariadb-root-password [YOUR PASSWORD OF CHOICE]