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=”;

This entry was posted on Sunday, October 12th, 2008 at 8:04 pm and is filed under MySQL 6.0 New Features. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.