… but it was worth it!

Background Preparation

In the previous post in this series I gave a little background about MySQL and my motivation for learning it now. The next basic question is “well, that’s nice… but how do I go about installing MySQL?”. This is the focus of this post.

Basically, in order to install MySQL, you need to have two core compoments, a server that manages or (controls) access to the database (model) by one or more client programs by which users view the information that they need. The emphasis in the previous statement is deliberate, as it reminds me of the components of the Model-View-Controller framework.

I did some background reading to make sure that I understood what I was doing, I ended up following the instructions at mysql.com to install their implementation of MySQL. In particular, I followed the instructions to perform a fresh installation. There were other overviews, such as this informative one and others here and here but I chose the go with the “official” line, after getting burnt going homebrew ().

Note: this series handles the installation and use of MySQL on the Linux platform. MySQL.com also has instructions for Windows or Mac OSX… “All the best in thine quest!”. The installation chapter also has instructions for other OS environments.

MySQL Installation

Setup Guide

Step 1: Downloaded the deb file from the MySQL apt rep and select the appropriate file for your OS. More information can be found in the guide here.

# General syntax:
# Note: you may be able to select MySQL version at this point.
shell> sudo dpkg -i /path/to/file/mysql-apt-config_w.x.y-z_all.deb

Step 2: Update APT software package repo versions, then install updated versions. Note: you can use either apt or apt-get.

shell> sudo apt-get update
shell> sudo apt-get upgrade

Note: The update command simply updates the package list with the latest versions, then upgrade performs the actual package installation. Here is an interesting discussion of how to apply the upgrade instruction, and here is a cool cheat sheet.

Step 3: install mysql-server (and client) package.

shell> sudo apt-get install mysql-server

Note: At this point it is a good idea to check to see whether the mysql core installation works as intended.

Step 4: install other MySQL components:

This step was only useful to me after doing a basic check to make sure that the core installation was workng properly. I didn’t do this on my first install attempt. That said, there is no reason not to install these components before checking out the install.

shell> sudo apt-get install mysql-workbench-community
shell> sudo apt-get install libmysqlclient18

There is a list of other MySQL packages here.

Step 5: Optional - Reconfigure MySQL This step appears to be optional and can be done at any time after mysql installation (step 3). However however, this step seems to be critical for production server setup after step 3. I stuck with the standard MySQL 5.7 (at step 2 or3) major release.

# reconfigure MySQL and change release version
shell> sudo dpkg-reconfigure mysql-apt-config
# update APT repo version if changed
shell> sudo apt-get update
# implement changes
shell> sudo apt-get install mysql-server

Summary: Command flow

The following code block gives a concise summary of the installation instructions above and how the steps fit together. These instructions can be used sequentially at the command line, packaged into an installation script or even entered as a single line separated by

# Step 1: Setup and Configure MySQL apt repo
shell> sudo dpkg -i /path/to/file/mysql-apt-config_0.8.0-1_all.deb
# Step 2: Update System
shell> sudo apt-get update
shell> sudo apt-get upgrade
# Step 3: Install MySQL
shell> sudo apt-get install mysql-server
# Step 4: Install 
shell> sudo apt-get install mysql-workbench-community
shell> sudo apt-get install libmysqlclient18

ODBC installation

Setup Guide

Exploring databases using RMySQL and ran into an issue that seemed to be linked to OBDC driver installation. After some background reading, I found that (ODBC) Open Database Connectivity is a standard relational database API… so I figured that this was something that was important to have so that I could use third party database access apps (such as R packages like RMySQL) without hassle. Unsurprisingly, MySQL.com also has a set of ODBC drivers, and the unixODBC project implements the Unix ODBC API. So… I proceeded to install the MySQL OBDC driver set.

Step 1: Install unixobdc so that the components unixodbc, unixodbc-dev and unixodbc-bin are prepared.

shell> sudo apt-get install unixodbc-dev unixodbc-bin unixodbc

Step 2: install the official mysql OBDC driver. Note: Apparently the libmyodbc ODBC driver package is now basically obsolete for 16.04. I previously tried [this](OBDC driver method but I’m not sure if it worked. The official instructions work , should have learned from the previous section… . Note, you may have to sudo these commands… so beware!

• a) Go to the mysql.com obdc page and select the “Ubuntu Linux 15.10 (x86, 64-bit), Compressed TAR Archive” for my Ubuntu 16.04 installation.

• b) extract files from the compressed archive.

shell> tar -xvzf mysql-connector-odbc-5.3.6-linux-ubuntu15.10-x86-64bit.tar.gz

• c) From the terminal, cd into the main directory of the archive extracted in b).

shell> cp bin/* /usr/local/bin
shell> cp lib/* /usr/local/lib

• d) Register the installed drivers

# Unicode Driver
shell> myodbc-installer -a -d -n "MySQL ODBC 5.2 Driver" -t "Driver=/usr/local/lib/libmyodbc5w.so"
# ANSI Driver
shell> myodbc-installer -a -d -n "MySQL ODBC 5.2 Driver" -t "Driver=/usr/local/lib/libmyodbc5a.so"

• e) Verify driver installation and registration

shell> myodbc-installer -d -l

Summary: Command flow

The series of setup commands to excecute once the driver compressed file has been downloaded and extracted (from step c) onwards):

# Copy drivers and installation program
shell> cp bin/* /usr/local/bin
shell> cp lib/* /usr/local/lib
# Install Drivers
shell> myodbc-installer -a -d -n "MySQL ODBC 5.2 Driver" -t "Driver=/usr/local/lib/libmyodbc5w.so"
shell> myodbc-installer -a -d -n "MySQL ODBC 5.2 Driver" -t "Driver=/usr/local/lib/libmyodbc5a.so"
# check installation
shell> myodbc-installer -d -l

Conclusion

After much drama, I have learned much, and now I am able to explore the powers of MySQL. I will write about solutions to further configuration issues as I come across them in later posts. I will try to add updates in the section below to keep things easy to find !

Update

Watch this space