How to troubleshoot mysql database server high cpu usage/slowness

Steps:

1. Firstly find out what's causing server CPU high usage
Normally, we firstly will notice that server cpu load is high, run 'top' to confirm which process is contributing the cpu high usage, it's mysql or other process. Also, run uptime, vmstat 2 and iostat -x to find out if there's any abnormal situation.

Here, we need some baseline to compare with, before the problem happens and server is running fine, we should have recorded some baseline information first such as the output of:

vmstat 2 20
top -b -n 5
iostat -x 5 | grep sdb

2. check mysql error log , slow query log etc from /etc/my.cnf

How to Import/Export MySQL Databases with SSH

To export a MySQL database, access your Dedicated Server or VPS via SSH and run:

# mysqldump -u username -p database_name > dumpfile.sql

To import a MySQL Database, run:

# mysql -u username -p database_name < dumpfile.sql

How to Install EAccelerator, IonCube, Zend Optimizer, SourceGuardian, and SuHosin outside of EasyApache

You can use /scripts/phpextensionmgr to install or uninstall these modules!

    /scripts/phpextensionmgr list
    Available Extensions:
    EAccelerator
    IonCubeLoader
    Zendopt
    SourceGuardian
    PHPSuHosin

If you wanted to install Zend Optimizer use the below command.

    /scripts/phpextensionmgr install Zendopt
    Installing Zendopt
    Determining PHP version
    Installing Zend Optimizer binary
    Activating Zend Optimizer in /usr/local/lib/php.ini
    Zend Optimizer activated
    Skipping install to /usr/local/php4, missing php.ini

PHP script to find php shells

his is a script written by Karen Chun and published under GPL



<?php
/*    lookforbadguys.php         2012-04-09
Copyright (C)2012 Karen Chun, Steven Whitney.
Initially published by http://25yearsofprogramming.com.

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License (GPL)
Version 3 as published by the Free Software Foundation.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.

--Purpose: iterate through server files looking for hacker code snippets, backdoor scripts,
suspicious .htaccess code, suspicious file names.
Suspicious things to search for are stored in easily modifiable lists of regular expressions.

--Tested with PHP 5.2 and 5.3. It might work with earlier versions.
--It is designed for use in either Linux or Windows.
On my system, it runs much slower in Windows.

--Not all things it finds are hacks. Not all hacks are found.
--You should also search manually for weird files (such as .php files) in your image directories,
especially if your .htaccess has redirects or was made executable.
--Some searches are commented out because they can give too many false positives.

----------
CHANGELOG:

--2011-03-08 First published

MySQL downgrade on cPanel server

You know downgrades are not recommended, it’s always best to stick with a newer version and work around whatever issues you have but if you really have to downgrade then this way is pretty easy and always works 99% of the time to be honest.

As always make sure you take BACKUPS of your databases and mysql directory before you continue..

Edit /var/cpanel/cpanel.config and look for the mysql-version line and edit the number to whatever version you need i.e.

mysql-version=5.5

to

mysql-version=5.1

Save it and run the following command:

/scripts/mysqlup –force

After this is done – login to your WHM control panel and run easyeapache and rebuild based on your last profile to connect everything back together.

IP remove from Brute Force Attack database by SSH

Sometimes, because of the Brute Force Attack we are unable to login to WHM if we are using correct details also. Then at that time try using below method.

Login to server via SSH.

Connect with mysql database using mysql command. It will show below output.

root@testserver [/var/log]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 172123
Server version: 5.1.65-cll MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

Then type show databases commands in mysql it will show all databases.

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| cphulkd |
| eximstats |
| horde |
| leechprotect |
| logaholicDB_test |
| modsec |
| mysql |
| roundcube |
+——————–+
9 rows in set (0.00 sec)

Then go to cphulkd database using below command.

mysql> use cphulkd;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Now, it’s time to show tables.

mysql> show tables;
+——————-+
| Tables_in_cphulkd |
+——————-+
| auths |
| blacklist |
| brutes |
| good_logins |
| logins |
| report |
| whitelist |
+——————-+
7 rows in set (0.00 sec)

You can take backup of tables using below command

mysql>BACKUP TABLE `brutes` TO ‘/path/to/backup/directory’;

Run below command to check your IP is blocked in CPHulk Brute Froce database. Replace xxx.xxx.xxx.xxx with your IP

mysql> SELECT * FROM `brutes` WHERE `IP`=’xxx.xxx.xxx.xxx’;

Run below command to remove IP from database

mysql> DELETE FROM `brutes` WHERE `IP`=’xxx.xxx.xxx.xxx’;

Also, check IP in login table also by running the below command

mysql> SELECT * FROM ‘logins’ WHERE ‘IP’ =’xxx.xxx.xxx.xxx’;

Remove the IP from login table using below command.

mysql> DELETE FROM ‘logins’ WHERE ‘IP’ =’xxx.xxx.xxx.xxx’;

OR

You can use below method to clear brutes and login tables. Please note it will remove all the IP’s from database.

Now empty some tables by giving the below commands.

mysql> DELETE FROM brutes;
Query OK, 60 rows affected (0.00 sec)

mysql> DELETE FROM logins;
Query OK, 34 rows affected (0.00 sec)

9 rows in set (0.00 sec)

Solve PHP Fatal Error: Allowed memory size of 8388608 bytes exhausted (tried…

This error message can spring up in a previously functional PHP script when the memory requirements exceed the default 8 MB limit. However, do not fret because this is an easy problem to overcome.


To change the memory limit for one specific script, include a line such as this at the top of the script:

ini_set("memory_limit","12M");

The 12M sets the limit to 12 megabytes (12582912 bytes). If this does not work, keep increasing the memory limit until your script fits or your server squeals for mercy.

How to Uninstall MySQL Governor

Run the below commands to uninstall MySQL Governor

    /usr/share/lve/dbgovernor/install-mysql.py –delete
    yum erase db-governor db-governor-mysql

Upgrading MySQL Governor on CPanel, Plesk and DirectAdmin server

Run the below command to update MySQL Governor

    yum update db-governor db-governor-mysql –enablerepo=cloudlinux-updates-testing

Run the below command to restart the service

    service db_governor restart

How to install MySQL Governor on CPanel, Plesk and DirectAdmin

MySQL governor is set of utilities to monitor and restrict MySQL usage in shared hosting environment. The monitoring is done via USER_STATISTICS table that is available in CloudLinux version of MySQL as well as via tracking of slow queries.

To install MySQL governor on cPanel server

    yum install db-governor –enablerepo=cloudlinux-updates-testing
    /usr/share/lve/dbgovernor/install-mysql.py –install

Note :

MySQL on cPanel servers will be updated from CloudLinux RPMs.

MySQL Governor is compatible only with MySQL 5.x

phplist is sending 2 mails

This is very simple to fix.

Open the mailing_list_name/config/config.php

in the config file, find define(‘SEND_ONE_TESTMAIL’,0);
change the value from 0 to 1

You did i

Securing PHP

Now a days PHP is an essential part of Web Develop environment, so try to secure from a System Admin’s perspective.

Hardening with php.ini

1) open_basedir = /dir

When the open_basedir parameter is enabled, PHP will be able to access only those files, which are placed in the specified directories

2) expose_php = Off

Turning off the “expose_php” parameter causes that PHP will not disclose information about itself in HTTP headers that are being sent to clients in responses to web requests.

3) register_globals = Off

Phpmyadmin error : “The Configuration File Now Needs A Secret Passphrase (blowfish_secret)”

After installing phpmyadmin on one of my client server (Centos), I had to face above error after login into phpmyadmin at the bottom of the phpmyadmin page.

Error :
====================================================

”The Configuration File Now Needs A Secret Passphrase (blowfish_secret)”

====================================================

I tried this Soution which resolved the issue :

Solution :
Copy sample Configuration File To Real Configuration File:
# cp -p config.sample.inc.php config.inc.php

If you have already copied the file just locate it and go to the file :
#vi config.inc.php

Find the below line :

===========================================================

$cfg['blowfish_secret']  ’ ‘; /* YOU MUST FILL IN THIS FOR COOKIE AUTH!  */

===========================================================

Probably on the line number 16 or 18.
You can see its not set, so you need to update  the Blowfish Secret here.
After doing this, Try to visit your phpmyadmin url again and you will see the error is disappeared.

Njoy!!!

PHP Setting change, register_globals and more using htaccess

In order to change your PHP  setting, from upload limit to register_globals just do the follow:

1) Create a file called .htaccess inside your public_html

(Note: if you want to change PHP 5 settings, you need to put these values in a file called php.ini inside the folder of the php files and

remove the php_value part)

1) Create a file called .htaccess inside your public_html
(Note: if you want to change PHP 5 settings, you need to put these values in a file called php.ini inside the folder of the php files and  remove the php_value part)

2) Edit it and add the line below in this format
php_value value_name newvalue
example:

# to turn off register_globals
php_value register_globals off

Error PHP Fatal error: Out of memory (allocated 24641536) (tried to allocate 24381441 bytes) on cPanel server with php scripts

Today came across with the memory exhausted error and while investigation note that after increasing the memory in server php.ini file even though increased php memory limit for the particular user as server is suphp enabled, but did not help…
 
While browsing the site I find the error as below under error logs :

==========================================
[root@server/home/username/public_html]# tail -f error_log
[01-May-2012 20:45:55] PHP Fatal error:  Out of memory (allocated 24641536) (tried to allocate 24381441 bytes) in /home/username/public_html/filename.php on line 117

==========================================

Soution 1 :