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 *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
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
2. 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.
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.
- 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.
Thanks for your reply! That answer actually makes complete sense. Ok, so here is what I tried, so far without success. I gave the mysql group ownership of all related directories. And changed group permissions so that group can access them:
[root@web2:/etc] #ls -ld /etc/pki/CA drwxrwxr-x. 6 root mysql 4096 Jan 20 15:58 /etc/pki/CA [root@web2:/etc] #ls -ld /etc/pki/tls/{private,certs} drwxrwxr-x. 2 root mysql 4096 Mar 11 22:57 /etc/pki/tls/certs drwxrwxr-x. 2 root mysql 4096 Mar 11 22:57 /etc/pki/tls/private
Restarted the mariadb service. And when I took another look at the SSL variable, it's still showing that SSL is not enabled:
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.00 sec)
Do you think I'm going about this in the right way? Is there anything else I can try to resolve this?
Thanks 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
Tim Dunphy wrote:
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.
<snip>
Thanks for your reply! That answer actually makes complete sense. Ok, so here is what I tried, so far without success. I gave the mysql group ownership of all related directories. And changed group permissions so that group can access them:
[root@web2:/etc] #ls -ld /etc/pki/CA drwxrwxr-x. 6 root mysql 4096 Jan 20 15:58 /etc/pki/CA [root@web2:/etc] #ls -ld /etc/pki/tls/{private,certs} drwxrwxr-x. 2 root mysql 4096 Mar 11 22:57 /etc/pki/tls/certs drwxrwxr-x. 2 root mysql 4096 Mar 11 22:57 /etc/pki/tls/private
Restarted the mariadb service. And when I took another look at the SSL variable, it's still showing that SSL is not enabled:
<snip> Some of those will *not* work. For example, you will has ssh issues yourself is ~/.ssh is *anything* other than 700.
No: /etc/pki/CA should NOT be group writeable. Ditto for /etc/pki/tls/cernts and private.
mark
No: /etc/pki/CA should NOT be group writeable. Ditto for /etc/pki/tls/cernts and private
Ok, yeah I can understand that. I'll correct it. Still need a way to get SSL enabled however. Any suggestions there?
Thanks Tim
On Thu, Mar 12, 2015 at 11:40 AM, m.roth@5-cent.us wrote:
Tim Dunphy wrote:
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.
<snip> > Thanks for your reply! That answer actually makes complete sense. Ok, so > here is what I tried, so far without success. I gave the mysql group > ownership of all related directories. And changed group permissions so > that group can access them: > > [root@web2:/etc] #ls -ld /etc/pki/CA > drwxrwxr-x. 6 root mysql 4096 Jan 20 15:58 /etc/pki/CA > [root@web2:/etc] #ls -ld /etc/pki/tls/{private,certs} > drwxrwxr-x. 2 root mysql 4096 Mar 11 22:57 /etc/pki/tls/certs > drwxrwxr-x. 2 root mysql 4096 Mar 11 22:57 /etc/pki/tls/private > > Restarted the mariadb service. And when I took another look at the SSL > variable, it's still showing that SSL is not enabled: <snip> Some of those will *not* work. For example, you will has ssh issues yourself is ~/.ssh is *anything* other than 700.
No: /etc/pki/CA should NOT be group writeable. Ditto for /etc/pki/tls/cernts and private.
mark
CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
On Thu, Mar 12, 2015 at 10:49 AM Tim Dunphy bluethundr@gmail.com wrote:
No: /etc/pki/CA should NOT be group writeable. Ditto for /etc/pki/tls/cernts and private
I agree - Sorry I did not mean to imply that the directory permissions on /etc/pki/CA should be modified. However it was mentioned it as a probable issue for his ssl configuration on mysql/mariadb not .
Ok, yeah I can understand that. I'll correct it. Still need a way to get SSL enabled however. Any suggestions there?
Thanks Tim
Here's a test I did on a vanilla Centos 7 with mariadb from the stock Centos repo, first with the locations you chose on your non-working scenario, and second with an alternate location chosen for the ssl key and cert files.
First example:
-- /etc/my.cnf.d/server.cnf
[mysqld] ssl-key=/etc/pki/CA/private/test-key.pem ssl-cert=/etc/pki/CA/certs/test-cert.pem
Here were the errors on startup from having the ssl files in that location which subsequently end up ssl being disabled, much like you are experiencing.
-- /var/log/mariadb/mariadb.log
150312 13:37:51 InnoDB: Waiting for the background threads to start 150312 13:37:52 Percona XtraDB (http://www.percona.com) 5.5.40-MariaDB-36.1 started; log sequence number 0 150312 13:37:52 [Note] Plugin 'FEEDBACK' is disabled. SSL error: Unable to get private key from '/etc/pki/CA/private/test-key.pem' 150312 13:37:52 [Warning] Failed to setup SSL 150312 13:37:52 [Warning] SSL error: Unable to get private key 150312 13:37:52 [Note] Server socket created on IP: '0.0.0.0'. 150312 13:37:52 [Note] Event Scheduler: Loaded 0 events 150312 13:37:52 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.41-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
MariaDB [(none)]> show variables like '%ssl%'; +---------------+----------------------------------+ | Variable_name | Value | +---------------+----------------------------------+ | have_openssl | DISABLED | | have_ssl | DISABLED <snip>
Second example:
Here is the results of the ssl key and cert files being moved to another specific location where the mysql user can read those files and restarting the service:
-- /etc/my.cnf.d/server.cnf
[mysqld] ssl-key=/etc/mysql/test-key.pem ssl-cert=/etc/mysql/test-cert.pem
-- /var/log/mariadb/mariadb.log 150312 13:48:19 InnoDB: Waiting for the background threads to start 150312 13:48:20 Percona XtraDB (http://www.percona.com) 5.5.40-MariaDB-36.1 started; log sequence number 1597945 150312 13:48:20 [Note] Plugin 'FEEDBACK' is disabled. 150312 13:48:20 [Note] Server socket created on IP: '0.0.0.0'. 150312 13:48:20 [Note] Event Scheduler: Loaded 0 events 150312 13:48:20 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.41-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
MariaDB [(none)]> show variables like '%ssl%'; +---------------+--------------------------+ | Variable_name | Value | +---------------+--------------------------+ | have_openssl | YES | | have_ssl | YES | <snip>
If you want to maintain consistency and store the certificates in /etc/pki/* these should be the locations where they can be stored:
#certs, including intermediates /etc/pki/tls/certs
# keys /etc/pki/tls/private
Here were the result of a third test:
-- /etc/my.cnf.d/server.cnf
[mysqld]
ssl-key=/etc/pki/tls/private/test-key.pem ssl-cert=/etc/pki/tls/certs/test-cert.pem
MariaDB [(none)]> show variables like '%ssl%'; +---------------+-----------------------------------+ | Variable_name | Value | +---------------+-----------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | | | ssl_capath | | | ssl_cert | /etc/pki/tls/certs/test-cert.pem | | ssl_cipher | | | ssl_key | /etc/pki/tls/private/test-key.pem | +---------------+-----------------------------------+
Best,
On Thu, Mar 12, 2015 at 10:49 AM Tim Dunphy bluethundr@gmail.com wrote:
No: /etc/pki/CA should NOT be group writeable. Ditto for /etc/pki/tls/cernts and private
Ok, yeah I can understand that. I'll correct it. Still need a way to get SSL enabled however. Any suggestions there?
I totally misread your configuration options and locations on your original post, my apologies. You indeed had what should be considered correct locations for the ssl cert and key files. So if it still not functioning I would defer you to the MysQL mailing list / support channels to see if they can assist you in figuring out any further.
Best of luck once again.
On Thu, March 12, 2015 10:40 am, m.roth@5-cent.us wrote:
Tim Dunphy wrote:
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.
<snip> > Thanks for your reply! That answer actually makes complete sense. Ok, so > here is what I tried, so far without success. I gave the mysql group > ownership of all related directories. And changed group permissions so > that group can access them: > > [root@web2:/etc] #ls -ld /etc/pki/CA > drwxrwxr-x. 6 root mysql 4096 Jan 20 15:58 /etc/pki/CA > [root@web2:/etc] #ls -ld /etc/pki/tls/{private,certs} > drwxrwxr-x. 2 root mysql 4096 Mar 11 22:57 /etc/pki/tls/certs > drwxrwxr-x. 2 root mysql 4096 Mar 11 22:57 /etc/pki/tls/private > > Restarted the mariadb service. And when I took another look at the SSL > variable, it's still showing that SSL is not enabled: <snip> Some of those will *not* work. For example, you will has ssh issues yourself is ~/.ssh is *anything* other than 700.
No: /etc/pki/CA should NOT be group writeable. Ditto for /etc/pki/tls/cernts and private.
I have my doubts about permissions on /etc/pki/tls/private and on private key inside it as well. Somebody hopefully will correct me as I don't know how it is implemented in mysql/mariadb, but I assume sanity. And sanity suggests that the first process (mysqld_safe) that runs as root reads private key (and likely certificate), then passes private key to the child process(es) which runs as regular user that is not able to read private key, but gets it from parent proces. My assumption comes from what apache is doing (only apache used droppriv).
I would (roll perms/ownership) back to default, and try to check locally using openssl whether daemon is using ssl/cert/key, maybe start mysql daemon in debugger to see what is going on with reading private key. I would also think of other reasons why your instance of mysql (or mariadb) could not be able to use _your_ key and cert, see, e.g.:
http://forums.mysql.com/read.php?11,400856,401127
(your case may be different, I would just try think wider, but maybe debugger will give you the direct lead).
Valeri
++++++++++++++++++++++++++++++++++++++++ Valeri Galtsev Sr System Administrator Department of Astronomy and Astrophysics Kavli Institute for Cosmological Physics University of Chicago Phone: 773-702-4247 ++++++++++++++++++++++++++++++++++++++++
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
-
Alberto Rivera Laporte -
m.roth＠5-cent.us -
Tim Dunphy -
Valeri Galtsev