AWS glue connection to RDS Mysql

Question

I want to test a connection to RDS from glue. For this I have created the RDS Mysql instance with public access and password and IAM authentication.

I have created a role with permissions to RDS, Glue (and S3 just in case):

[AmazonRDSFullAccess AmazonS3FullAccess AWSGlueServiceRole AmazonRDSDataFullAccess]

I also have a S3 endpoint already created from another connection for redshift

the security group has the self-referencing for glue and the 3306 port opened for the external connections (local PC)

when I test the connection I get the following error:

Check that your connection definition references your JDBC database with correct URL syntax, username, and password. Could not create connection to database server.


Exiting with error code 30

I can connect from my local machine to the instance with:

mysql -h database-1.xxx.eu-west-1.rds.amazonaws.com -P 3306 -u admin -p

could you tell me what I'm missing for the connection from glue?

Answer 1

Make sure your connection string is in the proper format like the below one -

jdbc:mysql://***aws_connection_url***amazonaws.com:3306/database_name

Also make sure that your VPC name while creating the glue connection is the one that contains your data store(same as RDS instance) and the subnet within the VPC also match. I have connected to RDS through glue and in my security group I have opened all TCP ports and security group self referencing. You might like to try these steps.

Answer 2

I also had this issue and in my case it was due to RDS MySQL version 8.0. By default, RDS will create mysql db instance with version 8.0.20 (in my case). After spending a considerable time on why JDBC fails is glue, I concluded that MySQL 8.0 is not supported, or at least it needs to be setup in some special, not documented way.

The solution was to use MySQL 5.7 or MariaDB. With these db engines, there are no problems with AWS Glue and JDBC.

I think the reason why MySQL 8.0 does not work could be that it introduced changes to connection and password handling. This is also why MySQL 8.0 causes problems with wordpress.