I went to implement MySQL replication for a client this evening and ran into some interesting issues that I haven't ran into before. Guess it has been a while since I had to set it up for a client.\u00a0\u00a0 So this post is for notes for me or someone else who might need to do this in the future. The normal installation replication installation works great but if you are going to enable ssl connections this is where the things can get a bit more complex.<\/p>\n
The first thing to find out is if you have your SSL setup correct, try doing:
\nAnd verify the SSL is enabled and build in, in my case everything looked good:
\n
mysql> SHOW VARIABLES LIKE '%ssl%';\n\nhave_openssl = YES\nhave_ssl = YES\nssl_ca = \/etc\/mysql\/certs\/ca-cert.pem\nssl_capath =\nssl_cert = \/etc\/mysql\/certs\/server-cert.pem\nssl_cipher =\nssl_crl =\nssl_crlpath =\nssl_key = \/etc\/mysql\/certs\/server-key.pem\\";\n<\/pre>
\nThis looks correct, so the next thing to figure out is where the error log file is located;
\nmysql> SHOW VARIABLES LIKE '%error_log%';\n\nlog_error = .\/mysql-bin.err\nor something like\nlog_error = \/var\/log\/mysql\/error.log\n<\/pre>
\nNow that you know where the error log is at you can see why it is failing.<\/p>\nIn my case the error was this:
\n2016-12-06 23:21:33 32695 [Warning] Failed to setup SSL\n2016-12-06 23:21:33 32695 [Warning] SSL error: SSL_CTX_set_default_verify_paths failed<\/pre>
\nI love that it is a \"Warning\".\u00a0\u00a0 It is totally broken, but we will list it as a Warning...<\/p>\nWell, this can be caused by several things:<\/p>\n
\n
- No permissions to the files in the folder, use chmod\/chown<\/strong> to give perms.<\/li>\n
- SELinux blocking it, disable selinux or grant permissions via SELinux<\/li>\n
- AppArmor blocking it.\u00a0 (this was my case)<\/li>\n<\/ol>\n
Edit the \/etc\/apparmor.d\/usr.sbin.mysqld<\/strong> file.<\/p>\n
You'll see something like this in the file:
\n\/etc\/mysql\/*.pem r,\n\/etc\/mysql\/conf.d\/ r,\n\/etc\/mysql\/conf.d\/* r,\n\/etc\/mysql\/*.cnf r,\n---> \/etc\/mysql\/certs\/*.pem r, <---\n\/usr\/lib\/mysql\/plugin\/ r,<\/code><\/pre>
\nAdd the ---> line <---, make sure it matches your path to where you are storing the certs.\u00a0 Then restart mysql. After restarting the server, I then got this error: SSL error: Unable to get private key from '\/etc\/mysql\/certs\/server-key.pem' 2016-12-06 23:53:32 21728 [Warning] Failed to setup SSL 2016-12-06 23:53:32 21728 [Warning] SSL error: Unable to get private key Ok, this one threw me for a while.\u00a0 The files are fully readable by MySQL.\u00a0 The issue ends up being incompatibilities between SSL libraries in use.\u00a0 OpenSSL 1.0x vs yaSSL The key file will start like this:
\n-----BEGIN PRIVATE KEY-----<\/pre>
\nIf you used OpenSSL to generate the keys;\u00a0 OpenSSL creates keys in PKCS#8 with a SHA256 digest.\u00a0 Of course yaSSL which is (normally) used by MySQL doesn't support either, and want PKCS#1.\u00a0 So despite having the files fully readable, MySQL is telling you it can't figure out how to \"get the private key\" out of the file.\u00a0 Once you know the issue, it has a simple solution:
\nopenssl rsa -in server-key.pem -out server-key.pem<\/pre>
\nwhen you are done with this command the beginning of the file should look like this:
\n-----BEGIN RSA PRIVATE KEY-----<\/pre>
\nAgain, the internal format is different, so don't try and just change the text and insert the \"RSA\" into it -- it will look like it works until something try's to connect using SSL.<\/p>\nOnce you have this done, restart mysql again and you should be good to go.<\/p>\n","protected":false},"excerpt":{"rendered":"
I went to implement MySQL replication for a client this evening and ran into some interesting issues that I haven't ran into before. Guess it has been a while since I had to set it up for a client.\u00a0\u00a0 So this post is for notes for me or someone else who might need to do… Continue reading MySQL SSL required connection Ubuntu solutions<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[7,39],"tags":[93,94,95],"class_list":["post-461","post","type-post","status-publish","format-standard","hentry","category-performance","category-tips","tag-mysql","tag-replication","tag-ssl","entry"],"_links":{"self":[{"href":"http:\/\/fluentreports.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/461","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/fluentreports.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/fluentreports.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/fluentreports.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/fluentreports.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=461"}],"version-history":[{"count":5,"href":"http:\/\/fluentreports.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/461\/revisions"}],"predecessor-version":[{"id":707,"href":"http:\/\/fluentreports.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/461\/revisions\/707"}],"wp:attachment":[{"href":"http:\/\/fluentreports.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=461"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/fluentreports.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=461"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/fluentreports.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=461"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}