As frameworks and services evolve to remove us further away from boilerplate code, the first casualty of saved time is the fundamental understanding of what we're doing sometimes. This has good reason; one can only learn so much from repetitive command-line interactions with databases, thus making any service's one-click-deploy button all the more sensible. If I had to imagine the least sexy title for a post in software development, it would be something along the lines of How to Configure MySQL on a VPS, as opposed to like, a cloud-based solution, or Even a Docker Container, as Though we Live in the God Damn 90s or Something." And that's more or less the gist of this post.

I'm not exactly crushing it in the MySQL shell every day- chances are a lot of us aren't considering we have plenty of tools to protect us from ever thinking about doing so. That said, this is very much a real use case for pretty much any self-hosted application running a database natively.

So here it goes: a crash course in MySQL, by An Idiot.

Installation

Installing MySQL server on Ubuntu is simple:

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

Configure MySQL via the Shell

Creating databases, users, and permissions, all happens within the MySQL shell. This can be accessed via:

$ mysql -u root -p

This will log you into MySQL as the root user. In the future, the shell can be accessed as any other MySQL user you may create in the future.

View Databases Within a MySQL Instance

Let's see which MySQL databases exist using the SHOW DATABASES; command. The example I use below happens to be the SQL instance for this very blog (or at least it was, at the time of writing):

mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| ghost_prod         |
| hackers_prod       |
| ind_prod           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Nice databases bro. Notice the mysql database. As you might imagine, there's probably a lot of cool important shit in there that makes everything work. Let's check it out.

View the Contents of a Database

Each of the databases within our instance contains tables. We can expect all tables within a database to contain data that serves a single larger purpose, ranging in scale from serving a single application, to serving a single service (or microservice) in a larger application ecosystem.

To view or modify the contents of the tables of a database, the USE [database_name]; query activates a session limited to the scope of the given database. The "top-level" database in a MySQL instance happens to be called mysql. I wouldn't recommend messing around in here too much, but let's have some fun and see what's inside:

mysql> USE mysql;

Database changed

Let's see which tables are chillin' in here via SHOW tables;:

mysql> SHOW tables;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

Yep, that does look pretty important. The mysql database is how our entire instance is defined, such as core configurations, user privledges, and much more. When we create users and grant them permissions, we'll be doing so in mysql. We'll worry about that later, but let's see who's in there anyway for the hell of it:

mysql> SELECT user FROM user;

+------------------+
| user             |
+------------------+
| debian-sys-maint |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+

Without knowing much SQL, we can already see our databases and their tables and get the values of their columns. Now, let's start doing stuff.

Create a Database

Go ahead and create a new database. In my case, I want to create a database that lists my Github repositories, so I'll create a database named github:

CREATE DATABASE github;
Query OK, 1 row affected (0.00 sec)

mysql> USE github;
Database changed

Creating a Table

Now it's getting good. We're going to create a table in our database. To do this, we're going to need to define our columns upfront, including the type of data each column can accept as well as the restrictions on that column. I'm keeping it simple and storing values of text for now.

mysql> CREATE TABLE `repo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `repo_name` varchar(255),
  `url` text,
  `owner_id` int NOT NULL,
  `category` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=171 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

By best practices, rows should have an id column to serve as a PRIMARY KEY, which indicates the numerical index of each row. Here, we're stating that our rows will increment by 1 as we add them. Having some form of PRIMARY KEY is vital for the performance of our database, even if as simple as an incrementing integer.

Each following line creates a column by [name] [type of data]****[limit]. In this example we're creating columns that accept alphanumeric characters, up to a maximum of the the number specified.

Fruits of Your Labor

Go ahead and check out what you've done:

mysql> SHOW tables;

+------------------------+
| Tables_in_github       |
+------------------------+
| repos                  |
+------------------------+

Damn dude, you did it. Let's take a look just to make sure:

explain repos;

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(200) | NO   |     | NULL    |                |
| url         | text         | NO   |     | NULL    |                |
| owner_id    | varchar(200) | NO   |     | NULL    |                |
| created_at  | datetime     | YES  |     | NULL    |  |
| updated_at  | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

Holy shit it's literally a table.

Making Changes

Let's make some changes to our table after the fact. We can use ALTER TABLE to add, modify, or remove columns.

mysql> ALTER TABLE github_repos ADD tags text;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

This added a text column called tags, presumably separated by commas (if you like).

Now that we've created our own database with our own defined structure, the possibilities are endless. The next step is to actually fill it with data, but let's save that for next time.

mysql> \q
Bye