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.

[email protected] :~# mysql -u root -p

Replace database name and dbprefix with yours. you can find database name and database prefix from /usr/local/etc/ezlogin/ez.conf in ezeelogin gateway server.

mysql > use ezlogin_somz;

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;

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. you can find database name from /usr/local/etc/ezlogin/ez.conf config file.

[email protected]:~# mysqldump -u root -ppassword ezlogin_somz > ezlogin_somz_28_12_2021 .sql

Take MySQL tables dumps having count greater than 200000. you can find dbprefix from /usr/local/etc/ezlogin/ez.conf config file.

[email protected]:~# mysqldump -u root -ppassword ezlogin_somz dbprefix_gwactivity_logs > dbprefix_gwactivity_logs _28_12_2021 .sql

[email protected]:~# mysqldump -u root -ppassword ezlogin_somz dbprefix _serveractivity_logs > dbprefix _serveractivity_logs _28_12_2021 .sql

[email protected]:~# mysqldump -u root -ppassword ezlogin_somz dbprefix _webactivity_logs > dbprefix _webactivity_logs _28_12_2021 .sql

[email protected]:~# mysqldump -u root -ppassword ezlogin_somz dbprefix _sshlogs > dbprefix _sshlogs _28_12_2021 .sql

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

[email protected] :~# mysql -u root -p

mysql > use ezlogin_somz;

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

[email protected]:~# cp dbprefix_gwactivity_logs _28_12_2021 .sql dbprefix_gwactivity_logs _copy.sql

[email protected]:~# cp dbprefix _serveractivity_logs _28_12_2021 .sql dbprefix _serveractivity_logs _copy .sql

[email protected]:~# cp dbprefix _webactivity_logs _28_12_2021 .sql dbprefix _webactivity_logs _copy .sql

[email protected]:~# 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_

[email protected]:~# sed -i 's/ qwe_ / wer_ /g' dbprefix_gwactivity_logs _copy.sql

[email protected]:~# sed -i 's/ qwe_ / wer_ /g' dbprefix _serveractivity_logs _copy .sql

[email protected]:~# sed -i 's/ qwe_ / wer_ /g' dbprefix _webactivity_logs _copy .sql

[email protected]:~# sed -i 's/ qwe_ / wer_ /g' dbprefix _sshlogs _copy .sql

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

[email protected]:~# mysqldump -u root -ppassword ezlogin_serd > ezlogin_serd_28_12_2021 .sql

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

[email protected]:~# mysql -u ezlogin_pyy -p P4&][*V][email protected]]G ezlogin_serd < dbprefix_gwactivity_logs _copy.sql

[email protected]:~# mysql -u ezlogin_pyy -p P4&][*V][email protected]]G ezlogin_serd < dbprefix _serveractivity_logs _copy .sql

[email protected]:~# mysql -u ezlogin_pyy -p P4&][*V][email protected]]G ezlogin_serd < dbprefix _webactivity_logs _copy .sql

[email protected]:~# mysql -u ezlogin_pyy -p P4&][*V][email protected]]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; 