Skip to Content

Migrating Ezeelogin database manually when the table size is large for faster Ezeelogin software upgrades

The Ezeelogin database table will have to be manually migrated when the row count of the database tables _gwactivity_logs, _serveractivity_logs, bprefix_webactivity_logs, _sshlogs exceeds 500,000 entries. The reason for such large row counts in the DB tables would be because of a large number of Ezeelogin gateway users accessing the remote servers.

Below, we have outlined the steps on how to check the table count in the database before performing the upgrade.

 

Run the following command to find out the count for log tables in the Ezeelogin gateway server. Enter the MySQL root password when it prompted

root@gateway:~# mysql -u root -p -e "USE $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf); SELECT 'gwactivity_logs', COUNT(*) FROM $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)gwactivity_logs UNION ALL SELECT 'serveractivity_logs', COUNT(*) FROM $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)serveractivity_logs UNION ALL SELECT 'webactivity_logs', COUNT(*) FROM $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)webactivity_logs UNION ALL SELECT 'sshlogs', COUNT(*) FROM $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)sshlogs UNION ALL SELECT 'authlogs', COUNT(*) FROM $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)authlogs;"

 
For example: 
 

root@prigw:~# mysql -u root -p -e "USE $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf); SELECT 'gwactivity_logs', COUNT(*) FROM $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)gwactivity_logs UNION ALL SELECT 'serveractivity_logs', COUNT(*) FROM $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)serveractivity_logs UNION ALL SELECT 'webactivity_logs', COUNT(*) FROM $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)webactivity_logs UNION ALL SELECT 'sshlogs', COUNT(*) FROM $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)sshlogs;"
Enter password:
+---------------------+----------+
| gwactivity_logs     | COUNT(*) |
+---------------------+----------+
| gwactivity_logs     | 38554    |
| serveractivity_logs | 1545     |
| webactivity_logs    | 12842    |
| sshlogs             | 8145     |
+---------------------+----------+

If the counts of the tables are greater than 500,000 tuples, then take a database dump, table dump, and then truncate the table in the db before performing the upgrade.
 
1. Backup ezeelogin database. Run the following command to take the backup of the Ezeelogin database. Enter the MySQL root password when it prompted.
 

root@jumpserver:~# mysqldump -u root -p $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf) > $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)_28_12_2021.sql

 
Take MySQL tables dumps having a count greater than 200000. you can find the following commands to backup the MySQL tables.
 

root@jumpserver:~# mysqldump -u root -p $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf) $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)gwactivity_logs > $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)gwactivity_logs_28_12_2021.sql

root@jumpserver:~# mysqldump -u root -p $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf) $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)serveractivity_logs > $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)serveractivity_logs_28_12_2021.sql

root@jumpserver:~# mysqldump -u root -p $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf) $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)webactivity_logs > $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)webactivity_logs_28_12_2021.sql

root@jumpserver:~# mysqldump -u root -p $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf) $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)sshlogs > $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)sshlogs_28_12_2021.sql

 
Truncate the tables that have entries of more than 200000 after taking the table dump. you can find dbprefix from /usr/local/etc/ezlogin/ez.conf config file.
Run the command to find the dbprefix

grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf

 
Replace the "dbprefix" before running the following commands.

root@jumpserver:~# mysql -u root -p -e "USE $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf); truncate table $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)gwactivity_logs;"

root@jumpserver:~# mysql -u root -p -e "USE $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf); truncate table $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)serveractivity_logs;"

root@jumpserver:~# mysql -u root -p -e "USE $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf); truncate table $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)webactivity_logs;"

root@jumpserver:~# mysql -u root -p -e "USE $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf); truncate table $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)sshlogs;"

 
Now you can proceed with the Ezeelogin upgrade. Refer to the article to upgrade Ezeelogin.
 
Follow the steps after upgrading ezeelogin
 
1. Replace the old db prefix with the new db prefix(dbprefix will be changed after the upgrade). You can view the new db prefix from /usr/local/etc/ezlogin/ez.conf after upgrade.
 
Take a copy of the MySQL tables dump
 

root@jumpserver:~# cp dbprefix_gwactivity_logs_28_12_2021.sql dbprefix_gwactivity_logs_copy.sql

root@jumpserver:~# cp dbprefix_serveractivity_logs_28_12_2021.sql dbprefix_serveractivity_logs_copy.sql

root@jumpserver:~# cp dbprefix_webactivity_logs_28_12_2021.sql dbprefix_webactivity_logs_copy.sql

root@jumpserver:~# cp dbprefix_sshlogs_28_12_2021.sql dbprefix_sshlogs_copy.sql

 
 
Replace the old db prefix with the new db prefix(dbprefix will be changed after the upgrade). You can view the new db prefix from /usr/local/etc/ezlogin/ez.conf after the upgrade.
 

sed -i 's/old_dbprefix/new_dbprefix/g' copy.sql

 
For example : old_dbprefix : qwe_
new_dbprefix : wer_

root@jumpserver:~# sed -i 's/qwe_/wer_/g' dbprefix_gwactivity_logs _copy.sql

root@jumpserver:~# sed -i 's/qwe_ /wer_/g' dbprefix _serveractivity_logs _copy.sql

root@jumpserver:~# sed -i 's/qwe_ /wer_/g' dbprefix _webactivity_logs _copy.sql

root@jumpserver:~# sed -i 's/qwe_ /wer_/g' dbprefix _sshlogs _copy.sql

 
2. Back up the new Ezeelogin database after the upgrade. you can find the new database name from /usr/local/etc/ezlogin/ez.conf config file.
 

root@jumpserver:~# mysqldump -u root -ppassword ezlogin_serd > ezlogin_serd_28_12_2021.sql

 
3. Restore the old MySQL table dumps (replaced with new_dbprefix) to the new database. Refer to the article to retrieve database credentials after the upgrade
 

root@jumpserver:~# mysql -u ezlogin_pyy -p P4&][*V]Qx3jn3n7A6@p6p7]G ezlogin_serd < dbprefix_gwactivity_logs _copy.sql

root@jumpserver:~# mysql -u ezlogin_pyy -p P4&][*V]Qx3jn3n7A6@p6p7]G ezlogin_serd < dbprefix _serveractivity_logs _copy.sql

root@jumpserver:~# mysql -u ezlogin_pyy -p P4&][*V]Qx3jn3n7A6@p6p7]G ezlogin_serd < dbprefix _webactivity_logs _copy.sql

root@jumpserver:~# mysql -u ezlogin_pyy -p P4&][*V]Qx3jn3n7A6@p6p7]G ezlogin_serd < dbprefix _sshlogs _copy.sql

 
Login to MySQL and check the count of the restored logs table and verify
 

mysql > SELECT COUNT(*) FROM dbprefix_gwactivity_logs;

mysql > SELECT COUNT(*) FROM dbprefix _serveractivity_logs;

mysql > SELECT COUNT(*) FROM dbprefix _webactivity_logs;

mysql > SELECT COUNT(*) FROM dbprefix _sshlogs;

 
Login to Ezeelogin GUI and verify the logs