I need to setup replication via SSL. 2 nodes master/master for H/A. Then 2 slave nodes for backup. Node 3 will slave off of node 1, and node 4 will slave off of node 2 for redundant backups. Nodes 3 & 4 will store backups to a directory mapped to S3 via S3FS. All nodes are physical.
We're using mariadb-5.5.41 server on CentOS 7 hosts.
It's a neat plan!
However I am unable to get authentication to work with ssl required of the user.
We already have a CA certificate and key established in the environment. And we used those to generate the cert and key to be used with mariadb.
This is the process we used to generate the cert/key:
Using those keys I put together this my.cnf file. Haven't gotten around to configuring replication yet, as I have yet to get ssl logins to work.
[root@db1:~] #cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd ssl ssl-ca=/opt/mysql/ca.crt ssl-cert=/opt/mysql/db1.example.com.crt ssl-key=/opt/mysql/db1.example.com.key
If I create one replication user without SSL required, he is able to log in from node 2 to node 1 no problem:
MariaDB [mysql]> grant replication slave on *.* to 'slave1'@' db2.example.com' identified by 'secret'; Query OK, 0 rows affected (0.00 sec)
[root@db2:~] #mysql -uslave1 -p -h db1.example.com Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 15 Server version: 5.5.41-MariaDB MariaDB Server
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
If I show grants on this user I can confirm that SSL is not required
MariaDB [mysql]> show grants for 'slave1'@'db2.example.com';
+--------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for slave1@db2.example.com |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'db2.example.com' IDENTIFIED BY PASSWORD '*somelongpasswordhash' |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
If I create the second slave user with the SSL requirement, and even flush privileges, I can not log into node 1 from node 2
MariaDB [mysql]> grant replication slave on *.* to 'slave2'@' db2.example.com' identified by 'test' require ssl; Query OK, 0 rows affected (0.00 sec)
[root@db2:~] #mysql -uslave2 -p -h db1.example.com Enter password: ERROR 1045 (28000): Access denied for user 'slave2'@'db2.example.com' (using password: YES)
And if I look at the grants for this user I able to tell that ssl is required for him to be able to login:
MariaDB [mysql]> show grants for 'slave2'@'db2.example.com;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for slave2@db2.example.com |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'db2.example.com' IDENTIFIED BY PASSWORD '*somelongpasswordhash' REQUIRE SSL |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
[root@db1:~] #tail /var/log/mariadb/mariadb_error.log 150720 5:18:24 InnoDB: Initializing buffer pool, size = 128.0M 150720 5:18:24 InnoDB: Completed initialization of buffer pool 150720 5:18:24 InnoDB: highest supported file format is Barracuda. 150720 5:18:24 InnoDB: Waiting for the background threads to start 150720 5:18:25 Percona XtraDB (http://www.percona.com) 5.5.40-MariaDB-36.1 started; log sequence number 120637807 150720 5:18:25 [Note] Plugin 'FEEDBACK' is disabled. 150720 5:18:25 [Note] Server socket created on IP: '0.0.0.0'. 150720 5:18:25 [Note] Event Scheduler: Loaded 0 events 150720 5:18:25 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.41-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
I see absolutely NO indication as to why the login for the 'slave2' user (that requires SSL) is failing.
So my questions are 1) how to I bump up the verbosity on the logs so I can get an indication as to why this is failing? 2) what is the best way to troubleshoot this?
OK so question 2 may seem a little redundant to question 1. But I am truly stumped.
Reindl Harald Re: can't authenticate ssl user account
Jul 21, 2015; 10:25
Reindl Harald
Re: can't authenticate ssl user account
Jul 21
Tim Dunphy Re: can't authenticate ssl user account
Jul 21, 2015; 14:32
Tim Dunphy
Re: can't authenticate ssl user account
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!