Virtually every website depends on a Database Management System to store and serve data to users as they interact with a website or application.
In Linux environments you will commonly see the use of MariaDB or MySQL as the Database Management System.
Such software allows for us to store and manage our site data securely and on an administrative standpoint we are able to interact with that data using command line tools or software such as MySQL Workbench.
In this article, we’ll be going over how you can accomplish some basic tasks within a MariaDB server such as how to list all databases and make a connection to a database.
What is MariaDB?
MariaDB is a widely used database management system that was created by a founding member of the MySQL project.
The basis of MariaDB’s creation was focused on keeping the software free to use and open source.
MariaDB is commonly used in web hosting environments for storing and serving data to users of an application or web site through the use of databases.
Please be aware that any command we cover in this article can be used for MySQL as well.
How to show databases in MariaDB/MySQL (method 1)
1. Connect to MariaDB/MySQL on the command line interface
Before we are able to see all of our databases we will need to first log into our server using SSH and access MariaDB/MySQL.
Using the command line interface on your server as the root user you’ll connect to MySQL/MariaDB with the following command:
Don’t have a password file set for MariaDB/MySQL? You can also specify the user on connection and enter in that users password as shown below:
2. Show databases within a MariaDB server
Now that we’re connecting to MariaDB/MySQL on the command line we can move onto how to list databases in MariaDB.
The following “show databases” command would be used to show every database within MariaDB/MySQL:
How to show databases in MariaDB/MySQL (method 2)
As long as the root password for MariaDB/MySQL is correct within your /root/.my.cnf file, you can view all of your databases without needing to log into the database server.
To list all of your database names without logging into MariaDB/MySQL you would use the following statement/command:
Now that we know how to show a list of all database names within our environment we can move on to some other basics such as connecting to a specific database and showing the tables within it.
How to connect to databases in MariaDB/MySQL
Once we have our list of databases we may want to connect to a specific database in order to craft a query for obtaining some data.
1. Using the “show databases” command to list databases in MariaDB/MySQL
Below we see a database labeled “Sample_DB” that we will connect to:
2. Connect to a certain database with the “use” statement in MariaDB/MySQL
With the “use” statement we can connect to the “Sample_DB” database:
From the above example we see under our command that “database changed”.
This indicates that we have connected to that specific database within our MariaDB/MySQL server.
Now we can interact with this specific database without affecting any others on the server.
How to show database tables within MariaDB/MySQL databases
Maybe you need to run a query on a couple of tables within a database but are not completely sure of the spelling.
In this case we could list our tables for the database to obtain that information.
With the following “show tables” statement we can obtain a list of our tables:
Leave a Reply