Installing percona ans sysbench on ubuntu encrypting with aesni-enabled

This time we are dividign this section in 4 topics:

1. Installing percona
2. Installing Sysbench
3. Enablingaesni
4.  Ecnrypting /var/lib/mysql
5. Issuing a sysbench test

INSTALLING Percona
From: http://www.percona.com/doc/percona-server/5.5/installation/apt_repo.html

ubuntu@ip-10-40-255-125:~$ gpg --keyserver  hkp://keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
gpg: directory `/home/ubuntu/.gnupg' created
gpg: new configuration file `/home/ubuntu/.gnupg/gpg.conf' created
gpg: WARNING: options in `/home/ubuntu/.gnupg/gpg.conf' are not yet active during this run
gpg: keyring `/home/ubuntu/.gnupg/secring.gpg' created
gpg: keyring `/home/ubuntu/.gnupg/pubring.gpg' created
gpg: requesting key CD2EFD2A from hkp server keys.gnupg.net
gpg: /home/ubuntu/.gnupg/trustdb.gpg: trustdb created
gpg: key CD2EFD2A: public key "Percona MySQL Development Team <mysql-dev@percona.com>" imported
gpg: Total number processed: 1
gpg:               imported: 1
ubuntu@ip-10-40-255-125:~$ gpg -a --export CD2EFD2A | sudo apt-key add -
OK
ubuntu@ip-10-40-255-125:~$

Add this to /etc/apt/sources.list, replacing VERSION with the name of your distribution:

deb http://repo.percona.com/apt precise  main
deb-src http://repo.percona.com/apt precise main

Remember to update the local cache:

$ apt-get update

After that you can install the server and client packages

# apt-get install percona-server-server-5.5 percona-server-client-5.5

Start and stop percona to verify that it is working:

root@ip-10-40-255-125:/home/ubuntu# sudo service mysqld restart
mysqld: unrecognized service
root@ip-10-40-255-125:/home/ubuntu# sudo service mysql restart
 * Stopping MySQL (Percona Server) mysqld                                                                                                                                              [ OK ]
 * Starting MySQL (Percona Server) database server mysqld                                                                                                                              [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
root@ip-10-40-255-125:/home/ubuntu# sudo service mysql status
 * /usr/bin/mysqladmin  Ver 8.42 Distrib 5.5.30, for Linux on x86_64
Copyright (c) 2000, 2013, 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.

Server version        5.5.30-30.2
Protocol version    10
Connection        Localhost via UNIX socket
UNIX socket        /var/run/mysqld/mysqld.sock
Uptime:            5 sec

Threads: 1  Questions: 106  Slow queries: 0  Opens: 171  Flush tables: 1  Open tables: 41  Queries per second avg: 21.200
root@ip-10-40-255-125:/home/ubuntu#

2. INSTALLING SYSBENCH

root@ip-10-40-255-125:/home/ubuntu# apt-get install sysbench
Reading package lists... Done
Building dependency tree      
Reading state information... Done
The following NEW packages will be installed:
  sysbench
0 upgraded, 1 newly installed, 0 to remove and 86 not upgraded.
Need to get 66.0 kB of archives.
After this operation, 170 kB of additional disk space will be used.
Get:1 http://us-east-1.ec2.archive.ubuntu.com/ubuntu/ precise/universe sysbench amd64 0.4.12-1build2 [66.0 kB]
Fetched 66.0 kB in 0s (398 kB/s)   
Selecting previously unselected package sysbench.
(Reading database ... 62104 files and directories currently installed.)
Unpacking sysbench (from .../sysbench_0.4.12-1build2_amd64.deb) ...
Processing triggers for man-db ...
Setting up sysbench (0.4.12-1build2) ...
root@ip-10-40-255-125:/home/ubuntu# sys
sysbench  sysctl   
root@ip-10-40-255-125:/home/ubuntu# sysbench

That is it! Later we will prepare a database and execute a test


3. ENABLING AESNI

ubuntu@ip-10-40-255-125:~$ lsmod }| grep aesni
Usage: lsmod
ubuntu@ip-10-40-255-125:~$ lsmod | grep aesni
aesni_intel            55664  0
cryptd                 20530  1 aesni_intel
aes_x86_64             17255  1 aesni_intel
ubuntu@ip-10-40-255-125:~$

4. encrypting with zNcrypt

Let's say it is already installed and /mnt/encrypt is our encryption directory.

Add the rule zncrypt acl --add --rule="ALLOW @mysql * /usr/sbin/mysqld"
as well as "ALLOW @mysql * /usr/bin/mysqld_safe --shell=/bin/dash"

root@ip-10-40-255-125:/home/ubuntu# zncrypt acl --add --rule="ALLOW @mysql * /usr/sbin/mysqld"
Type MASTER passphrase:
1 rule(s) were added
root@ip-10-40-255-125:/home/ubuntu# sudo service mysql stop
 * Stopping MySQL (Percona Server) mysqld                                                                                                                                              [ OK ]
root@ip-10-40-255-125:/home/ubuntu# zncrypt encrypt @mysql /var/lib/mysql /mnt/encrypted/
encrypt: invalid command action.
root@ip-10-40-255-125:/home/ubuntu# zncrypt-move encrypt @mysql /var/lib/mysql /mnt/encrypted/
Type MASTER passphrase:

Size to encrypt: 29928 Kb
Moving from: '/var/lib/mysql'
Moving to:   '/mnt/encrypted/mysql/var/lib/mysql'
  [=====================================================================>] 100%

Done.
root@ip-10-40-255-125:/home/ubuntu#


root@ip-10-40-255-125:/home/ubuntu# zncrypt acl --add --rule="ALLOW @mysql * /usr/bin/mysqld_safe --shell=/bin/dash"
Type MASTER passphrase:
1 rule(s) were added
root@ip-10-40-255-125:/home/ubuntu# sudo service mysql start
 * Starting MySQL (Percona Server) database server mysqld                                                                                                                              [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
root@ip-10-40-255-125:/home/ubuntu#

5. EXECUTING SYSBENCH

First lets create sbtest database:

root@ip-10-40-255-125:/home/ubuntu# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.5.30-30.2 Percona Server (GPL), Release 30.2

Copyright (c) 2000, 2013, 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> create database sbtest;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
root@ip-10-40-255-125:/home/ubuntu#

Now let's prepare sysbench:

root@ip-10-40-255-125:/home/ubuntu# sysbench --db-driver=mysql --test=oltp --mysql-user=root --max-requests=100000 --oltp-table-size=1000000 --mysql-password=root --mysql-db=sbtest   --mysql-socket=/var/run/mysqld/mysqld.sock prepare
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Creating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...
root@ip-10-40-255-125:/home/ubuntu#

Then test a with multiple threads:



Check the aesni_intel usage

root@ip-10-40-255-125:/home/ubuntu# lsmod | grep aesni
aesni_intel            55664  100
cryptd                 20530  1 aesni_intel
aes_x86_64             17255  1 aesni_intel
root@ip-10-40-255-125:/home/ubuntu#

Enjoy!
P.S. No Flags for today!

Installing percona and sysbench on redhat

 Percona is a software and services company specializing in database support, development, consulting and training


 INSTALLING SYSBENCH ON REDHAT



Based on: http://www.serveradminblog.com/2010/02/sysbench-on-centos-howto/
http://tech-for-me.blogspot.mx/2012/07/installing-sysbench-from-launchpad.html

1.  Install compiler tools to compile the source

#yum -y install gcc gcc-c++ autoconf automake make libtool

2.  Install bzr so you download the source from the repository

#yum -y install bzr

3.  Install MySQL dependencies to build sysbench:

#yum -y install mysql mysql-server mysql-devel

4.  Download sysbench

#bzr branch lp:sysbench

5.  Compile and install sysbench

#cd sysbench
#./autogen.sh
#./configure --prefix=/usr --mandir=/usr/share/man
#make
#make install

6.  Copy lua templates

# mkdir /usr/share/sysbench/tests/db -p
# cp sysbench/tests/db/* /usr/share/sysbench/tests/db

NOTE: Verify location of your lua files (*.lua)

6.  Test if sysbench is working

#sysbench

You should see the help


7. Since we are going to install percona you can remove mysql*

#yum -y remove mysql mysql-server mysql-devel

Troubleshooting:

If you receive an error like:

# sysbench --db-driver=mysql --test=oltp --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root --mysql-password= preparesysbench 0.5:  multi-threaded system evaluation benchmark

PANIC: unprotected error in call to Lua API (cannot open oltp: No such file or directory)
#

IT IS BECAUSE --TEST PARAM doesn't have the complete path: it should be:

# sysbench --db-driver=mysql --test=/usr/share/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root --mysql-password= prepare

INSTALLING PERCONA

Based on : http://www.percona.com/doc/percona-server/5.5/installation/yum_repo.html


Remember that mysql* has to be uninstalled if not, you might have conflicts.

1.-  Add percona repository

vi /etc/yum.repos.d/Percona.repo

 Then Add:

[percona]
name = CentOS $releasever - Percona
baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/
enabled = 1
gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona
gpgcheck = 1

2. Verify that percone is listed as an installation package

 # yum list | grep percona
# yum list | grep percona
                              5.0.92-b23.89.rhel6  percona                     
Percona-SQL-client-50.x86_64  5.0.92-b23.89.rhel6  percona                     
Percona-SQL-devel-50.x86_64   5.0.92-b23.89.rhel6  percona                     
Percona-SQL-server-50.x86_64  5.0.92-b23.89.rhel6  percona                     
Percona-SQL-shared-50.x86_64  5.0.92-b23.89.rhel6  percona                     
                              5.0.92-b23.89.rhel6  percona                     
Percona-SQL-test-50.x86_64    5.0.92-b23.89.rhel6  percona                     
                                                   percona  

3. Download GPG keys:

Add follow key to: /etc/pki/rpm-gpg/RPM-GPG-KEY-percona

-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v1.4.9 (GNU/Linux)

mQGiBEsm3aERBACyB1E9ixebIMRGtmD45c6c/wi2IVIa6O3G1f6cyHH4ump6ejOi
AX63hhEs4MUCGO7KnON1hpjuNN7MQZtGTJC0iX97X2Mk+IwB1KmBYN9sS/OqhA5C
itj2RAkug4PFHR9dy21v0flj66KjBS3GpuOadpcrZ/k0g7Zi6t7kDWV0hwCgxCa2
f/ESC2MN3q3j9hfMTBhhDCsD/3+iOxtDAUlPMIH50MdK5yqagdj8V/sxaHJ5u/zw
YQunRlhB9f9QUFfhfnjRn8wjeYasMARDctCde5nbx3Pc+nRIXoB4D1Z1ZxRzR/lb
7S4i8KRr9xhommFnDv/egkx+7X1aFp1f2wN2DQ4ecGF4EAAVHwFz8H4eQgsbLsa6
7DV3BACj1cBwCf8tckWsvFtQfCP4CiBB50Ku49MU2Nfwq7durfIiePF4IIYRDZgg
kHKSfP3oUZBGJx00BujtTobERraaV7lIRIwETZao76MqGt9K1uIqw4NT/jAbi9ce
rFaOmAkaujbcB11HYIyjtkAGq9mXxaVqCC3RPWGr+fqAx/akBLQ2UGVyY29uYSBN
eVNRTCBEZXZlbG9wbWVudCBUZWFtIDxteXNxbC1kZXZAcGVyY29uYS5jb20+iGAE
ExECACAFAksm3aECGwMGCwkIBwMCBBUCCAMEFgIDAQIeAQIXgAAKCRAcTL3NzS79
Kpk/AKCQKSEgwX9r8jR+6tAnCVpzyUFOQwCfX+fw3OAoYeFZB3eu2oT8OBTiVYu5
Ag0ESybdoRAIAKKUV8rbqlB8qwZdWlmrwQqg3o7OpoAJ53/QOIySDmqy5TmNEPLm
lHkwGqEqfbFYoTbOCEEJi2yFLg9UJCSBM/sfPaqb2jGP7fc0nZBgUBnFuA9USX72
O0PzVAF7rCnWaIz76iY+AMI6xKeRy91TxYo/yenF1nRSJ+rExwlPcHgI685GNuFG
chAExMTgbnoPx1ka1Vqbe6iza+FnJq3f4p9luGbZdSParGdlKhGqvVUJ3FLeLTqt
caOn5cN2ZsdakE07GzdSktVtdYPT5BNMKgOAxhXKy11IPLj2Z5C33iVYSXjpTelJ
b2qHvcg9XDMhmYJyE3O4AWFh2no3Jf4ypIcABA0IAJO8ms9ov6bFqFTqA0UW2gWQ
cKFN4Q6NPV6IW0rV61ONLUc0VFXvYDtwsRbUmUYkB/L/R9fHj4lRUDbGEQrLCoE+
/HyYvr2rxP94PT6Bkjk/aiCCPAKZRj5CFUKRpShfDIiow9qxtqv7yVd514Qqmjb4
eEihtcjltGAoS54+6C3lbjrHUQhLwPGqlAh8uZKzfSZq0C06kTxiEqsG6VDDYWy6
L7qaMwOqWdQtdekKiCk8w/FoovsMYED2qlWEt0i52G+0CjoRFx2zNsN3v4dWiIhk
ZSL00Mx+g3NA7pQ1Yo5Vhok034mP8L2fBLhhWaK3LG63jYvd0HLkUFhNG+xjkpeI
SQQYEQIACQUCSybdoQIbDAAKCRAcTL3NzS79KlacAJ0aAkBQapIaHNvmAhtVjLPN
wke4ZgCePe3sPPF49lBal7QaYPdjqapa1SQ=
=qcCk
-----END PGP PUBLIC KEY BLOCK-----

4.-  Then install percona

#  yum install Percona-Server-client-55 Percona-Server-server-55

5.- do some start/stop operations with percona to verify installation:

# sudo service mysql status
 ERROR! MySQL (Percona Server) is not running
# sudo service mysql start
Starting MySQL (Percona Server).. SUCCESS!

6. Defaultt user is root without password

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30-30.2 Percona Server (GPL), Release rel30.2, Revision 509

Copyright (c) 2000, 2013, 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> 

VERIFYING SYSBENCH

1. show databases:

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30-30.2 Percona Server (GPL), Release rel30.2, Revision 509

Copyright (c) 2000, 2013, 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> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

mysql> exit
Bye
#

2.- create 'sbtest' database, we will use it to execute sysbench

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30-30.2 Percona Server (GPL), Release rel30.2, Revision 509

Copyright (c) 2000, 2013, 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> create database sbtest;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
# 

3. Run sysbench test:

# sysbench --db-driver=mysql --test=/usr/share/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root --mysql-password= prepare
sysbench 0.5:  multi-threaded system evaluation benchmark

Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
#

4. You will see that sbtest has some tables:

mysql> use sbtest;
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
mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
+------------------+
1 row in set (0.00 sec)

mysql>

Enjoy!
P.S. Would you believe about this flag