Deploying instance with MySQL on Amazon RDS?

Hi,

I was wondering if there a way to deploy an instance of ERPNext using Amazon RDS (MySQL). If so, is there any guide or wiki page with steps or something like that?

I hope this is the place to post this kind of questions, please educate me if I’m wrong.

Thanks in advance :smile:

I’d be interested too! I tried deploying with Google CloudSQL https://cloud.google.com/sql/ with no luck. I can’t find if if Google or Amazon use straight MySQL or the MariaDB fork which is what ERPNext uses. Hopefully some clarification will come our way!

The only incompatiblity with MySQL is the microsecond precision (which should be available in MySQL 5.6). You can try it if Amazon RDS has MySQL 5.6

To try, you will have to run

bench set-mariadb-host {hostname/ip}

and then create a new site again.

Thanks @pdvyas! Google is currently using 5.5 stable which has their SLA and all that, but are already allowing 5.6 on beta which doesn’t have any of their SLA yet. I will look into deploying a demo site with 5.6 sometime as it can take some fine tuning.

For anyone who reads this, I had a year of timeouts and issues using their D0 tier which is the cheapest they offer. After upgrading to D1 at persistent use my issues have gone away but my bill has doubled. If you use D0 with issues, I would suggest first changing the settings from on demand to always on, and if that doesn’t help then up the tier in the settings. Wish I had this info 1+ year ago for another app :smile:

2 Likes

@DrTrills Any luck installing it in google cloud sql ?

Hey @ccfiel no luck unfortauntely. The current stable version and the V2 are just MySQL which doesn’t work perfectly with MariaDB.

Hopefully Google will eventually adopt different DB’s. One can wish

@DrTrills I see. I did successfully deploy ERPNext in Amazon EC2 and Amazon RDS. I hope this is not to expensive in my pocket :smile: How about you where did you deploy your ERPNext?

1 Like

@ccfiel Ah good to know! What instance size did you use our of curiosity? Also, performance seems good?

And I originally had version 4 on Linode, but now I have v6 on Google Cloud Compute Engine which seems to be doing the trick

@DrTrills Nice for know. For me its just development phase. A micro for EC2 and micro for RDS and only me is using it :slight_smile: the cost now is average of 1 dollar a day. My apprehension if user will start using it. What I like in amazon is there monitoring app and scaling. You can scale down or up dynamically in terms in cpu, memory and even disk space. What I do not like in digitalocean and linode is the space. You have to upgrade everything even you just need addition spaces. What I like in them is a fix pricing and cheap. How about you? What is your experiences in GCCE in terms in cost and features.

@ccfiel Not bad, 30 bucks a month for all that power/run your company is a deal indeed.

Anyways I have a few apps on Google so I can give a breakdown to show where the most cost comes from.

ERPNext v6 is running on the google compute g1-small instance. at the moment, this month costs me $11.04. Everything is local host on this as I used bench to install. This is almost used on the backend primarily for keep track of simple needs, still in the process of moving more business functions to ERPNext (users can be resistant at times)

I run on another instance a JAVA timesheet application (ehour.nl) which also is running on a g1-small instance. but that is more expensive. The VM cost for last month was: $19.41 as it ran for 721 hours, and is accessed from US, Europe and India. (google charged 1 cent for data trasnfer)
The Database for eHour is hosted on CloudSQL D1 which was $43.80 for the month. As you can see the DB is the expensive part on Google.

Overall I am happy, when I joined google cloud 2 years ago it wasnt great, slow response times, and would time out a lot. I almost went back to Linode and said screw it on the cloud as it was so bad…Now it’s rock solid and I am incredibly happy with it overall. I think the security is also great as you can only access the VM with a google account using gutil. So in order to access, i must be logged in (2 factor auth), already added to the server through admin, and then it transfers keys to the server etc creating a lot more security out of the box. The sustained use is also nice, usually get 5 bucks off at the end of the month since they discount usage.

That said, google the DB should be cheaper in a few months once they deploy V2 of their service out of beta - Google Cloud Platform Blog: The next generation of managed MySQL offerings on Cloud SQL.

Any more questions let me know!

I did successfully deploy ERPNext in Amazon EC2 and Amazon RDS.

I am also trying the same, can you help with how did you do it actually and what are the steps you followed, have been trying this from almost 3 days now. Any help would be really helpful at this stage for me.

Thanks

+1
time for an official guide?
• docker setup for easier management (test vs live install)
• considering amazon’s official AMI with nightly reboots to automate OS-security patches

1 Like

Related,

To be able to take a system up and down (under AWS) I’m planning to use two disk mounts, one small for the root-system, and one for the data (i.e. the frappe home directory) that’s a separate EBS volume.

This way the actual AMI can be changed around without having to care about the actual frappe instance data.

Question: is there a good doc/overview somewhere that lists where files are stored, which ones are important. i.e. I don’t want to risk some important logs from the background-worker, redis or such to be lost in case the root-volume is created from scratch.

Hey all,

Any more information on how to connect to an external RDS hosted MariaDB database? I have used the bench maria-db-host hostname/ip command. I’ve also altered my test site’s site.conf file with the following:

{ "db_name": "DB_NAME", "db_user": "RDS_USER", "db_password": "RDS_PASSWORD", "db_host": "RDS_HOST", "db_port": "RDS_PORT" }

I get the following response when trying to run bench mysql:

ERROR 1045 (28000): Access denied for user ‘DB_NAME’@my.ip.address.at.some.location’ (using password: YES)

I’ve been struggling with this for the past few days. One potential work around is to add this user ‘‘DB_NAME’@my.ip.address.at.some.location’ to my RDS account but would prefer not to. I can easily access my RDS instances using the mysql command:

mysql -h myinstance.123456789012.us-east-1.rds.amazonaws.com -P 3306 -u mymasteruser -ppassword

No space for passowrd proceeding the -p

Cost can be reduced significantly like 40%, if you opt for a reserved instance. I bought a reserved T2 Micro for 136$ for 3 years. So it would cost me only $3.7 per month, at least just for the server. Hope this helps.

1 Like

Try using the same name for database and user, like in frappe.

Steps followed: (under the guidance of @revant_one)

  1. Log on to EC2.
$ ssh -i key_file.pem user@instance_name
  1. Log on to RDS via EC2.
$ mysql -h abc.rds.amazonaws.com -u username -P 3306 -p
  1. Create a new database.
mysql [(none)]> create database frappedb;
  1. Add a user. (Username must be identical to database name)
mysql [(none)]> create user frappedb; 
  1. Set privileges for user.
mysql [(none)]> GRANT ALL ON frappedb.* TO 'frappedb'@'%';
mysql [(none)]> FLUSH PRIVILEGES;
  1. Exit mysql prompt and restore RDS database to frappedb.
mysql -h abc.rds.amazonaws.com -u username -P 3306 -p frappedb < frappe_backup.sql
  1. Update mariadb host.
$ bench set-mariadb-host abc.rds.amazonaws.com
  1. Bench update to confirm setup. Should run smoothly.
$ bench update  
8 Likes

Hello, I am getting error like below while performing the 6th step:-1:

ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 767 bytes.

Can anyone help me out with this? Thanks.

@gvrvnk I got the following error when I connect RDS with the bench update command command

Traceback (most recent call last):
File “/usr/local/lib/python3.7/runpy.py”, line 193, in _run_module_as_main
main”, mod_spec)
File “/usr/local/lib/python3.7/runpy.py”, line 85, in _run_code
exec(code, run_globals)
File “/home/rivererp/erpnext/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 99, in
main()
File “/home/rivererp/erpnext/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 18, in main
click.Group(commands=commands)(prog_name=‘bench’)
File “/home/rivererp/erpnext/frappe-bench/env/lib/python3.7/site-packages/click/core.py”, line 764, in call
return self.main(*args, **kwargs)
File “/home/rivererp/erpnext/frappe-bench/env/lib/python3.7/site-packages/click/core.py”, line 717, in main
rv = self.invoke(ctx)
File “/home/rivererp/erpnext/frappe-bench/env/lib/python3.7/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/rivererp/erpnext/frappe-bench/env/lib/python3.7/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/rivererp/erpnext/frappe-bench/env/lib/python3.7/site-packages/click/core.py”, line 956, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/home/rivererp/erpnext/frappe-bench/env/lib/python3.7/site-packages/click/core.py”, line 555, in invoke
return callback(*args, **kwargs)
File “/home/rivererp/erpnext/frappe-bench/env/lib/python3.7/site-packages/click/decorators.py”, line 17, in new_func
return f(get_current_context(), *args, **kwargs)
File “/home/rivererp/erpnext/frappe-bench/apps/frappe/frappe/commands/init.py”, line 26, in _func
ret = f(frappe._dict(ctx.obj), *args, **kwargs)
File “/home/rivererp/erpnext/frappe-bench/apps/frappe/frappe/commands/site.py”, line 287, in migrate
migrate(context.verbose, rebuild_website=rebuild_website, skip_failing=skip_failing)
File “/home/rivererp/erpnext/frappe-bench/apps/frappe/frappe/migrate.py”, line 43, in migrate
for app in frappe.get_installed_apps():
File “/home/rivererp/erpnext/frappe-bench/apps/frappe/frappe/init.py”, line 906, in get_installed_apps
installed = json.loads(db.get_global(“installed_apps”) or “[]”)
File “/home/rivererp/erpnext/frappe-bench/apps/frappe/frappe/database/database.py”, line 702, in get_global
return self.get_default(key, user)
File “/home/rivererp/erpnext/frappe-bench/apps/frappe/frappe/database/database.py”, line 706, in get_default
d = self.get_defaults(key, parent)
File “/home/rivererp/erpnext/frappe-bench/apps/frappe/frappe/database/database.py”, line 724, in get_defaults
defaults = frappe.defaults.get_defaults(parent)
File “/home/rivererp/erpnext/frappe-bench/apps/frappe/frappe/defaults.py”, line 77, in get_defaults
globald = get_defaults_for()
File “/home/rivererp/erpnext/frappe-bench/apps/frappe/frappe/defaults.py”, line 204, in get_defaults_for
where parent = %s order by creation"“”, (parent,), as_dict=1)
File “/home/rivererp/erpnext/frappe-bench/apps/frappe/frappe/database/database.py”, line 122, in sql
self.connect()
File “/home/rivererp/erpnext/frappe-bench/apps/frappe/frappe/database/database.py”, line 75, in connect
self._conn = self.get_connection()
File “/home/rivererp/erpnext/frappe-bench/apps/frappe/frappe/database/mariadb/database.py”, line 90, in get_connection
local_infile = frappe.conf.local_infile)
File “/home/rivererp/erpnext/frappe-bench/env/lib/python3.7/site-packages/pymysql/init.py”, line 94, in Connect
return Connection(*args, **kwargs)
File “/home/rivererp/erpnext/frappe-bench/env/lib/python3.7/site-packages/pymysql/connections.py”, line 325, in init
self.connect()
File “/home/rivererp/erpnext/frappe-bench/env/lib/python3.7/site-packages/pymysql/connections.py”, line 599, in connect
self._request_authentication()
File “/home/rivererp/erpnext/frappe-bench/env/lib/python3.7/site-packages/pymysql/connections.py”, line 861, in _request_authentication
auth_packet = self._read_packet()
File “/home/rivererp/erpnext/frappe-bench/env/lib/python3.7/site-packages/pymysql/connections.py”, line 684, in _read_packet
packet.check_error()
File “/home/rivererp/erpnext/frappe-bench/env/lib/python3.7/site-packages/pymysql/protocol.py”, line 220, in check_error
err.raise_mysql_exception(self._data)
File “/home/rivererp/erpnext/frappe-bench/env/lib/python3.7/site-packages/pymysql/err.py”, line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, “Access denied for user ‘_2e68096a1650b265’@‘RDS’ (using password: YES)”) help me how to over come the issue

try connecting to mysql from bash from the same server where bench is installed.
as can be seen in last line, mysql authentication is failing.
btw user name looks weird… possibly it is db name.

even when login/passwords are correct, it could be because of connectivity issue (say, port and IP not allowed for that user, or mysql setup corrupt).