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.


 
 
 

MySQL Encrypted Database Backup for Mysqldump and Xtrabackup

What does Database Encryption and Decryption mean?

Database encryption is the process of converting data, within a database, in plain text format into a meaningless cipher text by means of a suitable algorithm.

Database decryption is converting the meaningless cipher text into the original information using keys generated by the encryption algorithms.

Database encryption can be provided at the file or column level.


Here am going to explain how to encrypt your mysql databases with mysqldump and xtrabackup.first we will see for mysqldump

MySQLdump Encryption & Decryption:

Mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database.

To encrypt your database here I have used ccrypt.ccrypt is a utility for the secure encryption and decryption of files and streams.

I have used Fedora 20 server to do this process.

First install ccrypt

#yum install ccrypt

am already have an mysql server in this machine so i will just show you how to take encrypted backup with ccrypt.

Create a hidden keyfile.

#vi /etc/key/.backupkey

in this file add any content like below,

ex : Ukl8GiJ4Q#uy@iP

Now give permission for this file,

#chmod 600 /etc/key/.backupkey

Now we can encrypt our backup,

Encryption:

#mysqldump -u root -ppa$$123 --databases country world | ccrypt -k /etc/key/.backupkey > backup_c.sql

This backup.sql will be now an encrypted backup when you open it, it will be a non readable format.

Decryption:

#cat backup_c.sql | ccrypt -d -k /etc/key/.backupkey > backup_d.sql 

Now you can see the real content in this file.
 

Compressed Mysqldump Encrypted backup:

Encryption:

#mysqldump -u root -ppa$$123 --databases country world | ccrypt -k /etc/key/.backupkey | gzip -c > backup_c.sql.gz

Decryption:

#gunzip  backup_c.sql.gz > backup_c.sql

#cat backup_c.sql | ccrypt -d -k /etc/key/.backupkey > backup_d.sql 

Xtrabackup Encryption & Decryption:
 
Percona XtraBackup is the world’s only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases.

For xtrabackup you need to install latest Percona Xtrabackup(Version should be more than 2.1.4) and openssl for encryption.

Xtrabackup installation steps in this below link,
http://knowmysql.blogspot.in/2013/08/percona-xtrabackup-tool.html

Now we can encrypt our backup with openssl for Xtrabackup.

Before doing that we need to generate openssl key. 

#openssl enc -aes-256-cbc -pass pass:Prabhu123 -P -md sha1

Replace your password in the bold place,when you executed the above command  you will get the output, 

salt=76AC44528ED1441B
key=C6CA8F2AC3A824653438F7B4E2493892BCC01238E1FF4B5651588AFB6D4DA111
iv =FEC2DE284641AC8A0636D07BEEC5A514

 

with the above key we are going to encrypt with Xtrabackup

Encryption: 

#innobackupex --user=root --password=pa$$123 --export  --encrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup
 

 after executed the above command you can see some line like below,

[01] Encrypting ./xxxx/xxxx.ibd to /backup/2014-12-27_04-49-28/xxxx/yyyy.ibd.xbcrypt
[01]        ...done
[01] Encrypting ./yyyy/aaaa.ibd to /backup/2014-12-27_04-49-28/dddd/rrrr.ibd.xbcrypt
[01]        ...done
[01] Encrypting ./rrr/ssss.ibd to /backup/2014-12-27_04-49-28/yysss/ddasffd.ibd.xbcrypt
[01]        ...done
[01] Encrypting ./gggds/ssssss.ibd to /backup/2014-12-27_04-49-28/dsfsd/fdsfds.ibd.xbcrypt
.
.
.

and at the end,

innobackupex: Backup created in directory '/backup/2014-12-27_04-49-28'
141227 04:51:51  innobackupex: Connection to database server closed
141227 04:51:51  innobackupex: completed OK!
   Now it has taken an encrypted backup.

Xtrabackup encryption with keyfile,

#openssl enc -aes-256-cbc -pass pass:Prabhu123 -P -md sha1

Replace your password in the bold place,when you executed the above command  you will get the output, 

salt=76AC44528ED1441B
key=C6CA8F2AC3A824653438F7B4E2493892BCC01238E1FF4B5651588AFB6D4DA111
iv =FEC2DE284641AC8A0636D07BEEC5A514

Now copy the iv result in a txt file,

#cat /etc/key/xtrabackupkey.txt

FEC2DE284641AC8A0636D07BEEC5A514

Now do the xtrabackup with key file, 

#innobackupex --user=root --password=pa$$123 --export  --encrypt=AES256 --encrypt-key-file=/etc/key/xtrabackupkey.txt  /backup

Decryption:

For decryption required xtrabackup 2.1.4 version.

#innobackupex  --decrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup/2014-12-27_05-49-08/

It will extract the xtrabackup encrypted folder to decrypted folder in same location.

Compressed Xtrabackup Encrypted backup:

Encryption:

# innobackupex --user=root --password=pa$$123 --export --compress --encrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup

Decryption:

First we need to decompress the file, see how to decompress in the below link,

http://knowmysql.blogspot.in/2013/08/percona-xtrabackup-tool.html

then decrypt by the below command with same encrypt key,

#innobackupex  --decrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup/2014-12-27_05-49-08/

Now all the databases are decrypted in the same directory.