CODEWITHSUNDEEP
mysql
Waddler
Waddler

Reputation: 219

Federated Table Clarification

In my prior job, I was able to copy data from our production environment in a breeze by using the following statements:

from tablename@UNIXPROD2 INSERT INTO tablename@UNIXTEST2

My current job's databases aren't setup in this fashion.

So, I did some research on MySQL 5.0+ because that's what we are using for one of our customers. And I came across FEDERATED tables, so as I was reading, I found this (here):

As of MySQL 5.0.46, FEDERATED performs bulk-insert handling such that multiple rows are sent to the remote table in a batch. This provides a performance improvement. Also, if the remote table is transactional, it enables the remote storage engine to perform statement rollback properly should an error occur. This capability has the following limitations:

To me, this indicates that (A) I can copy the data from our prod database to our test database; (B) any actions performed on the federated table will also be processed on the source table, which is not what I want to do. I have some scripts that I need to run and I want to run it against actual prod data to make sure it works before I use it in the prod environment.

My question: Is my interpretation correct?

Assuming it is, I've tried:

I would appreciate any help in this matter.

EDIT: After further research, I think might be able to do what I need using OUTFILE and INFILE whereby I would use OUTFILE on the prod table(s) and then INFILE those rows on the test table(s). Thoughts?

Upvotes: 1

Views: 2474

Answers (1)

drzymala
drzymala

Reputation: 2049

My answer:

A - correct

B - correct.

You could set the user permission to read-only, but in your situation I would not use federated tables, instead dump the whole db into file and then restore it on the other server. Easiest way - use MySql Workbench.

and some info about federated tables:

  • 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
        ...

Upvotes: 1

Related Questions