CentOSDebianLinuxLinux MintUbuntu

How To Show List All Databases on MySQL

Show List All Databases on MySQL

In this tutorial, we will show you how to show list all databases on the MySQL Linux system. MySQL is one of the most popular open-source relational database management systems used today. With MySQL, you can create databases to store, organize, and manage data for applications and websites. As a MySQL user or administrator, it is important to understand how to view and manage the databases on your MySQL server. One of the most basic tasks is to list all existing databases on the server. This allows you to see what databases are present, ensure they are all accounted for, and determine if any maintenance or changes need to be made.

This article assumes you have at least basic knowledge of Linux, know how to use the shell, and most importantly, you host your site on your own VPS. The installation is quite simple and assumes you are running in the root account, if not you may need to add ‘sudo‘ to the commands to get root privileges. I will show you the step-by-step show list of MySQL databases on Linux.

Prerequisites

  • A server running one of the following operating systems: Ubuntu 22.04, 20.04, and any other Debian-based distribution like Linux Mint.
  • Basic knowledge of MySQL and database administration.
  • Access to the MySQL server, either locally or remotely.
  • A user account on the MySQL server with adequate permissions.

Show List All Databases on MySQL

Step 1. First, make sure that all your system packages are up-to-date by running the following apt commands in the terminal.

sudo apt update
sudo apt upgrade

Step 2. Show List All Databases on MySQL

To show databases in MySQL, you will need to log in to the MySQL/MariaDB shell with the root user as shown below:

$ mysql –u username –p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.8.64-MySQL-ubuntu0.22.04 (Ubuntu)
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)]>

From within the MySQL shell execute the following command:

MySQL [(none)]> show databases;

Output:

+--------------------+
| Database |
+--------------------+
| information_schema |
| drupal |
| magento |
| mysql |
| owncloud |
| performance_schema |
| wordpressdatabase |
+--------------------+
7 rows in set (0.01 sec)

The default output from SHOW DATABASES displays database names in a single-column table. Each row represents one database managed by the MySQL server. Some details to note about the output:

  • Database names have no schema or namespace. They are globally unique names.
  • The databases are not shown in any particular order. Do not rely on the order or placement of any database in the list.
  • The output may contain a mix of system and user-created databases. System databases start with mysql or information_schema.
  • The list reflects all databases the user account has permission to see. Restricted databases are omitted.

Next, if you want to use a specific database and list all tables in it, you can use the following commands:

MySQL [(none)]> use mysql;

Next, list available tables with the following command:

MySQL [mysql]> show tables;

Output:

+---------------------------+
| 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 |
+---------------------------+

If you want to find out about the structure of a specific table you can use the DESCRIBE statement in MySQL:

MySQL [(none)]> DESCRIBE user;

Step 3. Best Practices and Considerations.

  1. Fortify Security: Ensure robust passwords for MySQL to safeguard your databases from unauthorized access.
  2. Backup Priorities: Before undertaking any database actions, create backups to mitigate potential data loss.
  3. Efficiency Boost: Employ aliases and shortcuts for swift database navigation and management.

Step 4. Troubleshooting and Common Errors.

  1. Access Denied: If you encounter an “Access Denied” error, verify your credentials and permissions.
  2. Command Not Found: If MySQL isn’t recognized, ensure it’s properly installed on your Linux system.
  3. Blank Output: A blank output might indicate no databases or an issue with MySQL. Double-check your connection.

Step 5. Advanced Techniques.

  1. Custom Queries: Craft targeted SQL queries to extract specific data for advanced SEO analysis.
  2. Data Aggregation: Utilize SQL’s aggregate functions to gather and process SEO-related data.

Congratulations! You have successfully shown all databases MySQL. Thanks for using this tutorial for installing the MySQL server in Ubuntu 20.04 Focal Fossa system. For additional help or useful information, we recommend you check the official MySQL website.

VPS Manage Service Offer
If you don’t have time to do all of this stuff, or if this is not your area of expertise, we offer a service to do “VPS Manage Service Offer”, starting from $10 (Paypal payment). Please contact us to get the best deal!

r00t

r00t is a seasoned Linux system administrator with a wealth of experience in the field. Known for his contributions to idroot.us, r00t has authored numerous tutorials and guides, helping users navigate the complexities of Linux systems. His expertise spans across various Linux distributions, including Ubuntu, CentOS, and Debian. r00t's work is characterized by his ability to simplify complex concepts, making Linux more accessible to users of all skill levels. His dedication to the Linux community and his commitment to sharing knowledge makes him a respected figure in the field.
Back to top button