Connecting to MySQL DB on AWS EC2 with JDBC for Java

I looked all over the web and couldn’t find the solution to this hell of a task, so this is my gift to anybody who is on the search, too. These instructions are primarily for Mac or Unix users, but it should be adaptable to other OS’s. Happy coding!

*Important Note: You may not be able to connect to mysql through the shell after doing this. If all goes well, as it should since this is pretty simple, you will be able to connect to your database through JDBC but not through shell.

Step 1: Download JDBC Jar

Step 1.a

Visit this link to the MySQL Dev downloads page to download the JDBC connector zip. When downloaded, unzip it and navigate the folders to find the .jar file. It should be in the root of the unzipped folder.

Step 1.b

Create a new Java project or open your current project. Add this jar as a library to that project.

Step 2

Add this code to your project wherever you want to make the connection:

public static void connectJDBCToAWSEC2() {

    System.out.println("----MySQL JDBC Connection Testing -------");
    
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        System.out.println("Where is your MySQL JDBC Driver?");
        e.printStackTrace();
        return;
    }

    System.out.println("MySQL JDBC Driver Registered!");
    Connection connection = null;

    try {
        connection = DriverManager.
                getConnection("jdbc:mysql://" + PUBLIC_DNS + ":" + PORT + "/" + DATABASE, REMOTE_DATABASE_USERNAME, DATABASE_USER_PASSWORD);
    } catch (SQLException e) {
        System.out.println("Connection Failed!:\n" + e.getMessage());
    }

    if (connection != null) {
        System.out.println("SUCCESS!!!! You made it, take control     your database now!");
    } else {
        System.out.println("FAILURE! Failed to make connection!");
    }

}

We will create a remote database user in the following steps, but for now, you will need to get the Public DNS url for your EC2 instance by following this path from the AWS console home:

EC2 → Running Instances → [Select an instance] → Description → Public DNS

Copy and paste that whole url in place of the PUBLIC_DNS constant in the code above.

Keep this window open for the next step.

Step 3: Editing Security Group

On the EC2 instance that you grabbed the Public DNS record for, scroll over to the “Security Groups” column and launch the wizard. If you do not yet have a security group for this instance, create one. Select a security group and at the bottom of the page, find “Inbound”. Click edit.

A modal called “Edit inbound rules” should pop up. If you do not have a “MYSQL/Aurora” type rule, click the “Add Rule” button and add it. Make sure the “Type” of the rule is “MYSQL/Aurora”, the “Protocol” is “TCP”, the “Port Range” is “3306" and the Source is “Anywhere” with an IP address of “0.0.0/0”. Click save.

Hold on to that Port Range value. You will need that for the JDBC connection code from above. It will replace the PORT constant.

DO NOT DO STEP 4, 5, AND 6 OUT OF ORDER!

Step 4: Creating a Remote User

Next, we need to create a remote user on the MySQL database we want to access through JDBC. This user will need all privileges to the database. Log into mysql from your EC2 instance in the shell; logged into the root user. Either way, once you are in, create a remote user and grant it all access on the database you want to access:

GRANT ALL PRIVILEGES ON dbTest.* To 'remoteu'@'%' IDENTIFIED BY 'password';

I called my remote user “remoteu”. I created my user at the host “%”. That makes it a remote user, which is what we want. My remote user has all privileges on the database “dbTest”.

You can try to run your JDBC connection code from above now with this remote user, its password, and the database. If it works, you are done. If not, we need to do one last thing before JDBC can work.

DO NOT DO STEP 4, 5, AND 6 OUT OF ORDER!

Step 5: Edit bind-address

SSH into your EC2 instance. Next, find the path to your mysql config file, my.cnf. Once you have that path, run the following command to open the my.cnf file for editing. We need to change the bind-address so that we can connect to our EC2 instance from remotely instead of only from the localhost.

Try running these commands to find your mysql config file; one of them should work:

locate my.cnf
whereis my.cnf
find . -name my.cnf

It looks something like this in the shell:

ubuntu@ip-xxx-xx-xx-xx:~$ locate my.cnf

To edit the config file, run sudoedit. My config file is at /etc/mysql/my.cnf, so I’ll run the following command to edit it:

sudoedit /etc/mysql/my.cnf

It looks something like this in the shell:

ubuntu@ip-xxx-xx-xx-xx:~$ sudoedit /etc/mysql/my.cnf

When the window opens, find bind-address and it should be an IP address that looks something like “bind-address = 127.xxx.xx”. Change it to “bind-address = 0.0.0.0”.

Type Ctrl-O to save the file and exit the file.

Keep the shell open and stay logged into your EC2 instance for the next step.

DO NOT DO STEP 4, 5, AND 6 OUT OF ORDER!

Step 6: Restart MYSQL [VITAL]

This part is vital if you did steps 4 and 5. We need to restart your MySQL service now before moving on for the changes to take effect.

Run the following command:

sudo service mysql restart

It looks something like this in the shell:

ubuntu@ip-xxx-xx-xx-xx:~$ sudo service mysql restart

If your mysql service is named something else, you may need to adjust that command appropriately. While most users have mysql, some users may have mysqld.

Last Step: Connect to MySQL through JDBC

Run your Java code from above and it should connect successfully! Congrats!

This is how your code should look now with the credentials of the remote user and your MYSQL/AURORA Security Group Port Number. You still need to enter your Public DNS.

public static void connectJDBCToAWSEC2() {

    System.out.println("----MySQL JDBC Connection Testing -------");
    
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        System.out.println("Where is your MySQL JDBC Driver?");
        e.printStackTrace();
        return;
    }

    System.out.println("MySQL JDBC Driver Registered!");
    Connection connection = null;

    try {
        connection = DriverManager.
                getConnection("jdbc:mysql://" + PUBLIC_DNS + ":3306/dbTest", "remoteu", "password");
    } catch (SQLException e) {
        System.out.println("Connection Failed!:\n" + e.getMessage());
    }

    if (connection != null) {
        System.out.println("SUCCESS!!!! You made it, take control     your database now!");
    } else {
        System.out.println("FAILURE! Failed to make connection!");
    }

}

Run Test Query if You Connected Successfully

And here’s a little query you can run to test your connection and database courtesy of TutorialsPoint:

private static void runTestQuery(Connection conn) {
    Statement statement = null;
    try {

        System.out.println("Creating statement...");
        statement = conn.createStatement();
        String sql;
        sql = "SELECT * FROM user";
        ResultSet rs = statement.executeQuery(sql);

        //STEP 5: Extract data from result set
        while (rs.next()) {
            //Retrieve by column name
            int id = rs.getInt("id");
            String email = rs.getString("email");

            //Display values
            System.out.print("ID: " + id);
            System.out.print(", Email: " + email);
        }
        //STEP 6: Clean-up environment
        rs.close();
        statement.close();
        conn.close();
    } catch (SQLException se) {
        //Handle errors for JDBC
        se.printStackTrace();
    } catch (Exception e) {
        //Handle errors for Class.forName
        e.printStackTrace();
    } finally {
        //finally block used to close resources
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException se2) {
        }// nothing we can do
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }//end finally try
    }//end try
}

Thanks for reading! If this has helped you, please hit that little green heart below to recommend this article. You, too, can help others find the way :)