2

We have a Magento site on a VM with 2GB of RAM. Right now, both Apache and MySQL are running on that same VM. Since that's just not enough RAM for this site, I have been tasked with separating the MySQL server onto a separate 2GB VM (which is running on a different host machine in the same datacenter).

Because the network between the two VMs is not secure, I want to use SSL on the MySQL connection between them. I cannot seem to find how to tell Magento to use SSL.

I know that in PDO directly I would do something like this:

<?php
$db = new PDO(
    'mysql:host=hostname;dbname=ssldb',
    'username',
    'password',
    array(
        PDO::MYSQL_ATTR_SSL_KEY    =>'/path/to/client-key.pem',
        PDO::MYSQL_ATTR_SSL_CERT=>'/path/to/client-cert.pem',
        PDO::MYSQL_ATTR_SSL_CA    =>'/path/to/ca-cert.pem'
    )
);

Where in the config file would I put those SSL options though?

  • I just found this from earlier this year ... with no response :(. – Moshe Katz Dec 3 '14 at 7:01
  • Why not just grow the VM and save yourself the grief? Or move to a VM provider that supports private VLANs? Or if you must, use a VPN between the two and have many more benefits. The overhead of SSL with MySQL is significant (25% in Percona's testing, percona.com/blog/2013/10/10/mysql-ssl-performance-overhead) – choco-loo Dec 3 '14 at 8:12
  • @choco-loo Because separate web and database servers is good practice and two VMs are what I've been given to do the job with. – Moshe Katz Dec 3 '14 at 18:15
  • My question was rhetorical :) It's certainly not good practice, it's the polar opposite of good practice. Magento is infinitely slower in a web+db configuration. At your scale, you would be far better pooling your resources, you would have better performance, better reliability, better security, ease of management etc. – choco-loo Dec 3 '14 at 23:13
  • @choco-loo It is definitely good practice. See here among many other sources for some of the reasons why. Having separate Web and Database servers is required in order to pass certain security audits. – Moshe Katz Dec 4 '14 at 5:07
3

Not really any clean solution for this as the class in question is under lib so you can't rewrite it as per a normal model etc. Have a look at class Zend_Db_Adapter_Pdo_Abstract and protected method _connect(). This is where you will see the call to new PDO() and where you need to make the changes. As the only option for overriding this method is to move the entire class to the local codepool, there is better, much shorter class you can move instead. Class Magento_Db_Adapter_Pdo_Mysql extends off Zend_Db_Adapter_Pdo_Abstract and as method _connect() is protected and not private you can copy it into the class and it will run instead of the Zend class method.

So copy lib/Magento/Db/Adapter/Pdo/Mysql.php to app/code/local/Magento/Db/Adapter/Pdo/Mysql.php, add the _connect() method from class Zend_Db_Adapter_Pdo_Abstract and make the necessary changes to the new PDO() call.

Note that the classes here are only relevant from 1.8CE onwards - lib/Magento/ didn't exist before that. Also bear in mind moving core files to the local codepool is not a good way of doing things. Even though it's the only real option that I can see in this case, it's still not good practice.

2

In the end, I went with an SSH tunnel maintained with autossh. It may not be as performant as raw tcp, but my testing indicates that it is perfectly adequate for us (with room for future growth too).

We have already seen a significant performance boost now that the web server and the database server are no longer fighting for RAM and disk IO.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.