How to get started with MySQL and MariaDB

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.

Client and server

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.

Install MySQL

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) 

Start the database service

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

Connect to your database

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> 

Skip to the bottom of list

Image

Download now

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.

Show data with SHOW

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. ]

Query tables with SELECT

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.

Create a new user with CREATE

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> 

Grant remote privileges with GRANT

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.

Tend to your firewall

To access MySQL from outside, you also must configure the server's firewall to permit mysql traffic.

Database interactions

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.