Just a little HOWTO on how to set up Oracle’s SQL Developer for use with MySQL/MariaDB.
Oracle’s SQL Developer is the heavyweight among database development tools*. While Dell’s Toad for MySQL may be more lightweight and newbie friendly, it doesn’t run natively on Linux (being able to run the Windows executable in a wine session doesn’t count as native). There are Linux packages available for Oracle’s MySQL Workbench. While Workbench includes handy tools for probing running MySQL servers (think jconsole for Java applications), I’ve found SQL Developer more convenient due to my specific need for switching between Oracle and MySQL databases. If that weren’t a requirement, I’d probably lean more heavily towards Workbench.
This is how to install and configure SQL Developer for working with MySQL/MariaDB databases.
Make sure the full OpenJDK is installed as well as the standard JDBC driver for MySQL. On Red Hat family operating systems use yum or (in the case of Fedora) dnf to install:
yum install java-1.8.0-openjdk-devel yum install mysql-connector-java
Download the latest zip distribution of the SQL Developer package from the Oracle Technical Network (OTN). This will be an approximately 300 MB large zip file. Don’t forget to agree to Oracle’s terms!
Next, extract the contents of the archive as /opt/oracle/sqldeveloper.
Then create /usr/local/bin/sqldeveloper and set it to executable. This will be a shell
script with the following minimalist code:
#!/bin/bash cd /opt/oracle/sqldeveloper ./sqldeveloper.sh
Now copy /opt/oracle/sqldeveloper/icon.png to /usr/share/pixmaps/sqldeveloper.png.
Create the file /usr/share/applications/sqldeveloper.desktop with the following contents:
[Desktop Entry] Version=1.0 Type=Application Name=SQL Developer GenericName=Database IDE Comment=IDE for Database Development Exec=sqldeveloper Terminal=false MimeType=text/plain; Icon=sqldeveloper.png Categories=Application;Development; StartupNotify=true
Out of the box SQL Developer can’t connect to MySQL or MariaDB databases. To do that you need to configure it to use the MySQL JDBC driver.
Start SQL Developer by running /usr/local/bin/sqldeveloper or from your desktop’s applications menu.
Go to Tools… Preferences… Third Party JDBC Drivers.
Click Add Entry, navigate to /usr/share/java/mysql-connector.jar and click OK.
Once SQL Developer is configured with the MySQL driver you should be able to connect to any MySQL/MariaDB database that allows for external connections.
To set up a read-only user with which you can make external connections do this:
Log onto the database using the mysql client as root.
Create a user with privileges to read the database:
mysql> grant select, show view, process, replication client --> on *.* to 'dbreader'@'localhost' identified by '***';
mysql> grant select, show view, process, replication client --> on *.* to 'dbreader'@'%' identified by '***';
The first command creates a local user in case you need to log onto the database from its host, the second an external user who can log in from anywhere.