Setting of Encrypted Connections MySQL

MySQL

First check that you have encrypted support, you should see have_openssl and have_ssl they may be set

to disabled

show global variables like ‘%ssl’;

Then check that the grant tables can support setting encryption requirements for user

select column_name from information_schema.columns where table_schema=’mysql’ and table_name=’user’ and (column_name like ‘ss%’ or column_name like ‘x509%’);

setup a public and private key, the public key is stored in a certificate file and the private key is

stored in a key file. You do this at the operating system level

shell> openssl req -nodes -new -out mysql-server.csr -days 3650 -keyout mysql-server.key

at this point your certficate signing request must be signed off by a Certificate Authority (for

example VeriSign), or you could use your own CA. Once signed you will have a certificate file

containing the public key.

now setup the my.ini file to point to the new certficates

ssl-ca=/path/to/cacert.pem
ssl-cert=/path/to/mysql-server.csr
ssl-key=/path/to/mysql-server.key

restart mysqld and see if the system variables are now set to yes

show global variables like ‘%ssl’;

at this point any user can create an encrypted connection if he/she has access to the same CA certificate file

shell> mysql -u -p –ssl=/path/to/cacert.pem

to check that the connection is encrypted, this field should not be blank.

show session status like ‘ssl_cipher’;

you can force users to only connect securely, notice the require ssl at the end

grant seelct on .* to [email protected] require ssl;