How to Install MySQL


Installing MySQL

Installing MySQL on an Ubuntu 16.04 machine is a very simple process since, starting in Ubuntu 16, the only MySQL package available in apt-get is the most current version. What follows are the steps needed to install a fresh version of MySQL onto an Ubuntu machine (Note: The Ubuntu machine can be a VM as well, so long as your VM uses persistent memory so the MySQL install isn't wiped when you shut down the machine).

To install the latest version of the mysql package, simply update the package index on your server (or Ubuntu machine) and install the default package with apt-get

$ sudo apt-get update
$ sudo apt-get install mysql-server

You'll be prompted to create a root password during the installation. It doesn't matter what your password is in your local environment, but DO remember to make it a secure on if you are going to be using this install of MySQL on a live server.


Configuring MySQL

In order to configure MySQL on a fresh installation, you are going to want to run the included security script. This script changes some of the less secure default options like remote root logins and sample users.

$ sudo mysql_secure_installation

This script will prompt you for the root password you created earlier (I hope you remembered it!). You can just press Y and then ENTER to accept the defaults to all of the following questions, with the exception of the one that asks you to change the root password (normally, MySQL will create a root user with a simple default password, so this question is here as a safeguard against anyone who, unlike us, didn't choose a safe password to begin with).


Testing the MySQL Installation

Now that we have successfully installed and optimized MySQL, it should be running. To test this, check the status of the mysql.service like this:

$ systemctl status mysql.service

You should see output in your command line similar to the following:

mysql.service - MySQL Community Server
Loaded: loaded(/lib/systemd/system/mysql.service; enabled; vendor preset: en)
Active: active (running) since Wed 2017-03-25 21:21:25 UTC; 30min ago
Main PID: 3754 (mysqld)
Tasks: 28
Memory: 142.3M
CPU: 1.994s
CGroup: /system.slice/mysql.service

***NOTE: if MySQL ISN'T running, you can start it with sudo systemctl mysql start***

As an additional check, you can always try connecting to the database with the mysqladmin tool, which is a command-line-based client that allows you to run administrative commands. For example, the following command says to connect to MySQL as root (-u root), prompt for a password (-p), and return the MySQL version.

$ mysqladmin -p -u root version

If you use this, you should see output similar to the following:

mysqladmin  Ver 8.42 Distrib 5.7.16, for Linux on x86_64
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version 5.7.16-0ubuntu0.16.04.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 30 min 54 sec

Threads: 1 Questions: 12 Slow queries: 0 Opens: 115 Flush tables: 1 Open tables: 34 Queries per second avg: 0.006

Creating && Deleting a Database

First, in order to do anything with MySQL, you need to log into MySQL inside your Ubuntu machine. Do this using the following command:

mysql -u root -p

MySQL will then prompt you to enter your root password. Once the password is entered, you will be inside of MySQL. Before we go any further, here are a few pieces of information about the MySQL server that will be helpful:

  • All MySQL commands end with a semicolon (;). If you enter a command and forget the semicolon, the command will NOT run.
  • While it is not required, the most common convention is to write SQL commands in uppercase and databases, tables, usernames, and text in lowercase to help distinguish between the two. Do note, however, that the MySQL command line is NOT case-sensetive!

How a MySQL Database is Organized:

Before going any further, I think it's important to point out exactly how a MySQL database is laid out. If you already know the basics of SQL databases, go ahead and skip this subsection.

A MySQL database consists of a hierarchical arrangement of databases and tables such that one database can contain many tables, but one table cannot be stored amongst many databases. Within each table, you have multiple rows and columns. Each row consists of one entry into the table, while each column consists of one data point.

Creating a Database

Creating a database within MySQL is very simple. The first thing you should do is check what databases are already available within your MySQL server. Do this by entering the following command into the MySQL command line:

SHOW DATABASES;

When you enter this command, you should see a list of all of the databases currently in your MySQL server.

Let's create a new database:

CREATE DATABASE database_name;

Now if we were to run the SHOW DATABASES; command again, we would see that a new database (in this exact case, named "database_name") shows up in our list of databases.

If we wanted to delete this new database from our MySQL server, we would enter the following command:

DROP DATABASE database_name

Once again, if you were to run the command SHOW DATABASES;, you would notice that the new database that we created is now gone. As you continue using MySQL, you'll notice that the phrasing used the most when deleting items is the word "drop".

Creating a Table

Now that we've learned how to create a new database, it's time to learn how to add tables to that database so we can actually begin storing and manipulating data. Before we can start doing anything new with tables, however, you need to create a new database. Just for example's sake, let's create a database that will hold all sorts of information on events.

Accessing a MySQL Database:

Now that you've created a new database, let's fill it up with some tables. Before we get right to adding tables, though, we have to let MySQL know exactly what database to use when we issue commands. We do that by making use of the USE keyword. For example:

USE events;

Now that we have told MySQL that we want to use the new events database, we can actually check what tables are present in the database in similar fashion to what we did earlier when we were checking what databases were present in the MySQL server.

SHOW tables;

Since this is a new database, MySQL has nothing to show, so you'll get a message that talks about an empty set, since that is exactly what is being returned to you. Let's fix that, shall we?

Let's Make a Table

Using the example events database, let's pretend that it's football season and we're trying to organize a massive tailgate before the season's first home game. We could try to remember what person is bringing what, who we've invited, etc., OR we can just use a new MySQL table to do that for us! Enter the following into the MySQL command line:

CREATE TABLE tailgate (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
food VARCHAR(30),
confirmed CHAR(1),
signup_date DATE);

This massive SQL command accomplishes a few different things for us:

  1. It creates a table called tailgate within the events database.
  2. It sets up 5 columns within the table--id, name, food, confirmed, and signup_date.
  3. The id column actually has a command attached to it that numbers each row:
    • INT: Limits this column to only containing integer values
    • NOT NULL: Doesn't allow this column to ever contain a NULL value
    • PRIMARY KEY: Lets MySQL know that this is the column that will be reference if any other table wants to reference an entry from this table
    • AUTO_INCREMENT: Tells MySQL to automatically increment the value of subsequent columns so that we never have the same id number more than once
  4. The name column has been limited to be under 20 characters long.
  5. The food column has been limited to be under 30 characters long.
  6. The confirmed column records whether the person has RSVP'd with one letter, either Y or N.
  7. The date column will show when the person signed up for the event. MySQL requires that dates be written as yyyy-mm-dd

Now if we use the SHOW tables; command, we can see that our new table is within the database. If we ever forget the exact organization of our table, we can always see the structure of the table using this command:

DESCRIBE tailgate

Make sure to keep in mind that, even though the MySQL command line doesn't pay attention to cases, the table and database names ARE case sensitive; i.e. tailgate is not the same as Tailgate or TAILGATE.

Adding Information to a MySQL Table

So by this point we have created a table within our database, but it's looking a little bit empty. Let's change that! Here's the standard format that all of your data-insertions will take when entering them into a MySQL database:

INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "John", "Casserole","Y", '2012-04-11');

Note that the ` is NOT a single quote, it is a tick-mark (that key generally above TAB on most keyboards). Once you've got that down and entered correctly into your table, you'll see the words:

Query OK, 1 row affected (0.00 sec)

Now let's make things interesting and add a couple more people into our tailgate table:

INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Sandy", "Key Lime Tarts","N", '2012-04-14');
INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Tom", "BBQ","Y", '2012-04-18');
INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Tina", "Salad","Y", '2012-04-10');

Once again, you should see the Query OK response if your data was stored into the table without any problems.

Now that all the data is entered into our tailgate table, use the following command to get a visual representation of what data is currently in the table:

SELECT * FROM tailgate

All this command is saying is that you want to select all the data (SELECT *) from the table called "tailgate" (FROM tailgate).

Managing Columns

Updating Entries:

Now that we've created our table and added a bunch of data, we're going to want to know how to change things that are already in the table! For example, say that Sandy has FINALLY confirmed that she is coming to my tailgate, how do I update my table to reflect that change? Check out the SQL command below:

UPDATE `tailgate` SET `confirmed` = 'Y' WHERE `tailgate`.`name` = 'Sandy';

This command is really useful, especially because you can use it to not only update data, but to add information into empty cells as well.

Adding Columns

Our table is almost complete, but what if we realize halfway through collecting all of our tailgate data that we forgot to include a column in the table? What if we needed to get the email addresses of all of the people coming so that we could send them out time and date information if plans changed? Well, luckily for us, MySQL makes it really easy to add and delete columns in a table.

ALTER TABLE tailgate ADD email VARCHAR(40);

If you remember anything about adding columns from earlier in the example, this ought to look really familiar. The only thing different is that instead of having a CREATE command, we have an ALTER TABLE command. This does exactly what it looks like, it takes the table that you specify and alters it in the way that you say specifically (in this case, we add a column onto the end of the table).

Say, though, that you would find it easier to read if the email column was right next to the name column. If that's the case, you just have to add an extra phrase to the command like so:

ALTER TABLE tailgate ADD email VARCHAR(40) AFTER name;

Just adding that little phrase at the end lets MySQL know that you want to alter the table by adding this new column directly after the name column.

Deleting Columns

Just like you can add a column to a table in MySQL, you can delete one as well:

ALTER TABLE tailgate DROP email;

I guess we'll never know how to reach the tailgaters...

Deleting Rows

Sometimes when you're dealing with entering data into a database, whether manually or automatically, you come across the need to remove an entry (row) from the table. If that is the case, use the following structure to remove a row:

DELETE from [table name] where [column name]=[field text];

For example, after she was already late to RSVP to my tailgate, Sandy has now had something come up and she just told me that she isn't going to be participating in my potluck after all! Since she's not coming at all, we can just delete her details from the table to give us a more accurate reading of who is going to be attending my tailgate like this:

DELETE from tailgate where name='Sandy';

And just like that, if you SELECT * FROM tailgate like we did earlier, you'll notice that Sandy's entry (row) in the table has dissappeared and we only have 3 people signed up for the tailgate now!

Alexander Six, 2017