MySQL Cluster usage has certainly continued to spread and recently accelerate well beyond its initial telco vertical roots into Healthcare, Financial Services, SaaS and more. With those additions it certainly becomes desirable for many to provide transparent encryption on the NDB nodes where the data, logs, and checkpoints that write to disk. I’ll not go into all those reasons in this blog, but certainly there are plenty, these white papers provide more details, especially if you are running within hosted, managed, or cloud environments platforms.
The solution for ndb in a nutshell was straight forward:
1 Set up Gazzang ezNcrypt Flex Platform
2 Stop the ndb process prior to encrypting the ndb_data directory
3 Encrypt the ndb_data directory
ezncrypt -e @ndbdata /home/mysql/my_cluster/ndb_data
4 Add a Flex ACL Rules granting ndbd access to the encryption keys.
ezncrypt-access-control -a “ALLOW @ndbdata * /home/mysql/mysql-cluster-gpl-7.1.18-linux-i686-glibc23/bin/ndbd”
5 Restart ndb
Note: if you setup a single node test environment or if for some some reason want to run it for you will also need to add a rule for ndb_mgmd then also add -
ezncrypt-access-control -a “ALLOW @ndbdata * /home/mysql/mysql-cluster-gpl-7.1.18-linux-i686-glibc23/bin/ndb_mgmd”
Certainly there are many more things you can do to protect MySQL Cluster data on Linux – and I will follow through with those details or details on usage in specific environments including clouds, but this is a good start and shows how easy this is to accomplish, and Gazzang adds key management, process, access, monitoring, and many other benefits aside from the encryption itself. For more ideas around that see this EMA paper
With the release of of our 2.2.2 product coming in February of 2012 you will see that we have added ndb to our supported engines list to MySQL. Gazzangs platform is simple and easy to install and as you can see here. If you are interested just Try it out.
If a DBA needs to reset the mysql root password – one method of doing this is to run mysqld with -skip-grant-tables as a command line parameter. This is a bad practice for many reasons and as a DBA friend of mine says – it’s a lot like locking your door with the window open. If you are using mysql for especially secure data you should consider your options.
One option, and likely preferred, is to replace the default mysql from various sources with one built with this feature disabled – see http://dev.mysql.com/doc/refman/5.1/en/source-configuration-options.html#option_configure_disable-grant-options
I am not aware of any distro’s that are built with this option – but seems like a fair number of folks out there would appreciate having a build like this.
But some either still want to be able to somehow reset roots password, or don’t wish to compile mysql on their own, so the second option is to use Gazzang ezncrypt. Beyond the advantages of the Transparent Encryption which provides encryption for table data, config files, backup data and more, you can also use ezNcrypt to prevent use of –skip-grant-tables. This “closes the window” and will also let you still change the root password if needed. This is accomplished by transparently encrypting the mysqld executable, and adding a simple wrapper executable that will detect and remove the –skip-grant-tables. It can also optionally send a signal to mysqld to force loading the privileges.
If and when you need to change the root password for mysql, you can still do this – but you will need to have and use the proper RSA key and password or Passphrase and Salt, which is protected and only known to a select few in your administration.
Here’s the how-to for skip-grant-table protection
1. Encrypting mysqld process.
# ezncrypt -e @protected /usr/sbin/mysqld
2. Create and compile mysqld wrapper (called mysqld.secure) that calls mysqld process
g++ -o /usr/sbin/mysqld.secure mysqld.secure.cpp
Note: we wrote just a few lines of C++, but you could use a script or perl or php … etc. as the hashes and fingerprinting prevents alternation.
3. Add the following rules using ezncrypt-access-control
# - Type Category Path Process
1 EE ALLOW @mysql * /var/lib/ezncrypt/ezncrypted/protected/usr/sbin/mysqld
2 ALLOW @protected /usr/sbin/mysqld /usr/sbin/mysqld.secure
Note: if mysqld.secure is changed SHA-256 hashing and other fingerprinting detect that it is tainted and permissions will be denied.
4. Edit my.cnf
mysqld = /usr/sbin/mysqld.secure
And that’s it.
When you call:
# mysqld_safe start
# mysqld_multi start
This will call mysqld.secure with all arguments and mysqld.secure will remove any –skip-grant-table found, and it will call /usr/sbin/mysqld without it, and again mysqld can’t be started on its own with –skip-grant-table – unless you have the encryption key.
One of the challenges many face when running a secure encrypted MySQL backup on Linux is using managed or scheduled mysqldumps without exposing them.
- The data – within the mysqldump backup file
- The credentials – that are used connect into mysql
So, how might this be accomplished? I’ll show you one option. It starts with the installation of Gazzang’s ezNcrypt. Its not open source, but it is inexpensive and provides you a simple and secure means to protect and encrypt data transparently with the flexibly to map to your environment and applications.
This technique also applies to other backup tools such as xtrabackup.
Follow these steps to perform a secure mysqldump:
Step 1. Create a mysqldump cnf file. This provides the username, password and secure file destination.
> cat protected.cnf
Note: you can also encrypt this cnf file off the system if needed
Step 2. Encrypt this mysqldump cnf file. If its in plain text it’s not protected.
> sudo ezncrypt –e @mysqlbackup /home/mfrank/protected.cnf
ezncrypt | Checking system dependencies
| Verifying ezncrypt license
| getting information about location
| > path: /var/lib/ezncrypt/ezncrypted/mysqlbackup
ezncrypt | Checking encryption status
keymgr | Retrieving key from KSS
| > Encryption password retrieved from KSS
| generating keys
ezncrypt | encrypting files
| > checking disk space
| > encrypting /home/mfrank/protected.cnf
ezncrypt | congratulations. you have encrypted your Files!!
Step 3. Create a backup directory and set permissions appropriately. This is where the backups will be stored.
> sudo mkdir /var/lib/mysqlbackup
> sudo chown <linux_user> /var/lib/mysqlbackup
Step 4. Encrypt the backup directory with ezNcrypt. All files going to this directory /var/lib/mysqlbackup will be encrypted.
> sudo ezncrypt –encrypt @mysqlbackup /var/lib/mysqlbackup
Step 5. Create the access control rule for mysqldump. This allows access to the key plus permissions to the files in @mysqlbackup.
> sudo ezncrypt-access-control –add “ALLOW @mysqlbackup * /usr/bin/mysqldump”
Step. 6 Run the mysqldump. From either the commandline or cron.
> mysqldump –defaults-extra-file=/home/mfrank/protectedlogin.cnf –all-databases
Note: the backup file and cnf file are actually physically located (for my default installation) in /var/lib/ezncrypt/ezncrypted/mysqlbackup
You can see the links using
> ls –l
Heres the link to the ezNcrypt download if your interested.
Review the benefits of running a mysqldump with ezNcrypt:
- The OS user can now run the backup without knowing the username and password to mysql.
- The OS user cannot read the files that were generated by mysqldump.
- If another process copies the backup file – i.e. a scheduled filesystem backup – the file would be AES-256 encrypted. Without access to the encryption keys the files are protected.
- For recovery, the file can be easily be decrypted (ezncrypt – decrypt) and restored using mysql.
Note: There are ways to setup a trusted auto restore executable or script such that you can restore with seeing the username/password or mysqldump. data as well. I’ll blog about some other time.
5. The files can be sent to another system and decrypted (if you have the key) and then can be restored.
It’s hard to continually develop your own security solutions with encryption and key management. Transparent encryption solves many problems. For a more in-depth look, you might be interested in this EMA paper “Unifying Data Encryption: Liberating Transparent Encryption for Any Purpose”
In conclusion, I think transparent encryption provides a somewhat novel way to accomplish this task that is both easy and secure. It has become increasingly important to improve security and enforce principles of “need to know” and “separation of duties” across business partners and 3rd parties, especially in Cloud and PaaS environments. This is one method towards providing that for MySQL backups using mysqldump.
If you are interested or concerned about MySQL Security you may want to check out OurSQL episode 55 where I discuss transparent encryption with Sheeri Cabral and Sarah Novotny and how it can help to secure mysql data and other linux apps (apache, nosqls, drizzle) plus protection for config files, code ….. and more –
Sheeri and Sarah dive deep with many of the technical questions you might have yourself.
This should be valuable for you whether your concerns are directly mysql related or more general as related to encrypting data or further securing your linux ecosystems.
OurSQL Episode 55: MySQL Data Encryption
MySQL Workbench provides secured access to MySQL Instances and Servers using SSH. The follow FAQ formated blog post shows a few details on what this is and how it works.
What is SSH?
A secure shell and communications tool
- Replaces older insecure telnet, rsh, rlogin, rcp
- Provides the same functionality with added security.
- Avoids passing clear text user-ids & passwords
- Supports strong encryption for user authentication
- All TCP traffic sent through ssh can be strongly encrypted.
- Means that various TCP protocols can be given the same strong level of encryption as the ssh channel.
How is SSH used in Workbench?
- As a secure communication tunnel for Direct MySQL connections
- As a method to remotely monitor and manage MySQL instance via SSH shell commands.
Where is SSH configured in Workbench?
Again 2 places
- Creating/Editing MySQL DB Connections
- Creating/Editing MySQL Server Instances (for remote Adminitration)
When Workbench is setup with SSH
WB SQL Editor and Datamodel will use
- Tunneled MySQL TCP Connections
MySQL Admin will use both
- SSH Shell
Runs MySQL related OS commands
- Tunneled MySQL TCP Connections
Connect and execute SQL-based commands within MySQL Instance
What components are used in Workbench to provide its SSH support?
MySQL Workbench uses the the 100% Python-based Paramiko project to deliver its SSH capabilities.
To learn more about Paramiko >> http://www.lag.net/paramiko/
As always – hope this helps and look forward to any feedback or comments.
For those of you that attended today’s webinar or for those of you who were unable to do so, we discussed and demo’ed many of the new exciting features in Workbench 5.2.
The presentation slides in pdf and audio are available –
If you are currently using MySQL Query Browser or MySQL Administrator, you’ll be pleased to know that their functionality is now in MySQL Workbench, creating a single, unified GUI for designing, developing and administering MySQL databases.
For more info visit:
- WB Downloads: http://dev.mysql.com/downloads/workbench/
- WB Forums: http://forums.mysql.com/index.php?151
- WB Blogs: http://wb.mysql.com/
Beyond simple basic vitualization, there are added new and interesting layers of management abstractions and features offered that MySQL users will benefit from by understanding. Being aware of how you might use these features in your enterprise may be beneficial in your next architecture design. To assist we explored using those HA, FT, load balancing features in VMware in the context MySQL. So if your interested please check out the mysql whitepaper http://preview.tinyurl.com/y8n9ktq
As always looking for feedback or your experience.