使用SSL安全链接Mysql数据库

一、使用SSL安全连接

To use SSL connections between the MySQL server and client programs, your system must support either OpenSSL or yaSSL and your version of MySQL must be built with SSL support.

To make it easier to use secure connections, MySQL is bundled with yaSSL as of MySQL 5.0.10. (MySQL and yaSSL employ the same licensing model, whereas OpenSSL uses an Apache-style license.) yaSSL support initially was available only for a few platforms, but now it is available on all platforms supported by MySQL AB.

To get secure connections to work with MySQL and SSL, you must do the following:

To start the MySQL server so that it allows clients to connect via SSL, use the options that identify the key and certificate files the server needs when establishing a secure connection:

shell> mysqld --ssl-ca=cacert.pem /
--ssl-cert=server-cert.pem /
--ssl-key=server-key.pem

一般情况下mysql服务器会随开机自启动，如果需要支持ssl，则修要修改配置文件/etc/mysql/my.cnf，
设置ssl-ca,ssl-cert,ssl-key. 然后/etc/init.d/mysql restart

To establish a secure connection to a MySQL server with SSL support, the options that a client must specify depend on the SSL requirements of the user account that the client uses. 

If the account has no special SSL requirements or was created using a GRANT statement that includes the REQUIRE SSL option, a client can connect securely by using just the --ssl-ca option:

shell> mysql --ssl-ca=cacert.pem 

To require that a client certificate also be specified, create the account using the REQUIRE X509 option. Then the client must also specify the proper client key and certificate files or the server will reject the connection:

shell> mysql --ssl-ca=cacert.pem /
--ssl-cert=client-cert.pem /
--ssl-key=client-key.pem 

In other words, the options are similar to those used for the server. Note that the Certificate Authority certificate has to be the same.

A client can determine whether the current connection with the server uses SSL by checking the value of the Ssl_cipher status variable. The value of Ssl_cipher is non-empty if SSL is used, and empty otherwise. For example:

mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+

For the mysql client, you can use the STATUS or /s command and check the SSL line:

mysql> /s
...
SSL:                    Not in use
...

Or:

mysql> /s
...
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
...

To establish a secure connection from within an application program, use the mysql_ssl_set() C API function to set the appropriate certificate options before calling mysql_real_connect().

There are a number of different possibilities for limiting connection types for a given account:

The SUBJECT, ISSUER, and CIPHER options can be combined in the REQUIRE clause like this:

GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
  IDENTIFIED BY 'goodsecret'
  REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
    O=MySQL demo client certificate/
    CN=Tonu Samuel/Email=tonu@example.com'
  AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/
    O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com'
  AND CIPHER 'EDH-RSA-DES-CBC3-SHA';

The AND keyword is optional between REQUIRE options.

三、为Mysql制作ssl证书

This section demonstrates how to set up SSL certificate and key files for use by MySQL servers and clients. The first example shows a simplified procedure such as you might use from the command line. The second shows a script that contains more detail. Both examples use the openssl command that is part of OpenSSL.

The following example shows a set of commands to create MySQL server and client certificate and key files. You will need to respond to several prompts by the openssl commands. For testing, you can press Enter to all prompts. For production use, you should provide non-empty responses.

# Create clean environment
shell> rm -rf newcerts
shell> mkdir newcerts && cd newcerts

# Create CA certificate
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 1000 /
-key ca-key.pem > ca-cert.pem

# Create server certificate
shell> openssl req -newkey rsa:2048 -days 1000 /
-nodes -keyout server-key.pem > server-req.pem
shell> openssl x509 -req -in server-req.pem -days 1000 /
-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

# Create client certificate
shell> openssl req -newkey rsa:2048 -days 1000 /
-nodes -keyout client-key.pem > client-req.pem
shell> openssl x509 -req -in client-req.pem -days 1000 /
-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem