Using Oracle SQL Developer to Connect to MySQL/Mariadb Databases

Introduction

One of my most favorite tools is Oracle SQL Developer. I mainly use it to connect to Oracle databases, but recently since I’ve been developing a number of Symfony web applications, I’ve wanted to connect to Mariadb (replacement for MySQL databases). This article describes how to setup Oracle SQL Developer to do that.

SQL Developer

You can download the latest SQL Developer here:

• http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

You will also need to download the MySQL Connector/J. The Mariadb jar client doesn’t work with Oracle SQL Developer (or at least I haven’t found a way to get it to work yet). You can download the archive here:

• https://dev.mysql.com/downloads/connector/j/

Extract the file to a convenient location.

Configuration of SQL Developer

Start SQL Developer and then perform the following steps:

• Select “Tools > Preferences“.
• Then in Preferences, expand Database > Third Party JDBC Drivers.
• Click “Add Entry…“.
• Select the Location where you unzipped the MySQL Connector/J archive and select the “mysql-connector-java-x.x.xx-bin.jar” file (where “x.x.xx” is the version).

When you’ve done this, the screenshot should look something like this:

Restart SQL Developer once you’ve made these changes.

Adding a Connection

To add a MySQL/Mariadb connection, click the plus sign near “Connections” on the left. You will now see a MySQL tab, where you can enter the connection details. It will look like the following:

Connecting Remotely

When you add a connection above, there is a Test button that you use to test connecting to the hostname and database. If the MySQL or Mariadb is remote, then you most likely will get a error connecting, since you need to permit the user to connect from remotely.

In the MySQL/Mariadb login as an admin (need access to the “mysql” database), and then run the following commands:

use mysql;
SELECT Host, User,ssl_type FROM user;

That will show you the users and hosts that are allowed to connect to the databases. I added “ssl_type” to the query above in case you plan to use SSL connections when connecting remotely (this is a good idea when connecting from a DMZ or external to your company).

If you want to grant access to a user to all databases (schema) with all privileges from a remote host, then you’ll need to issue a command like the following:

GRANT ALL PRIVILEGES ON *.* TO 'abunk'@'192.168.0.2' IDENTIFIED BY 'mypass';

This grants access to the user “abunk” to all databases. You can modify the above to limit the schema/privileges as needed. The above is just an example.

Once you’ve done the above steps, you can now test the connection in Oracle SQL Developer and it should connect. You can now use SQL Developer to connect to your MySQL/Mariadb databases.

Enjoy!