Enable and test TDE in MySQL Workbench

mysql workbench is a versatile visual tool for database design, sql development, and server administration it offers advanced data modeling, query building, and mysql specific features for easier management of mysql databases this section demonstrates the following tasks connect to your mysql server from mysql workbench use mysql workbench to create a new database table with transparent data encryption (tde) enabled this generates an aes 256 key on the kmes series 3 that serves as the master encryption key for mysql tde insert example data into the table and list the contents to verify that the data is decrypted transparently confirm that we can successfully rotate the master encryption key stored on the kmes connect to the instance perform the following steps to connect to your mysql server instance by using mysql workbench launch mysql workbench start the program on your computer to open the initial dashboard open database connection window on the mysql workbench dashboard, locate the mysql connections section and select the \[+] button input connection details in the setup new connection window, input the details of your mysql server connection option description or required configuration connection name a label for your reference connection method typically, select standard (tcp/ip) for connecting to a standard mysql server other methods are available, such as tcp/ip over ssh or a local socket/pipes for local connections hostname the ip address or domain name of the mysql server you're connecting to if your mysql server is on the same machine as your mysql workbench, you can use localhost port the port number that the mysql server is listening on the default mysql port is 3306 username the username you use to authenticate with the mysql server password if your account requires a password, select \[ store in vault ] to enter and save your password test connection after you enter the preceding details, select \[ test connection ] to ensure that your settings are correct and that mysql workbench can reach the mysql server connect if the test succeeds, select \[ ok ] to close the window and save the connection then, double click on the saved connection to connect to your mysql server create a new database table with tde enabled open a new sql tab for executing queries run the following query to create a new encrypted table called t1 in the world schema/database, which is a sample database that comes pre installed in mysql use world; create table t1 (column1 int) encryption='y'; alternatively, you can enable encryption for an existing table by using the following sql query alter table t1 encryption='y'; insert example data into the table open a new sql tab for executing queries run the following query to insert example data into the t1 table use world; insert into t1 (column1) values (10), (20), (30); verify the data is decrypted transparently open a new sql tab for executing queries run the following query to retrieve the data you inserted into the t1 table select from t1; this should return the rows you just inserted, unencrypted from the user perspective, using transparent data encryption (tde) is indeed transparent data is automatically decrypted when you select it, and you won't see any difference compared to unencrypted data tde is about securing data at rest—that is, the data files on disk are encrypted when data is read from disk into memory, it's automatically decrypted by the mysql server when data is written back to disk, it's automatically encrypted so, as a user, you won't see any difference between encrypted and unencrypted data when you're querying it the encryption doesn't affect the data itself, just how the data is stored on disk the purpose of tde is to protect data if someone gets unauthorized access to the raw database files on the disk rotate the tde master key open a new sql tab for executing queries run the following query to rotate the tde master key alter instance rotate innodb master key; verify the rotation by inserting some new data into your encrypted table and then retrieving it insert into t1 (column1) values (40); select from t1; if the insert and select operations succeed, mysql can use the new key to encrypt and decrypt data the process of key rotation doesn't re encrypt existing data with the new key—it just uses the new key for new encryptions you must retain the old versions of the key as long as there is data that was encrypted with them view the mysql tde master keys on the kmes log in to the kmes series 3 application interface with the default admin identities go to key management > keys you can see the keys that mysql tde created through kmip in its respective key group