How to show active user connections to MySQL or MariaDB server

Managing and monitoring a database server like MySQL or MariaDB involves keeping track of the active user connections. By monitoring these connections, database administrators can ensure that the server is running efficiently, gauge the current load, and identify any potential issues or bottlenecks.

The number of active connections can give insights into how many users or applications are currently interacting with the database. High connection counts might indicate heavy database usage, possible connection leaks in applications, or even potential misuse. Both MySQL and MariaDB offer commands within their interactive shell to help admins monitor and manage these connections.

By using the SHOW command in the MySQL or MariaDB shell, you can display detailed information about each of the active connections, including the user and the actions they are performing.

1. Access the MySQL or MariaDB shell with the appropriate user credentials.

   $ mysql -u root -p
   Enter password:

2. Switch to the database 'mysql'.

   USE mysql;

3. List active connections using the SHOW command.

   SHOW PROCESSLIST;

   The output will display a list of active connections including their ID, User, Host, DB they're connected to, and the Command they're executing, among other details.

4. For a more concise list, only show the active users.

   SELECT user, host FROM processlist;

   This command filters the full PROCESSLIST and shows only the usernames and their associated hosts.

5. To get the count of active connections, use:

   SELECT COUNT(*) as "Active Connections" FROM processlist;

6. Exit the MySQL or MariaDB shell when done.

   EXIT;