mysqlopensslmariadbreplicationmaster-slave

Mariadb Master - Slave replication SSL Error


I am trying to do a Master Slave replication with Mariadb.

On the master Server, i created the certificate/key with:

openssl genrsa 2048 > ca-key.pem
openssl req -sha1 -new -x509 -nodes -days 10000 -key ca-key.pem > ca-cert.pem
openssl req -sha1 -newkey rsa:2048 -days 10000 -nodes -keyout server-key.pem > server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -sha1 -req -in server-req.pem -days 10000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

I create a folder in /etc/mysql/certs/ And modify accordingly the file /etc/mysql/mariadb.conf.d/50-server.cnf adding:

ssl = on
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem
server-id              = 1
log_bin                = /var/log/mysql/mysql-bin.log
log_error              = /var/log/mysql/error.log
binlog_do_db           = my_db
bind-address           = my_ip_server_address

After i created certificat/key for my replica server:

openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout client-key.pem > client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -sha1 -req -in client-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

I send theses files to my replica server And configure my replica server with :

[mysqld]
server-id              = 2
log_bin                = /var/log/mysql/mysql-bin.log
relay-log              = /var/log/mysql/mysql-relay-bin.log
binlog_do_db           = my_db

And

[client]
ssl-ca = /etc/mysql/certs/ca.pem
ssl-cert = /etc/mysql/certs/client-cert.pem
ssl-key = /etc/mysql/certs/client-key.pem

After i tried to connect from replica to master : mysql -h my_host -u my_user -p The connection succeed and if write status; i got:

--------------
mysql  Ver 15.1 Distrib 10.5.15-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

Connection id:      11828
Current database:   
Current user:       my_replica_user
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server:         MariaDB
Server version:     10.3.34-MariaDB-0+deb10u1-log Debian 10
Protocol version:   10
Connection:     my_server via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         15 hours 37 min 31 sec

Threads: 12  Questions: 239736  Slow queries: 0  Opens: 813  Flush tables: 1  Open tables: 807  Queries per second avg: 4.261
--------------

So, i decided to start my replica with:

CHANGE MASTER TO MASTER_HOST='my_host', 
MASTER_USER='my_user_replica', 
MASTER_PASSWORD='my_user_replica_password', 
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000019',
MASTER_LOG_POS=670,
MASTER_CONNECT_RETRY=10,
MASTER_SSL_VERIFY_SERVER_CERT=1,
MASTER_SSL=1,
MASTER_SSL_CA='/etc/mysql/certs/ca.pem',
MASTER_SSL_CERT='/etc/mysql/certs/client-cert.pem',
MASTER_SSL_KEY='/etc/mysql/certs/client-key.pem';

But, when i do a SHOW REPLICA STATUS\G;

*************************** 1. row ***************************
                Slave_IO_State: Connecting to master
                   Master_Host: my_host
                   Master_User: my_user_replica
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: mysql-bin.000019
           Read_Master_Log_Pos: 670
                Relay_Log_File: mysql-relay-bin.000003
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mysql-bin.000019
              Slave_IO_Running: Connecting
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 670
               Relay_Log_Space: 256
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: Yes
            Master_SSL_CA_File: /etc/mysql/certs/ca.pem
            Master_SSL_CA_Path: 
               Master_SSL_Cert: /etc/mysql/certs/client-cert.pem
             Master_SSL_Cipher: 
                Master_SSL_Key: /etc/mysql/certs/client-key.pem
         Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: Yes
                 Last_IO_Errno: 2026
                 Last_IO_Error: error connecting to master 'my_user_replica@my_host:3306' - retry-time: 10  maximum-retries: 86400  message: SSL connection error: SSL certificate validation failure
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 0
                Master_SSL_Crl: /etc/mysql/certs/ca.pem
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0

Can you help me to make it works ? And why i can connect remotly with mysql -h my_host -u my_user_replica -p And mysql can't with the replica ?

Thanks.


Solution

  • Your problem is MASTER_SSL_VERIFY_SERVER_CERT=1, your command line connect worked as you did not use the --ssl-verify-server-cert option there.

    With this option in effect it will check whether the host name you want to connect to (here: 'my_host') matches the CN value (CommonName) of the certificates Subject string, or one of the SAN (ServerAlternateNames) entries in the certificate if that x509 extension is used.

    I can't see what you entered as the CN when generating the certificates, but I bet it was not 'my_host' (or whatever the actual host name was before you edited it for this post, as a name with an underscore is not a valid host name)

    So your options are to either drop the MASTER_SSL_VERIFY_SERVER_CERT option, or to generate the server certificate with the correct server name as its CN