Frappe Installation connecting to Remote mariadb host

Steps to setup Remote Database

1. First of all, create ssh keys in the frappe app server's .ssh folder (Use ssh-keygen command with or without passphrase ) and add the public key into MariaDB (Database) installed server instance's .ssh folder's authorized_keys file.

Then following steps into frappe server instance -

• Recognise the ssh agent evalssh-agent -s
• Add the private key to ssh agent ssh-add <private_key_file_name> We are good to go next...

2. Setup the remote DB to accept remote connections, In /etc/mysql/my.cnf or /etc/my.cnfcomment out

   • bind-address=0.0.0.0
   • skip-networking (on later versions of mariadb this is not present) and add following -

         [mysqld]
         character-set-client-handshake = FALSE
         character-set-server = utf8mb4
         collation-server = utf8mb4_unicode_ci
         [mysql]
         default-character-set = utf8mb4
     

3. 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;

4. Restart remote MySQLsudo service mysql restart

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

Inside the /sites/site_config.json add two things - Remote Database Host Settings

• db_host:
• db_port: 3306 (default)

6. So changes are applied do:bench restart

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

8. 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:

output -

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 ***

Congratulations 👏 we are all Good! 🙂

Refs -

• https://discuss.erpnext.com/t/remote-database-setup/22687/6
• https://discuss.erpnext.com/t/remote-database-setup/22687
• https://frappeframework.com/docs/user/en/basics/site_config#remote-database-host-settings