At Brim, building a strong sense of community around our store has always been our goal. Moving beyond simply providing extensions and support, we want to use the store as a platform where we can share our personal experiences, while learning from yours. The following entry reflects this effort, and details how we recently utilized Fishpig’s WordPress Integration extension to run queries against our WordPress database from our Magento server. Recently, we decided to host more of our content on our WordPress site and connect Magento to it via Fishpig’s WordPress integration extension. However, our WordPress site is on a completely different server than our Magento commerce store, resulting in complications with certain queries. Some WordPress association tables that Fishpig’s extension creates need to be accessible to the WordPress site, but they are also on different database servers and we wanted to keep it that way for security reasons.
So, we decided to look for a solution and found it in MySQL’s federated tables. We have heard of federated tables before but never really had a use case for them. Nonetheless, it seemed like it might be a good fit for our needs, as it allows you to access remote tables as if they were local tables. So we decided to try them out by creating a couple federated tables on our WordPress database server.
We ran the federated connection through an SSH tunnel managed by AutoSSH and a custom upstart script. Here are the steps of what we did on our Ubuntu servers.
Create an SSH Tunnel
Our Magento server establishes the SSH tunnel and ensures it’s running using an upstart script.
1) Create a system user to manage the tunnel, with a SSH public/private key pair used for authentication. Github has a great article
useradd mytunnel -m su mytunnel ssh-keygen -t rsa -C "[email protected]"
2) Take the contents of the newly generated public key and place it on the remote WordPress server in the user’s authorized_keys (~/.ssh/authorized_keys) file.
3) Setup a SSH Tunnel for mysql connectivity by installing Auto SSH.
sudo apt-get install autossh
4)Create an upstart script to start and keep the tunnel alive. We placed ours in /etc/init/mytunnel.conf
description "AutoSSH Tunnel" author "Brim" start on (local-filesystems and net-device-up IFACE=eth0) start on runlevel  respawn respawn limit 5 30 exec start-stop-daemon --start -c mytunnel --exec autossh -M 0 -N -R 3399:127.0.0.1:3306 -L 3399:127.0.0.1:3306 -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" -o "StrictHostKeyChecking=no" -o "BatchMode=yes" -i /home/mytunnel/.ssh/id_rsa [email protected]
5) Start the tunnel.
service mytunnel start
This needs to be done on your WordPress server.
1) Enabled federated tables in your mysql config. Add “federated” under the [mysqld] section of your MySQL configuration file: /etc/mysql/my.cnf.
2) Create a database with the same name as your Magento database on your WordPress database server.
3) Create the federated tables as shown below. Substitute your database username and password.
DROP TABLE IF EXISTS wordpress_association; CREATE TABLE `wordpress_association` ( `assoc_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `type_id` int(3) unsigned NOT NULL DEFAULT '0', `object_id` int(11) unsigned NOT NULL DEFAULT '0', `wordpress_object_id` int(11) unsigned NOT NULL DEFAULT '0', `position` int(4) unsigned NOT NULL DEFAULT '4444', `store_id` smallint(5) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`assoc_id`) ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql:[email protected]:3399/mage_db_name/wordpress_association'; DROP TABLE IF EXISTS wordpress_association_type; CREATE TABLE `wordpress_association_type` ( `type_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `object` varchar(16) NOT NULL DEFAULT '', `wordpress_object` varchar(16) NOT NULL DEFAULT '', PRIMARY KEY (`type_id`) ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql:[email protected]:3399/mage_db_name/wordpress_association_type';
Fishpig’s extension can now run queries against your WordPress database from your Magento server.