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;"

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

mysql > use ezlogin_dbname;

mysql > truncate table dbprefix_gwactivity_logs;

mysql > truncate table dbprefix_serveractivity_logs;

mysql > truncate table dbprefix_webactivity_logs;

mysql > truncate table dbprefix_sshlogs;

Now you can proceed with Ezeelogin upgrade. Refer to the article to upgrade Ezeelogin

Follow the steps after upgrading ezeelogin

1. Replace the old db prefix with new db prefix(dbprefix will changed after 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 new db prefix(dbprefix will changed after upgrade). you can view the new db prefix from /usr/local/etc/ezlogin/ez.conf after 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

 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; 