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
$ 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.
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).
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)
***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
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
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:
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 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:
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".
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.
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:
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.
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?
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,
This massive SQL command accomplishes a few different things for us:
idcolumn actually has a command attached to it that numbers each row:
namecolumn has been limited to be under 20 characters long.
foodcolumn has been limited to be under 30 characters long.
confirmedcolumn records whether the person has RSVP'd with one letter, either Y or N.
datecolumn will show when the person signed up for the event. MySQL requires that dates be written as
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:
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.
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" (
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.
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
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
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...
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