I have a daily task that collects data from 3 MySql Servers and then pushes that aggregate data back out to each of the servers deleting the previous days data. All servers are MySql. I would like to put this into a stored proc.
I have been searching for a way to connect from one MySql Server to another, run a query, next server – rinse and repeat. Besides not finding any examples of a stored procedure that connects to another MySql server, my immediate impression is that this is not really acceptable, nor intended.
I can select into an outfile which creates a CSV. Is there any way to force an Xml document instead? I don't want to have to read into a datatable just to get Xml. Of course, Xml won't work with the Bulkload method, will it?
What are my realistic options? One concern about selecting into an outfile is the use of weird characters: ' , \ \\ & @ `` / //
etc…how will those characters affect the Bulkload process?
Best Answer
I tend to shy away from the FEDERATED storage engine for three(3) reasons:
What to do ???
One possibility is to use mysqldump and merge them
EXAMPLE
Assuming all tables have identical structures
Assuming all tables are not using auto_increment IDs
Assuming all tables are InnoDB
On the server that will combine the three tables, create four(4) import databases (one time operation):
Import Process Goes Something Like This
mysqldump the three(3) tables
Load the mysqldumps into the separate import databases in parallel:
Next, combine the data.
Now, perform the task you already have in place against the ImportCombined.tbname table.
"You fill in these steps"
Then, when the table is ready to be exported, mysqldump it and copy 2 times:
As show from these steps, these things should be shell scripted and perhaps crontab'd.
Here is another maneuver: If the tables are MyISAM, you could copy the MyISAM tables (.frm, .MYD, and .MYI files for each table) in other folders (databases in the eyes of mysqld) instead of doing mysqldumps, and perform similar operations as mentioned before. Make sure no writes to the tables take place during the copy.