Adding MySQL to Your Data Science Workbench

Sept. 21, 2019, 10:31 a.m.

Summary

We are going to install MySQL in our previously created Linux environment that will be the cornerstone of our data science workbench (you can follow along and create your own Linux Ubuntu 18.04 environment in our Standing Up A Linux Server post).  MySQL is a Relational Database Management System (RDMS) that will enable you to persist data that you scape, output from your model development efforts and even to persist variables in the data science applications that you will develop.  This is a powerful tool that will enable you to perfect the knowledge, skills and abilities associated with Structured Query Language (SQL).  This is a necessary foundational step on the road to becoming a data science professional.

Requirements

The following requirements are necessary for successfully completing this tutorial:

Learning Objectives

The following learning objectives are intended to describe what elements will be tackled in this section along with how and why we are doing it this way.

  • Install a functional version of MySQL within a Linux Ubuntu 18.04 virtual environment
  • Insert several records with a sample query from the command line
  • Read several records with a sample query from the command line
  • Update several records with a sample query from the command line
  • Delete several records with a sample query from the command line

Implementation

Let's get things started!  You should be logged in to your Virtual Ubuntu instance with the account you created in the resource above as a non-root user with sudo privileges.  This is where we are going to start, so we'll begin at the command line in the top level directory of this user account.

Note: We are going to use the source at the bottom of the page1, so you can follow along there if there are any gaps.

Let's jump right in and start creating the environment for our new MySQL 8.x installation:

> mkdir temp
> cd temp
 

Now let's download the target file using the Linux `wget` command:

> wget -c https://repo.mysql.com//mysql-apt-config_0.8.13-1_all.deb
 

Let's use the dpkg command to install the MySQL repository package, then update the latest packages and install MySQL.  You'll be asked to configure the mysql-apt-config and you should simply select `ok`—finally be prompted to enter a new password (remember this) and then confirm it—do these things as you are prompted.  Also select the `Use Strong Password Encryption` option when prompted:

> sudo dpkg -i mysql-apt-config_0.8.13-1_all.deb
> sudo apt update
> sudo apt-get install mysql-server
 

Now we are going to enhance the security features of this installation by executing the following command:

> sudo mysql_secure_installation
 

You'll be prompted for responses to a few questions—this is how I've answered mine:

Validate Password Component...No
Change the password for root...No
Remove anonymous users...Yes
Disallow root login remotely...Yes
Remove test database and access to it...Yes
Reload privilege tables now...Yes
 

Now we should have everything installed and it should be running properly—we can check this with a simple command:

> sudo systemctl status mysql
 

You should see some output like this that indicates things are running just fine!

If needed we can stop and start this MySQL service using the following commands:

> sudo systemctl stop mysql
> sudo systemctl start mysql
 

Finally, we can access the MySQL client application using the following command—your user name should be `root` and your password should be the one you created above:

> mysql -u root -p yourpassword
 

Once you have accessed the application we can execute a few basic commands below that will enable to get on the road to storing and engineering our own data as needed:

mysql> SHOW DATABASES;
mysql> CREATE DATABASE Customers;
mysql> USE Customers;
mysql> SHOW TABLES;
mysql> CREATE TABLE Customers (customer_id INT NOT NULL AUTO_INCREMENT, last_name VARCHAR(255), first_name VARCHAR(255), address VARCHAR(255), city VARCHAR(255), state VARCHAR(20), postal_code VARCHAR(20) PRIMARY KEY (customer_id) );
mysql> SHOW TABLES;
mysql> SELECT * FROM Customers;
 

mysql> INSERT INTO Customers (last_name,first_name,address,city,state,postal_code) VALUES ('Williams','Bill','123 Elm Street','Anytown','MA','12345');
mysql> SELECT * FROM Customers;
 

Congratulations!  You've installed the MySQL 8.x Relational Database Management System (RDMS) on your virtual server, you've created a user, created a new database, created a new table and have inserted data into this table.

Awesome job!

Cheers, JAH

Sources

  1. How to Install MySQL 8.0 in Ubuntu 18.04 (TecMint)

Comment Count: 0 Comments: