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.
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