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;"
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 |
+---------------------+----------+
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
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
grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf
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;
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
sed -i 's/old_dbprefix/new_dbprefix/g' copy.sql
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
root@jumpserver:~# mysqldump -u root -ppassword ezlogin_serd > ezlogin_serd_28_12_2021.sql
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