# Introduction to MariaDB and MySQL Many applications, whether they're running on your laptop or on a server, have the need to save data. Sometimes it's enough to just dump data into files within a structured file system, but sometimes it's easier to record data in a database. If you plan on setting up a web application like Drupal, Wordpress, Bugzilla, and many others, then you're probably going to also need to make a database available. 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 langauge. For this reason, there's usually an Application Programming Interface (API) for MySQL (sometimes simply called "bindings") for Java, Python, Lua, PHP, Ruby, C++, and many many others. However, before using an API, it helps to have an understanding of what's actually happening with the database engine. ## Client and server For web applications, databases are often, although not always, run on a dedicated database server. Your web app can access your database in a similar way that you access remote computers over 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 demonstrate a local MySQL install. The process for a remote one is essentially the same, except a few privileges required to access a database outside of `localhost`. I make not of these differences when necessary. ## Install The open source implementation of MySQL is MariaDB. To install MariaDB on a RHEL, CentOS, or Fedora server: ```bash [server]$ sudo dnf install mariadb mariadb-server ``` Were you also setting up a client machine, you would also install the client software on that machine: ```bash [client]$ sudo dnf install mariadb ``` You can confirm the install using the `--version` option. MariaDB responds to either the `mariadb` or `mysql` command: ```bash $ 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) ``` ## Starting the database service You've installed a database engine, but you haven't yet started it. To start it and to set it to autostart after a reboot: ```bash [server]$ sudo systemctl enable --now mariadb ``` Alternately, you can just start MariaDB until you either reboot or stop it manually: ```bash [server]$ sudo systemctl start mariadb ``` ## Connecting to your database After installation, you can open an interactive MariaDB session as `root` with the `--user` option: ```bash [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, exactly, 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, then that application handles most of the SQL for you, because that's what it's been 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 present in your MariaDB install: ```bash 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: ```bash 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: ```bash MariaDB [mysql]> SHOW TABLES; +---------------------------+ | Tables_in_mysql | +---------------------------+ | column_stats | | columns_priv | | db | | event | [...] | transaction_registry | | user | +---------------------------+ 31 rows in set (0.000 sec) ``` Tables are 2-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: ```bash 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. ## Query tables with SELECT Once you've identified what tables are present 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, you 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: ```bash MariaDB> SELECT User FROM user; +-------------+ | User | +-------------+ | mariadb.sys | | mysql | | root | +-------------+ 3 rows in set (0.001 sec) ``` You can also perform a combined query: ```bash 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 don't generally want to interact with MariaDB as an unconstrained, ultimately-priviledged root user. Instead, create a user with limited privileges for you or for the application that's going to interact with the database. In this example, you're using the database yourself, so create a user and an example password: ```bash 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. To open a new local connection, the syntax 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: ```bash $ mariadb --user tux -p [sudo] password for tux: Welcome to the MariaDB monitor. Commands end with ; or \g. MariaDB [(none)]> ``` ## Granting remote privileges with GRANT Remote logins are disabled by default, but you can add a user from a specific known host: ```bash 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 absolutely requires to perform an SQL function. If you're configuring a database specifically for another application's use, as you do for Wordpress or Drupal and so on, then that project's documentation provides a list of privileges required. By limiting privileges granted, and by tethering an expected user to a specific IP address, you greatly 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: ```bash $ 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 [(none)]> ``` If you're using a virtual machine as your MySQL database, and you're accessing that database from the host, then 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 In order to access MySQL from outside, you also must [configure the server's firewall](https://www.redhat.com/sysadmin/secure-linux-network-firewall-cmd) to permit `mysql` traffic. ## Database interactions It's easy to get lost 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, the vital information you need is 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.