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!