MySQL

Mysql - How to execute the sql file in mysql prompt?

Having sql commands in the file and want to execute it in mysql prompt? To execute the sql file in sql prompt, you can use source or /. command.

For example you have staff.sql file and want to execute it.

mysql> source staff.sql
or

mysql> /. staff.sql

It will display the output of the commands.

MySQL - FEDERATED Storage Engine

MySQL supports storage engines : MyISAM, InnoDB, Memory, Merge, CSV, Federated, Archive, NDBCLUSTER, Blackhole. In most of the application i have worked either MyISAM or InnoDB. Recently come across Federated Storage engine usage experience.

Federated Storage Engine: In normal storage engines MyISAM/InnoDB, the table consists of the table definition and data. That is each table have .frm (table definition) file and data in the local machine.

In the federated storage engine, the table consists of table definition but the data in remote server.
That is each table in MySQL have .frm file associated with it and this defintion have connection string for remote server.

Whenever the insert/delete/update/select to local server transfer to remote using the connection string, execute in remote server and remote server perform/return the result of the sql to local.

This available from MySQL version 5.0.3 onwards.

Show Case:

1. Create table in remote server

create table new_fed_table(
id int(11) not null auto_increment,
name varchar(50) not null
) Engine=MyISAM;

2. use show create in remote server like below
2.1 show create table new_fed_table;
2.2 copy the create statement and change the Engine to Federated and add connection string

create table local_new_fed_table(
id int(11) not null auto_increment,
name varchar(50) not null
) Engine=FEDERATED Connection='mysql://usr@remotehost:port/db_name/new_fed_table';

Points
1. The two servers can be running in the same or different host
2. First the remote table has to create.
3. It can be any database engine
4. The structure of the federated and remote host should be same except the Engine and connection string.

Limitations:
There are limitation like
* can't do alert table
* no transaction support
* the remote server should be MySQL server
* the remote table should exists prior create federated table and so

You can read more on limitation in http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html

Syndicate content