SysAdmin CheatSheet
Mysql DB Restore - Manual
- Make sure no one is in the server.
- Create a temporary working directory for the restoration, like: /home/temp/aroy/
- Restore everything from “/var/lib/mysql” from Acronis to “/home/temp/aroy/”
- Create a variable named “dir” in your terminal instance so you can work using it later in the upcoming steps:
dir=/home/temp/aroy
- Change the ownership of your working directory to MySQL user so that you can spin up another MySQL instance from that directory:
chown mysql. $dir -R
1
2
3
4
5
6
7
8
9
10
11
| command to spin up the MySQL instance:
mysqld \
--datadir=/home/temp/aroy/mysql \
--socket=/home/temp/aroy/mysql/socket.mysql \
--pid-file=/home/temp/aroy/mysql/mysql.pid \
--log-error=/home/temp/aroy/mysql/67-43-14-17.cprapid.com.err \
--skip-grant-tables \
--skip-networking \
--user=mysql \
--innodb-force-recovery=4 &
|
- Make sure the database you’re trying to restore is actually present in the secondary MySQL instance running from the $dir; command to do that:
1
2
3
|
mysql -h localhost -S /home/temp/aroy/mysql/socket.mysql -e 'show databases;' | grep 'Database_name'
**This command should output the same database name.**
|
Now we can dump the required database or the table from the secondary MySQL instance that’s running from the restored files in $dir; commands to do that:
1
2
3
4
| Entire Database:
mysqldump -h localhost -S $dir/socket.mysql #target_db > database.sql
Specific Table:
mysqldump -h localhost -S $dir/socket.mysql #target_db #target_table > database_table.sql
|
Now we can simply restore the dump file to the actual database:
1
| mysql dbrestore_wp400 < wpyu_users.sql
|
Kill/shutdown the secondary MySQL instance with the command:
1
2
|
mysqladmin -h localhost -S $dir/socket.mysql shutdown
|
And remove your restored files:
1
2
|
rm -rf /home/temp/aroy
|
NOTE: You may face this error:
ERROR 1813 (HY000) at line 25: Tablespace for table ‘dbrestore_wp400.wpyu_users’ exists. Please DISCARD the tablespace before IMPORT
Fix for that:
1
2
3
4
5
6
7
| look out for that table in the original database folder:
cd /var/lib/mysql/dbrestore_wp400
ls -al | grep wpyu_users
-rw-rw---- 1 mysql mysql 114688 Nov 8 18:59 wpyu_users.ibd
|
If you only see an .ibd file and not .frm file for the same table then, move out the .ibd file as well:
mv wpyu_users.ibd wpyu_users.ibd_lwbk
Now try to restore the dump again, and it should go fine unless you face some other weird errors.
Method - 2
- Make sure no one is in the server.
- Create a temporary working directory for the restoration, like: /home/temp/aroy/
- Restore everything from “/var/lib/mysql” from Acronis to “/home/temp/aroy/”
- Just for safety, backup the current MySQL data directory:
cp -ar /var/lib/mysql /var/lib/mysql_lw_aroy_bk
- Stop the MySQL service:
systemctl stop mariadb.service
- Rsync the restored directory with the live directory:
rsync -avHlp /home/temp/aroy/* /var/lib/mysql
- And start the service:
systemctl start mariadb.service
- Eveything should run fine after this, and if it does, remove the restoration directory:
rm -rf /home/temp/aroy
HTTP STATUS CODE:
DB Error Check
1
2
3
| mysqlcheck tijojeenajose_w244 >> will check the database for any corruption
mysqlcheck -r tijojeenajose_w244 >> will repair the database
|
Check for compressed files along with space consumption
1
| find /home/* -type f -size +100M -regex '.*\.\(tar\|zip\|tar\.gz\|jpa\)' -exec ls -la '{}' \; | awk 'BEGIN{pref[1]="K";pref[2]="M";pref[3]="G";pref[4]="T"} {x = $5; y = 0; while( x > 1024 ) { x = x/1024; y++; } printf("%g%s\t%s %s %s\n",int(x*10+.5)/10,pref[y],$6,$7,$9); sum += $5} END {print "Total:" sum/1024**3 " G" }'
|
1
| find /home/ -type f -size +500000k 2>/dev/null -exec ls -lh {} \; | egrep -v 'virtfs|tmpDSK' | awk '{ print $5 ": " $9 }' | sort -rn
|
Allow-Origin
1
2
3
| <IfModule mod_headers.c>
Header set Access-Control-Allow-Origin "*"
</IfModule>
|
File Details Check
1
2
3
| filescout malicious_file.php
filescout -p PID - get result account to a specific process.
filescout -t '2017-06-17 21:46:58.594000000 -0400' username - Time based
|
DirectoryIndex info.php
EXIM Message Commands
1
2
3
4
| exim -bp = show the msg in queue.
exim -bpc = show msg count in queue.
exim -Mvb <msg id> = show msg body.
exim -Mvh <msg id> = show msg header.
|
1
| exim -bp|grep "[admin@totallyjewishtravel.com](mailto:admin@totallyjewishtravel.com)" | awk {'print $3'}| xargs exim -Mrm
|
Screen Commands
1
2
3
4
5
| screen -S disk_investigation
crtl + A + D {for detaching from the screen session}
screen -ls
screen -r disk_investigation {for re-attaching}
ctrl + D to kill the screen session after attaching to it
|
Mysql Indexing Size Check
1
| mysql -Bse 'show variables like "datadir";'|awk '{print $2}'|xargs -I{} find {} -type f -printf "%s %f\n"|awk -F'[ ,.]' '{print $1, $NF}'|awk '{array[$2]+=$1} END {for (i in array) {printf("%-15s %s\n", sprintf("%.3f MB", array[i]/1048576), i)}}' | egrep '(MYI|ibd)';
|
PHP-FPM max children hit check
1
| [root@host logs] echo; for errorlog in $(ls /opt/cpanel/ea-php74/root/usr/var/log/php-fpm/error.log); do echo $errorlog; grep -i max_children $errorlog | perl -pe 's/^.*\[pool\s([a-zA-Z0-9\_\-]+)\].setting\s\(([0-9]+)\).$/$1 = $2/' | sort | uniq; echo; done
|
1
| [root@host logs] for days_ago in {0..6}; do date=$(date -d "$days_ago days ago" +%d-%b-%Y); bar="==========="; printf "$bar\n$date\n$bar\n"; grep -h $date /opt/cpanel/ea-php*/root/usr/var/log/php-fpm/error.log* | grep max_children | sed -e 's/^.*\[pool \(.*\)\].*(\(.*\)).*$/\1 (limit \2)/' -e 's/_/./g' | sort | uniq -c | sort -rn; done - To check how many time each domain hit the limit
|
1
| [root@host logs] echo; for errorlog in $(ls /opt/cpanel/ea-php74/root/usr/var/log/php-fpm/error.log); do echo $errorlog; grep -i max_children $errorlog | perl -pe 's/^.*\[pool\s([a-zA-Z0-9\_\-]+)\].setting\s\(([0-9]+)\).$/$1 = $2/' | sort | uniq; echo; done | grep arcyclops_com
|
Mysql Process Usage
1
2
3
| [root@host] watch mysqladmin proc status
[root@host] mysql -e 'show full processlist\G' | less
|
Swap Consumption by each process
1
| for file in /proc/*/status ; do awk '/VmSwap|Name/{printf $2 " " $3}END{ print ""}' $file; done | sort -k 2 -n -r | less
|
Exim Log Status
1
2
3
4
5
6
| == : Deferred email (currently not able to send but it will recheck to send it later as issue with the receiver end)
<= : Arrival of the message on your mail server. (sender email)
=> : Message delivery. (recv email)
H= : This specifies the hostname of the server that the mail originates from.
T= : The subject of the e-mail.
C= : "250" is a success.
|
phpInfo
1
2
3
| <?
phpphpinfo( );
?>
|
ModSec - Request Body Limit Raise
1
2
3
4
| [root@host2 conf.d]# cat /etc/apache2/conf.d/modsec2/custom.conf
<IfModule security2_module>
SecRequestBodyLimit 256000000
</IfModule>
|
ModSec - Remove Rule
/etc/apache2/conf.d/modsec2/whitelist.conf -cpanel
/etc/httpd/conf.d/whitelist.conf - interwox
[To remove rule by id for a specific page]
1
2
3
4
5
6
7
| <IfModule mod_security2.c>
<LocationMatch />
SecRuleRemoveById 981176
</LocationMatch>
</IfModule>
[root@host] service httpd reload
|
[Turn off the specific rule for a specific IP]
1
2
3
4
5
| Add:
SecRule REMOTE_ADDR "^64\.217\.150\.198" "phase:1,nolog,allow,ctl:ruleEngine=off,id:340464"
[root@host] service httpd reload
|
Roundcube Upload Limit
[URL:https://www.plesk.com/kb/support/how-to-change-the-maximum-attachment-size-for-webmail-on-the-linux-plesk-server/]
1
2
3
| /etc/psa-webmail/roundcube/php.ini
upload_max_filesize = 256M
post_max_size = 256M
|
Changing the file permission
1
2
| find -type f -exec chmod 644 {} \;
find -type d -exec chmod 755 {} \;
|
CSF - Ignore a process
If a customer receives multiple alerts from an application. We can add the binary in this file to exclude receiving notifications.
1
2
3
| /etc/csf/csf.piignore - ignore a process
[root@corvette2 ~]# csf -r
|
CSF Open/Close Ports in firewall:
1
2
3
| Remove the ports from the "TCP_IN" line in the /etc/csf/csf.conf
csf with "csf -ra"
|
CSF - Load Alert Email Threshold
1
2
3
| [root@corvette2 ~]# nano /etc/csf/csf.conf
PT_LOAD_AVG = "10" to PT_LOAD_AVG = "32"
[root@corvette2 ~]# csf -r
|
Mysql Dummy DB and Restore
Create a dummy db and set permission and assign user
1
2
| mysqldump tijojeenajose_w244 > tijojeenajose_w244.sql
mysql tijojeenajose_w244 < tijojeenajose_w244.sql
|
Disk Usage Investigation
1
2
3
4
5
6
| df -h
du -sch *
du -sch phillips
sudo du -aBm / 2>/dev/null | sort -nr | head -n 10
alias duf='du -skxc .[!.]* * | sort -rn | perl -ne '\''($s,$f)=split(m{\t});for (qw(K M G T)) {if($s<1024) {printf("%.1f",$s);print "$_\t$f"; last};$s=$s/1024}'\'
find /home/ -type f -size +500000k 2>/dev/null -exec ls -lh {} \; | egrep -v 'virtfs|tmpDSK' | awk '{ print $5 ": " $9 }' | sort -r
|
Cron
“Cronguru’ for Easy Creation and understanding - Website
1
2
3
| crontab -l - list the defined jobs
crontab -e - opens the editor to add jobs and edit and remove them.
/var/log/crontab - log
|
Imunify-AV
To get the infected files and location to a fie
1
| imunify-antivirus malware malicious list --limit -1 | awk '{print $8}' | xargs -I % ls % 2>/dev/null | sort -u >> imunify-list.txt
|
Mysql - Buffer Pool
To raise Buffer Pool Size
1
2
| /etc/my.cnf
innodb_buffer_pool_size=160M
|
Csf - Block/Unblock/Whitelist
1
2
3
4
| csf -g 10.20.5.231 - check blocked or not.
csf -dr 10.20.5.231 - unblock
csf -a 1.2.3.4 - add to whitelisted
csf -d 1.2.3.4 - Block the IP
|
CSF - Port Open. IP filter
Edit /etc/csf/csf.allow for adding a port and a specific IP to allow through firewall.
1
2
3
| [eg: tcp:in:d=3306:s=10.20.5.231]
csf -ra - Restart firewall.
|
HulkBan
1
2
| /scripts/hulk-unban-ip 192.168.1.1 - unblock IP from cPhulk.
/scripts/cphulkdwhitelist 10.20.5.231 - to add IP to whitelist in cPhulk.
|
Exim
This command helps identify frequently used user directories in Exim processes, highlighting potential suspicious activity like excessive email commands from a specific home directory.
1
2
3
| grep cwd=\/home\/ /var/log/exim_mainlog| cut -d' ' -f4 | sort | uniq -c | sort -n
grep cwd=\/home\/ /var/log/exim_mainlog| cut -d' ' -f3 | sort | uniq -c | sort -n
|
Server Spamming Check
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
| Abusive IP:
echo -e "\nPotentially abusive IP's:"; grep -s "$(date +%d/%b/%Y)" /usr/local/apache/domlogs/* | egrep '(ajax|login|xmlrpc|administrator)' | grep -v ftp | awk '{print $1 " " $7}' | sort -rn | uniq -c | sort -rn | head
To detect the number of requests received by each domain:
grep -s "$(date +"%d/%b/%Y")" /usr/local/apache/domlogs/* | awk '{print $1}' | cut -d: -f1 | sort | uniq -c | sort -rn | head
To detect IP with a high number of request to the domain:
grep -s "$(date +"%d/%b/%Y")" /usr/local/apache/domlogs/ecolecua.com.mx-ssl_log | awk '{print $1}' | cut -d: -f1 | sort | uniq -c | sort -rn | head
To detect IPs with a high number of POST requests to the domain:
grep -s "$(date +"%d/%b/%Y")" /usr/local/apache/domlogs/ecolecua.com.mx-ssl_log | grep POST | awk '{print $1}' | cut -d: -f1 | sort | uniq -c | sort -rn | head
check with specific IP what the IP trying to access or which domain
grep -sl "198.251.83.117" /usr/local/apache/domlogs/* | while read i; do echo $i; grep -c 198.251.83.117 $i; done
Specific IP made request endpoints:
grep -d skip $(date +%d/%b/%Y): /var/log/apache2/domlogs/*/*|grep 135.181.138.58|awk '{print $7}'|cut -d? -f1|sort|uniq -c|sort -rh|head
Show Current connection IPs to 80/443 Ports:
netstat -nt | egrep ':80|:443' | awk '{print $5}' | cut -d : -f 1 | sort | uniq -c | sort -nr | head
|
Memory Consumption
1
2
3
4
5
6
7
| Memory based process sort:
ps -eo pid,ppid,cmd,%mem,%cpu --sort=-%mem | head
Cpu based process sort:
ps -eo pid,ppid,cmd,%mem,%cpu --sort=-%cpu | head -n 20
|
Apache Redirect
Add the following lines to the .htaccess file.
1
2
3
| RewriteEngine On
RewriteCond %{HTTPS} off
RewriteRule .* https://%{HTTP_HOST}%{REQUEST_URI} [R=301,L]
|
Logs
1
2
3
4
5
6
7
8
9
10
| /var/log/loadwatch/ - Find Date and time-based Usage and Ful dump report.
/var/log/apache2/error_log - apache log
/home/tijojeenajose/logs/tijo_jeenajose_com.php.error.log
/var/log/exim_mainlog - mail content based mail log
/var/log/maillog - mail log login attempts
/var/log/messages - ftp log
/var/log/secure - ssh log
/home/username/logs/ - php error log
email_mainlog - logs of emails
maillog - auth based
|
1
2
3
4
5
| /usr/local/cpanel/logs/login_log - cpanel login history
/usr/local/cpanel/logs/access_log - access logs cpanel
/usr/local/cpanel/logs/error_log - error log cpanel
/usr/local/cpanel/logs/cpbackup - backup log
/usr/local/cpanel/logs/cpbackup_transporter - transporter log
|
1
2
3
4
5
6
7
| /var/cpanel/accounting.log - logs regarding account creation,termination.
/var/lib/mysql/67-43-14-17.cprapid.com.err - mysql error
/var/log/lfd.log - failed login log of csf
/var/log/apache2/domlogs/tijojeenajose/tijo.jeenajose.com - apache
/var/log/apache2/domlogs/tijojeenajose/tijo.jeenajose.com-ssl_log apache ssl log
/opt/cpanel/ea-php$$/root/usr/var/log/php-fpm/error.log - php-fpm log
/var/log/cron - cron log
|
