ChatGPT is the hottest topic in the tech world right now. One story even says that ChatGPT has passed Google’s Level 3 programming interview. I wondered, does that mean ChatGPT is ready to replace MySQL DBAs, too? No. Let me show you why.
Recently, one of our clients was considering encrypting their data at rest using the Percona file-based keyring plugin. To make the process more secure, they considered removing the local keyring_file after MySQL started. So even if someone gets host access, the data files are still protected because they do not have access to the master key used to encrypt the keys for the tables running Encryption at Rest.
Let ChatGPT try its hand at MySQL administration
Let’s see what ChatGPT will say.
I asked ChatGPT the question: “Can we backup and then remove the keyring file used for encryption at rest plugin after MySQL starts?”
The answer from ChatGPT: “No, it is not recommended to remove the keyring file used for the encryption at rest plugin while MySQL is running, as this can lead to data corruption or unexpected behavior. The keyring file is an important part of the encryption at rest plugin and is used to securely store encrypted information, such as passwords and other sensitive data.
To safely make changes to the keyring file, it is recommended to stop the MySQL service, back up the file, and then make any necessary modifications or deletions. Once the changes have been made, you can start the service again.
Failing to stop the MySQL service before making changes to the keyring file can result in data loss or corruption, and it may not be possible to recover the data. To ensure the safety and integrity of your data, it is important to follow the recommended procedure of stopping the service, backing up the file, and making any necessary changes.”
The answer looks so correct at first glance and seems very reasonable if you don’t have enough experience working on the Percona file-based keyring plugin. In fact, the answer is only partially correct or even entirely wrong in many places.
First, we can remove the keyring file used for the encryption at rest plugin while MySQL is running. The plugin will still work as normal, to encrypt a new table, to decrypt the contents of encrypted tables, without corruption, or unexpected behavior.
This is because the keyring plugin on startup loads the entire content of the keyring_file (i.e., key id, key user, key type, together with keys themselves) into the cache, unless MySQL is restarted, or you want to rotate the master key (you won’t though, as you know you have removed the keyring_file), it will not need the keyring file anymore. We can verify that through the following example:
- We started MySQL with the configuration of “early-plugin-load=keyring_file.so and keyring_file_data=/var/lib/mysql/keyring_file” to enable the Keyring_file plugin. When MySQL started, it created an empty file /var/lib/mysql/keyring_file. However, when we then create a table using ENCRYPTION=’Y’, we can see that the file /var/lib/mysql/keyring_file is not empty anymore. This is because the Keyring_file plugin has created a master key putting all the information in memory and saving a backup copy of the data into the file /var/lib/mysql/keyring_file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | ec2-user@ip-172-31-32-242 ~]$ cat /etc/my.cnf #encryption at rest early-plugin-load=keyring_file.so keyring_file_data=/var/lib/mysql/keyring_file mysql> create database test; mysql> show create schema testG *************************** 1. row *************************** Database: test Create Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ [ec2-user@ip-172-31-32-242 ~]$ sudo ls -lhr /var/lib/mysql/ -rw-r----- 1 mysql mysql 0 Feb 9 00:04 keyring_file mysql> use test; Database changed mysql> CREATE TABLE t1 (c1 INT, PRIMARY KEY pk(c1)) ENCRYPTION='Y'; mysql> insert into t1(c1) values(1); mysql> select * from t1; +----+ | c1 | +----+ | 1 | +----+ mysql> show create table t1G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int NOT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y' [ec2-user@ip-172-31-32-242 ~]$ sudo ls -lhr /var/lib/mysql/keyring_file -rw-r----- 1 mysql mysql 187 Feb 9 00:07 /var/lib/mysql/keyring_file mysql> show variables like '%UUID%'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | ccdb4a6c-9c3b-11ed-805e-0a357402d413 | +---------------+--------------------------------------+ [ec2-user@ip-172-31-32-242 ~]$ sudo vi /var/lib/mysql/keyring_file 00000000: 4b65 7972 696e 6720 6669 6c65 2076 6572 Keyring file ver 00000010: 7369 6f6e 3a32 2e30 8000 0000 0000 0000 sion:2.0........ 00000020: 3000 0000 0000 0000 0300 0000 0000 0000 0............... 00000030: 0000 0000 0000 0000 2000 0000 0000 0000 ........ ....... 00000040: 494e 4e4f 4442 4b65 792d 6363 6462 3461 INNODBKey-ccdb4a 00000050: 3663 2d39 6333 622d 3131 6564 2d38 3035 6c-9c3b-11ed-805 00000060: 652d 3061 3335 3734 3032 6434 3133 2d31 e-0a357402d413-1 00000070: 4145 537d 7c7f a42e 8e5a 85ff 2301 cd95 AES}|....Z..#... 00000080: d4b0 9e67 dd5c 3bfe 1cc7 77e2 8d22 57e6 ...g.;...w.."W. 00000090: bb60 c500 0000 0000 454f 4691 068e 5190 .`......EOF...Q. 000000a0: d204 4689 8620 3022 80fb 90af 4b9d 1302 ..F.. 0"....K... 000000b0: 5a7c 84bb 516f 07a8 d1b6 9a0a Z|..Qo...... |
- We backed up and then removed the file keyring_file without restarting MySQL. MySQL is still running fine, we can select from the encrypted table, and we can also create a new table with ENCRYPTION=’Y’; everything works the same as before we removed the file keyring_file
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | [ec2-user@ip-172-31-32-242 ~]$ sudo mv /var/lib/mysql/keyring_file /var/lib/mysql/keyring_file_1 [ec2-user@ip-172-31-32-242 ~]$ sudo ls -lhr /var/lib/mysql/keyring_file ls: cannot access /var/lib/mysql/keyring_file: No such file or directory [ec2-user@ip-172-31-32-242 ~]$ sudo ls -lhr /var/lib/mysql/keyring_file_1 -rw-r----- 1 mysql mysql 187 Feb 9 00:07 /var/lib/mysql/keyring_file_1 mysql> select * from t1; +----+ | c1 | +----+ | 1 | +----+ mysql> CREATE TABLE t2 (c1 INT, PRIMARY KEY pk(c1)) ENCRYPTION='Y'; mysql> show create table t2G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `c1` int NOT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y' mysql> insert into t2(c1) values(2); mysql> select * from t2; +----+ | c1 | +----+ | 2 | +----+ |
- We restarted MySQL, and found out MySQL started with errors complaining ”[InnoDB] Encryption can’t find master key, please check the keyring is loaded.”. But it created another empty file /var/lib/mysql/keyring_file after restarting. Now, we couldn’t do any operation on the encrypted tables since it will report the error, even if we copy the backup file /var/lib/mysql/keyring_file_1 to override the file /var/lib/mysql/keyring_file without restarting.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | [ec2-user@ip-172-31-32-242 ~]$ sudo systemctl stop mysql [ec2-user@ip-172-31-32-242 ~]$ sudo systemctl start mysql [ec2-user@ip-172-31-32-242 ~]$ mysql -u root -p mysql> select * from t1; ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully. mysql> select * from t2; ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully. mysql> show create table t1G ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully. mysql> show create table t2G ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully. mysql> [ec2-user@ip-172-31-32-242 ~]$ sudo cat /var/log/mysqld.log ... 2023-02-09T00:14:56.719139Z 1 [ERROR] [MY-012657] [InnoDB] Encryption can't find master key, please check the keyring is loaded. 2023-02-09T00:14:56.719170Z 1 [ERROR] [MY-012226] [InnoDB] Encryption information in datafile: ./test/t1.ibd can't be decrypted, please confirm that keyring is loaded. 2023-02-09T00:14:56.720098Z 1 [ERROR] [MY-012657] [InnoDB] Encryption can't find master key, please check the keyring is loaded. 2023-02-09T00:14:56.720117Z 1 [ERROR] [MY-012226] [InnoDB] Encryption information in datafile: ./test/t2.ibd can't be decrypted, please confirm that keyring is loaded. [ec2-user@ip-172-31-32-242 ~]$ sudo ls -lhr /var/lib/mysql/keyring_file -rw-r----- 1 mysql mysql 0 Feb 9 00:14 /var/lib/mysql/keyring_file [ec2-user@ip-172-31-32-242 ~]$ sudo mv /var/lib/mysql/keyring_file_1 /var/lib/mysql/keyring_file [ec2-user@ip-172-31-32-242 ~]$ sudo ls -lhr /var/lib/mysql/keyring_file -rw-r----- 1 mysql mysql 187 Feb 9 00:07 /var/lib/mysql/keyring_file mysql> select * from t1; ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully. mysql> select * from t2; ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully. |
- We restarted MySQL again, with the backup file /var/lib/mysql/keyring_file_1 overriding the file /var/lib/mysql/keyring_file. Now everything is back to normal.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [ec2-user@ip-172-31-32-242 ~]$ sudo systemctl stop mysql [ec2-user@ip-172-31-32-242 ~]$ sudo systemctl start mysql [ec2-user@ip-172-31-32-242 ~]$ mysql -u root -p mysql> select * from t1; +----+ | c1 | +----+ | 1 | +----+ mysql> select * from t2; +----+ | c1 | +----+ | 2 | +----+ |
Second, we are not supposed to make any changes to the keyring file. Instead, we are supposed to rotate the key via the plugin, and we need to back up the key before we rotate it. However, the answer provided by ChatGPT implies that we can make some changes to the keyring file, which is terribly wrong.
Conclusion
ChatGPT may provide only partially correct, or even worse, entirely wrong, answers to your question, which still requires expertise to double-check the correctness. Compared to the other search engines, which provide possible links to the answer to your question, relying on expertise to see which one is useful, and/or how to use the information. You will have to have a DBA for your MySQL database no matter with ChatGPT and/or other search engines.
P.S.: When I was done with the blog, I asked the ChatGPT again, and here was what I got:
Ask: “Can we back up and then remove the keyring file, which is used for the Percona plugin of encryption at rest, after MySQL starts?”
ChatGPT: ”Yes, you can backup and then remove the keyring file used by the Percona encryption at rest plugin after MySQL has started. However, removing the keyring file will result in loss of encryption for data at rest, so it is important to make sure that you have backed up the encryption key and have a plan for re-encrypting the data before removing the keyring file to prevent data loss or corruption.”
Ask:” Can we back up and remove the keyring file used for encryption at rest plugin after MySQL starts?”
ChatGPT: “Yes, it is possible to backup and remove the keyring file used for encryption at rest plugin after MySQL has started. However, removing the keyring file will result in loss of encryption for data at rest, so it is important to backup the encryption key and have a plan for re-encrypting the data before removing the keyring file to avoid data loss or corruption.”
The next day, I got another answer again:
Ask: “Can we back up and remove the keyring file, which is used for the Percona plugin of encryption at rest, after MySQL starts?”
ChatGPT: ”The keyring file is an important component of the Percona encryption at rest plugin, and it is used to securely store encryption keys. Backing up the keyring file before removing it would ensure that you have a copy of the encryption keys and would be able to restore them if necessary. However, removing the keyring file while MySQL is running is not recommended, as it could cause unexpected behavior or data corruption. It is important to stop MySQL and back up the keyring file before removing it to avoid any potential issues.”
So, ChatGPT may give different answers to the same question at different times. It makes ChatGPT more unreliable, especially if you want it to be your DBA. In fact, people ask questions to ChatGPT and vote ? or ? next to the answer to help the AI learn. In other words, we are looking at an alpha version, and we are helping it become better.
Interesting topic (the GPT stuff). I wonder what can happen in 10 years or so.
I would try to put the text of this blog post into ChatGPT and then ask the same question again.