Skip to Content

Basic MySQL commands for troubleshooting database related issues in Ezeelogin

Basic MySQL commands for troubleshooting database related issues in Ezeelogin

 

You can get the Ezeelogin database and prefix from the /usr/local/etc/ezlogin/ez.conf.

 

1.Ezeelogin database backup

[email protected]:~#  mysqldump databse_name > database_name_backup.sql -u root -p

Example: 
 

[email protected]:~# mysqldump ezlogin_mbdb > ezlogin_mbdb_backup.sql -u root -p
Enter password:
[email protected]:~# ls 

[email protected]:~# ezlogin_mbdbbackup.sql

 
2. Restore Ezeelogin database
 

mysql -u root -p database_name < /path to database_backup.sql

Example:

[[email protected] ~]# mysql -u root -p 
Enter password:
MariaDB [(none)]> create database ezlogin_cdw;
MariaDB [(none)]> exit

[[email protected] ~]# ls
ezlogin_cdw.backup
[[email protected] ~]# mysql -u root -p ezlogin_cdw < ezlogin_cdw.backup
Enter password:
[[email protected] ~]#

 
3. How to take a backup of a table in the Ezeelogin database
 

mysqldump -u <db_username>  -p db_name table_name > table_name.sql

Example:

[[email protected] ~]# mysqldump -u root -p ezlogin_cdw aihh_sshlogs > aihh_sshlogs.backup
Enter password:
[[email protected] ~]#
[[email protected] ~]# ls
aihh_sshlogs.backup

 
4. How to restore a table in Ezeelogin
 

mysql -u username -p db_name < /path/to/table_name.sql

Example:

[[email protected] ~]# mysql -u root -p ezlogin_cdw < aihh_sshlogs.backup
Enter password:
[[email protected] ~]#

 
5. To update the specific field in a table to null of the admin user
 

update dbprefix_gwactivity_logs set status="" where id=1;

Example:

update aihh_gwactivity_logs set status="" where id=1;

Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0

 

6. To reset fingerprint in cluster tab:

You can also reset the fingerprint by running :  php /usr/local/ezlogin/ez_queryrunner.php " update dbprefix_settings set value="" where name='local_fp' "

update dbprefix_settings set value="" where name='local_fp';

Example:

update aihh_settings set value="" where name='local_fp';

Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0

 
7. To check the count of the remote devices in Ezeelogin
 

select count(*) from dbprefix_servers;

Example:

select count(*) from aihh_servers;

+----------+
| count(*) |
+----------+
| 2        |
+----------+
1 row in set (0.000 sec)

 
8. To display id and the hostname of remote devices in order by name. 
 

select id,name from dbprefix_servers order by name;

Example:

select id,name from aihh_servers order by name;

+----+---------------+
| id | name          |
+----+---------------+
| 1  | centos server |
| 2  | ubuntu server |
+----+---------------+
2 rows in set (0.000 sec)

 
9. Flush Ezeelogin license from database

You can also flush the license from the terminal by the following command: php /usr/local/ezlogin/ez_queryrunner.php "update prefix_settings set value='' where name='ezlel' "

 

update dbprefix_settings set value='' where name='ezlel';

Example:

update aihh_settings set value='' where name='ezlel';

Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0

 
10. To display user details of a particular user in a readable format.
 

select username,status,lpwc_ts,expiry,last_login_at,last_login_from,last_login_type,last_activity from dbprefix_users where username='username'\G;

Example:
 

select username,status,lpwc_ts,expiry,last_login_at,last_login_from,last_login_type,last_activity from aihh_users where username='ezadmin'\G;

*************************** 1. row ***************************
username: ezadmin
status: 1
lpwc_ts: 1633038922
expiry: NULL
last_login_at: 2021-11-07 19:36:43
last_login_from: 192.168.56.1
last_login_type: w
last_activity: 1636313804
1 row in set (0.001 sec)