Starting in version 5.7.3 MySQL added SSL support to mysqlbinlog client program. This feature allows system administrators to perform remote binlog queries (using --read-from-remote-server option) over secure connections.
So, the behavior of mysqlbinlog client program using SSL options is now the same as other MySQL client tools, with same SSL options and same SSL defaults. See the References section if you want more information about MySQL SSL options.
Overview
The remote administration of MySQL servers is a very common task as many MySQL servers are deployed in remote hosting facilities or in remotely located data centers.
There are many problems with remote administration of servers. With respect to security, the major concerns are:
- If the traffic between the administrative console and the remote server passes through an insecure network connection, how can we be sure that the sensitive content is not eavesdropped and tampered?
- How can the system guarantee the identity of the parts involved? How can I certify that the server I'm trying to connect remotely is really the server that I want to connect and not a “man in the middle” trying to get my credentials and vice-versa?
With the SSL options available at MySQL server and client programs you can both secure your traffic and certify the identity of the servers and clients involved in the process.
And now, this SSL options are available also to mysqlbinlog client program.
Securing mysqlbinlog remote connections with SSL
In order to secure your remote binlog queries you have to use MySQL SSL options on both client and server sides. If you want to enforce that no access will be allowed without SSL you have also to setup the user account that will perform the remote binlog queries over secure connections.
To enforce the use of SSL connections during remote binlog queries, you can create a user with the REPLICATION SLAVE privilege and use the REQUIRE SSL option for that user:
mysql> CREATE USER repladmin@localhost IDENTIFIED BY 'adminpass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repladmin'@'localhost'
-> REQUIRE SSL;
For the case where the user account with the REPLICATION SLAVE privilege already exists, you can just add REQUIRE SSL to it with this statement:
mysql> GRANT USAGE ON *.*
-> TO 'repladmin'@'localhost' REQUIRE SSL;
After setting up the SSL requirements you can start using the mysqlbinlog client program to fetch binlog content securely over the insecure Internet:
$ mysqlbinlog --read-from-remote-server --ssl --protocol=tcp \
--host=localhost --user=repladmin -p mysql-bin.000001
Enter password:
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#131203 11:47:32 server id 1 end_log_pos 120 CRC32 0x5147d52f Start: binlog v 4, server v 5.7.3-debug-log created 131203 11:47:32 at startup
ROLLBACK/*!*/;
BINLOG '
1MSdUg8BAAAAdAAAAHgAAAAAAAQANS43LjQtbTE0LWRlYnVnLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADUxJ1SEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKIiIAAS/V
R1E=
'/*!*/;
# at 120
#131203 11:49:26 server id 1 end_log_pos 143 CRC32 0xacbc7241 Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
So, if your replication user account requires SSL and you try to perform a remote binlog query without using the appropriate SSL options, the mysqlbinlog client program would return an access denied error:
ERROR: Failed on connect: Access denied for user 'repladmin'@'localhost' (using password: YES)
Note: if your server is not configured to support SSL connections yet, the remote binlog query attempt using SSL options will fail with the following message:
ERROR: Failed on connect: SSL connection error: SSL is required but the server doesn't support it
Setting up your client to use SSL certificates
In order to certify client and server identities, you can use the list of trusted SSL certificate authorities option (--ssl-ca), the name of the SSL certificate file option (--ssl-cert) and the name of the SSL key file option (--ssl-key). The use of these parameters depends on your server configuration. See References section for more information.
For example, if your server was started with --ssl-cert and --ssl-key options set, you should run mysqlbinlog client program with at least the Certificate Authority certificate option (--ssl-ca).
You can set up these SSL client options into your MySQL options file to avoid passing them as parameters to the mysqbinlog client program.
To add the client certificates configuration to your option file, just add the following lines to the [mysqlbinlog] or [client] section:
[mysqlbinlog]
ssl-ca=cacert.pem
If the replication user account was created using the REQUIRE X509 option (client must have a valid certificate), the REQUIRE ISSUER option (client must present a valid X509 certificate issued by an specific CA), or the REQUIRE SUBJECT option (client must present a valid X509 certificate containing an specific subject), you will have to specify also the proper client key and certificate files, or else the server will reject your connection.
[mysqlbinlog]
ssl-ca=cacert.pem
ssl-cert=client-cert.pem
ssl-key=client-key.pem
Conclusion
The mysqlbinlog client program now supports the same SSL options as any other MySQL client program.
With the SSL options it is possible to use mysqlbinlog client program over secure connections and it is also possible to certify the identities of the servers and clients involved in the process.
