title: SQL Developer for MySQL link: https://onemoretech.wordpress.com/2016/04/04/sql-developer-for-mysql/ author: phil2nc description: post_id: 10999 created: 2016/04/04 12:49:01 created_gmt: 2016/04/04 16:49:01 comment_status: closed post_name: sql-developer-for-mysql status: publish post_type: post

SQL Developer for MySQL

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.

Installation

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

Configuration

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.

Use

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.

Copyright 2004-2019 Phil Lembo