Replication

MariaDB/MySQL SSL 複製失敗

  • September 29, 2020

在過去 6 小時尋找解決方案後，我嘗試將 SSL 添加到複製中。我設法讓它通過mysql命令行工具與 SSL 連接而沒有問題，但是我似乎無法解決這個複制問題。根據我所做的研究，這是一個非常通用的包羅萬象的 SSL 錯誤。

系統一：

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)

系統二：

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)

我正在嘗試將兩台伺服器設置為主伺服器和從伺服器以進行完全複製。在我實施 SSL 之前，它一直在工作。我正在嘗試使用 Let’s Encrypt 證書。我已經將私鑰轉換為 RSA 並製作了證書和鏈的完整副本，所以它不僅僅是一個符號連結。兩台伺服器都在同一個埠（非標準）上執行，並且具有相同的使用者和密碼。我已完全禁用 SELinux，但無濟於事。

權限應該沒問題…

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

謝謝你的時間。

更新： 我嘗試將 PEM 文件的權限更改為 600，但它沒有修復它。我設法讓它以最大的詳細程度記錄，這是與錯誤相關的部分：

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)

我還ssl_cipher從我忘記從中刪除它的伺服器中刪除了該選項，因此密碼配置匹配。

聽起來 MariaDB 伺服器可能正試圖通過 DNS “解析”一個 IP 地址。要麼關閉此功能（見下文），要麼在您的配置中使用可解析的主機名而不是 IP 地址。

要關閉，請編輯/etc/my.cnf.d/server.cnf兩個伺服器的文件或類似文件並添加以下內容，然後重新啟動 MariaDB 伺服器。

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

mysql 5.6.44從 a 複製到a 時，我遇到了同樣的錯誤mariadb 10.4。

對我來說，這只是由mysql支持TLSv1和mariadb要求引起的TLSv1.1。

我的解決方案是更新mysql到一個版本5.6.46（或更高版本），因為它支持TLSv1.1從5.6.46.

引用自：https://dba.stackexchange.com/questions/245076

相關問答

Replication

我是否需要重新設計我的數據庫並擺脫外鍵才能成功複製？

  • May 5, 2020
檢視問答
Mysql

Mariadb上的master_master複製

  • June 12, 2018
檢視問答
Mysql

MariaDB 10.2 加密的靜態日誌文件未寫入

  • September 21, 2017
檢視問答
Replication

為什麼使用 MariaDB 的 extra_port 進行複制不是一個好主意？

  • April 6, 2022
檢視問答
Replication

MariaDB GTID current_pos 與 slave_pos

  • March 29, 2022
檢視問答
Mysql

執行 mysql 轉儲文件會觸發複製嗎？

  • March 11, 2022
檢視問答