This post will probably not contain anything new, but maybe it will all just be in one place. Recently I decided to do something with my Raspberry Pi (RPi), as for some time now, it has been sitting on my desk acting as an XBMC server. Well no more. But

what should I do? How about running a database on the thing? Sounds like a plan, and of course we will need to make sure #SQLDeveloper can connect to it. Anything else would just be ridiculous. Let’s get started:

Raspberry Pi:

I won’t go into the initial setup of the RPi, but to follow along, I suggest you boot with Raspbian which you can download here.  After booting your RPi, you’ll need to make sure that everything is updated to the latest and greatest version.

pi@raspberrypi:~ $ sudo apt-get update && sudo apt-get upgrade

should do the trick. Make sure to answer ‘Y’es to the prompt. After a few minutes, and a few screens of files updating, you will be back at the command prompt. I rebooted my RPi at this point, but probably not necessary. Now that the RPi is updated, let’s move on to #MySQL

MySQL:

The installation should be quite simple. We will install MySQL 5.5, the MySQL client, and for grins php5-mysql in case you want to do some PHP with a web server (out of scope for this post).

pi@raspberrypi:~ $ sudo apt-get install mysql-server –fix-missing

should get you going in the right direction. As the installation progresses, you will be asked for a root password.make up something good and complex. How about ‘Welcome1’? The next screen will ask you to verify that strong password and then the installation will continue. Once MySQL is installed, execute the following:

pi@raspberrypi:~ $ sudo apt-get install mysql-client php5-mysql

Installs will start up again, and this time, you should have a full fledged MySQL database running on your RPi and a means by which to connect to the database from the CLI and via PHP, but we aren’t quite done yet. We need to create a database and a user. In order to do this, we need to log into MySQL.

pi@raspberrypi:~ $ mysql -uroot -hlocalhost -p

Enter your password from the install: Welcome1 (I know that’s what you used). If you did everything right, you should get something like this:

then enter the following commands at the MySQL prompt:

mysql> CREATE DATABASE rpidb;
Query OK, 1 row affected (0.01 sec)

Here is just called my database rpidb. Feel free to be a little more unique that that. Next create a user for your new database.

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username‘@’%‘ IDENTIFIED BY ‘password‘ WITH GRANT OPTION;

Now this ain’t secure at all, as I am giving my account full access to everything from everywhere. That’s what the ‘username’@’%’ part does. You can read the MySQL docs for more info on securely accessing the database. Finally we need to modify a configuration file.

pi@raspberrypi:~ $ sudo vi /etc/mysql/my.cnf

Restart the MySQL services:
pi@raspberrypi:~ $ sudo service mysql restart

Wait a few seconds for the services to restart and confirm you are able to login in with the new user credentials. In my case, the login would look something like this:

With access confirmed, let’s get off the RPi and get SQL Developer in the mix.

SQL Developer:

I will assume that you never used SQL Developer to connect to a MySQL database. If you have, you can skip this part. For those of you that haven’t we will need to download the MySQL JDBC drivers which you can find here.

1. Download and unzip the file. I like to put my SQL Developer drivers in a central location, but this is not a requirement.
2. Open SQL Developer and navigate to “Preferences > Database > Third Party JDBC Driver”
3. Click the “Add Entry…” button and browse t0 the “mysql-connector-java-5.0.4-bin.jar” file and click the “Select” button. (Note, you may find a newer version of this driver if you use a different link from the one I have provided).
4. Click the “OK” button to add the driver and exit the Preferences dialog.
5. When you create a new connection in SQL Developer, you will now have a MySQL tab
6. Fill in the appropriate login credentials you created earlier and test the connection
7. Save your connection and connect to the MySQL database running on you RPi

 

Now go do something fun with this!

Enjoy