New Features In MySQL 6.x

By Peter Gulutzan

A new addition inside the new “MySQL Online Backup” feature is encryption. Australian Senior Software Engineer Stewart Smith has already mentioned it a few months ago in his blog. There’s a preview that you can download on the online backup pages. It’s probably going to get architecture-reviewer approval next week. Probably it will be part of MySQL-6.0 source downloads soon. I extract some paragraphs from what I wrote in a non-public worklog task, WL#4271.

For BACKUP:
BACKUP DATABASE database_name TO ‘image-file-name’
ENCRYPTION_ALGORITHM = { AES | 3DES }
[ ENCRYPTION_KEYSIZE = { 128 | 192 | 256 } ]
PASSWORD = ‘password’;

For RESTORE:
RESTORE FROM ‘image-file-name’
ENCRYPTION_ALGORITHM = { AES | 3DES }
[ ENCRYPTION_KEYSIZE = { 128 | 192 | 256 } ]
PASSWORD = ‘password’;

According to WL#4037 “Online backup: Use zlib compression to reduce backup file size”, there might be compression clauses, for example “WITH COMPRESSION COMPRESSION_ALGORITHM = gzip”. The order does not matter, that is, this worklog task does not attempt to specify the relative positions of the compression and encryption clauses. But in any case compression occurs before encryption.

If ENCRYPTION_ALGORITHM = AES then ENCRYPTION_KEYSIZE may be specified, or it may be omitted (default = 128). If ENCRYPTION_ALGORITHM = 3DES then ENCRYPTION_KEYSIZE may not be specified.

The minimum length of password is zero. The maximum length of password is implementor-defined.

There are no new privilege requirements.

Example:

BACKUP DATABASE d TO ‘1′ ENCRYPTION_ALGORITHM=AES ENCRYPTION_KEYSIZE=128
PASSWORD=”;
RESTORE FROM ‘1′ ENCRYPTION_ALGORITHM=3DES PASSWORD=”;

Effect

MySQL encrypts with the specified algorithm and password, for example something analogous to AES_ENCRYPT(,’password’);
MySQL decrypts with the specified algorithm and password, for example something analogous to AES_DECRYPT(,’password’);

Therefore the RESTORE statement will fail if the algorithm and password differ in any way from what was used in BACKUP. For example, if backup was done with BACKUP DATABASE d TO ‘1′
ENCRYPTION_ALGORITHM=AES ENCRYPTION_KEYSIZE=256 PASSWORD=”, these statements will fail:
RESTORE FROM ‘1′;
RESTORE FROM ‘1′ ENCRYPTION_ALGORITHM=AES PASSWORD=”;
RESTORE FROM ‘1′ ENCRYPTION_ALGORITHM=AES ENCRYPTION_KEYSIZE=256 PASSWORD=’a';

Encryption is of the whole image, not per-database.

Assumptions

Assume password interception won’t occur, because there is some protection from an SSL connection or due to a later worklog task, WL#1054 “Pluggable authentication support”.

Assume logs which would contain passwords are stored in secure places. The PASSWORD clause is logged, replicated, and apparent in columns like mysql.online_backup.command — but protected by privileges on such metadata.

Assume PROCESS privilege is rare so don’t worry that SHOW PROCESSLIST exposes the IDENTIFIED BY clause.

Assume AES and 3DES are all we need for now.

New Reserved words

None.

Encryption Algorithms

The encryption algorithms are in the yassl library. If mysqld is built without the yassl library, then the encryption/decryption algorithms won’t work, at least for now.

Observations on the preview

There are a few anomalies that might be bugs. Generally I don’t report bugs to bugs.mysql.com until/unless they’re in a main tree.

1. This statement causes a crash:
restore from ‘5.bak’ password=’pants’;

2. This statement causes no error although keysize should not be 0 or have a default:
backup database test to ‘5.bak’ encryption_algorithm=aes encryption_keysize=0 password=”The Rain In Spain”;

3. This statement causes a crash:
backup database test to ‘4.bak’ encryption_keysize=128;

4. This series of statements causes a crash:
use test
drop table if exists t;
create table t (s1 varchar(1000)) engine=falcon;
insert into t values (0×1);
set names utf8;
backup database test to ‘1.bak’ encryption_algorithm=aes password=’Γ’;
/* The Γ character is GREEK CAPITAL LETTER GAMMA U+0393 */

5. Nothing happens to backup_history or backup_progress.

6. This statement causes no error although AES should not be inside “s:
BACKUP DATABASE test TO ‘8.bak’ ENCRYPTION_ALGORITHM=`aes` PASSWORD=”;

Last month I went to the Sun Microsystems / MySQL Developer Conference in Riga Latvia. Along with about 250 other people. The majority were MySQL developers, but as usual we had several attendees from MySQL support and administration, and a few non-MySQL people came to talk about Google, Drizzle, and PBXT. This was one of our annual “internal” conferences, so lots of stuff that we talked about in Riga will only be publicly announced around, say, the time of the next MySQL User Conference . I’ll just talk about the stuff that’s already leaked, and about my own involvements.

First let me give my impression of ‘morale’. It’s up, way up, since two years ago. I attribute this to two causes: removal of a problem, and a better team.

Huh? Haven’t I read non-MySQLers’ blogs about people leaving the team? Sure, and I mumble to myself ‘get a grip, get a sense of proportion’ when I read them. All organizations have comings and goings. Some of the departures are exaggerated; for example Jim Starkey hasn’t totally left us. Others won’t affect development because the departers haven’t been contributing to the code base significantly for a long time, they’ve concentrated on evangelism or visionary stuff. More people have joined than have left, and I wish it was obvious how high-quality the new ones are.
* We have several new managers from “Classic Sun” with experience and training as managers. I laud the pure-old-MySQL Team Leads who were running some things before, but they were seeing supervisory or project-administration tasks as burdens, and the ones I’ve talked to seemed glad that their trophies at last they lay down.
* We have other Sun teams helping us out with performance analysis (especially on SPARC or Solaris), and with putting together builds on exotic platforms (MySQL has several million machines available for community testing but having direct access to more machines is a plus too).
* We have new people joining MySQL development teams from other Sun departments. I’ve hesitated to say that the difference so far has been significant, but orientation time for a developer is always several months, and now that we old-guarders have met these ‘newbies’ we’re happy. I lost count of the corridor conversations I overheard that went something like “hey, so-and-so seems to be what we needed for [online backup] [optimizer] [fill-in-the-blank]“.

Other things that contributed to my personal morale were the great hotel facilities, the fact that Georg Richter (Development Manager - Connectors and Client Connectivity Engineering) made sure all the Russians got visas this time (I really missed seeing the whole gang on a previous occasion), and the sound of people actually discussing the work that I think is important. Our idea of having developers working out of home offices worldwide is great, but at least once a year we have to interact with people instead of with IRC chat lines and Skype noises. I’m less isolated than most since I’m not the only MySQLer in Edmonton Canada; however, man is a social animal.

The big theme for all-hands talk was MySQL version 5.1; don’t get the wrong impression from me; I was cheering along with the crowd when our fearless leaders exhorted us to give 5.1 all our waking thoughts. And now I’ll drop that topic since this is a MySQL-6.x blog.

The high point of the conference was a slide show by MySQL 6.x release architect Peter Gulutzan (that’s me) about what’s in the 6.x roadmap. Community Relations Manager Lenz Grimmer took a few pictures, here and here. I’d like to say it was well received, and perhaps most objective observers would say that it was. But our technical discussions do sometimes lead to what diplomats call ‘a frank exchange’. Updating from what’s been said earlier for 6.x: (1) maybe we’re using data types BINARY and VARBINARY excessively and maybe the fixing should happen before I thought it would; (2) specific references about optimizer-related tasks should now be regarded as uncertain; (3) online backup might occur with a different schedule, although this mostly means that things will happen sooner (my next blog post will discuss one of the new online-backup features that we settled in Riga); (4) Sun has ‘globalization requirements’ that we have to meet with 6.1 so that means more character-set and collation tasks for Alexander Barkov (Technical Lead of Localization); (5) there are new committees charged with commenting about new features or overriding decisions made by other committees. I should re-explain here that we often use the term ‘6.x’ for ‘MySQL 6.1, 6.2, or [if necessary] 6.3′ not 6.0.

A historic occasion: I and Senior Software Developer Marc Alff gave the first-time-ever live demo of MySQL’s SIGNAL and RESIGNAL statements, and of a bombshell project that’s so neat we will defer discussion till we know how to describe it better. A picture is here. That’s me in the background demoing to Senior QA Architect Omer BarNir. Marc is the French-looking guy with the rapt audience. One question that I had to face often was “since it works today why will you wait many months to release it?” but I fended that off with the usual that’s-not-my-department shrugging.

Another historic occasion: the “WL#148 (Implement Foreign Keys (all storage engines)” project reached Milestone 7. This project is approximately on schedule. That’s a good thing because soon we’re going to have to say what’s in ‘6.1′ as opposed to the vague ‘6.x’ designation, and I see no reason why WL#148 will be anything but a flagship feature in that release.