8 thoughts on - Mysql Replication – Problems

• 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.

• 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

• Tim Dunphy wrote:
  files
  
  
  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

• Ok, yeah I can understand that. I’ll correct it. Still need a way to get SSL enabled however. Any suggestions there?

  Thanks Tim

• 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
  ++++++++++++++++++++++++++++++++++++++++

• 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 .

  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
  

  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 |
  

  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,

• 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.

• 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