In this post, I will show you how to list all databases on MySQL or MariaDB.
There are multiple cases where theses commands will come in handy, for example listing all the MySQL databases you might need to backup.
mysqlshow
mysqlshow
is a little client that can be used to easily show databases, tables and even more.
mysqlshow provides a command-line interface to several SQL
SHOW
statements. See Section 13.7.7, “SHOW Statements”. The same information can be obtained by using those statements directly. For example, you can issue them from the mysql client program.
To show all databases using mysqlshow
, run below command in your terminal:
mysqlshow -u YOUR_USER_NAME -p
You will see a list of tables similar to this:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wordpress_project |
+--------------------+
5 rows in set (0.00 sec)
This comes in handy when you need to execute this from scripts and can easily be filtered from there.
SHOW DATABASES
.First, you'll need to access the MySQL database and then execute the SHOW DATABASES
command.
To open the MySQL shell, run the following command:
mysql -u user -p
Then run the following command:
SHOW DATABASES
You will see a list of tables similar to this:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wordpress_project |
+--------------------+
5 rows in set (0.00 sec)
SHOW DATABASES
The SHOW DATABASES
command can be filtered using the LIKE
clause.
SHOW DATABASES LIKE 'wordpress%';
All databases with a name starting with 'wordpress' will be returned
+--------------------+
| Database |
+--------------------+
| wordpress_project |
+--------------------+
1 rows in set (0.00 sec)
information_schema
The INFORMATION_SCHEMA
database (sometimes referred to as a data dictionary or a system catalogue), provides access to database metadata, such as the name of a database or table, the data type of a column, or access privileges.\
\
This table can also be used to query the list of databases, with more flexibility than the SHOW DATABASES
described above.
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE 'wordpress%' OR
schema_name LIKE 'wp%';
Free 7-day trial. No credit card required.
Not convinced yet? Need help?
Get in touch via chat or at [email protected]