We have set up two Percona MySQL 8.0 servers with one being set as the master and the other as a slave using replication. We are looking to configure automatic failover such that if the master server goes down, the slave server becomes the new master and a front-end web application knows to switch which database it points to.
According to a Percona blog post in 2016, Orchestrator is used to monitor and detect when the master server is down and automatically promotes the slave as the new master by setting read_only to false. ProxySQL is then used to redirect a front-end web application to the new master by taking advantage of which server has read_only set=false.
We were wondering if this is still best practice in 2022? The main concern with this approach is that ProxySQL does not seem to have the ability to enable TDE, unlike Percona which can use the keyring_vault plugin with HashiCorp vault to store the encryption key.
1 Like
I am using this solution right now in my environments, I think we have about 10 cluster right now that orchestrator is managing. I have no experience with the keyring_vault plugin so I cant help with that part of your question. This solution is still best practice. This GitHub - sql2/vault-proxysql-plugins: with ProxySQL Secrets Engine might interest you.
1 Like
Hello @LukeYangJMA,
Iâm not understanding the need for ProxySQL+Vault. TDE is implemented and managed 100% by MySQL. ProxySQL has nothing to do with TDE. TDE is 100% transparent to the application. Install the keyring_vault plugin on both MySQL servers, configure it normally and youâre done. ProxySQL has no involvement here in this process. If the replica gets promoted to be the new source, thatâs fine because the new source has the same vault config and can pull the key.
1 Like
Thanks for your response @matthewb,
After some thought I think I understand what youâre saying, but I think I need to clarify my concerns with ProxySQL. Forgive me in advance for any misunderstandings.
From what I understand in the ProxySQL documentation, you will need to create two users on the MySQL servers, one that will allow ProxySQL to monitor the MySQL servers (monitor user) and another to manage connections (client user). These users will need to be granted all permissions on all tables with the MySQL servers. However, I want to draw attention to the fact that the monitor user is created with a wildcard host (â%â).
mysql@pxc2> CREATE USER 'proxysql'@'%' IDENTIFIED WITH mysql_native_password by '$3Kr$t';
mysql@pxc2> GRANT USAGE ON *.* TO 'proxysql'@'%';
On the server running ProxySQL, you will need to set the username and password of both the monitor and client users which are then stored in a table somewhere in ProxySQL. If an outside were able to make a copy of the hard disk and extract the username and password for the monitor user, wouldnât they be able to access the data from another host using these credentials? This is my basis for trying to encrypt the data stored in ProxySQL.
1 Like
Your original post at the top talks about TDE. Transparent Data Encryption is an InnoDB feature which encrypts data pages as they are flushed to disk and then decrypts when read from disk into memory. TDE has nothing to do with ProxySQL.
That is completely incorrect. In 100% of cases, you only grant access to those databases/tables which the user needs to access.
Correct. User 1) Create a monitor user on MySQL. It does NOT require wildcard. The documentation is showing you an example. You should always follow standard MySQL best practices for securing your database and the users. You can create this user and restrict it to ProxySQLâs IP/subnet. User 2) Create an application user in MySQL that can only connect from ProxySQLâs IP/subnet and GRANT permissions to specific databases and tables for this user.
That depends on if you followed MySQL best practices for creating and securing users. If you created the user with @%, then yes, they could log in from anywhere. If you created âproxymonitorâ@â10.1.1.1â, then no, they can only log in from 10.1.1.1. Additionally, the proxysql monitor user requires NO PRIVILEGES. âUSAGEâ just means âyou can log inâ. There is no ability to read data with the monitor user.
Also, when you âLOAD USERS TO RUNTIMEâ and also âSAVE USERS TO DISKâ in ProxySQL, the passwords for these accounts are NOT stored in plaintext. They are stored in their hashed state.
AFAIK, there is no encryption support in ProxySQL for purposes of storing sensitive data on disk because ProxySQL has no sensitive data to store.
1 Like
Thank you once again for your detailed response @matthewb. Your response makes complete sense and my apologies for bringing the discussion further away from the original question.
To bring everything back in, do you have any input on the best way of handling the connection from the frontend web server to a replica Percona server in the case that the master Percona server fails? We are not sure whether using Orchestrator and ProxySQL is best way to go about implementing this since we are just looking for something simple. If there are other methods, would you be able to point us in the correct documentation?
1 Like
You need both Orc and ProxySQL. Orchestrator only handles the âmaster is down, need to promote slaveâ aspect. Orc only handles topology. Orc does not handle connections.
ProxySQL only handles connections. ProxySQL does not handle topology. If proxysql sees a mysql server with read_only=0, it assumes that is a master. If read_only=1, it assumes replica.
There is nothing simpler than this. AFIAK, no other tool exists to automatically manage MySQL replication topology.
1 Like
@matthewb I agree with you. Simple solution that works well. I have some key apps using it and they are all very happy with the little to know down time this provides.
1 Like