RMySQL远程SSL数据库连接报错（Bad Handshake）求助

Hey there! I’ve dealt with similar SSL connection headaches between RMySQL and remote databases on OS X before, so let’s walk through the most likely fixes—since your command-line mysql client works fine, the issue is almost certainly a mismatch or missing configuration in RMySQL.

Common Causes & Fixes #

1. Check if RMySQL was compiled with SSL support #

First, confirm your installed RMySQL package actually has SSL enabled. Run this in R:

RMySQL::mysqlHasSSL()

If this returns FALSE, that’s the root problem—your RMySQL build didn’t link against SSL libraries. To fix this:

• Uninstall the current RMySQL package:

  remove.packages("RMySQL")
  

• Reinstall from source, pointing it to your working command-line MySQL client’s config (adjust the path if you used a non-Homebrew install):

  install.packages("RMySQL", type = "source",
                   configure.args = "--with-mysql-config=/usr/local/bin/mysql_config")
  

  For older MySQL versions (like 5.7), the path might be /usr/local/opt/mysql@5.7/bin/mysql_config.

2. Explicitly pass SSL parameters in your connection call #

The command-line client uses --ssl-mode=REQUIRED, but older versions of RMySQL (compatible with R 3.4.2) don’t infer this automatically. Try these options:

Option A: Specify SSL certificates (if your server requires them)

If your command-line connection uses specific CA/cert/key files, pass their absolute paths in R:

library(RMySQL)

con <- dbConnect(MySQL(),
                 host = "host.com",
                 user = "uname",
                 password = "your_password",
                 ssl.ca = "/absolute/path/to/ca-cert.pem",
                 ssl.cert = "/absolute/path/to/client-cert.pem",
                 ssl.key = "/absolute/path/to/client-key.pem")

Avoid relative paths here—they can cause permission or lookup issues for the R process.

Option B: Force SSL mode (if your server allows implicit SSL)

If your command-line connection works without specifying cert files (your OS trusts the server’s CA), add the ssl.mode option via connect.options:

con <- dbConnect(MySQL(),
                 host = "host.com",
                 user = "uname",
                 password = "your_password",
                 dbname = "your_database_name",
                 connect.options = list(ssl.mode = "REQUIRED"))

3. Fix MySQL client library mismatches #

Your command-line mysql might be using a different library version than RMySQL. To confirm:

• In terminal, run:

  mysql --version
  

• In R, run:

  RMySQL::mysqlClientLibraryVersions()
  

If versions don’t match, RMySQL is linking to an outdated system library (like the pre-installed OS X MySQL client). Reinstalling RMySQL with the correct mysql_config path (as in step 1) will resolve this.

4. Verify SSL file permissions #

If you’re using certificate files, ensure the R process can read them. Check permissions in terminal:

ls -l /path/to/your/ssl/files

If files are restricted (e.g., only readable by root), adjust permissions:

chmod 644 /path/to/ca-cert.pem

Quick Error Matching #

If you’re getting specific errors, here’s targeted guidance:

• SSL connection error: error:00000001:lib(0):func(0):reason(1): SSL version mismatch—recompile RMySQL against your command-line’s client library.
• Access denied for user 'uname'@'your_ip' (using password: YES): Double-check your database user has SSL connection permissions (unlikely since command-line works, but worth confirming).
• Unknown SSL protocol error in connection to host.com:3306: Explicitly set ssl.mode=REQUIRED or verify your certificate paths.

内容的提问来源于stack exchange，提问作者Alex Gold