Hey Alberto,
Perfect! Thanks for your response. Moving the certs and keys to an alternate location worked exactly right.
Master:
MariaDB [(none)]> show variables like '%ssl%'; +---------------+----------------------+ | Variable_name | Value | +---------------+----------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /opt/mysql/ca.crt | | ssl_capath | | | ssl_cert | /opt/mysql/mysql.crt | | ssl_cipher | | | ssl_key | /opt/mysql/mysql.key | +---------------+----------------------+ 7 rows in set (0.01 sec)
Slave:
mysql> show variables like '%ssl%'; +---------------+----------------------------+ | Variable_name | Value | +---------------+----------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /opt/mysql/ca.crt | | ssl_capath | | | ssl_cert | /opt/mysql/mysql-slave.crt | | ssl_cipher | | | ssl_key | /opt/mysql/mysql-slave.key | +---------------+----------------------------+ 7 rows in set (0.00 sec)
At least now SSL is recognized by the systems.
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: web2.somewhere.com Master_User: jf_slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 27664 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 391 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: tesdb 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: 27664 Relay_Log_Space: 548* Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /opt/mysql/ca.crt Master_SSL_CA_Path: Master_SSL_Cert: /opt/mysql/mysql-slave.crt Master_SSL_Cipher: Master_SSL_Key: /opt/mysql/mysql-slave.key Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec)
Thanks so much for all your help! This was very sanity-saving. :)
Best! Tim
On Thu, Mar 12, 2015 at 10:42 AM, Alberto Rivera Laporte < arlaporte@gmail.com> wrote:
On Thu, Mar 12, 2015 at 8:57 AM Tim Dunphy bluethundr@gmail.com wrote:
Hey everybody,
I'm trying to get mysql master/slave replication to work under SSL. I've created the certs for both the slave and the master. I've configured the master and slave my.cnf. And it does appear that replication is actually working.
Master is actually MariaDB (version 5.5.41-MariaDB-log, and the slave is MySQL (version 5.5.41-log).
But there are two issues I'd like to resolve. One is that SSL appears to
be
disabled.
If I look at both the master and the slave and do a 'show variables' command, I can see that it's recognizing the certs. But the
'have_openssl'
and 'have_ssl' variables are showing as DISABLED.
Watch, on the master:
MariaDB [(none)]> show variables like '%ssl%'; +---------------+--------------------------------+ | Variable_name | Value | +---------------+--------------------------------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | /etc/pki/CA/certs/ca.crt | | ssl_capath | | | ssl_cert | /etc/pki/tls/certs/mysql.crt | | ssl_cipher | | | ssl_key | /etc/pki/tls/private/mysql.key | +---------------+--------------------------------+ 7 rows in set (0.01 sec)
On the slave:
mysql> show variables like '%ssl%'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | /etc/pki/CA/certs/ca.crt | | ssl_capath | | | ssl_cert | /etc/pki/tls/certs/mysql-slave.crt | | ssl_cipher | | | ssl_key | /etc/pki/tls/private/mysql-slave.key | +---------------+--------------------------------------+ 7 rows in set (0.00 sec)
And yet I clearly have SSL enabled in both configurations.
In the master mysql configuration I have:
[root@web2:~] #cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 *ssl* *ssl-ca=/etc/pki/CA/certs/ca.crt* *ssl-cert=/etc/pki/tls/certs/mysql.crt* *ssl-key=/etc/pki/tls/private/mysql.key* server-id = 1 log_bin = /var/log/mariadb/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = jokefire
[mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid
On the mysql slave:
[root@ops:~] #cat /etc/my.cnf [mysqld] # Settings user and group are ignored when systemd is used (fedora >=
15).
# If you need to run mysqld under different user or group, # customize your systemd unit file for mysqld according to the # instructions in http://fedoraproject.org/wiki/Systemd user=mysql http://fedoraproject.org/wiki/Systemduser=mysql *ssl* *server-id=2*
*replicate-do-db=jokefire* *ssl-ca=/etc/pki/CA/certs/ca.crt* *ssl-cert=/etc/pki/tls/certs/mysql-slave.crt* *ssl-key=/etc/pki/tls/private/mysql-slave.key* thread_cache_size = 4
datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 ;plugin-load=rpl_semi_sync_master=semisync_master.so ;plugin-load=rpl_semi_sync_slave=semisync_slave.so ;rpl_semi_sync_master_enabled=1 ;rpl_semi_sync_master_timeout=10 ;rpl_semi_sync_slave_enabled=1 ;performance_schema query_cache_size = 8MB innodb_buffer_pool_size = 199M general_log_file=/var/log/mysql/mysql.log general_log=1 log-error=/var/log/mysql/mysql_error_log log-slow-queries=/var/log/mysql/mysql_slow_log wait_timeout = 86400
[mysqld_safe] general_log_file=/var/log/mysql/mysql.log general_log=1 log-error=/var/log/mysql/mysql_error_log log-slow-queries=/var/log/mysql/mysql_slow_log pid-file=/var/run/mysqld/mysqld.pid innodb_buffer_pool_size = 199M wait_timeout = 28800 interactive_timeout = 28800 master-connect-retry=60
So my first question is, why is SSL not enabled in either database? I restarted the service on both machines before taking a look at the variables.
The next problem I'm having is that I can't seem to get the replication user to connect. I had to use an account with more privileges (grant all) in order to connect from the slave to the master.
I used this grant on the master to try and setup the replication user:
GRANT REPLICATION SLAVE ON *.* TO 'jf_slave'@'ops.somewhere.com' IDENTIFIED BY 'secret' REQUIRE SSL;
Then back on the slave I used this command to connect the slave to the master:
mysql> CHANGE MASTER TO MASTER_HOST='web2.somewhere.com', MASTER_USER='jf_slave', MASTER_PASSWORD='secret', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=34697, MASTER_SSL=1, MASTER_SSL_CA = '/etc/pki/CA/certs/ca.crt', MASTER_SSL_CERT = '/etc/pki/tls/certs/mysql.crt', MASTER_SSL_KEY = '/etc/pki/tls/private/mysql.key';
And when I start up the slave I see that there's a problem connecting
from
the slave to the master:
mysql> show slave status \G *************************** 1. row *************************** *Slave_IO_State: Connecting to master* Master_Host: web2.somewhere.com Master_User: jf_slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 761404 Relay_Log_File: mysqld-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000002 *Slave_IO_Running: Connecting* Slave_SQL_Running: Yes Replicate_Do_DB: testdb 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: 761404 Relay_Log_Space: 107 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/pki/CA/certs/ca.crt Master_SSL_CA_Path: Master_SSL_Cert: /etc/pki/tls/certs/mysql.crt Master_SSL_Cipher: Master_SSL_Key: /etc/pki/tls/private/mysql.key Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1045 Last_IO_Error: error connecting to master *'jf_slave@web2.somewhere.com:3306 http://jf_slave@web2.somewhere.com:3306' - retry-time: 60 retries: 86400* Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec)
And if I go back the command line in bash, and try to connect from the slave to the master, it seems that I can't:
[root@ops:~] #mysql -ujf_slave -p -h web2.somewhere.com Enter password: ERROR 1045 (28000): *Access denied* for user 'jf_slave'@'
ops.somewhere.com
' (using password: YES)
So I made sure that I could connect from the slave to the master using an admin account, that has some more privileges:
[root@ops:~] #mysql -uadmin -p -h web2.somewhere.com Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1062 Server version: 5.5.41-MariaDB-log MariaDB Server
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
And then setup that account as the replication user:
mysql> CHANGE MASTER TO MASTER_HOST='web2.somewhere.com', MASTER_USER='admin', MASTER_PASSWORD='secret', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=767030, MASTER_SSL=1, MASTER_SSL_CA = '/etc/pki/CA/certs/ca.crt', MASTER_SSL_CERT = '/etc/pki/tls/certs/mysql.crt', MASTER_SSL_KEY = '/etc/pki/tls/private/mysql.key'; Query OK, 0 rows affected (0.02 sec)
You can see that replication is working:
mysql> show slave status \G *************************** 1. row *************************** *Slave_IO_State: Waiting for master to send event* Master_Host: web2.somewhere.com Master_User: admin Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 * Read_Master_Log_Pos: 771825* Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 391 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: testdb 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: 771825 Relay_Log_Space: 548 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/pki/CA/certs/ca.crt Master_SSL_CA_Path: Master_SSL_Cert: /etc/pki/tls/certs/mysql.crt Master_SSL_Cipher: Master_SSL_Key: /etc/pki/tls/private/mysql.key Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec)
And if you run that command a couple times you can see that the bin log position changes.
I realize that it can be dangerous to setup a user with elevated
privileges
to perform the replication. But I'm using a test database with test data until I can get this working correctly. Plus I also have the firewall limiting the connection to only the slave from the master over the
database
port.
Ok, so my second question is, why can't the replication user connect from the slave to the master, using that grant command I showed you a bit earlier? It seems to me like it should have worked.
And my last question is more of a minor annoyance, and shouldn't affect
the
overall operation of the database.
If I put this command: master-connect-retry=60 in the [mysqld] section on the slave, the mysqld service will not start. If, instead I put it into the [mysqld_safe] section, I'm able to start up mysql with no issues. Again, this is something I'm just curious about. The other two questions are quite a bit more important.
I realize this is more of a mysql question, than it is a CentOS admin question. But you guys seem really knowledgable on this topic. And I've
had
great luck with this list in the past. So I hope you won't mind me
tapping
your expertise in this area.
I definitely welcome the advice of the experts in this community.
Thanks! Tim
-- GPG me!!
gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B _______________________________________________ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Hello Tim,
Here is a suggestion to take into consideration and may explain why your ssl configuration isn't working:
The mysqld process runs as the mysql user. It's parent which is the mysqld_safe runs as the root user. That being said the mysql user needs to have at least read permission to the locations where the ssl files are located. By default on Centos the /etc/pki/CA/private directory has its directory permissions to only allow the root user. If the mysql user cannot read all ssl files SSL will not work.
[root@example.com CA]# ls -l /etc/pki/CA/ total 16 drwxr-xr-x 2 root root 4096 Jan 20 11:32 certs drwxr-xr-x 2 root root 4096 Jan 20 11:32 crl drwxr-xr-x 2 root root 4096 Jan 20 11:32 newcerts drwx------ 2 root root 4096 Jan 20 11:32 private
- Regarding your replication specific user not being able to connect to
the master. It may not work until SSL is fully working since you specifically stated to require and SSL connection. So the symptom of this might be resolved when SSL is fixed.
Best of luck. _______________________________________________ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos