[Ilugc] mysql db to db query
- From: lug@xxxxxxxxxxxxxxxxxx (Chandrashekar Babu)
- Date: Thu, 10 Feb 2011 22:42:52 +0530
On 10/02/11 4:33 PM, JAGANADH G wrote:
Is it possible to quary and fetch data in one mysql db and store it in a
second on .
I have machine M1 and M2 with mysql configured . There is a DB in M1 namely
employee_details . Now I have to fectch all the recods in M1
employee_details from and have to store M2 mysql database via mysql query.
You might want to try using mysqldump as mentioned by Sathia.
If you want to replicate just one table from another machine
(M1), you can first try creating a federated table on the
target machine (M2) which has the same table structure as the
table in M1.
On target machine (M2) you might want to try something like
below:
--------------------------------------------------------------
CREATE TABLE `fed_emp_details`(
`id` SERIAL PRIMARY KEY,
`name` VARCHAR(32) NOT NULL,
`dept` VARCHAR(32) NOT NULL
) ENGINE=FEDERATED
CONNECTION='mysql://someuser at M1:3306/mydb/employee_details';
--------------------------------------------------------------
You might want to modify the table structure and connection
details to suit your requirement.
Now running queries on the federated table on M2 will actually
show/affect the contents from table that is stored on M1.
Now, you can use a query similar to the one below on the
target machine (M2) to actually replicate the table:
--------------------------------------------------------------
CREATE TABLE `employee_details` ENGINE=MyISAM AS
SELECT * FROM `fed_emp_details`;
--------------------------------------------------------------
This method could be used only if you do not have the luxury
of running mysqldump on your shell prompt. Of course, this
technique is non-optimal, and might not work if your source
table is not using MyISAM storage engine. Some deployments
of MySQL server might also have FEDERATED engine disabled.
Nevertheless, it is worth noting that there is a possibility.
To know more about federated table usage, kindly lookup
the URL:
http://dev.mysql.com/doc/refman/5.0/en/federated-use.html
Cheers,
Chandrashekar.
--
http://www.chandrashekar.info/
http://www.slashprog.com/
Other related posts:
