?

Log in

No account? Create an account

New Federated "CREATE SERVER" for MySQL

« previous entry | next entry »
Jan. 9th, 2007 | 12:05 pm

One of the features not yet mentioned that is in 5.1 is Federated's new "CREATE SERVER" command.

The synopsis?

Tired of typing URL's for Federated tables? AKA:

CREATE TABLE A (
`a_id` int(20) NOT NULL,
`some_string` varchar(128) NOT NULL default ''
)
ENGINE="FEDERATED" CONNECTION='mysql://joe@foo.example.com:3306/ schema_a/A';

With 5.1 you have a new option, the "CREATE SERVER" command.

CREATE SERVER 'master_database' foreign data wrapper 'mysql' options
(HOST '10.0.2.128',
DATABASE 'schema_a',
USER 'joe',
PASSWORD '',
PORT 3306,
SOCKET '',
OWNER 'root');

For the create table all you then need to do is:
CREATE TABLE A (
`a_id` int(20) NOT NULL,
`some_string` varchar(128) NOT NULL default ''
)
ENGINE="FEDERATED" CONNECTION='master_database';

Want to change the definition?

ALTER SERVER 'master_database' options(DATABASE 'schema_b');

So why is this nifty?

  • If you have a number of tables you don't have to retype the CONNECTION string over and over again.
  • When updating, you just update the server definition.
  • You can control the permissions on who sees the connection definition.

    Patrick Galbraith provided the functionality.

    If you want to play with this, you will need to build 5.1 from source. Lenz recently made post on how to do this.
  • Link | Leave a comment |

    Comments {0}