Steps to check MySQL Slow queries with Percona Tool(Pt-Query-digit)

1.Download latest Percona tool kit.

Download from here ,

http://www.percona.com/downloads/percona-toolkit/LATEST/

2.Percona tool kit Installation steps here,

http://knowmysql.blogspot.in/2013/09/percona-tool-kit-installation.html

3.After installation enable Slow query log variable in MySQL

a.Enable through my.cnf for permanent

add below two lines,

    slow_query_log=ON
    long_query_time=5

Restart mysql

b.Enable for the current session

login to mysql server

    >set global slow_query_log=ON;
    > set global long_query_time=5;

4.By default slow query log file will be in MySQL data directory

go to mysql data directory and see hostname-slow.log

wait for few hours to get the slow queries in the log file.

5.Now run the pt-query-digit to see the slow queries

#pt-query-digest /mysqldatadirectorypath/hostname-slow.log > /backup/slowqueries.txt

6.Now evaluate the slow queries.

#less /backup/slowqueries.txt

here first in overall session you can see the total details

Second you can see the list of top queries in rankwise.

Third full details of each queries.

in the each queries the main things to see is,

Total number of execute time of the query from by 95% column.


 
 
 

Percona Plugins for Cacti MySQL Graph View Monitoring

Every DBA would like to monitor mysql server wit out any point of failure.

Here am going to explain how to monitor MySQL Server with each and every internal process by Cacti and Percona MySQL Plugins.

First install cacti in your machine.Here I have given the steps for ubuntu 12.04.

Cacti Installation :

1.Update you machine.

#apt-get update

2. Install the Apache,PHP,MySQL and its dependencies.

#apt-get install apache2  apache2-mpm-prefork apache2-utils

#apt-get install libapache2-mod-php5 php5-cli php5-common php5-cgi php php5-mysql

#apt-get install mysql-server mysql-client

3. Install snmpd

# apt-get install snmpd

edit the snmpd.conf file.

#vi /etc/snmp/snmpd.conf

#agentAddress udp:161,udp6:[::1]:161   --- remove # here

#rocommunity secret  CactiServerIpAddress   ---  remove # here and add Your cacti server public ip

restart snmpd
#service snmpd restart

4.Install cacti
# apt-get install cacti cacti-spine

here it will ask the mysql user name and password give the correct user name and password. once installation finish check in mysql there one new database name "cacti" is created or not.

5.Go to web browser

http://cacti ip address /cacti/install

Default user name password for cacti login is admin.

Once you done the installation in browser you will go to the dashboard page.

Percona Plugins installation in Cacti:

1.Download cacti percona tar.gz plugin from the below link in cacti server and in your local machine desktop.

http://www.percona.com/downloads/percona-monitoring-plugins/LATEST/

2.Extract the file percona-monitoring-plugins-1.1.3.tar.gz in both cacti server and local machine.

#tar -xvzf percona-monitoring-plugins-1.1.3.tar.gz

3.In cacti server do the following.

#cd  percona-monitoring-plugins-1.0.0/cacti/
#cp scripts/ss_get_mysql_stats.php /usr/share/cacti/site/scripts

Once you copied the php file to cacti path go to cacti dashboard in browser.

In the left side you can see import templates option click it.The you will see a new page like below.

Here browse "cacti_host_template_percona_mysql_server_ht_0.8.6i-sver1.1.1.xml" file from your local machine where you have extracted the percona plugin.

>percona-monitoring-plugins-1.1.1> cacti>templates>cacti_host_template_percona_mysql_server_ht_0.8.6i-sver1.1.1.xml

Then do import in the dashboard.It will take few minutes to import from your local machine. After imported it will show the page like below.

Percona Plugin was installed now we need to add MySQL server to monitor from cacti.

From the home page click devices.then add a new device.

Console > devices > add new device

Here give your mysql server IP and select the host templete as "Percona MySQL Server HT" option and create it,

In the next page it will show list of options to be monitor to select it.like below

once you selected the options then create it. after that it will show what are the graphs are created to monitor.

Then go to graph page and see your MySQL server current process through graph like below.

 Now you can monitor your MySQL Server for each and every process.


Note :
For host MySQL server machine. you need to add this cacti server ip in snmpd.conf

as like this,

vi /etc/snmp/snmpd.conf

rocommunity secret  CactiServerIpAddress

 Enjoy and monitor your MySQL server now!!!


References:

http://www.percona.com/doc/percona-monitoring-plugins/1.0/cacti/installing-templates.html

http://www.percona.com/doc/percona-monitoring-plugins/1.0/cacti/mysql-templates.html

https://www.digitalocean.com/community/articles/installing-the-cacti-server-monitor-on-ubuntu-12-04-cloud-server 

http://myconfigure.blogspot.in/2013/01/install-snmp-cacti-on-ubuntu-1210.html 

Percona Xtrabackup Tool

We all know to take backup MySQL databases through Mysqldump options but here I am going to explain another tool called Xtrabackup to backup our MySQL databases without any disturbance to your Server.

We can download Xtrabackup from this link : http://www.percona.com/downloads/XtraBackup/


Xtrabackup RPM  Installation:

In RPM there is 3 files:

1.Xtrabackup ( percona-xtrabackup-2.1.2-611.rhel6.x86_64.rpm )
2.Debug-files (percona-xtrabackup-debuginfo-2.1.2-611.rhel6.x86_64.rpm )
3.Test (percona-xtrabackup-test-2.1.2-611.rhel6.x86_64.rpm )

Download these above three files.

Installation :

Step 1:

$ rpm -Uvh percona-xtrabackup-2.1.2-611.rhel6.x86_64.rpm

Step 2:

$ rpm -Uvh  percona-xtrabackup-debuginfo-2.1.2-611.rhel6.x86_64.rpm

Step 3:

$ rpm -Uvh  percona-xtrabackup-test-2.1.2-611.rhel6.x86_64.rpm


After installed it check it whether it is installed or not by using,

$ yum list|grep percona

Here you can see the list for percona files was installed.


Take Backup Using Xtrabackup:

Add your data directory in my.cnf or my.ini file.

$ innobackupex --user=root --password=pa$$word --export  /nfsdrive

/nfsdrive  --> backup path.

Take compressed backup:

$ innobackupex --user=root --password=pa$$word --export --compress /nfsdrive

or

$ innobackupex --user=root --password=pa$$word --stream=tar ./ | gzip -c > /nfsdrive/backup.tar.gz


Restore Backup:

1.Stop mysql
2.Move your mysql data directory to other location.

3.Copy the backup from backup path to mysql data directory.
4.Start the server.

Now you can use your databases.

Note:

If you are using "--compress" option while taking backup.It will save in ".qp" format you need to extract it by download qpress and extract it by,

/path/qpress  -d  /backup_path/example.qp  /backup_path

More details : http://www.percona.com/software/percona-xtrabackup