Basic MySQL or MariaDB Installation

• Databases are great—they keep Database Administrators (DBAs) in jobs, and they provide a convenient way of storing data on a system that isn’t a series of randomly sized flat files in a directory.
• Traditionally, databases have been a great place to store ordered data of specific type and size, meaning that you get databases backing all sorts of things, from bank transaction records to website inventory numbers.
• SQL databases are what people are most familiar with (more on NoSQL databases later), and one of the most common is MariaDB, which is a fork of MySQL.
• If you run a WordPress site, you may have already come across MariaDB or MySQL because they’re the databases of choice for most people when setting up that particular blogging platform.

How to do it…

• It’s considered a good practice to install software before you try to interact with it. Bearing this in mind, install mariadb-server:

sudo yum install mariadb-server -y

• Next, ensure that it’s started and configured to start at boot (this can take a few seconds)

sudo systemctl enable --now mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

• Before we go any further, run the mysql_secure_installation script:

$ mysql_secure_installation

• This will present you with a series of prompts. Answer them as follows:

    Enter current password for root (enter for none): <BLANK, HIT ENTER>
    Set root password? Y
    New password: examplerootpassword
    Remove anonymous users? Y
    Disallow root login remotely? Y
    Remove test database and access to it? Y
    Reload privilege tables now? Y

• Next, log in to your database:

$ mysql -uroot -pexamplerootpassword
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> 

• Listing, creating, and selecting databases and tables
• From inside your new prompt, you’re now able to list the databases within MariaDB (confusing I know, but the database server (MariaDB) can have multiple databases that it manages):

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

• We want to take a look at the built-in mysql database, so let’s flip to that:

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

• Once we’re using this database, we can list the tables inside it:

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)

• Listing, creating, and selecting databases and tables

From inside your new prompt, you’re now able to list the databases within MariaDB (confusing I know, but the database server (MariaDB) can have multiple databases that it manages):

MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
+——————–+
3 rows in set (0.00 sec)

We want to take a look at the built-in mysql database, so let’s flip to that:

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Once we’re using this database, we can list the tables inside it:

MariaDB [mysql]> show tables;
+—————————+
| Tables_in_mysql |
+—————————+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+—————————+
24 rows in set (0.00 sec)

• Now we can get information about specific tables. Here, we’re getting the Host, User, and Password from the user table:

MariaDB [mysql]> select Host,User,Password from user;
+-----------+------+-------------------------------------------+
| Host | User | Password |
+-----------+------+-------------------------------------------+
| localhost | root | *F61E89B5042AB6D880D5BA79586B46BA93FABF09 |
| 127.0.0.1 | root | *F61E89B5042AB6D880D5BA79586B46BA93FABF09 |
| ::1 | root | *F61E89B5042AB6D880D5BA79586B46BA93FABF09 |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)

• On top of this, we can create our own database and tables, too.
• Let’s create exampledb as a database:

MariaDB [mysql]> create database exampledb;
Query OK, 1 row affected (0.00 sec)

• We can then use this database and add a table:

MariaDB [mysql]> use exampledb;
Database changed
MariaDB [exampledb]> create table exampletable (food varchar(10), goodbad char(1));
Query OK, 0 rows affected (0.00 sec)

• Let’s describe the table we just created, looking at our fields:

MariaDB [exampledb]> describe exampletable;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| food | varchar(10) | YES | | NULL | |
| goodbad | char(1) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

• Next, populate it with some data:

MariaDB [exampledb]> insert into exampletable values ('strawberries','b');
Query OK, 1 row affected, 1 warning (0.00 sec)

• Now we can see what we’ve just put into our table by selecting the contents:

MariaDB [exampledb]> select * from exampletable;
+------------+---------+
| food | goodbad |
+------------+---------+
| strawberri | b |
+------------+---------+
1 row in set (0.00 sec)

• To exit your database, type exit (or hit CTRL + D):

MariaDB [exampledb]> exit
Bye
$

• We installed MariaDB on our system and started it. As a result of that, we created several databases and associated data in the /var/lib/mysql directory:

$ ls /var/lib/mysql
aria_log.00000001 exampledb ib_logfile0 mysql performance_schema
aria_log_control ibdata1 ib_logfile1 mysql.sock

• After running through the secure setup script, we then entered our database using the password we’d just set:

$ mysql -uroot -pexamplerootpassword

• This dropped us into a completely different shell, one within the MariaDB program, which enabled us to manipulate the databases that MariaDB controls.
• We created a database and a table inside it after a bit of poking around:

MariaDB [mysql]> create database exampledb;
MariaDB [exampledb]> create table exampletable (food varchar(10), goodbad char(1));

• The table we specifically created had the name exampletable. We gave it two fields: a food field and a goodbad field.
• We then inserted some data into the database:

MariaDB [exampledb]> insert into exampletable values ('strawberries','b');

• As it happens, because we set the food field to be a varchar of 10, the strawberries entry was too long, leading it to be cut off:

MariaDB [exampledb]> select * from exampletable;
+------------+---------+
| food | goodbad |
+------------+---------+
| strawberri | b |
+------------+---------+
1 row in set (0.00 sec)

• This highlights one of the good things about traditional SQL databases, where you can be very granular about the type of data that’s stored in each field and how much data you can store there.
• This database now exists on our filesystem, as seen when we listed the mysql directory previously:

sudo ls /var/lib/mysql/exampledb/
db.opt exampletable.frm