Create a DB connection to MySQL in Kettle

Introduction

Creating a DB connection from Kettle to MySQL involves creating a MySQL use who can access the DB in question, installing the JDBC driver, and creating a connection.

Install the MySQL JDBC driver

Download the MySQL JDBC driver from http://dev.mysql.com/downloads/connector/j/.

Login to mysql.com, then click Download.

cd ~/Downloads
tar -xzf mysql-connector-java-5.1.20.tar.gz
cd mysql-connector-java-5.1.20
cp mysql-connector-java-5.1.20-bin.jar ~/bin/data-integration/libext/

Create a MySQL user

In this post, I am going to create a connection to the Sakila DB.

mysql -u root -p

At the MySQL command prompt, enter the following (replace ‘password’ with your password):

mysql> GRANT ALL ON sakila.* TO akbar@localhost IDENTIFIED BY 'password';

Create the DB connection in Kettle

cd ~/bin/data-integration
./spoon.sh
  1. Click the New in the PDI toolbar.
  2. Click Database connection.
  3. Enter information similar to what’s shown below:
  4. PDI Database Connection

  5. Click Test.
  6. Click OK.

In Explorer in the left pane of PDI, right-click on the Sakila database connection, click Explore.

You should now be able to view the tables in the Sakila database.

Install MySQL 5.5 on Ubuntu 12.04 LTS

Introduction

Installing the MySQL package on Ubuntu is extremely simple.

Installation

Open a terminal and enter the following commands.

sudo apt-get install mysql-client mysql-navigator mysql-server

Type Y to accept the additional packages. Press Enter.

After downloading and during installation, the MySQL configuration dialogs will display in the terminal.

In the first dialog, press Enter.

Enter a password for the MySQL root user. Press Enter.

Reenter the root password. Press Enter.

That’s it, MySQL is now installed and ready for use.

Important update to earlier post on MySQL Installation

I have updated an earlier post on how to install MySQL 5.5 on Ubuntu 10.04 LTS.

Importantly, the new instructions include how to add the MySQL 5.5 libs to the loader path. This is very important as it’s highly likely that you’ll build software that depends on these MySQL libraries.

The updated instructions can be viewed at: MySQL 5.5 on Ubuntu 10.04 LTS.

Install MySQL 5.5 on Ubuntu 10.04 LTS running in the Rackspace Cloud

Update April 23, 2012
I added new instructions for adding the MySQL libs to the loader path. Failure to add the libs to the loader path will cause errors when you try to run/build programs that depend on MySQL, such as installing the MySQL driver for Pyramid (Python).

Update April 18, 2012
Thanks to Lampros for pointing out that WordPress was converting double dashes into single dashes “-“. I have updated the post so that double dashes are displayed correctly.

The following are installation notes for MySQL 5.5 on Ubuntu 10.04 LTS running on the Rackspace cloud.

Download MySQL

Download the MySQL package from http://www.mysql.com in the ~/Downloads directory.

Download the following package from http://www.mysql.com/downloads/mysql/#downloads by selecting Debian Linux from the Select Platform drop-down list:

mysql-5.5.22-debian6.0-x86_64.deb

Installation Prerequisities

After the download is complete, open a terminal and execute the following commands:

Note:
libpthread-stubs0 libpthread-stubs0-dev coreutils are really not required, but I installed them as I was working through another issue.

apt-get install build-essential cmake libncurses5-dev libaio-dev bison libpthread-stubs0 libpthread-stubs0-dev coreutils

Setup the MySQL User and Group

groupadd mysql

useradd -r -g mysql mysql

Install MySQL

cd ~/Downloads

dpkg -i mysql-5.5.22-debian6.0-x86_64.deb

Note:

MySQL is now installed into the following directory:

/opt/mysql/server-5.5

cd /opt
chown root:root mysql
cd mysql
chown root:root server-5.5
cd server-5.5
chown -R mysql:mysql ./*
./scripts/mysql_install_db --user=mysql
chown mysql:mysql ./*
cd lib
chown -h mysql libmysqlclient.so libmysqlclient.so.18 libmysqlclient_r.a libmysqlclient_r.so libmysqlclient_r.so.18 libmysqlclient_r.so.18.0.0
cd ../support-files/

Note:
Decide which of the configuration files meets your needs, then copy it to /etc.

cp my-medium.cnf /etc/my.cnf
cp mysql.server /etc/init.d/mysql

Add MySQL to PATH

vi /etc/environment

Add the following to the end of the path:

/opt/mysql/server-5.5/bin

Example:

PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/opt/mysql/server-5.5/bin"

source /etc/environment

Start the MySQL Server

/etc/init.d/mysql start

Set the MySQL Server to Start on Boot

update-rc.d mysql defaults

Create a root Password and Secure the Server

The following is the most basic and minimal security, but the steps below are required for a production server.

/opt/mysql/server-5.5/bin/mysql_secure_installation

Check the security settings:

mysql -u root -p
mysql> use mysql;
mysql> SELECT User, Host, Password FROM mysql.user;

Note:
None of the password fields should be blank.

Add the MySQL Libs to the Loader Path

We now need to update our loader path to include the MySQL libs. This is important for any future work we do that depends on the MySQL libraries, such as installing a MySQL driver for Pyramid.

cd /etc/ld.so.conf.d
vi mysql.conf

Paste in the following:

# MySQL 5.5 Libraries
/opt/mysql/server-5.5/lib

ldconfig

Post installation checks

mysqladmin version -u root -p
mysqladmin variables -u root -p