Integrate Magento and WordPress When on Different Servers.

Home / Blog / Integrate Magento and WordPress When on Different Servers.

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 "your_email@example.com"

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.

cat ~/.ssh/id_rsa.pub

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 [016]

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 remoteuser@example.com

5) Start the tunnel.

service mytunnel start

MySQL Config

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://dbuser@127.0.0.1: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://dbuser@127.0.0.1:3399/mage_db_name/wordpress_association_type';

Fishpig’s extension can now run queries against your WordPress database from your Magento server.

 

Leave a Comment