2

Lets say we have MySQL server A, where we need to create a 'copy' of table, which is situated on server B.

We don't have federated enabled. Reseting server A would cause much trouble and I believe, we cannot enable federated without reseting. I also believe that it is not enough to enable it on the B server (correct me if I'm wrong in anything)

What other solutions do we have? Is there something enabled by default in mysql server? Any ideas?

Oriesok Vlassky
  • 797
  • 1
  • 13
  • 26

3 Answers3

2
  • You need federated enabled just on server B
  • You can access a view on A by making a federated table on B
  • You can do INSERT UPDATE DELETE on federated table
  • If you need read-only access you can limit the user privileges

BUT! You can't do any aggregate func. on a view which will be federated (ex. COUNT(), MAX(), UNION...) (you can, however it will lag)

  • Remember to set the KEY's on the federated table you are creating. (or it will lag horr.)
  • Remember to use ALGORITHM=MERGE on views
  • Remember to grant acces to USERNAME(from connection string) on server A

example of a federated table on server B:

delimiter $$
CREATE TABLE `schemaName`.`tableName`(
    `keyName` VARCHAR(10) NOT NULL,
    `key2Name` DATE DEFAULT '2012-01-01',
    KEY `keyName` (`keyName`) 
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8
CONNECTION='mysql://USERNAME:PASSWORD@IPADDRESS:PORTNUMBER/baseSchema/baseTable'
$$

And the view on server A:

CREATE
    ALGORITHM = MERGE
    DEFINER = `ANOTHERUSERNAME`@`%`
    SQL SECURITY DEFINER
VIEW `baseSchema`.`baseTable` AS
    SELECT
        ... AS `keyName`,
        ... AS `key2Name`
    FROM
        ...
drzymala
  • 2,009
  • 20
  • 26
  • **WARNING** As of today **2012-09-18** MySql has a bug in making a federated table of a view which has joins inside. Returns no error, but ignores the join. – drzymala Sep 18 '12 at 18:12
2

You can't write to a FEDERATED table period ever under any circumstances! You must make a direct DB connection to the server running the database that you have created the FEDERATED table connection to.

I would be pretty angry if you could write to a FEDERATED table now. It has never allowed it for many many years. It would also make the 10 years worth of code I have written a complete waste of my time if that were true.

It is quite handy to use in the instance of making a centralized user database for 100 websites! i.e. A Social Network which is what I use it for...

I have used it since before anyone knew the ENGINE existed. It was there without documentation when I started using it and I had to experiment to even figure out what it could be used for. The ENGINE was always there and had little or no documentation for many years.

The only federated DB tables that I use or have, share network user information. All of my external websites still have to make a database connection to the main server when someone signs up as a new user.

You need two database connectors to use FEDERATED the right way!. One for UPDATE and INSERT etc and the other DB would be for all local queries and JOINS etc.

My network uses two external databases and one local for each website. I use three database connectors.

local connects to Music Hub and Network Hub with separate connectors for all UPDATE and INSERT and everything else JOIN etc just uses the local DB connector!

I hope this helps you understand it a little better :)

I am using MySQL 5.6 and PHP 5.

You have to ask yourself for almost five years, why was there little or no documentation on what it was used for? most people did not even know it existed...

It is used mainly to import query results into your local database! Even with full write access, you would never be able to write to a FEDERATED table! It is not allowed for security reasons and it would be very very slow as you said.

You may correct me if I am wrong but please know that I have been using FEDERATED since before they told anyone it existed.

It takes about 30 seconds to enable FEDERATED and about 30 seconds to reboot the server. In the instance that you are using a VPS, you might not be allowed to have FEDERATED enabled.

Edit your my.cnf file and restart the server...

FEDERATED is no longer slow by the way... Never was when used in the manner I just explained...

Even if I could write to a FEDERATED Table I would not do it. You are FEDERATING two servers by adding a federated table to your local website.

This will slow down depending on how many HOPS away you are from the remote MySQL server. Also be sure and add your IP to the remote server under their cPanel.

IF the websites reside all on the same server and are just separate user accounts and websites with separate IP's there should be Zero Lag.

McLeod and Heimbigner were among the first to define a federated database system in the mid-1980s.

Ernest Allen Buffington
  • 92
  • 1
  • 10
0

UPDATE: If you upgrade to mariaDB you will have to load federatedX as a plugin. The old federated project will live on but with new eyes and fresh souls working on it. Oracle no longer has a hand in it... It is now called federatedX

Ernest Allen Buffington
  • 92
  • 1
  • 10
  • You can edit your original answer to add an update, no need to post another answer. – lukess Oct 17 '17 at 23:45
  • Many people are still using federated and not federatedX. What I said above does not apply to federatedX – Ernest Allen Buffington Oct 18 '17 at 01:04
  • If you read the post it says with MariaDB you have to use a plugin and in the above post, it is compiled in with the standard engine and only needs to be enabled in my.cnf. The update is to let folks know that the old federated way and engine is gone and no longer in production. Oracle is no longer working on it. It was dropped and new folks are working on a new project with the old code and it has to be loaded via an external plugin. It can't be loaded in the same manner. As of now in MariaDB 10.02 it is loaded externally and there is no editing my.cnf file. – Ernest Allen Buffington Oct 18 '17 at 01:17