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, authlogs 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 , authlogs, gwactivity_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 generate full backup:

/usr/local/sbin/backup_ezlogin.php

Make sure to run the commands inside a screen session to avoid interruptions due to connection timeouts. You can start a screen session with screen -S upgrade
Make sure to synchronize the database before proceeding with the backup.

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 configuration directory. Run the following command to take the backup of the Ezeelogin database. Enter the MySQL root password when it prompted.

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

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

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

Step 3: Backup SSH logs

mv /var/log/ezlogin /var/log/ezlogin_backup $(date +%Y-%m-%d)

cp -pr /var/log/ezlogin_backup $(date +%Y-%m-%d) /var/log/ezlogin_backup_copy$(date +%Y-%m-%d)

Step 4: 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 > /root/ezlogin_backup_$(date +%Y-%m-%d)/ $(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 > /root/ezlogin_backup_$(date +%Y-%m-%d)/ $(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 > /root/ezlogin_backup_$(date +%Y-%m-%d)/ $(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 > /root/ezlogin_backup_$(date +%Y-%m-%d)/ $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf) sshlogs _ $(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) authlogs > /root/ezlogin_backup_$(date +%Y-%m-%d)/$(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf) authlogs _ $(date +%Y-%m-%d) .sql

Verify the database dump after backup

ls -lah /root/ ezlogin_backup_$(date +%Y-%m-%d)/ *.sql

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

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

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

Step 5.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 ;"

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

Step 6: Follow the steps below if SIEM settings are enabled. If SIEM is not enabled, you can skip this step

Step 6.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 and verfiy if it matches after upgrade

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

Step 6.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 7: Now you can proceed with the Ezeelogin upgrade. Refer to the article to upgrade Ezeelogin .

sh ezlogin_7.41.0_php82.bin -- -dbsuser <enter (root)db username> -dbspass <enter_(root)db_password> -auto -force -ACCEPT_SETTINGS -I_ACCEPT_EULA -skipbackup -update

Follow the below steps after upgrading Ezeelogin

Step 8: 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 8.a: Run the below command to find the dbprefix

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

Step 8.b: Take a copy of the backup directory.

cp -r /root/ezlogin_backup_$(date +%Y-%m-%d) /root/ezlogin_backup_$(date +%Y-%m-%d)_copy

Step 8.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.

cd /root/ezlogin_backup_$(date +%Y-%m-%d)_copy/

sed -i 's/old_dbprefix/new_dbprefix/g' copy.sql

For example:

old_dbprefix : cat /root/ ezlogin_backup_$(date +%Y-%m-%d)_copy / 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_dbprefix/new_dbprefix/g' /root/ezlogin_backup_$(date +%Y-%m-%d)_copy/ xxxxx_ gwactivity_logs _xxxx-xx-xx.sql

sed -i 's/old_dbprefix/new_dbprefix/g' /root/ezlogin_backup_$(date +%Y-%m-%d)_copy/ xxxxx_ serveractivity_logs _xx xx-xx-xx .sql

sed -i 's/old_dbprefix/new_dbprefix/g' /root/ezlogin_backup_$(date +%Y-%m-%d)_copy/ xxxxx_ webactivity_logs _xx xx-xx-xx .sql

sed -i 's/old_dbprefix/new_dbprefix/g' /root/ezlogin_backup_$(date +%Y-%m-%d)_copy/ xxxxx_ sshlogs _xx xx-xx-xx .sql

sed -i 's/old_dbprefix/new_dbprefix/g' /root/ezlogin_backup_$(date +%Y-%m-%d)_copy/ xxxxx_ authlogs _xx xx-xx-xx .sql

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

Step 10: 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 and replace with Ezeelogin username and password.

For example:

php /usr/local/ezlogin/eztool.php -show_db_credentials

Enter the Ezeelogin administrator password: *******
Database credentials:
Host: localhost
Port/Socket: /var/lib/mysql/mysql.sock
Database: ezlogin_serd
Username: ezlogin_pyy
Password: P4&][*V]Qx3jn3n7A6@p6p7]G
Table Prefix: vbsrc_

mysql -u ezlogin_pyy -p P4&][*V]Qx3jn3n7A6@p6p7]G ezlogin_serd < /root/ezlogin_backup_$(date +%Y-%m-%d)_copy/ xxxxx_ gwactivity_logs _xxxx-xx-xx.sql

mysql -u ezlogin_pyy -p P4&][*V]Qx3jn3n7A6@p6p7]G ezlogin_serd < /root/ezlogin_backup_$(date +%Y-%m-%d)_copy/ xxxxx_ serveractivity_logs _xx xx-xx-xx .sql

mysql -u ezlogin_pyy -p P4&][*V]Qx3jn3n7A6@p6p7]G ezlogin_serd < /root/ezlogin_backup_$(date +%Y-%m-%d)_copy/ xxxxx_ webactivity_logs _xx xx-xx-xx .sql

mysql -u ezlogin_pyy -p P4&][*V]Qx3jn3n7A6@p6p7]G ezlogin_serd < /root/ezlogin_backup_$(date +%Y-%m-%d)_copy/ xxxxx_ sshlogs _xx xx-xx-xx .sql

mysql -u ezlogin_pyy -p P4&][*V]Qx3jn3n7A6@p6p7]G ezlogin_serd < /root/ezlogin_backup_$(date +%Y-%m-%d)_copy/ xxxxx_ authlogs _xx xx-xx-xx .sql

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

Step 12: Restore SSH logs

mv /var/log/ezlogin /var/log/ezlogin_upgrade

mv /var/log/ezlogin_backup $(date +%Y-%m-%d) /var/log/ezlogin/

Step 13: Run the command to fix the log permission

 /usr/local/ezlogin/eztool.php -fix_log_permissions 

Step 14: Follow below step only if you have disabled SIEM settings in step6 or you can skip this step.

Step 14.a: Run below command to check the value of siem state.

#run below command to verify siem state value and verfiy if it matches after upgrade

Step 14.b: Run below command to enable it.

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

Step 14: Log in to the Ezeelogin GUI and backend. Verify that you are able to log in to servers at random and check if you can view the logs, etc.

Related Articles:

Upgrade Ezeelogin Jump server to the latest version

Migrate Ezeelogin database to RDS / remote SQL instance

How to retreive db credentials?