I have one Percona Server inside a container of a docker swarm in Germany and want to replicate it to another Percona Server inside a docker container in a docker swarm in Brazil. Obviously the data between the two need to be transferred via a secure connection. What are the options to establish this connection? I tried to expose the MySQL with traefik and create a Lets Encrypt certificate. Seems the certificate creation is working, but I do not know how to make my other Percona Server recognize it. Is it easier to create a self signed certificate and configure it on both instances? Can I connect somehow via SSH? What are the best ways to resolve that?
What you did with the SSL certificates is the right choice. On the source, modify the replica user with REQUIRE SSL. On the replica side, CHANGE REPLICATION SOURCE TO ... SOURCE_SSL =1, SOURCE_SSL_CA='/path/to/ca.pem', SOURCE_SSL_CERT='/path/to/cert.pem', SOURCE_SSL_KEY='/path/to/key.pem'
When you then START REPLICA, it will establish an SSL connection to the source, validating the SSL certificates.
Alright, I can define the certifficates there. But the certificate that traffic made for me is Lets Encrypt and will renew every 90 days. First I do not know where to access the Lets Encrypt certificate. And much less how to update it automatically when it changes. Or is it better to create a self signed certificate that never expires? I am still a bit lost in the certificate world.
This would be much easier for your setup, yes. Here are the instructions that I use in our MySQL training courses to generate the SSL certificates:
Alright, I could follow this steps. The three commands that created the certificate request ignored the -days flag, is that right? I guess they are not needed in those 3 commands. Otherwise things worked correctly.
I have now a -key, a -req and a -cert file for the CA, the server and the client. Which ones do I need, and where do they go?
You can put the CA and server-* on both and call it done. The client-* files would be for your application. After you install the certs (editing my.cnf appropriately) and changing the replica as I mentioned above, you should be all SSL.
I tried to do the following on the server side:
CREATE USER ‘replicate’@‘%’ IDENTIFIED BY ‘mypassword’;
GRANT ALL PRIVILEGES ON wp_ah.* TO ‘replicate’@‘%’
ALTER USER ‘replicate’@‘%’ REQUIRE SSL;
SET GLOBAL ssl_ca = ‘/etc/my.cnf.d/ca.pem’;
SET GLOBAL ssl_cert = ‘/etc/my.cnf.d/server-cert.pem’;
SET GLOBAL ssl_key = ‘/etc/my.cnf.d/server-key.pem’;
All those commands were running fine
Then I tried to test on the client side running:
mysql --host=128.0.0.0 --user=replicate --password=‘mypassword’ --ssl-ca=/etc/my.cnf.d/ca.pem --ssl-cert=/etc/my.cnf.d/client-cert.pem --ssl-key=/etc/my.cnf.d/client-key.pem
And I get the error:
ERROR 2026 (HY000): SSL connection error: error:0A000086:SSL routines::certificate verify failed
I tried to test also connecting with MySQL Workbench and get basically the same error
Firstly, change your SET GLOBAL to SET PERSIST. The SET GLOBAL will only change that temporarily. If you restart, those are lost. Use SET PERSIST to make it permanent.
Secondly, the replication user only needs REPLICATION SLAVE; please don’t grant all privs, for security reasons.
Try passing --ssl_mode=PREFERRED or --ssl_mode=REQUIRED. If those work, then try --ssl_mode=VERIFY_CA and if that fails, there’s more debugging to be done with certs.
Thanks for the help so far, the certificates are working now. I can establish now a secure connection. I still have some challenge to get the replication to work. Here is my server config file:
[mysqld]
Compatibility
default-authentication-plugin = mysql_native_password
Replication
server_id = 1 # must be distinct on all nodes in the cluster
binlog_format = ROW
log_bin = binlog
log_slave_updates = 1
gtid_mode = ON
enforce_gtid_consistency = 1
binlog_expire_logs_seconds = 604800 # 7 days
sync_binlog = 1
report_host = 128.0.0.0 # IP address of this host
read_only = OFF # Set ON on slave
super_read_only = OFF # Set ON on slave
Replication safety
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
I was running inside the SQL Server:
CREATE USER ‘replicate’@‘%’ IDENTIFIED BY ‘mypassword’;
GRANT REPLICATION SLAVE ON . TO ‘replicate’@‘%’;
ALTER USER ‘replicate’@‘%’ REQUIRE SSL;
SET PERSIST ssl_ca = ‘/etc/my.cnf.d/ca.pem’;
SET PERSIST ssl_cert = ‘/etc/my.cnf.d/server-cert.pem’;
SET PERSIST ssl_key = ‘/etc/my.cnf.d/server-key.pem’;
Then on the slave I have the config:
[mysqld]
ssl-ca=/etc/my.cnf.d/ca.pem
ssl-cert=/etc/my.cnf.d/client-cert.pem
ssl-key=/etc/my.cnf.d/client-key.pem
Compatibility
default-authentication-plugin = mysql_native_password
Replication
server_id = 2 # must be distinct on all nodes in the cluster
binlog_format = ROW
log_bin = binlog
log_slave_updates = 1
gtid_mode = ON
enforce_gtid_consistency = 1
binlog_expire_logs_seconds = 604800 # 7 days
sync_binlog = 1
report_host = 164.0.0.0 # IP address of this host
read_only = ON # Set ON on slave
super_read_only = ON # Set ON on slave
Replication safety
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
I restarted both servers and was running on the slave:
RESET MASTER;
CHANGE MASTER TO MASTER_HOST = ‘128.0.0.0’, MASTER_USER = ‘replicate’, MASTER_PASSWORD = ‘mypassword’, MASTER_AUTO_POSITION = 1;
START SLAVE;
When I run:
SHOW SLAVE STATUS;
It will show me this error:
Error connecting to source ‘replicate@128.0.0.0:3306’. This was attempt 28/86400, with a delay of 60 seconds between attempts. Message: Access denied for user ‘replicate’@‘10.0.0.4’ (using password: YES)
But I can connect fine with the command:
mysql --host=128.0.0.0 --user=replicate --password=‘mypassword’ --ssl-ca=/etc/my.cnf.d/ca.pem --ssl-cert=/etc/my.cnf.d/client-cert.pem --ssl-key=/etc/my.cnf.d/client-key.pem