MariaDB/MySQL SSL Replication Failure

Question 1

After searching for a solution for the last 6 hours, I have come up dry in my attempt to add SSL to the replication. I managed to get it to connect with SSL via the mysql command line tool without issues, however I cannot seem to solve this replication issue. Based on the research I did find, this is an extremely generic catch-all SSL error.

System 1:

OS: Fedora 30 Modular
Kernel: 5.0.16-300
Arch: x86_64
MariaDB Server: 10.3.16
OpenSSL: 1.1.1c FIPS

MariaDB [(none)]> STATUS;
--------------
mysql Ver 15.1 Distrib 10.3.16-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 42
Current database: 
Current user: root@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.3.16-MariaDB-log MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 18 min 0 sec
Threads: 11 Questions: 32 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.029
--------------
MariaDB [(none)]> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
 Slave_IO_State: Connecting to master
 Master_Host: REDACTED
 Master_User: REDACTED
 Master_Port: REDACTED
 Connect_Retry: 60
 Master_Log_File: master1-bin.000012
 Read_Master_Log_Pos: 364174
 Relay_Log_File: master1-relay-bin.000001
 Relay_Log_Pos: 4
 Relay_Master_Log_File: master1-bin.000012
 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: 364174
 Relay_Log_Space: 256
 Until_Condition: None
 Until_Log_File: 
 Until_Log_Pos: 0
 Master_SSL_Allowed: Yes
 Master_SSL_CA_File: /etc/pki/tls/certs/mariadb-chain.pem
 Master_SSL_CA_Path: /etc/pki/tls/certs/
 Master_SSL_Cert: /etc/pki/tls/certs/mariadb.pem
 Master_SSL_Cipher: TLS_AES_256_GCM_SHA384
 Master_SSL_Key: /etc/pki/tls/private/mariadb.pem
 Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: Yes
 Last_IO_Errno: 2026
 Last_IO_Error: error connecting to master 'REDACTED@REDACTED:REDACTED' - retry-time: 60 maximum-retries: 86400 message: SSL connection error: error:00000000:lib(0):func(0):reason(0)
 Last_SQL_Errno: 0
 Last_SQL_Error: 
 Replicate_Ignore_Server_Ids: 
 Master_Server_Id: 0
 Master_SSL_Crl: /etc/pki/tls/certs/mariadb-chain.pem
 Master_SSL_Crlpath: /etc/pki/tls/certs/
 Using_Gtid: No
 Gtid_IO_Pos: 
 Replicate_Do_Domain_Ids: 
 Replicate_Ignore_Domain_Ids: 
 Parallel_Mode: conservative
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
 Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
 Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
ERROR: No query specified
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%ssl%';
+---------------------+-------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/pki/tls/certs/mariadb-chain-x509.pem |
| ssl_capath | |
| ssl_cert | /etc/pki/tls/certs/mariadb-x509.pem |
| ssl_cipher | TLS_AES_256_GCM_SHA384 |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /etc/pki/tls/private/mariadb.pem |
| version_ssl_library | OpenSSL 1.1.1c FIPS 28 May 2019 |
+---------------------+-------------------------------------------+
10 rows in set (0.002 sec)

System 2:

OS: Fedora 30 Modular
Kernel: 5.0.16-300
Arch: x86_64
MariaDB Server: 10.3.16
OpenSSL: 1.1.1c FIPS

MariaDB [(none)]> STATUS;
--------------
mysql Ver 15.1 Distrib 10.3.16-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 60
Current database: 
Current user: root@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.3.16-MariaDB-log MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 40 min 44 sec
Threads: 12 Questions: 623 Slow queries: 0 Opens: 48 Flush tables: 1 Open tables: 42 Queries per second avg: 0.254
--------------
MariaDB [(none)]> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
 Slave_IO_State: Connecting to master
 Master_Host: REDACTED
 Master_User: REDACTED
 Master_Port: REDACTED
 Connect_Retry: 60
 Master_Log_File: master1-bin.000007
 Read_Master_Log_Pos: 344
 Relay_Log_File: master1-relay-bin.000006
 Relay_Log_Pos: 4
 Relay_Master_Log_File: master1-bin.000007
 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: 344
 Relay_Log_Space: 256
 Until_Condition: None
 Until_Log_File: 
 Until_Log_Pos: 0
 Master_SSL_Allowed: Yes
 Master_SSL_CA_File: /etc/pki/tls/certs/mariadb-chain.pem
 Master_SSL_CA_Path: 
 Master_SSL_Cert: /etc/pki/tls/certs/mariadb.pem
 Master_SSL_Cipher: 
 Master_SSL_Key: /etc/pki/tls/private/mariadb.pem
 Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: Yes
 Last_IO_Errno: 2026
 Last_IO_Error: error connecting to master 'REDACTED@REDACTED:REDACTED' - retry-time: 60 maximum-retries: 86400 message: SSL connection error: error:00000000:lib(0):func(0):reason(0)
 Last_SQL_Errno: 0
 Last_SQL_Error: 
 Replicate_Ignore_Server_Ids: 
 Master_Server_Id: 0
 Master_SSL_Crl: /etc/pki/tls/certs/mariadb-chain.pem
 Master_SSL_Crlpath: 
 Using_Gtid: No
 Gtid_IO_Pos: 
 Replicate_Do_Domain_Ids: 
 Replicate_Ignore_Domain_Ids: 
 Parallel_Mode: conservative
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
 Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
 Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
ERROR: No query specified
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%ssl%';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/pki/tls/certs/mariadb-chain.pem |
| ssl_capath | |
| ssl_cert | /etc/pki/tls/certs/mariadb.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /etc/pki/tls/private/mariadb.pem |
| version_ssl_library | OpenSSL 1.1.1c FIPS 28 May 2019 |
+---------------------+--------------------------------------+
10 rows in set (0.005 sec)

I'm trying to setup both servers as master and slave for full replication. It was working until I went to implement the SSL. I'm trying to use Let's Encrypt certificates. I have already converted the private key to RSA and made a full copy of the certificate and chain, so it's not just a symlink. Both servers are running on the same port (non-standard) and have the same users and passwords. I have completely disabled SELinux, to no avail.

the permissions should be fine...

ls -l /etc/pki/tls/*/mariadb*.pem
-rw-r--r--+ 1 mysql mysql 3566 Aug 11 02:17 /etc/pki/tls/certs/mariadb-chain.pem
-rw-r--r--+ 1 mysql mysql 1919 Aug 11 02:17 /etc/pki/tls/certs/mariadb.pem
-rw-r--r--+ 1 mysql mysql 1679 Aug 11 02:17 /etc/pki/tls/private/mariadb.pem

Thanks for your time.

UPDATE: I tried changing the permissions on the PEM files to 600, but it did not fix it. I managed to get it logging at maximum verbosity and this is the section pertinent to the error:

2019年08月14日 16:42:53 10 [ERROR] Slave I/O: error connecting to master 'REDACTED@REDACTED:REDACTED' - retry-time: 60 maximum-retries: 86400 message: SSL connection error: error:00000000:lib(0):func(0):reason(0), Internal MariaDB error code: 2026
2019年08月14日 16:43:54 12 [Warning] IP address 'REDACTED' could not be resolved: Name or service not known
2019年08月14日 16:43:54 12 [Warning] Aborted connection 12 to db: 'unconnected' user: 'unauthenticated' host: 'REDACTED' (CLOSE_CONNECTION)

I also removed the ssl_cipher option from the server I forgot to remove it from, so the cipher configs match.

Question 2

Have you read the MariaDB documentation page Replication with Secure Connections?

Question 3

@dbdemon yes, along with dozens of other sites and tutorials.

Question 4

You have a different filename for ssl_ca in the two servers. Also, you have defined ssl_cipher on server 1, but not on 2. Have you verified you can connect with mysql from server 1 to 2 and from 2 to 1? Have you looked for errors/clues in the MariaDB .err log?

Question 5

@dbdemon I didn't notice the file difference when I looked just now... As for the cipher, that's just left over from an attempted fix that didn't work. That cipher is the default cipher chosen by the server.

Question 6

And yes, I did connect server 1 to 2 and 2 to 1 via SSL using the client.

Question 7

It sounds like maybe the MariaDB server is trying to "resolve" an IP address through DNS. Either turn off this feature (see below) or maybe use resolvable host names instead of IP addresses in your configuration.

To turn off, edit your /etc/my.cnf.d/server.cnf files or similar for both servers and add the below, and then restart the MariaDB servers.

[mysqld]
skip-host-cache 
skip-name-resolve

Question 8

It is using DNS to resolve because it's using domains. Plus turning SSL off makes it work.

Question 9

I don't know if this specific solution works for the problem I was having but shoving in an IP instead of domain name for the MASTER_HOST server solved certificate errors for me when trying to do replication from a MariaDB to an old MySQL db, Thanks for putting me on the scent!

Question 10

I had that same error when replicating from a mysql 5.6.44 to a mariadb 10.4.
For me it was caused by mysql only supporting TLSv1 and mariadb requiring TLSv1.1.
My solution was to update mysql to a version 5.6.46 (or higher) because it supports TLSv1.1 starting from 5.6.46.

Question 11

Unfortunately, that's not the problem here. As you can see on the post, both servers are running MariaDB version 10.3.16

dbdemon dbdemon 7,0394 gold badges21 silver badges40 bronze badges · Answer 1 · 2019-08-14 17:51:52Z

1

It sounds like maybe the MariaDB server is trying to "resolve" an IP address through DNS. Either turn off this feature (see below) or maybe use resolvable host names instead of IP addresses in your configuration.

To turn off, edit your /etc/my.cnf.d/server.cnf files or similar for both servers and add the below, and then restart the MariaDB servers.

[mysqld]
skip-host-cache 
skip-name-resolve

Share

Improve this answer

answered Aug 14, 2019 at 17:51

dbdemon's user avatar

dbdemon dbdemon

7,0394 gold badges21 silver badges40 bronze badges

2

It is using DNS to resolve because it's using domains. Plus turning SSL off makes it work.

Timberwolf
– Timberwolf

2020年08月26日 13:40:30 +00:00
Commented Aug 26, 2020 at 13:40
I don't know if this specific solution works for the problem I was having but shoving in an IP instead of domain name for the MASTER_HOST server solved certificate errors for me when trying to do replication from a MariaDB to an old MySQL db, Thanks for putting me on the scent!

abetusk
– abetusk

2021年07月29日 04:09:47 +00:00
Commented Jul 29, 2021 at 4:09

Add a comment |

toydarian toydarian 2132 silver badges9 bronze badges · Answer 2 · 2020-08-26 09:23:10Z

I had that same error when replicating from a mysql 5.6.44 to a mariadb 10.4.
For me it was caused by mysql only supporting TLSv1 and mariadb requiring TLSv1.1.
My solution was to update mysql to a version 5.6.46 (or higher) because it supports TLSv1.1 starting from 5.6.46.

Unfortunately, that's not the problem here. As you can see on the post, both servers are running MariaDB version 10.3.16

Stack Exchange Network

MariaDB/MySQL SSL Replication Failure

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

MariaDB/MySQL SSL Replication Failure

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related

Hot Network Questions