Thursday, November 28, 2013

changing IP for a subdomain and addon domain

Edit Ip's in Needed domain's files .

/var/cpanel/userdata

/var/cpanel/users

Roundcube-Horde-Squirrelmail

Check if you can get any errors from the logs .
Common fixes recommended are :-

1 ) vi /usr/local/cpanel/base/3rdparty/roundcube/config/main.inc.php
and change
$rcmail_config['smtp_user'] = '%u';
to
$rcmail_config['smtp_user'] = '';
2) If you are using csf , check csf configuration file for the entry
SMTP_BLOCK = "1"
SMTP_ALLOWLOCAL = "1"
If the value is 0 change it to 1 and restart csf
3) update cPanel ( /scripts/upcp --force ) to latest "STABLE" version.
4) increase the memory_limit in php.ini under /usr/local/cpanel/base/3rdpart/roundcube/

/var/cpanel/roundcube/install.
/usr/local/cpanel/bin/update-roundcube.
/usr/local/cpanel/install/webmail

The update-roundcube script then does the following:
1.Removes the existing Roundcube installation (via the command rm -rf /usr/local/cpanel/base/3rdparty/roundcube).
2.Extracts the appropriate Roundcube source tarball to /usr/local/cpanel/base/3rdparty using the version specified in update-roundcube.
3.Changes the ownership of the Roundcube installation to the root user and the wheel group.
4.Extracts configuration values for Maildir, mbox, and MySQL from the system settings.
5.Backs up the MySQL Roundcube database to /var/cpanel/roundcube/roundcube.backup.sql.«current timestamp».
6.Please note that only 4 copies of the Roundcube database backup are retained in /var/cpanel/roundcube.
7.Drops the Roundcube database from MySQL.
8.Updates the Roundcube configuration files and Roundcube database SQL files with the server's settings.
9.Recreates the Roundcube database from the provided SQL files.
10.Reloads the previous Roundcube database backup, finishing the Roundcube update.
Please try the following procedure.
=====
drop database roundcube;
exit;
create database roundcube;
exit;
Then do the procedure:
mysql-u root-D roundcube-p </usr/local/cpanel/base/3rdparty/roundcube/SQL/mysql.initial.sql
After that, look for the file:
vi /usr/local/cpanel/base/3rdparty/roundcube/config/db.inc.php
In line similar to the database -> mysql://roundcube:SENHADOROUNDCUBE@localhost/roundcube
replace by:
mysql://root:SENHADEROOT@localhost/roundcube
Now try your webmail using roundcube
Also ,
* Check if db.inc.php and main.inc.php contains the correct entries such as username , password and database name
* Running the command /usr/local/cpanel/bin/update-roundcube --force will also help
* go to phpmyadmin and repair all round cube tables
* Also check and reset the mysql root password in whm just to make sure it is set .
*Last but not the least try /scripts/upcp --force

 

Horde Mail
One of the many unique features about Horde webmail is it’s spell checker facility. However, the use of this requires that certain components be installed. To install these follow these instructions:
1) Login to your Linux box via SSH as root.
2) Install aspell by running the following command:
yum -y install aspell aspell-en-gb
Leave it to install; you shouldn’t need to make any changes to Horde as it should automatically detect the ASPELL installation.

/scripts/fullhordereset

mysql horde
Then:
REPAIR TABLE horde_sessionhandler;

For Horde
/usr/local/cpanel/bin/update-horde --force

For SquirrelMail
/usr/local/cpanel/bin/update-squirrelmail --force

For Roundcube
/usr/local/cpanel/bin/update-roundcube --force

 

 

fsck---- Mount partition using alternate superblock

Mount partition using alternate superblock

Find out superblock location for /dev/sda2:
# dumpe2fs /dev/sda2 | grep superblock

Sample output:

Primary superblock at 0, Group descriptors at 1-6
Backup superblock at 32768, Group descriptors at 32769-32774
Backup superblock at 98304, Group descriptors at 98305-98310
Backup superblock at 163840, Group descriptors at 163841-163846
Backup superblock at 229376, Group descriptors at 229377-229382
Backup superblock at 294912, Group descriptors at 294913-294918
Backup superblock at 819200, Group descriptors at 819201-819206
Backup superblock at 884736, Group descriptors at 884737-884742
Backup superblock at 1605632, Group descriptors at 1605633-1605638
Backup superblock at 2654208, Group descriptors at 2654209-2654214
Backup superblock at 4096000, Group descriptors at 4096001-4096006
Backup superblock at 7962624, Group descriptors at 7962625-7962630
Backup superblock at 11239424, Group descriptors at 11239425-11239430
Backup superblock at 20480000, Group descriptors at 20480001-20480006
Backup superblock at 23887872, Group descriptors at 23887873-23887878
Now check and repair a Linux file system using alternate superblock # 32768:
# fsck -b 32768 /dev/sda2

Sample output:

fsck 1.40.2 (12-Jul-2007)
e2fsck 1.40.2 (12-Jul-2007)
/dev/sda2 was not cleanly unmounted, check forced.
Pass 1: Checking inodes, blocks, and sizes
Pass 2: Checking directory structure
Pass 3: Checking directory connectivity
Pass 4: Checking reference counts
Pass 5: Checking group summary information
Free blocks count wrong for group #241 (32254, counted=32253).
Fix? yes
Free blocks count wrong for group #362 (32254, counted=32248).
Fix? yes
Free blocks count wrong for group #368 (32254, counted=27774).
Fix? yes
..........
/dev/sda2: ***** FILE SYSTEM WAS MODIFIED *****
/dev/sda2: 59586/30539776 files (0.6% non-contiguous), 3604682/61059048 blocks
Now try to mount file system using mount command:
# mount /dev/sda2 /mnt

You can also use superblock stored at 32768 to mount partition, enter:
# mount sb={alternative-superblock} /dev/device /mnt
# mount sb=32768 /dev/sda2 /mnt

Try to browse and access file system:
# cd /mnt
# mkdir test
# ls -l
# cp file /path/to/safe/location

You should always keep backup of all important data including configuration files

Sunday, November 24, 2013

How to Change the Location of MySQL on cPanel

here may be some situations where you have to move the location of MySQL, for example, if you’re out of disk space or perhaps looking to host it on another device to increase performance. Whatever the reason, moving MySQL is simple and has no impact ct on cPanel’s functionality.

1) Create a backup

This should go without saying, but never mess with your data without making a backup of it. One simple way:
tar -cvf mysql.tar /var/lib/mysql

2) Modify my.cnf

In the [mysqld] section of /etc/my.cnf, add/modify this line:

datadir=/new/path/to/mysql

For example, if you are moving MySQL from /var/lib/mysql to /home2/mysql:

datadir=/home2/mysql

Don’t restart MySQL yet.

3) Sync the data

Now migrate the data to the new location using rsync. Typically you’ll want to stop MySQL, sync the data, then start it up again. If you have a lot of data and know the sync will take a while, do several syncs while the server is running, until they take less time. However, your last sync should always be done with MySQL stopped, especially if you have InnoDB tables. Here’s the command to sync with the example of MySQL being moved to /home2/mysql:
rsync -av /var/lib/mysql /home2
Now, relink the socket:
ln -sf /home2/mysql/mysql.sock /tmp

4) Restart MySQL

Since you already added the datadir entry to my.cnf, all you need to do is restart again and everything should be working.

CPAN to install Perl modules

CPanel has a internal script for using CPAN to install Perl modules. Learn it and love it:
/scripts/perlinstaller
Most common Perl modules can be installed from WHM ~> Install a Perl Module, or from command line. If you don't know the name of the Perl module you're installing, you may want to use the WHM installer instead, as it has a search feature and its usage is pretty self-explanatory.
For command line installations, pass the name of the perl module (case-sensitive) to the installer like so:
/scripts/perlinstaller MD5
/scripts/perlinstaller IO::Compress::Base
If the module is already installed and you need to update or reinstall it, pass –force:
/scripts/perlinstaller –force MD5
Since cPanel 11, you can now also allow your users to install their own perl modules locally in /home/$user/perl (which is automatically added to their Perl module path) so they don't have to bug you when they need a Perl module, nor to they need SSH access. You can enable this in WHM ~> Module Installers ~> Perl Module [Manage] . You do need to have compilers enabled for users though, which can be done in WHM ~> Security Center ~> Compilers Tweak .

cPanel Out of Memory Errors

I’ve seen several features of cPanel appear to malfunction, and upon reviewing /usr/local/cpanel/logs/error_log, I’d see something similar to this:
Out of memory during request for 2180 bytes, total sbrk() is 130234368 bytes!
Common places this has been known to occur:
In Webmail (Horde and Roundcube) when opening large attachments
Using cPanel’s perl module installer
You could legitimately be out of RAM, but most likely the cause is cPanel’s internal memory limit. You can raise this in WHM > Tweak Settings:
“The maximum memory a cPanel process can use before it is killed off (in megabytes). Values less than 128 megabytes can not be specified. A value of “0″ will disable the memory limits.”
Or you can adjust the maxmem setting in /var/cpanel/cpanel.config.

THREADED MODE | LINEAR MODE FFMPEG & FFMPEG-PHP CENTOS 5, 6 EASY INSTALL

########First way#########

Download the installer

Code:

wget http://9xhost.net/scripts/ffmpeg.sh


run the installer

Code:

sh ffmpeg.sh


The make install command will show PHP extensions path where ffmpeg PHP extension is installed:

Code:

root@server [~/ffmpeg-php-0.6.0]# make install
Installing shared extensions: /usr/local/lib/php/extensions/no-debug-non-zts-20060613/


Now edit php.ini file

Code:

nano /usr/local/lib/php.ini


Add following line at end of php.ini and this will enable ffmpeg PHP extension:

Code:

extension="ffmpeg.so"


Restart Apache to make this change effective:

Code:

/scripts/restartsrv_httpd


You can verify the status of ffmpeg extension on a PHP info web page or from command line as given below:

Code:

root@server [~]# php -i | grep ffmpeg
ffmpeg
ffmpeg-php version => 0.6.0-svn
ffmpeg-php built on => Jun 2 2012 20:48:04
ffmpeg-php gd support => enabled
ffmpeg libavcodec version => Lavc52.123.0
ffmpeg libavformat version => Lavf52.111.0
ffmpeg swscaler version => SwS0.14.1
ffmpeg.allow_persistent => 0 => 0
ffmpeg.show_warnings => 0 => 0
OLDPWD => /root/ffmpeg-php-0.6.0
_SERVER["OLDPWD"] => /root/ffmpeg-php-0.6.0
_ENV["OLDPWD"] => /root/ffmpeg-php-0.6.0


6. Installation paths

Following are the file system paths of tools that we installed:

Code:

ffmpeg: /usr/bin/ffmpeg


Now open yum.conf

Code:

nano /etc/yum.conf


and add ffmpeg* in exclude line.

===============================
#######Second way##########

Create and open a new file called /etc/yum.repos.d/dag.repo

Code:

nano /etc/yum.repos.d/dag.repo


Add the following text to the file:

Code:

[dag]
name=DAG RPM Repository
baseurl=http://apt.sw.be/redhat/el$releasever/en/$basearch/dag
gpgcheck=1
enabled=1


then run

Code:

rpm --import http://apt.sw.be/RPM-GPG-KEY.dag.txt


Now we are ready to install ffmpeg

First run

Code:

yum update


then

Code:

yum install ffmpeg ffmpeg-devel ffmpeg-libpostproc


Now ffmpeg is installed

Preparing for ffmpeg-php
download the latest ffmpeg-php package:

Code:

wget http://downloads.sourceforge.net/ffmpeg-php/ffmpeg-php-0.6.0.tbz2


Untar this package, build and install it with following commands:

Code:

tar xjf ffmpeg-php-0.6.0.tbz2



Code:

cd ffmpeg-php-0.6.0


sed -i ‘s/PIX_FMT_RGBA32/PIX_FMT_RGB32/g’ ffmpeg_frame.c ####copy paste it too##

Code:

phpize



Code:

./configure



Code:

make



Code:

make install


The make install command will show PHP extensions path where ffmpeg PHP extension is installed:

Code:

root@server [~/ffmpeg-php-0.6.0]# make install
Installing shared extensions: /usr/local/lib/php/extensions/no-debug-non-zts-20060613/


Now edit php.ini file

Code:

nano /usr/local/lib/php.ini


and make sure that value of extension_dir is set to PHP extension directory as given by above make install command:

Code:

extension_dir = "/usr/local/lib/php/extensions/no-debug-non-zts-20060613"


Add following line just below extension_dir and this will enable ffmpeg PHP extension:

Code:

extension="ffmpeg.so"


Restart Apache to make this change effective:

Code:

/scripts/restartsrv_httpd


You can verify the status of ffmpeg extension on a PHP info web page or from command line as given below:

Code:

root@server [~]# php -i | grep ffmpeg
ffmpeg
ffmpeg-php version => 0.6.0-svn
ffmpeg-php built on => Jun 2 2012 20:48:04
ffmpeg-php gd support => enabled
ffmpeg libavcodec version => Lavc52.123.0
ffmpeg libavformat version => Lavf52.111.0
ffmpeg swscaler version => SwS0.14.1
ffmpeg.allow_persistent => 0 => 0
ffmpeg.show_warnings => 0 => 0
OLDPWD => /root/ffmpeg-php-0.6.0
_SERVER["OLDPWD"] => /root/ffmpeg-php-0.6.0
_ENV["OLDPWD"] => /root/ffmpeg-php-0.6.0


6. Installation paths

Following are the file system paths of tools that we installed:

Code:

ffmpeg: /usr/bin/ffmpeg


Now open yum.conf

Code:

nano /etc/yum.conf


and add ffmpeg* in exclude line.
-------------------
Error 1

Code:

/root/ffmpeg/ffmpeg-php-0.7.0/ffmpeg_movie.c: In function 'zim_ffmpeg_movie___construct':
/root/ffmpeg/ffmpeg-php-0.7.0/ffmpeg_movie.c:318: error: 'list_entry' undeclared (first use in this function)
/root/ffmpeg/ffmpeg-php-0.7.0/ffmpeg_movie.c:318: error: (Each undeclared identifier is reported only once
/root/ffmpeg/ffmpeg-php-0.7.0/ffmpeg_movie.c:318: error: for each function it appears in.)
/root/ffmpeg/ffmpeg-php-0.7.0/ffmpeg_movie.c:318: error: 'le' undeclared (first use in this function)
/root/ffmpeg/ffmpeg-php-0.7.0/ffmpeg_movie.c:353: error: expected ';' before 'new_le'
/root/ffmpeg/ffmpeg-php-0.7.0/ffmpeg_movie.c:363: error: 'new_le' undeclared (first use in this function)
 


19 ffmpeg commands for all needs

fmpeg is a multiplatform, open-source library for video and audio files. I have compiled 19 useful and amazing commands covering almost all needs: video conversion, sound extraction, encoding file for iPod or PSP, and more.

 


Getting infos from a video file

ffmpeg -i video.avi

Turn X images to a video sequence

ffmpeg -f image2 -i image%d.jpg video.mpg

This command will transform all the images from the current directory (named image1.jpg, image2.jpg, etc…) to a video file named video.mpg.
Turn a video to X images

ffmpeg -i video.mpg image%d.jpg

This command will generate the files named image1.jpg, image2.jpg, …

The following image formats are also availables : PGM, PPM, PAM, PGMYUV, JPEG, GIF, PNG, TIFF, SGI.
Encode a video sequence for the iPpod/iPhone

ffmpeg -i source_video.avi input -acodec aac -ab 128kb -vcodec mpeg4 -b 1200kb -mbd 2 -flags +4mv+trell -aic 2 -cmp 2 -subcmp 2 -s 320x180 -title X final_video.mp4

Explanations :

  • Source : source_video.avi

  • Audio codec : aac

  • Audio bitrate : 128kb/s

  • Video codec : mpeg4

  • Video bitrate : 1200kb/s

  • Video size : 320px par 180px

  • Generated video : final_video.mp4


Encode video for the PSP

ffmpeg -i source_video.avi -b 300 -s 320x240 -vcodec xvid -ab 32 -ar 24000 -acodec aac final_video.mp4

Explanations :

  • Source : source_video.avi

  • Audio codec : aac

  • Audio bitrate : 32kb/s

  • Video codec : xvid

  • Video bitrate : 1200kb/s

  • Video size : 320px par 180px

  • Generated video : final_video.mp4


Extracting sound from a video, and save it as Mp3

ffmpeg -i source_video.avi -vn -ar 44100 -ac 2 -ab 192 -f mp3 sound.mp3

Explanations :

  • Source video : source_video.avi

  • Audio bitrate : 192kb/s

  • output format : mp3

  • Generated sound : sound.mp3


Convert a wav file to Mp3

ffmpeg -i son_origine.avi -vn -ar 44100 -ac 2 -ab 192 -f mp3 son_final.mp3

Convert .avi video to .mpg

ffmpeg -i video_origine.avi video_finale.mpg

Convert .mpg to .avi

ffmpeg -i video_origine.mpg video_finale.avi

Convert .avi to animated gif(uncompressed)

ffmpeg -i video_origine.avi gif_anime.gif

Mix a video with a sound file

ffmpeg -i son.wav -i video_origine.avi video_finale.mpg

Convert .avi to .flv

ffmpeg -i video_origine.avi -ab 56 -ar 44100 -b 200 -r 15 -s 320x240 -f flv video_finale.flv

Convert .avi to dv

ffmpeg -i video_origine.avi -s pal -r pal -aspect 4:3 -ar 48000 -ac 2 video_finale.dv

Or:
ffmpeg -i video_origine.avi -target pal-dv video_finale.dv

Convert .avi to mpeg for dvd players

ffmpeg -i source_video.avi -target pal-dvd -ps 2000000000 -aspect 16:9 finale_video.mpeg

Explanations :

  • target pal-dvd : Output format

  • ps 2000000000 maximum size for the output file, in bits (here, 2 Gb)

  • aspect 16:9 : Widescreen


Compress .avi to divx

ffmpeg -i video_origine.avi -s 320x240 -vcodec msmpeg4v2 video_finale.avi

Compress Ogg Theora to Mpeg dvd

ffmpeg -i film_sortie_cinelerra.ogm -s 720x576 -vcodec mpeg2video -acodec mp3 film_terminée.mpg

Compress .avi to SVCD mpeg2

NTSC format:
ffmpeg -i video_origine.avi -target ntsc-svcd video_finale.mpg

PAL format:
ffmpeg -i video_origine.avi -target pal-svcd video_finale.mpg

Compress .avi to VCD mpeg2

NTSC format:
ffmpeg -i video_origine.avi -target ntsc-vcd video_finale.mpg

PAL format:
ffmpeg -i video_origine.avi -target pal-vcd video_finale.mpg

Multi-pass encoding with ffmpeg

ffmpeg -i fichierentree -pass 2 -passlogfile ffmpeg2pass fichiersortie-2

Wednesday, November 20, 2013

Mysql Innodb Recovery

InnoDB

130306 22:02:18 mysqld_safe Number of processes running now: 0
130306 22:02:18 mysqld_safe mysqld restarted
130306 22:02:18 [Note] Plugin 'FEDERATED' is disabled.
130306 22:02:18 InnoDB: The InnoDB memory heap is disabled
130306 22:02:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130306 22:02:18 InnoDB: Compressed tables use zlib 1.2.3
130306 22:02:18 InnoDB: Using Linux native AIO
130306 22:02:18 InnoDB: Initializing buffer pool, size = 128.0M
130306 22:02:18 InnoDB: Completed initialization of buffer pool
130306 22:02:18 InnoDB: highest supported file format is Barracuda.
130306 22:02:18 InnoDB: 5.5.30 started; log sequence number 1629186928
130306 22:02:18 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130306 22:02:18 [Note] - '0.0.0.0' resolves to '0.0.0.0';
130306 22:02:18 [Note] Server socket created on IP: '0.0.0.0'.
130306 22:02:18 [Note] Event Scheduler: Loaded 0 events
130306 22:02:18 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.30-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
130306 22:02:19 InnoDB: Assertion failure in thread 47204348393792 in file trx0purge.c line 840
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
03:02:19 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail.

Steps to get it back up.

1. Stop mysqld.
2. Backup /var/lib/mysql/ib*
3. Add the following line into /etc/my.cnf

innodb_force_recovery = 4

4. Restart mysqld.
5. Dump all tables:# mysqldump -A > dump.sql
6. Drop all databases which need recovery.
7. Stop mysqld.
8. Remove /var/lib/mysql/ib*
9. Comment out innodb_force_recovery in /etc/my.cnf
10. Restart mysqld. Look at mysql error log. By default it should be /var/lib/mysql/server/hostname.com.err to see how it creates new ib* files.
11. Restore databases from the dump:mysql < dump.sql
mysqlcheck –all-databases –repair

innodb force recovery options
Mode 1 - Doesn't crash MySQL when it sees a corrupt page
Mode 2 - Doesn't run background operations
Mode 3 - Doesn't attempt to roll back transactions
Mode 4 - Doesn't calculate stats or apply stored/buffered changes
Mode 5 - Doesn't look at the undo logs during start-up
Mode 6 - Doesn't roll-forward from the redo logs (ib_logfiles) during start-up


1 (SRV_FORCE_IGNORE_CORRUPT)
Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.
2 (SRV_FORCE_NO_BACKGROUND)
Prevent the main thread from running. If a crash would occur during the purge operation, this recovery value prevents it.
3 (SRV_FORCE_NO_TRX_UNDO)
Do not run transaction rollbacks after recovery.
4 (SRV_FORCE_NO_IBUF_MERGE)
Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.
6 (SRV_FORCE_NO_LOG_REDO)
Do not do the log roll-forward in connection with recovery.
The database must not otherwise be used with any nonzero value of innodb_force_recovery. As a safety measure, InnoDB prevents users from performing INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0.
**Hint : A simple query for finding all of your InnoDB tables in case you want to specifically target the corruption.

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb';

Wednesday, November 6, 2013

How To Extract a Single File / Directory from Tarball Archive

A. tar command allows to extract a single file or directory using the following format. It works under UNIX, Linux, and BSD operating systems.

tar xvf /dev/st0 filename
tar xvf /dev/st0 directory-name
tar xvf mytar.ball.tar filename
tar -zxvf mytar.ball.tar.gz directory-name


Extract file to /tmp directory
tar -zxvf mytar.ball.tar.gz -C /tmp filename
tar -zxvf mytar.ball.tar.gz -C /tmp dir-name


Read tar man page for more information:
man tar

Analyse slow-query-log using mysqldumpslow & pt-query-digest

Mysql can log slow queries which takes longer to execute. In some cases this is expected but some queries take longer because of coding mistakes. slow-query-log can definitely help you find those queries and make it easy to debug your application.

In WordPress world, many plugins are often coded my amateurs who have no idea about the scale at which big sites operate! Its better to use slow-query-log to find out such plugins.

Enable slow-query-log
You can enable slow-log by un-commenting following lines in /etc/mysql/my.cnf

slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes
Last line will tell slow-log to log queries not using indexes. You can keep it commented if you want to ignore queries which are not using indexes.

If your server has less RAM and you are seeing many of your queries in slow-query-log, you may increase value of long_query_time.

Its advisable to enable slow-query-log while debugging only and disable it once you are done with it. Lets move on to analysis part.

mysqldumpslow

This comes bundled with mysql-server.

mysqldumpslow /var/log/mysql/mysql-slow.log
Following will show top 5 query which returned maximum rows. It can find queries where you missed LIMIT clause. A common performance killer!

mysqldumpslow -a -s r -t 5 /var/log/mysql/mysql-slow.log
Following will sort output by count i.e. number of times query found in slow-log. Most frequency queries sometimes turned out to be unexpected queries!

mysqldumpslow -a -s c -t 5 /var/log/mysql/mysql-slow.log
pt-query-digest

This is part of percona toolkit.

Then basic usage is:

pt-query-digest /var/log/mysql/mysql-slow.log
If you have multiple databases, you can enable filtering for a particular database:

pt-query-digest /var/log/mysql/mysql-slow.log --filter '$event->{db} eq "db_wordpress"'
mysqlsla

This is another 3rd party tool. Can be downloaded from here.

Basic Usage:

./mysqlsla /var/log/mysql/mysql-slow.log
Filter for a database:

./mysqlsla /var/log/mysql/mysql-slow.log -mf "db=db_name"
https://github.com/box/Anemometer