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

How to manually migrate Ezeelogin databases for faster Ezeelogin upgrade?

Overview: This article helps Ezeelogin admin users to manually migrate Ezeelogin databases when the row count of the database tables gwactivity_logs, serveractivity_logs, webactivity_logs, sshlogs exceeds 500,000 entries for faster Ezeelogin upgrade.

Manual migration of the Ezeelogin database becomes necessary when the row count in tables such as serveractivity_logs, webactivity_logs, and sshlogs exceeds 500,000 entries. This large row count typically results from a large number of Ezeelogin gateway users accessing remote servers.

Note: Its recommended to take the full backup of Ezeelogin installation before manual migrate to avoid risk of data loss.

Run below command to take full backup: /usr/local/sbin/backup_ezlogin.php

Step 1: Run the following command to find out the count for log tables in the database of the Ezeelogin gateway server before performing the upgrade. Enter the MySQL root password when it prompted

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:

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, first take a database dump, table dump, and then truncate the table in the database before performing the upgrade.

Step 2: Backup ezeelogin database and ezlogin directory. Run the following command to take the backup of the Ezeelogin database. Enter the MySQL root password when it prompted.

mkdir ezlogin_backup_$(date +%Y-%m-%d)/ezlogin -p

db_name=$(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf) && file="${db_name}_old_$(date +%Y-%m-%d).sql" && mysqldump -u root -p "$db_name" > "$file" && cp "$file" "ezlogin_backup_$(date +%Y-%m-%d)/"

cp /usr/local/etc/ezlogin/* ezlogin_backup_$(date +%Y-%m-%d)/ezlogin

Step 3: Take MySQL table dumps having a count greater than 200000. Run the following commands to backup the MySQL tables.

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_$(date +%Y-%m-%d).sql

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_$(date +%Y-%m-%d).sql

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_$(date +%Y-%m-%d).sql

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_$(date +%Y-%m-%d).sql

Step 4: After taking the table dump, truncate the tables that have entries more than 200000. Find dbprefix from /usr/local/etc/ezlogin/ez.conf config file.

Step 4.a: Run the below command to find the dbprefix

grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf > old_dbprefix_$(date +%Y-%m-%d).txt

Step 4.b: Run below commands to truncate tables.

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

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

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

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

Step 5: Follow below step if SIEM settings is enabled.

Step 5.a: Run below command to check SIEM is enabled or not and the value of siem state. If the output is 1, it is enabled, and if the value is 0, it is disabled.

#run below command to check if siem enabled or not

mysql -u root -p -e "USE $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf); select name,value from $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)settings where name='siem_enable';"

+-------------+-------+
| name | value |
+-------------+-------+
| siem_enable | 1 |
+-------------+-------+

#run below command to verify siem state value

+--------------+----------------------------------------------------------------------------------+
| name | value |
+--------------+----------------------------------------------------------------------------------+
| ezsiem_state | q1YqrizOyU9XsqpWSiwtyQAyi0HsnMTiEiUrJWNDQyUQlq+jYWh2l8tSkxOq3NSK1GRUlQYYymprAQ== |
+--------------+----------------------------------------------------------------------------------+

Step 5.b: If its enabled, run below command to disable it.

php /usr/local/ezlogin/ez_queryrunner.php "update prefix_settings SET value= 0 WHERE name = 'siem_enable'"

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

Follow the below steps after upgrading Ezeelogin

Step 7: Replace the old_db_prefix with the new_db_prefix(db_prefix will be changed after the upgrade). You can view the new db_prefix from /usr/local/etc/ezlogin/ez.conf after upgrade.

Step 7.a: Run the below command to find the dbprefix

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

Step 7.b: Take a copy of the table dumps to backup directory.

cp *_gwactivity_logs_$(date +%Y-%m-%d).sql ezlogin_backup_$(date +%Y-%m-%d)

cp *_serveractivity_logs_$(date +%Y-%m-%d).sql ezlogin_backup_$(date +%Y-%m-%d)

cp *_webactivity_logs_$(date +%Y-%m-%d).sql ezlogin_backup_$(date +%Y-%m-%d)

cp *_sshlogs_$(date +%Y-%m-%d).sql ezlogin_backup_$(date +%Y-%m-%d)

Step 7.c: Run below command to 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 : cat old_dbprefix_$(date +%Y-%m-%d).txt
new_dbprefix : grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf

sed -i 's/old_/new_/g' xxxxx_gwactivity_logs_xxxx-xx-xx.sql

sed -i 's/old_/new_/g' xxxxx_serveractivity_logs_xxxx-xx-xx.sql

sed -i 's/old_/new_/g' xxxxx_webactivity_logs_xxxx-xx-xx.sql

sed -i 's/old_/new_/g' xxxxx_sshlogs_xxxx-xx-xx.sql

Step 8: Backup the new Ezeelogin database after the upgrade. You can find the new database name from /usr/local/etc/ezlogin/ez.conf config file.

db_name=$(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf) && file="${db_name}_new_$(date +%Y-%m-%d).sql" && mysqldump -u root -p "$db_name" > "$file" && cp "$file" "ezlogin_backup_$(date +%Y-%m-%d)/"

Step 9: 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,

mysql -u ezlogin_pyy -p P4&][*V]Qx3jn3n7A6@p6p7]G ezlogin_serd < xxxxx_gwactivity_logs_xxxx-xx-xx.sql

mysql -u ezlogin_pyy -p P4&][*V]Qx3jn3n7A6@p6p7]G ezlogin_serd < xxxxx_serveractivity_logs_xxxx-xx-xx.sql

mysql -u ezlogin_pyy -p P4&][*V]Qx3jn3n7A6@p6p7]G ezlogin_serd < xxxxx_webactivity_logs_xxxx-xx-xx.sql

mysql -u ezlogin_pyy -p P4&][*V]Qx3jn3n7A6@p6p7]G ezlogin_serd < xxxxx_sshlogs_xxxx-xx-xx.sql

Step 10: Login to MySQL and check the count of the restored logs table and verify.

Step 11: Login to Ezeelogin GUI and verify the logs.

Related Articles:

Upgrade Ezeelogin Jump server to the latest version

Migrate Ezeelogin database to RDS / remote SQL instance

How to retreive db credentials?