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 actually 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 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 in to MySQL as the root user. In the future, the shell can be accessed as any other MySQL user you may create in the future.
Explore your Databases
See which MySQL databases exit:
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.
Get in There
To access and start messing with your db, use the USE query:
mysql> USE mysql;
Database changed
Let's see which tables are chillin in here.
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 |
+---------------------------+
Oh wow yeah, that looks pretty important. This is where configurations such as user information exists. 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 |
+------------------+
Sick. Without knowing much SQL at all, we can already see our databases, their tables, and get the values of whichever columns they might have. 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 which lists my Github repositories, so I'll create a db named github_repos:
CREATE DATABASE github_repos;
Query OK, 1 row affected (0.00 sec)
mysql> USE github_repos;
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 githubrepos (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> full_name VARCHAR(100),
-> description VARCHAR(300),
-> name VARCHAR(200),
-> url VARCHAR(150));
id is a standard column which indicates the numerical index of each row. Here, we're stating that our rows will count themselves.
Each following line creates a column by [name] [type of data]****[limit]. In this example we're creating columns which accept alphanumeric characters, up to a maximum of the the number specified.
NOTE: you should really overestimate the number of characters each field can accept. I didn't. Its a waste of time and might drive you crazy down the line: just accept a large number and be done with it.
Fruits of your labor
Go ahead and check out what you've done:
mysql> SHOW tables;
+------------------------+
| Tables_in_github_repos |
+------------------------+
| githubrepos |
+------------------------+
Damn dude, you did it. Let's take a look just to make sure:
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| full_name | varchar(200) | YES | | NULL | |
| description | varchar(300) | YES | | NULL | |
| name | varchar(200) | YES | | NULL | |
| url | varchar(200) | 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 githubrepos ADD homepage VARCHAR(255);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
This added column homepage which accepts alphanumeric characters.
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
