Recent Presentations in Cali, Colombia

On July 4 I gave two presentations at the OTN Tour Day, and on July 5 I have three presentations at the MySQL Training Days. This was my 3rd visit to Colombia and it was great to see a receptive audience. Thanks to Robin for organizing the events in 2010, 2011 and 2012.

You can download all presentations from the provided links.

New security fixes for MySQL reported

6 new security fixes for Oracle MySQL have been detailed in the most current Oracle Critical Patch Update (CPU).

These are:

  • CVE-2012-1735 (5.5.23 and earlier)
  • CVE-2012-0540 (5.1.62 and earlier, 5.5.23 and earlier)
  • CVE-2012-1757 (5.5.23 and earlier )
  • CVE-2012-1756 (5.5.23 and earlier)
  • CVE-2012-1734 (5.1.62 and earlier, 5.5.23 and earlier )
  • CVE-2012-1689 (5.1.62 and earlier, 5.5.22 and earlier )

Oracle strongly recommends that customers apply CPU fixes as soon as possible. Unfortunately there is no easy description for MySQL users what that really entails. There is a reference to Critical Patch Update July 2012 Patch Delivery Document for Oracle Sun Products Suite My Oracle Support Note 1446033.1, however all the information is behind having a support license. There appears to be no information easily available for the community users.

A full description of these CVEs can be found here. Unfortunately most say Vulnerability in the MySQL Server component of Oracle MySQL (subcomponent: Server). Supported versions that are affected are 5.5.23 and earlier. Easily exploitable vulnerability allows successful authenticated network attacks via multiple protocols. Successful attack of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete DOS) of MySQL Server. which is effectively useless information.

There is external information that can be found at the National Vulnerability Database (not linked in the Oracle article). For example http://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2012-1735 however this does not provide any more meaningful information either.

There was a recent 5.5.25a released on 2012-07-05, however this, 5.5.25, 5.5.24, and the yet to be released 5.5.26 release notes provide no information about these security issues.

While security is important for a database and system administrator, on first inspection the information provided does not offer an easy way to assess the risk and take appropriate actions.

More information at

I will be speaking at Percona Live New York

Percona Live New York City, October 1 - 2, 2012
Percona is back for a second New York Percona Live Conference. As the resident New York MySQL Expert, I will again be presenting. My session will be on MySQL Backup and Recovery Essentials.

You can only present so much in one hour, and this presentation just touches on the highlights of what is possible. More detailed information about the right backup and recovery strategy and associated tools is available in my current book Effective MySQL: Backup and Recovery.

Encrypting your MySQL backups and more

Assuming you have a backup and recovery strategy in place, how secure is your data? Does a hacker need to obtain access to your production system bypassing all the appropriate security protection you have in place, or just the unencrypted data on the backup server?

Encryption with zNcrypt

The following steps demonstrate how I setup a mysqldump encrypted backup with zNcrypt, a product from Gazzang. You can request a free trial evaluation of the software from http://gazzang.com/request-a-trial. I asked for a AWS EC2 instance, and was able to provide my bootstrap instructions for OS and MySQL installation. Following installation and configuration, the first step is to verify the zNcrypt process is running:

$ sudo ezncrypt-service status
  ezncrypt | Checking system dependencies
** ezncrypt system is UP and running **
       log | File: /var/log/ezncrypt/ezncrypt-service.log

If the process is not running you would find the following error message:

$ sudo ezncrypt-service status
  ezncrypt | Checking system dependencies
** ezncrypt system is NOT running **
       log | File: /var/log/ezncrypt/ezncrypt-service.log

$ sudo ezncrypt-service start
  ezncrypt | Checking system dependencies
  ezncrypt | checking encryption directories
    keymgr | Retrieving key from KSS
           |  > Encryption password retrieved from KSS
  ezncrypt | starting service
           |  > using "aes_256" cipher algorithm
           | done!
    access | Loading access control list
           | done!
  ezncrypt | Thank you for using ezncrypt.
       log | File: /var/log/ezncrypt/ezncrypt-service.log

Under the covers you will find the following attached devices, and no actual processes.

$ df -h
Filesystem ...
...
/var/lib/ezncrypt/storage/encrypted_private
/var/lib/ezncrypt/ezncrypted

$ ps -ef | grep ezn
uid  4947  3327  0 23:15 pts/3    00:00:00 grep ezn

$ ps -ef | grep cry
root        30     2  0 21:41 ?        00:00:00 [ecryptfs-kthrea]
root        31     2  0 21:41 ?        00:00:00 [crypto]
uid  4951  3327  0 23:15 pts/3    00:00:00 grep cry

The first step is to create a backup directory and encrypt all contents that are placed in the directory. ezNcrypt uses the concept of an @category for reference with an encrypted file or directory.

$ mkdir /mysql/backup/encrypted
$ sudo ezncrypt --encrypt @backup /mysql/backup/encrypted
  ezncrypt | Checking system dependencies
           | Verifying ezncrypt license
           | getting information about location
           |   > path: /var/lib/ezncrypt/ezncrypted/backup
  ezncrypt | Checking encryption status
           | done!
    keymgr | Retrieving key from KSS
           |  > Encryption password retrieved from KSS
           | generating keys
           | done!
    backup | backing up data
           | This can take a while. Please be patient
           |  > backing up /mysql/backup/encrypted
           |  > File: /opt/ezncrypt/backup/2012-04-27/encrypted.tar.gz
           | done!
  ezncrypt | encrypting files
           |  > checking disk space
           |  > encrypting /mysql/backup/encrypted
           | done!
  ezncrypt | congratulations. you have encrypted your Files!!
       log | File: /var/log/ezncrypt/ezncrypt.log

The underlying regular directory is now replaced:

$ ls -l /mysql/backup
total 0
lrwxrwxrwx 1 root root 59 2012-04-27 00:03 encrypted -> /var/lib/ezncrypt/ezncrypted/backup//mysql/backup/encrypted

Any attempts to write to this encrypted directory will now fail, even with the Linux super user:

$ mysqldump --all-databases > /mysql/backup/encrypted/edump1.sql
-bash: /mysql/backup/encrypted/edump1.sql: Permission denied

$ sudo mysqldump --all-databases > /mysql/backup/encrypted/edump1.sql
-bash: /mysql/backup/encrypted/edump1.sql: Permission denied

In order to read and write from an encrypted directory you need to grant access controls to a given program, for example mysqldump:

$ sudo ezncrypt-access-control -a "ALLOW @backup * /usr/bin/mysqldump"
passphrase:
salt:
Rule added

You verify the defined access control rules with:

$ sudo ezncrypt-access-control -L
passphrase:
salt:
# -  Type     Category       Path    Process
1    ALLOW    @backup        *       /usr/bin/mysqldump

However, writing with mysqldump still causes an error because it is the shell redirection that is performing the writing, as seen in the system error log:

$ mysqldump --all-databases > /mysql/backup/encrypted/edump1.sql
-bash: /mysql/backup/encrypted/edump1.sql: Permission denied
$ dmesg | tail
[4138848.618559] ezncryptfs: DENIED type="acl" exec="/bin/bash" script="/dev/pts/4" comm="bash" path="/var/lib/ezncrypt/ezncrypted/backup" pid=7448 uid=1000

You can use the –result-file option with mysqldump to enable the process to create the file directly. For example:

$ time mysqldump --all-databases   --result-file=/mysql/backup/encrypted/edump2.sql
real      1m34.714s
user      0m59.388s
sys       0m9.589s

$ sudo ezncrypt-run "ls -l /mysql/backup/encrypted/"
passphrase:
salt:
total 3.0G
-rw-rw-r-- 1 uid gid 2.9G 2012-04-27 02:43 edump2.sql

In this single test, the transparent encryption added only a very nominal overhead to the mysqldump test backup used. You can easily extract the file from the encrypted directory, however that would defeat the purpose of using encryption. The following syntax is shown just to confirm the validity of the encrypted file:

$ sudo /usr/sbin/ezncrypt-run "cp /mysql/backup/encrypted/edump2.sql ."
passphrase:
salt:
$ ls –al edump*
total 3916
-rw-r--r-- 1 uid gid 2.9G 2012-04-27 02:55 edump2.sql


$ grep "^CREATE.*DATABASE" edump2.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `book2` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `employees` ...
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `musicbrainz` ...
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` ...
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sakila` ...
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `world_innodb` ...
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `world_myisam` ...

When using correctly configured directories and access controls, the use is truly transparent to the backup process.

Restoring an encrypted file is a little more involved. The best approach is to create a script to perform the work, than encrypt this script. When executed, this script will have the permissions necessary to read and apply the encrypted file.

Perhaps the best tip about using this type of transparent encryption is that it is possible to encrypt the MySQL user and password securely in a plain text configuration file and used with appropriate MySQL client commands. This helps to address another common security problem.

What compression do you use?

The following is an evaluation of various compression utilities that I tested when reviewing the various options for MySQL backup strategies. The overall winner in performance was pigz, a parallel implementation of gzip. If you use gzip today as most organizations do, this one change will improve your backup compression times.

Details of the test:

  • The database is 5.4GB of data
  • mysqldump produces a backup file of 2.9GB
  • The server is an AWS t1.xlarge with a dedicated EBS volume for backups

The following testing was performed to compare the time and % compression savings of various available open source products. This was not an exhaustive test with multiple iterations and different types of data files.

Compression
Utility
Compression Time
(sec)
Decompression Time
(sec)
New Size
(% Saving)
lzo (-3) 21 34 1.5GB (48%)
pigz (-1) 43 33 995MB (64%)
pigz (-3) 56 34 967MB (67%)
gzip (-1) 81 43 995MB (64%)
fastlz 92 128 1.3GB (55%)
pigz [-6] 105 25 902MB (69%)
gzip (-3) 106 43 967MB (67%)
compress 145 36 1.1GB (62%)
pigz (-9) 202 23 893MB (70%)
gzip [-6] 232 78 902MB (69%)
zip 234 50 902MB (69%)
gzip (-9) 405 43 893MB (70%)
bzip2 540 175 757MB (74%)
rzip 11 minutes 360 756MB (74%)
lzo (-9) 20 minutes 82 1.2GB (58%)
7z 33 minutes 122 669MB (77%)
lzip 47 minutes 132 669MB (77%)
lzma 58 minutes 180 639MB (78%)
xz 59 minutes 160 643MB (78%)

Observations

  • The percentage savings and compression time of results will vary depending on the type of data that is stored in the MySQL database.
  • The pigz compression utility was the surprising winner in best compression time producing at least a size of gzip. This was a full 50% faster than gzip.
  • For this compression tests, only one large file was used. Some utilities work much better with many smaller files.

Find our more information of these tests and the results in Effective MySQL: Backup and Recovery

Recent Presentations Buenos Aires MySQL/NoSQL/Cloud Conference

The first annual Latin America MySQL/NoSQL/Cloud Conference was held in Buenos Aires Argentina from June 26-28. Kudos to Santiago Lertora from Binlogic who had the vision for the conference in his country and made it happen. I look forward to the second annual event.

My first presentation was “Improving Performance with Better Indexes”. This presentation details the six steps to SQL performance analysis, Capture, Identify, Confirm, Analyze, Optimize and Verify. An explanation of MySQL EXPLAIN, and working examples to create indexes and better covering indexes in several examples are provided. A production example of a 13 table join is used to detail how covering indexes and partial column indexes can make a dramatic improvement in performance. Download Presentation (PDF).

More detailed information about EXPLAIN and creating indexes is available in book Effective MySQL: Optimizing SQL Statements.

My second presentation was “MySQL Backup and Recovery Essentials”. This presentation covers the most common options for MySQL backup and the respective restore options. Also covered is the importance of the master binary logs and point in time recovery capabilities. Download Presentation (PDF)

More detailed information about the right backup and recovery strategy and associated tools is available in book Effective MySQL: Backup and Recovery.

References

Latin America MySQL/NoSQL/Cloud Conference Program.

Upcoming MySQL Connect Presentations


The MySQL Connect 2012 conference event being held in San Francisco on Sep 29-30 has a long list of quality MySQL speakers including myself. I will be giving 2 presentations on:

CON8322 – Lessons from Managing 500+ MySQL Instances

In this presentation, learn about the issues of managing a large number of instances of MySQL, supporting 50 billion SQL statements per day. Topics covered:
• The need for monitoring and instrumentation
• How to automate installations, upgrades, and deployments
• Issues with MySQL’s Replication feature with 300 slaves per master
• Traffic minimization techniques
• Creating high availability with regions and zones
• Real-time traffic stats (aggregated every five seconds)

CON8320 – Improving Performance with Better Indexes

Learn how to use one simple advanced technique to make better indexes in MySQL and improve your queries by 500 percent or more. Even with a highly indexed schema, you can achieve significant improvements in performance by creating better indexes. This presentation introduces an approach to correct identification and verification of problem SQL statements and then describes the means of identifying index choices for optimization. Then it discusses not only how to apply indexes to improve query performance but also how to apply better indexes and provide even greater performance gains.

You can also read more information with my Interview about MySQL Connect.

Recent Presentations at Charlotte South East LinuxFest

At the recent South East LinuxFest in June 2012 I gave two MySQL presentations.

The first was on Explaining the MySQL Explain. This presentation details the MySQL Query Execution Plan (QEP) of an SQL statement and how to understand and interpret the information from the EXPLAIN command. Also discussed are additional commands and tools that exist to add supplementary information. These are essential skills that will be used daily in production operations. Download Presentation (PDF)

Effective MySQL: Optimizing SQL StatementsMore detailed information about EXPLAIN and associated commands is available in book Effective MySQL: Optimizing SQL Statements.

Effective MySQL:Backup and Recovery
The second was on MySQL Disasters, and how to avoid yours. Organizations are always making improvements for scalability, however disaster preparedness is the poor cousin. This presentation will show you how to easily avoid the most common MySQL disaster situations.
Backup and recovery is critical for business continuity, many websites run the risk of data loss or corruption because existing procedures (if any) are generally flawed.
Download Presentation (PDF

More detailed information about the right backup and recovery strategy and associated tools is available in book Effective MySQL: Backup and Recovery.

References

South East Linux Fest Agenda