Percona MySQL Encryption is a powerful tool that ensures data security and privacy. It is an open source solution that provides encryption for the MySQL database. Percona provides a variety of encryption options for users to choose from, and it is easy to implement and maintain.
In this article, We will try to learn step by step about MySQL keyring encryption.
OS Version: "Ubuntu 20.04.3 LTS"
Database: 8.0.30-22 Percona Server (GPL)
MySQL Keyring Encryption
Before beginning the process, it is essential to understand why we are doing this. If the data directory is moved to another server and the database is started, all tables will be accessible. However, if the keyring encryption is configured, even if the data directory files are taken, the tables will not be accessible, since the tables can only be accessed through the keyring file. If the keyring file isn't available when the database is started, the tables will be inaccessible.
MySQL generates a .ibd file in the data directory for each table. If we utilize the string function of this file, the content will be visible. However, if the keyring encryption is used, the .ibd file will be encrypted, thus preventing us from viewing the content.
Now Let's begin with some hands-on work.
- Edit the my.cnf file.
vi /etc/mysql/conf.d/my.cnf
[mysqld] keyring_file_data=/var/lib/mysql-keyring/keyring early-plugin-load=keyring_file.so bind-address=*
- Login to the database and install the keyring plugin.
mysql> INSTALL PLUGIN keyring_file SONAME 'keyring_file.so';
- Restart the database.
service mysql restart
- Now VERIFY the KEYRING INSTALLATION which we installed in the previous step.
mysql> SELECT plugin_name, plugin_status FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name LIKE 'keyring%'; mysql> show global variables like '%keyring%';
- We will now create two tables in order to test the encryption; one table will be encrypted and the other will remain unencrypted so we can observe the difference. We will also insert a few records into the table.
create database enc;
use enc;
CREATE TABLE UNENCRYPTED_table(Col1 VARCHAR(255));
insert into UNENCRYPTED_table values('This is Un-Encrypted Data');
insert into UNENCRYPTED_table values('This is Un-Encrypted Data1' );
insert into UNENCRYPTED_table values('This is Un-Encrypted Data3' );
CREATE TABLE ENCRYPTED_TABLE(col1 VARCHAR(255)) ENCRYPTION='Y';
insert into ENCRYPTED_TABLE values('This is Encrypted Data1' );
insert into ENCRYPTED_TABLE values('This is Encrypted Data2' );
insert into ENCRYPTED_TABLE values('This is Encrypted Data3' );
- Now let's verify the .ibd file of both tables.
UNENCRYPTED_TABLE
ENCRYPTED_TABLE
- Let's now check by deleting the keyring file and restarting the database.
Before removing them, we will verify that the tables are accessible.
Let's now create a backup of the keyring file, delete the original, and then restart the database.
cd /var/lib/mysql-keyring/
cp -rp keyring keyring_bkp
rm keyring
ls -ltrh
In the database error log file, you can see the errors as well.
If the encryption can't find the master key, please check that the keyring is loaded. If the encryption information in the data file: ./enc/ENCRYPTED_TABLE.ibd can't be decrypted, please confirm that keyring is loaded.
Now let's look into the tables. We will find that the encrypted table is not available to us, whereas the unencrypted table is accessible.
Select * from ENCRYPTED_TABLE;
ERROR 3185 (HY000): Can't find the master key from the keyring, please check in the server log if a keyring is loaded and initialized successfully.
Summary:
Database encryption is an important tool for protecting sensitive data. It can be used to secure data stored on disks and tapes, as well as data transmitted over networks. By encrypting data, organizations can ensure that only authorized users can access the data and that it remains safe from malicious actors. Encryption also provides an additional layer of security, as it can protect data even if the database is compromised. With the right encryption methods and strategies, organizations can ensure that their databases are secure and that their data is properly protected.
*If you spot any errors or have a suggestion for a better technique, please leave a comment. Thank you!
