понедельник, 6 мая 2013 г.

How to encrypt MySQL database wih eCryptfs

1. Stop MySQL server

First stop MySQL server
$ sudo service mysl.server stop

2. Login as root 
$ sudo su -

3. Backup the database

$ mkdir -p ~/Backup/var
$ mv -v /usr/local/mysql/var/* ~/Backup/var

4. Create a folder for encrypted files

$ mkdir /usr/local/mysql/Private
$ chown mysql /usr/local/mysql/Private

5. Mount MySQL data folder 
$ mount -t ecryptfs /usr/local/mysql/Private /usr/local/mysql/var
You will be asked to enter encryption parameters and a passphrase.
Remember the passphrase.

6. Restore the database files from the backup folder
$ cp -Rpv ~/Backup/var/* /usr/local/mysql/var

7. Disable auto-starting MySQL server
You have to disable auto-starting MySQL server because you cannot start MySQL server before you mount the data folder and enter the passphrase. I use Ubuntu and my default runlevel is 2.

$ rm /etc/rc2.d/mysql.server

8. Now let's create a script which mounts the data folder and starts MySQL server.

Let's find out which mount options we need to use in the script. We need to indicate mount options for eCryptfs in the script to  prevent being asked about encryption parameters each time we mount the data folder.

$ mount
/usr/local/mysql/Private on /usr/local/mysql/var type ecryptfs (rw,ecryptfs_sig=1dd11cc502efe9d0,ecryptfs_cipher=aes,ecryptfs_key_bytes=16,ecryptfs_unlink_sigs)

$ vi /usr/local/bin/mysql-sec.sh

sudo mount -t ecryptfs /usr/local/mysql/Private /usr/local/mysql/var -o ecryptfs_passthrough=n,ecryptfs_enable_filename_crypto=n,ecryptfs_cipher=aes,ecryptfs_key_bytes=16,ecryptfs_unlink_sigs
sudo service mysql.server start

$ chmod +x  /usr/local/bin/mysql-sec.sh

9. Re-boot the computer

Then run the script.

$ sudo mysql-sec.sh

You will be asked to enter the passphrase.  After the script exits you should be able to connect to the mysql server and execute SQL  queries.
If anything goes wrong  you can recover from the backup folder which is /root/Backup/var.