MariaDB – Navigating your way around a MySQL Database

In this article we’re going to show some simple MySQL commands to help you navigate your way around databases and tables, and also view a table’s content. First we create a new mysql session:

[root@mariadb-client ~]# mysql -u root -p'rootpassword' -h mariadb-server.example.com 

Then let’s take a look at what dbs currently exist:

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

Note: Here we ran a command as opposed to a sql query. by convention these commands are always written in upper case. Whereas sql queries can be a mix of upper and lower cases.

Here we have 3 databases that comes as default. We can start querying one of these databases, but before we can do that we first need to select the database we’re interested in:

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
MariaDB [mysql]>

Notice that the command prompt has changed. Now we can list the tables that this database has:

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)

Next if you want to see a list of columns that makes up the ‘user’ table then we do:

MariaDB [mysql]> SHOW COLUMNS FROM user;
+------------------------+-----------------------+------+-----+---------+-------+
| Field                  | Type                  | Null | Key | Default | Extra |
+------------------------+-----------------------+------+-----+---------+-------+
| Host                   | char(60)              | NO   | PRI |         |       |
| User                   | char(16)              | NO   | PRI |         |       |
| Password               | char(41)              | NO   |     |         |       |
| Select_priv            | enum('N','Y')         | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')         | NO   |     | N       |       |
| Update_priv            | enum('N','Y')         | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')         | NO   |     | N       |       |
| Create_priv            | enum('N','Y')         | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')         | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')         | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')         | NO   |     | N       |       |
| Process_priv           | enum('N','Y')         | NO   |     | N       |       |
| File_priv              | enum('N','Y')         | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')         | NO   |     | N       |       |
| References_priv        | enum('N','Y')         | NO   |     | N       |       |
| Index_priv             | enum('N','Y')         | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')         | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')         | NO   |     | N       |       |
| Super_priv             | enum('N','Y')         | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')         | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')         | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')         | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')         | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')         | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')         | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')         | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')         | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')         | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')         | NO   |     | N       |       |
| Event_priv             | enum('N','Y')         | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')         | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')         | NO   |     | N       |       |
| ssl_type               | enum('','ANY')        | NO   |     |         |       |
| ssl_cipher             | blob                  | NO   |     | NULL    |       |
| x509_issuer            | blob                  | NO   |     | NULL    |       |
| x509_subject           | blob                  | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned      | NO   |     | 0       |       |
| max_updates            | int(11) unsigned      | NO   |     | 0       |       |
| max_connections        | int(11) unsigned      | NO   |     | 0       |       |
| max_user_connections   | int(11)               | NO   |     | 0       |       |
| plugin                 | char(64)              | NO   |     |         |       |
| authentication_string  | text                  | NO   |     | NULL    |       |
+------------------------+-----------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)

Note, an alternative command we could have used is DESCRIBE user;. This would have shown the same output as above.

Now we going to view contents of a table. Here’s how to view the Host, User, and Password fields of the user table, we do:

MariaDB [mysql]> SELECT Host,User,Password FROM user;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *B638EC5422004FCF44EE84FABA603D29A2259BC0 |
| 127.0.0.1 | root | *B638EC5422004FCF44EE84FABA603D29A2259BC0 |
| ::1       | root | *B638EC5422004FCF44EE84FABA603D29A2259BC0 |
| 10.2.5.12 | root | *B638EC5422004FCF44EE84FABA603D29A2259BC0 |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)

MariaDB [mysql]>

If we wanted to view the entire table then we would do:

MariaDB [mysql]> SELECT * FROM user;

Take the RHCSA Quiz

This article is part of our RHCSA Study guide (click on the yellow tab on the far left). By the end of this article you should be able to answer the following questions:


What is the mysql query to get a list of all column names for the table 'user'?

SHOW COLUMNS FROM user;
# or
DESCRIBE user;

What is the mysql command to show the Host, User, and Password columns from the table 'user'?

SELECT Host,User,Password FROM user;

What is the mysql command to show all columns for the table 'user'?

SELECT * FROM user;