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.
/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;"
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.
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/
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)
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
ls -lah /root/ ezlogin_backup_$(date +%Y-%m-%d)/ *.sql
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
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 ;"
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.
grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf
cp -r /root/ezlogin_backup_$(date +%Y-%m-%d) /root/ezlogin_backup_$(date +%Y-%m-%d)_copy
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
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_upgrade$(date +%Y-%m-%d).sql"
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
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;"
mv /var/log/ezlogin /var/log/ezlogin_upgrade
mv /var/log/ezlogin_backup $(date +%Y-%m-%d) /var/log/ezlogin/
#run below command to verify siem state value and verfiy if it matches after upgrade
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='ezsiem_state';"
+--------------+----------------------------------------------------------------------------------+
| name | value |
+--------------+----------------------------------------------------------------------------------+
| ezsiem_state | q1YqrizOyU9XsqpWSiwtyQAyi0HsnMTiEiUrJWNDQyUQlq+jYWh2l8tSkxOq3NSK1GRUlQYYymprAQ== |
+--------------+----------------------------------------------------------------------------------+
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'"
Related Articles: