How to show active user connection to MySQL or MariaDB server

Databases play a crucial role in storing and managing data for various applications. MySQL and MariaDB are two popular open-source relational database management systems (RDBMS) that are widely used across different industries. Both systems are built on a client-server architecture, allowing multiple users to connect and interact with the database concurrently. Monitoring active user connections is essential for administrators to maintain database performance, security, and stability.

Understanding active user connections on your MySQL or MariaDB server can help you identify potential performance bottlenecks, troubleshoot issues, and ensure that your resources are being used efficiently. By monitoring these connections, administrators can keep track of the number of users connected to the database, the queries being executed, and the overall performance of the system.

In this step-by-step guide, we will walk you through the process of showing active user connections on MySQL and MariaDB servers. The instructions provided are applicable for both database systems since MariaDB is a fork of MySQL and they share many similarities. By following these steps, you will be able to effectively monitor the connections on your server and make informed decisions about resource allocation, performance tuning, and security.

1. Open a terminal window or command prompt on your system.
2. Connect to your MySQL or MariaDB server using the following command.

   mysql -u root -p

   Replace “root” with your MySQL or MariaDB username. You will be prompted to enter your password.

3. Once connected, you can check the current user connections by executing the following command.

   SHOW PROCESSLIST;

4. If you want to filter the output to display only active connections, use the following command.

   SHOW FULL PROCESSLIST WHERE Command != 'Sleep';

   This command will exclude the connections in the 'Sleep' state, which are idle connections.

5. For a more concise and user-friendly view of active connections, you can run the following query.

   SELECT user, host, db, command, time, state, info
   FROM information_schema.processlist
   WHERE command != 'Sleep';

6. To monitor the active connections periodically, you can use the “watch” command on Linux systems or manually execute the queries in steps 3, 4, or 5 on a regular basis.
7. If you want to limit the number of concurrent connections for a specific user, you can set the “max_user_connections” variable in the “my.cnf” (MySQL) or “my.ini” (MariaDB) configuration file. For example, to limit the connections for the user “myuser” to 10, add the following line under the [mysqld] section.

   max_user_connections_myuser = 10

8. Save the changes to the configuration file and restart the MySQL or MariaDB server for the changes to take effect.