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 on Ezeelogin:

You can also reset the fingerprint by running : php /usr/local/ezlogin/ez_queryrunner.php "UPDATE prefix_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)