Posted In Internet

The FEDERATED MySQL Storage Engine

The FEDERATED MySQL storage engine is the coolest thing EVER! Seriously. It’s already saved me from having to do a whole bunch of synchronization coding. I can only imagine how it’ll come in useful in the future.

So, here’s my situation. I have two mysql servers sitting behind a firewall at “location 1”. People at “location 2” need to write some software to connect to both mysql servers at location 1. However, MyODBC gets confused when connecting to the same hostname on two different tcp ports, or so I’m told.

Anyway, since I was basically told that there’s no way to connect to two seperate mysql servers behind one firewall, I got to thinking. So, I set off searching google for method for mirroring data in MySQL and came across the FEDERATED storage engine.

Now, the servers at location 1 are on a VPN with the network at location 3, my location. So, my network (at location 3) can see the network at location 1 without the firewall getting in the way. Since that’s the case here, I can connect to the default mysql port, 3306, on both servers because I can see their LAN IP, where the people at location 2 can’t (no VPN).

So, we’ve got the network flow figured out, now we can go about getting the FEDERATED storage engine in MySQL working. First, you’ll need MySQL 5.x. I chose MySQL 5.0.24 as it’s the latest stable 5.x release.

To enable the FEDERATED storage engine in mysql 5, you must pass the –with-federated-storage-engine option when running configure. That’s pretty much all that’s required to start using the FEDERATED storage engine. Most linux distributions probably have a mysql 5 package that comes with the FEDERATED engine on already, although Slackware does not currently.

After you build mysql to support the FEDERATED storage engine, there’s really very little left to do to start making use of it. First you’ll need the table structure of the table you want to link to. We’ll use a table named “customers” as an example, with 3 fields, custId, fName, lName. The SQL used to build the initial “customers” table is below. The “customers” table is in the “companyData” database on the MySQL server.

A pretty simple and very basic table setup. Now, the SQL above is the SQL that’s used to generate the original table, the one that is actually storing all the data. To create a FEDERATED table that links to the original customers table, we use basically the same SQL, except we need to change a few things on the last couple lines:

See the last line there, the one starting with CONNECTION? That’s the real important one. It tells the new FEDERATED table where it needs to link to to get it’s data. The format of the CONNECTION clause is:

Pretty simple. You can read more about the FEDERATED storage engine in the MySQL reference manual. There’s also a support forum dedicated to the FEDERATED storage engine, as well as a nice article over at the MySQL developer zone.

Well, now what?

Work with Me

I'm available for hire and always taking new clients, big and small. Got a project or an idea you'd like to discuss? Startup plan but no developer to make it happen? Just get in touch, I'd love to see if I can help you out!

Leave some Feedback

Got a question or some updated information releavant to this post? Please, leave a comment! The comments are a great way to get help, I read them all and reply to nearly every comment. Let's talk. :) is proudly hosted by DigitalOcean

About these ads
  • Pingback: » Linking 2 MySQL Tables Together()

  • Pingback: Slackware 11.0 Release Candidate 3 « Slackware Blog()

  • Pingback: basil's blog » Blogrolling 2006-08-29()

  • Chang Wei ken

    i have not experience about mysql…
    can u tell the detail process how to enable the Federated MySQL Storage?
    i am using the mysql 5.0.24a version….
    after i install, i found the federated feature is disable…
    please reply as soon as possible
    Thank you

  • Chang Wei ken, you need to build MySQL from source. When you run the “./configure” command, make sure you pass the –-with-federated-storage-engine option.

  • pradeepk

    How i can create pool of connection in federated DB

  • Hi!

    You can enable federated storage in Windows like this:
    1. Stop your currently running MySQL service
    2. From Command Prompt, run the mysqld.exe with the parameter –federated (mysqld.exe –federated).
    3. You can close the command-window above.
    4. Start your service again, you now have federated storage engine enabled, check with SHOW ENGINES G.

    • Amit Rai

      Hi Robert..!
      but the problem occur is ” [Warning] timetemp with implicit Default values is deprecated. Please use –Explicit_defaults_for_timestamp”

  • Vincent

    mysqld.exe –federated
    there are 2 dashes

  • omd

    I have used mysql databases quite a lot mainly for open source content management software like oscommerce, wordpress and concrete5. What are the storage limits on your mysql hosting ?

  • the message error appear like this :
    Unable to connect to foreign data source: Can’t connect to MySQL server on ‘’ (10061)
    (2.922 sec)

    how to fix this?

  • Your instructions worked perfectly! Thanks!

    • Glad it was helpful Fred!