MySQL master-slave replication with SSL: got error certificate verfication failed

Question

Last_IO_Error: Error connecting to source 'repl@mysql-master:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: SSL connection error: error:0A000086:SSL routines::certificate verify failed

Above is the error message I got when I tried to connect slave with master.

I used openssl to create ca-cert.pem, master-cert.pem, master-key.pem, slave1-cert.pem, slave1-key.pem. I have also ensured that ca-cert, master-cert, and slave1-cert CN name are all totally different:

bash-5.1# openssl x509 -in /etc/mysql/ssl/ca-cert.pem -text -noout | grep -i subject
        Subject: CN = MySQL_CA
        Subject Public Key Info:
            X509v3 Subject Key Identifier:
bash-5.1# openssl x509 -in /etc/mysql/ssl/master-cert.pem -text -noout | grep -i subject
        Subject: CN = mysql-master
        Subject Public Key Info:
            X509v3 Subject Key Identifier:
bash-5.1# openssl x509 -in /etc/mysql/ssl/slave1-cert.pem -text -noout | grep -i subject
        Subject: CN = mysql-slave1
        Subject Public Key Info:
            X509v3 Subject Key Identifier:

And I tried verify the master-cert and slave1-cert, they all return OK.

bash-5.1# openssl verify -CAfile /etc/mysql/ssl/ca-cert.pem /etc/mysql/ssl/master-cert.pem
/etc/mysql/ssl/master-cert.pem: OK
bash-5.1# openssl verify -CAfile /etc/mysql/ssl/ca-cert.pem /etc/mysql/ssl/slave1-cert.pem
/etc/mysql/ssl/slave1-cert.pem: OK

When I tried to do Change Master on slave1, I would then get the SSL cerification verify failed error.

CHANGE MASTER TO
    MASTER_HOST='mysql-master', -- matched the CN name as well 
    MASTER_USER='repl',
    MASTER_PASSWORD='replpassword',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='binlog.000007',  -- Adjust based on master status
    MASTER_LOG_POS=157,               -- Adjust based on master status
    MASTER_SSL=1,
    MASTER_SSL_CA='/etc/mysql/ssl/ca-cert.pem',
    MASTER_SSL_CERT='/etc/mysql/ssl/slave1-cert.pem',
    MASTER_SSL_KEY='/etc/mysql/ssl/slave1-key.pem',
    MASTER_SSL_VERIFY_SERVER_CERT=1;

START SLAVE;
SHOW SLAVE STATUS\G;

Any suggestions on where I missed will be highly appreciated. I have been stuck here for days : ( Thanks!!!!

Other information that might be helpful:

master's my.cnf:

[mysqld]
server-id=1
log_bin=/var/lib/mysql/mysql-bin.log
binlog_do_db=master_db
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/master-cert.pem
ssl-key=/etc/mysql/ssl/master-key.pem
log-error=/var/log/mysql/master-error.log

Slave my.cnf:

[mysqld]
server-id=2
relay_log=/var/lib/mysql/mysql-relay-bin.log
log_bin=/var/lib/mysql/mysql-slave-bin.log
read_only=1
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/slave1-cert.pem
ssl-key=/etc/mysql/ssl/slave1-key.pem
log-error=/var/log/mysql/slave1-error.log
user=mysql

Do you have the right permissions for the certs? mysql:mysql is the owner? Take a look at this, you might be able to get an answer: stackoverflow.com/questions/42145925/… — user17526644
– user17526644, Commented Jun 2, 2024 at 0:50
Thank you!! after changing to mysql as the owner and rechecking my master SSL it worked — Yoey
– Yoey, Commented Jun 2, 2024 at 4:16

user17526644 · Accepted Answer · 2024-06-03 10:22:16Z

1

Many times i just look for the answer and don't read the commented, so i guess many others do that too, so i will also post the answer here.

The issue was with ownership. mysql:mysql should be the owner.

answered Jun 3, 2024 at 10:22

user17526644

664 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL master-slave replication with SSL: got error certificate verfication failed

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related