I have a web app that connects to a MYSQL database which has some encrypted data in it. The encrypted data is encrypted using the the mysql AES_ENCRYPT function and a key.
Now, this key has been static for a while, and we're looking at methods to change the key (and ideally change the key periodically). My problem is that I have about 20 million records that are encrypted with this key. So I need to decrypt them, re-encrypt them in with a new key, and save them off again, with minimal downtime for the server.
I'm thinking, I could run 2 databases in parallel, and have a separate process copying over the data with a new key (and somehow making sure records are in sync) and once it's done, switch to the alternate database. But, this would take a really long time, and would be a pain to run anytime we want to change the key in the future. I'm also wondering if there's something clever I could do at the web app level.
I've looked for other column-level encryption methods to make key rotation easier in future key changes, and haven't seen many options. I've looked at encrypting the entire database with mysql data-at-rest encryption (and I might implement that too), but I'd like reads from root and mysql users to be encrypted as well for an extra layer of security.
Anybody have any ideas, or any experience with this kind of thing? I'm sure other people have had this same problem but haven't found anything online.