Image
![IT Automation ebook](/sysadmin/sites/default/files/styles/image_cta/public/2021-07/SysAdminGuidetoITAutomation-e-book-medium.jpg?itok=8_5VlQhF)
If you plan to set up a web application like Drupal, WordPress, Bugzilla, or something similar, you're probably going to also need a database so that the app can save the data your users generate. One of the most popular databases is MySQL, which is prominently implemented as an open source project called MariaDB.
It's common to interact with a database through a programming language. For this reason, there's usually a MySQL application programming interface (API) (sometimes called a "binding") for Java, Python, Lua, PHP, Ruby, C++, and many, many other programming languages. However, before using an API, it helps to understand what's actually happening with the database engine.
For web applications, databases often (although not always) run on a dedicated database server. Your web app can access your database similarly to how you access remote computers over Secure Shell (SSH). Even when you run your database on the same server as your application, you access it through the server and client model using the loopback IP address 127.0.0.1, also known as localhost
.
In this article, I'll demonstrate a local MySQL install. The process for a remote one is essentially the same, except a few privileges are required to access a database outside localhost
. I note these differences when necessary.
The open source implementation of MySQL is MariaDB. To install MariaDB on a Red Hat Enterprise Linux (RHEL), CentOS, or Fedora server:
[server]$ sudo dnf install mariadb mariadb-server
If you're also setting up a client machine, install the client software on that machine too:
[client]$ sudo dnf install mariadb
You can confirm the installation using the --version
option. MariaDB responds to either the mariadb
or mysql
command:
$ mariadb --version
mariadb Ver 15.1 Distrib 10.5.13-MariaDB, for Linux (x86_64)
$ mysql --version
mysql Ver 15.1 Distrib 10.5.13-MariaDB, for Linux (x86_64)
You've installed a database engine, but you haven't started it. To start it and to set it to autostart after a reboot:
[server]$ sudo systemctl enable --now mariadb
Alternately, you can set MariaDB to start and run until you either reboot or stop it manually:
[server]$ sudo systemctl start mariadb
After installation, you can open an interactive MariaDB session as root
with the --user
option:
[server]$ sudo mariadb --user root
password for tux:
Welcome to the MariaDB monitor.
MariaDB>
You communicate with MariaDB through Structured Query Language (SQL) commands. SQL isn't a vast language, but it can be nuanced and, as its name suggests, highly structured. Unless you're designing a database from scratch or developing software that uses a database, you don't need to know much SQL. If you're setting up a database for another application to use, that application handles most of the SQL for you, because that's what it's programmed to do. You do need administrative functions, however, so here are the basics.
The SHOW
statement displays information about your database. To get a list of databases in your MariaDB installation:
MariaDB> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
As you can see, there are a few default databases present. You can make one active with the USE
command:
MariaDB> USE mysql;
Database changed
Once you've switched to a database, you can run queries on it. For instance, you can show what tables exist in the database using SHOW
again:
MariaDB> SHOW TABLES;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
[...]
| transaction_registry |
| user |
+---------------------------+
31 rows in set (0.000 sec)
Tables are two-dimensional arrays, sometimes visualized as a spreadsheet with columns and rows. You can see the columns with the SHOW
command, but because there are many tables to choose from, you must specify which table you want to see:
MariaDB> SHOW COLUMNS FROM user;
+-------------+------------+-----+----+--------+------+
| Field | Type | Null| Key| Default| Extra|
+-------------+------------+-----+----+--------+------+
| Host | char(60) | NO | | | |
| User | char(80) | NO | | | |
| Password | longtext | YES | | NULL | |
| Select_priv | varchar(1) | YES | | NULL | |
| Insert_priv | varchar(1) | YES | | NULL | |
| Update_priv | varchar(1) | YES | | NULL | |
[...]
47 rows in set (0.001 sec)
There are a lot of columns in the user
table (47, to be precise), which is a lot of data to handle. Luckily, SQL provides the SELECT
command.
[ Use the Linux system administration skills assessment to learn which training opportunities are right for you. ]
Once you've identified what tables are in a database, you've started to wander into the domain of actual data. Rather than getting all data from a table at once, SQL enables you to select just the parts of a table you care about.
To view the contents of a table, use the SELECT
command—but you have to know what you want to select. You know the columns from the results of your SHOW COLUMNS FROM user;
command. A reasonable column to be curious about is the one called User
, which, as it turns out, contains the usernames of all database users:
MariaDB> SELECT User FROM user;
+-------------+
| User |
+-------------+
| mariadb.sys |
| mysql |
| root |
+-------------+
3 rows in set (0.001 sec)
You can also perform a combined query:
MariaDB> SELECT User,Host FROM user;
+-------------+------------+
| User | Host |
+-------------+------------+
| mariadb.sys | localhost |
| mysql | localhost |
| root | localhost |
+-------------+------------+
3 rows in set (0.001 sec)
Try similar queries for other columns.
As with most login shells, you generally don't want to interact with MariaDB as an unconstrained, privileged root user. Instead, create a user with limited privileges for you or the application that will interact with the database. In this example, you're the one using the database, so create a user and an example password:
MariaDB> CREATE USER 'tux'@'localhost' IDENTIFIED BY 'Example123';
Query OK, 0 rows affected (0.012 sec)
If you're still logged in as root, type exit
to leave the MariaDB prompt. Now that you have a user identity with the database, you can access it as a regular user, but only locally. The syntax to open a new local connection is mostly the same as what you used to connect as root, except that you no longer need sudo
and you use the -p
option to have MariaDB prompt you for a password:
$ mariadb --user tux -p
[sudo] password for tux:
Welcome to the MariaDB monitor. Commands end with ; or \g.
MariaDB>
Remote logins are disabled by default, but you can add a user from a specific known host:
MariaDB> GRANT ALL PRIVILEGES ON *.* TO 'tux'@'192.168.122.31' IDENTIFIED BY 'Example123'
Granting ALL PRIVILEGES
to users isn't always necessary, so consider what permissions a user or an application requires to perform an SQL function. If you're configuring a database specifically for another application's use, as you do for WordPress, Drupal, and so on, then that project's documentation provides a list of privileges required. By limiting privileges granted and tethering an expected user to a specific IP address, you can restrict what damage mistakes or malice can do.
When logging in remotely, you must provide the IP address to your MySQL server using the --host
option and (optionally) the port you connect through with the -P
option:
$ mariadb --user tux -p --host 192.168.122.10 -P 3306
[sudo] password for tux:
Welcome to the MariaDB monitor. Commands end with ; or \g.
MariaDB>
If you're using a virtual machine as your MySQL database, and you're accessing that database from the host, your origin IP address may not be what you think it is. For instance, GNOME Boxes tunnels all traffic through a tap0
gateway, meaning that your virtualized MySQL server receives traffic from tun0
, not your wired or wireless IP network interface.
To access MySQL from outside, you also must configure the server's firewall to permit mysql
traffic.
It's easy to get lost in the vastness of an SQL database. It's a big, empty space filled with potential and a collection of new commands to explore. Don't let it overwhelm you. When setting up a database for an application to use, you can find the vital information you need in the project's documentation. WordPress, for instance, gives you the exact SQL command you need to run on your database to grant WordPress the privileges it needs to create and alter databases and tables. Unless you're building an application from scratch, the most you probably need to know is how to install MariaDB, how to start it, and how to get to an interactive prompt. And now you do.
Seth Kenlon is a UNIX geek and free software enthusiast. More about me