How do I tell if mysql replication is occurring over SSL

Question

We have a SQL server replication in place. I'm trying to ensure that the replication is occurring over secure channels. Given that MASTER_SSL_Allowed is true ("Yes"), does this suggest things are traveling over SSL/TLS?

How can I be sure that the replication connection is encrypted? How can I effectively forbid unencrypted traffic between master and replication?

mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.100 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000192 Read_Master_Log_Pos: 37748817 Relay_Log_File: mysqld-relay-bin.000032 Relay_Log_Pos: 1244 Relay_Master_Log_File: mysql-bin.000092 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: omega 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: 37748817 Relay_Log_Space: 124980 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/mysql/certs/omega-ca-cert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/mysql/certs/omega-client-cert.pem Master_SSL_Cipher: Master_SSL_Key: /etc/mysql/certs/omega-client-key.pem

Answer 1

There are two things you can do

SUGGESTION #1 : Check the Master

Make sure MySQL was started on the Master with lines like these in my.cnf

[mysqld]
ssl-ca=cacert.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

Login to the Master, and run

SHOW GLOBAL VARIABLES LIKE '%ssl%';

You should see something like this:

+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        | ...      |
| ssl_capath    | ...      |
| ssl_cert      | ...      |
| ssl_cipher    | ...      |
| ssl_key       | ...      |
+---------------+----------+

If have_open_ssl and have_ssl are disabled, then mysqld on the Master is not using SSL.

Please read the MySQL Documentation on ssl_ca, ssl_capath, ssl_cert, ssl_cipher and ssl_key on how to use them. Evidently, if none of them are set, even if have_open_ssl and have_ssl are enabled, then mysqld on the Master is not using SSL.

SUGGESTION #2 : Check the MySQL Grants on the Master

The connection itself should be using SSL options. In the SHOW SLAVE STATUS\G output, I see you have a user called slave_user. On the Master, please run this:

SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')
INTO @slaveusergrants FROM mysql.user WHERE user='slave_user';
PREPARE s FROM @slaveusergrants; EXECUTE s; DEALLOCATE PREPARE s;

In the output of this, you should see one or more of the following:

REQUIRE SSL
REQUIRE SUBJECT
REQUIRE X509
REQUIRE ISSUER
REQUIRE CIPHER

If the slave user on the Master has at least one of these things defined in its grants, the CHANGE MASTER TO command you run on the Slave needs to match the SSL settings on the Master.

For more clarification, please read Setting Up Replication Using SSL from MySQL Documentation

SUGGESTION #3 : Check the Live Connection

You should use tcpdump and inspect port 3306. If you see stuff in plain text, SSL ain't working for you. If you don't, that does not mean it's encrypted either. Why ? According to the Security Guidelines in the MySQL Documentation (see the bottom of the page):

Do not transmit plain (unencrypted) data over the Internet. This information is accessible to everyone who has the time and ability to intercept it and use it for their own purposes. Instead, use an encrypted protocol such as SSL or SSH. MySQL supports internal SSL connections. Another technique is to use SSH port-forwarding to create an encrypted (and compressed) tunnel for the communication.

Learn to use the tcpdump and strings utilities. In most cases, you can check whether MySQL data streams are unencrypted by issuing a command like the following:

shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings This works under Linux and should work with small modifications under other systems.

Warning If you do not see cleartext data, this does not always mean that the information actually is encrypted. If you need high security, consult with a security expert.