MySQL Connector/J / JDBC Integration (In Ubuntu)

connectorj-mysql-integration.md

Read through: https://www.digitalocean.com/community/tutorials/how-to-install-java-with-apt-get-on-ubuntu-16-04

Then install Oracle Java 8:

sudo add-apt-repository ppa:webupd8team/java
sudo apt-get update
sudo apt-get install oracle-java8-installer

Read through: https://help.ubuntu.com/community/JDBCAndMySQL

Then install the connector driver:

sudo apt-get install libmysql-java

Set classpath globally:

echo "# Set classpath for JDBC & MySQL
CLASSPATH=\".:/usr/share/java/mysql.jar\"" >> /etc/environment

Create a test script:

echo "import java.sql.*;
import java.util.Properties;

public class DBDemo
{
  // The JDBC Connector Class.
  private static final String dbClassName = \"com.mysql.jdbc.Driver\";

  // Connection string. mydatabase is the database the program
  // is connecting to. You can include user and password after this
  // by adding (say) ?user=craig&password=craig. Not recommended!

  private static final String CONNECTION =
                          \"jdbc:mysql://localhost:3306/mydatabase\";

  public static void main(String[] args) throws
                             ClassNotFoundException,SQLException
  {
    System.out.println(dbClassName);
    // Class.forName(xxx) loads the jdbc classes and
    // creates a drivermanager class factory
    Class.forName(dbClassName);

    // Properties for user and password. Here the user and password are both 'craig'
    Properties p = new Properties();
    p.put(\"user\",\"craig\");
    p.put(\"password\",\"craig\");

    // Now try to connect
    Connection c = DriverManager.getConnection(CONNECTION,p);

    System.out.println(\"It works !\");
    c.close();
    }
}" > DBDemo.java

Compile the test:

javac DBDemo.java

Run the test:

java DBDemo

If you get the error:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'performance_schema.session_variables' doesn't exist

Check out: http://stackoverflow.com/questions/36746677/table-performance-schema-session-variables-doesnt-exist

I personally needed to run:

mysql_upgrade -u root -p --force

And then:

echo "# JDBC Connection error
performance_schema=ON" >> /etc/mysql/mysql.conf.d/mysqld.cnf

You can make use of this to setup a data pipeline between MySQL and Amazon ElasticService by following these instructions here: https://gist.github.com/craigvantonder/8566c6992b930e7d2ac2520e18516e4d

I had to run sudo apt install openjdk-8-jdk instead of sudo apt-get install oracle-java8-installer