如何在QT中使用MYSQL SSL连接

1.在mysql服务器启动SSL连接

mysql> show variables like '%ssl%';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| have_openssl  | YES                              |
| have_ssl      | YES                              |
| ssl_ca        | /etc/mysql/certs/ca-cert.pem     |
| ssl_capath    |                                  |
| ssl_cert      | /etc/mysql/certs/server-cert.pem |
| ssl_cipher    |                                  |
| ssl_key       | /etc/mysql/certs/server-key.pem  |
+---------------+----------------------------------+
7 rows in set (0.00 sec)

2.qt中启用SSL选项

很多人都说在QSqlDatabase::open()函数之前，调用mysql_ssl_set函数设置证书即可使用，外加QSqlDatabase::setConnectOptions("CLIENT_SSL=1;CLIENT_IGNORE_SPACE=1"); 我试了几次，没作用，为什么呢？

考虑到qt的MySQL有一个调用接口，我翻看了其源码（src/sql/drivers/mysql/qsql_mysql.cpp）看其中的open（）实现

//显然这里使用mysql_init函数后，直接调用mysql_real_connect连接MySQL，也即没设置SSL连接证书，那怎么连接呢？
    if ((d->mysql = mysql_init((MYSQL*) 0))
 &&
            mysql_real_connect(d->mysql,
                               host.isNull() ? static_cast<const char *>(0)
                                             : host.toLocal8Bit().constData(),
                               user.isNull() ? static_cast<const char *>(0)
                                             : user.toLocal8Bit().constData(),
                               password.isNull() ? static_cast<const char *>(0)
                                                 : password.toLocal8Bit().constData(),
                               db.isNull() ? static_cast<const char *>(0)
                                           : db.toLocal8Bit().constData(),
                               (port > -1) ? port : 0,
                               unixSocket.isNull() ? static_cast<const char *>(0)
                                           : unixSocket.toLocal8Bit().constData(),
                               optionFlags))
    {
        if (!db.isEmpty() && mysql_select_db(d->mysql, db.toLocal8Bit().constData())) {
            setLastError(qMakeError(tr("Unable to open database '") + db +
                         QLatin1Char('\''), QSqlError::ConnectionError, d));
            mysql_close(d->mysql);
            setOpenError(true);
            return false;
        }
#if MYSQL_VERSION_ID >= 50000
        if(reconnect)
            mysql_options(d->mysql, MYSQL_OPT_RECONNECT, &reconnect);
#endif
    } else {
        setLastError(qMakeError(tr("Unable to connect"),
                     QSqlError::ConnectionError, d));
        mysql_close(d->mysql);
        d->mysql = NULL;

        setOpenError(true);
        return false;
    }
加上mysql_ssl_set试试

  d->mysql = mysql_init(NULL);
 mysql_ssl_set(d->mysql, "/etc/mysql/certs/client-key.pem", "/etc/mysql/certs/client-cert.pem", "/etc/mysql/certs/ca-cert.pem", NULL, NULL);
    if ( mysql_real_connect(d->mysql, "10.142.54.88","xxx","code","aaa",3306,"", 0))

...

其中三个文件是我的证书路径，经过如此更改，即可连接上

在这个过程中要注意一个问题

1)有些人把mysql直接编进qt内核中，而不是plugin；如果是这样的话，即使你重新编译qt中mysql的plugin，这个更改是无法生效的，自然也就连不上mysql