The Python connector prefers to use SSL by default, or if you set
That's unfortunately a double-negative. It means you did not disable SSL, therefore it is enabled. It would have been more clear if they had followed the convention of other MySQL clients that use
ssl-mode, set to one of 'DISABLED', 'PREFERRED', 'REQUIRED', 'VERIFY_CA', 'VERIFY_IDENTITY'.
MySQL users by default may connect via TCP with an encrypted SSL connection or a non-encrypted connection. By defining your user with
REQUIRE SSL, the user cannot connect if SSL is not enabled.
Encryption happens in the MySQL client library, which the Python connector calls. That's where the MySQL protocol is implemented. Many other clients use the same MySQL client library, such as Ruby, PHP, Perl, or C++ clients.
I can test a user similar to yours with the MySQL command-line client, which also uses the same client library.
I created my user:
mysql> create user testy@'%' require ssl;
Then in another window I tried connecting from a shell prompt:
% mysql -utesty -h 127.0.0.1 -p --ssl-mode DISABLED
ERROR 1045 (28000): Access denied for user 'testy'@'localhost' (using password: NO)
That's as expected. If the user is defined to require SSL, and the client tries to connect with SSL disabled, then that user cannot connect.
% mysql -utesty -h 127.0.0.1 -p --ssl-mode PREFERRED
Welcome to the MySQL monitor. Commands end with ; or \g.
SSL: Cipher in use is TLS_AES_128_GCM_SHA256
The status reports that the current session is using SSL, by naming the cipher in use. If I weren't connected via SSL, it would say "Not in use" for the SSL cipher.
Certificates are optional. Certificates are for secure authentication. You can use an SSL connection to encrypt the packets, without using certificates. There are separate options in the MySQL CREATE USER statement for requiring a user be authenticated with a certificate, but most people don't bother with that. I've worked at a company where we used certificates instead of passwords for MySQL users. This was very secure, but it required that the company have its own certificate authority (CA) to create a unique certificate for each MySQL user (they used certificates for lots of things within the company, not just MySQL).