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.
Login to MySQL
[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.
[email protected]:~# mysqldump -u root -ppassword ezlogin_somz > ezlogin_somz_28_12_2021 .sql
[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
[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;
[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
sed -i 's/old_dbprefix/new_dbprefix/g' copy.sql
[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
[email protected]:~# mysqldump -u root -ppassword ezlogin_serd > ezlogin_serd_28_12_2021 .sql
[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