View Complete Server and User list via MySQL.
How to view the complete Server and User list via MySQL?
Overview: This article offers detailed instructions on how to view the complete server and user lists through MySQL.
To display the full server and user list via MySQL follow the below steps:
Step 1: Login to MySQL
Note: The database name can be found in the Ezeelogin configuration file located at /usr/local/etc/ezlogin/ez.conf.
:~# mysql -u root -p
mysql > use db_name;
Step 2: Use the following query to view the complete user list. Replace dbprefix_ with the correct prefix found in /usr/local/etc/ezlogin/ez.conf
MariaDB [db_name]> SELECT A.username AS username, A.firstname AS firstname, A.lastname AS lastname, A.email AS email, B.name AS usergroup FROM dbprefix_users AS A INNER JOIN dbprefix_usergroups AS B ON A.usergroup_id = B.id;
+----------------+----------------+----------+--------------------------+-----------+
| username | firstname | lastname | email | usergroup |
+----------------+----------------+----------+--------------------------+-----------+
| admin | Administrator | NULL | | Admins |
| adam | adam | | [email protected] | Dummy |
| luke | luke | | luke | Dummy |
| chris | chris | | [email protected] | Dummy |
+----------------+----------------+----------+--------------------------+-----------+
Step 3: Use the following query to view the complete server list. Replace dbprefix_ with the correct prefix found in /usr/local/etc/ezlogin/ez.conf
MariaDB [db_name]> SELECT A.name AS name, A.description AS description, A.ssh_port AS ssh_port, B.name AS servergroup, C.ip AS IPAddress FROM dbprefix_servers AS A INNER JOIN dbprefix_servergroups AS B ON A.servergroup_id = B.id INNER JOIN dbprefix_ips AS C ON A.id = C.server_id;
+------------+-------------+----------+--------------+----------------+
| name | description | ssh_port | servergroup | IPAddress |
+------------+-------------+----------+--------------+----------------+
| centos8 | | 22 | LinuxServers | 192.168.56.194 |
| centos7 | | 22 | LinuxServers | 192.168.56.194 |
| Windows | | 22 | LinuxServers | 192.168.56.181 |
| production | | 22 | LinuxServers | 192.168.56.194 |
+------------+-------------+----------+--------------+----------------+
Note: You have to use the correct db_name and dbprefix. You can find it from /usr/local/etc/ezlogin/ez.conf
Related Articles:
How to view database connectivity credentials and configuration file?
Basic MySQL commands for troubleshooting database related issues in Ezeelogin