I looked at that type of idea, and the reason I didn't mention it was the indexes -- each table involved has ~5 indexes, and on a large table, that equals a lot of work that's already done on 'stage' that must be redone on 'prod'. I'd really like to preserve the indexes from stage if at all possible. Maybe there's a way to dump and reload the indexes as well as the data..
Thanks for the help, and I hope you don't get fired.. I won't tell anyone ;) Ross On Tue, 2003-06-03 at 16:53, Andrew Braithwaite wrote: > Hi, > > Assuming that this system runs on *nix and that "prod" is set up to > replicate to all the "replicas" you could write a small bash script to push > the data from the "stage" to the "prod" which would then replicate as > normal. > > I would author the script something like this... > > [bash]# mysqldump -e -hstage -usomeuser somedatabasename | mysql -hprod > -usomeuser > > This would dump the entire "somedatabasename" to the "prod" server which > would then replicate it to all the slaves using the -e option for faster > inserts: > > If you wanted to overwrite the existing data then use the --add-drop-table > option to mysqldump like this: > > mysqldump --add-drop-table -e -hstage -usomeuser somedatabasename | mysql > -hprod -usomeuser > > This will then replicate as fast as your network/hardware will allow. > > Hope this helps.. (I'm all for open source but it's a bit weird that I'm > helping out our state-side mapping competitors here - at least it's not > microsoft - I'll check tomorrow to make sure I don't get sacked :) > > Cheers, > > Andrew > multimap.com > > > -----Original Message----- > From: Ross Simpson [mailto:[EMAIL PROTECTED] > Sent: Tuesday 03 June 2003 22:44 > To: Dathan Vance Pattishall > Cc: [EMAIL PROTECTED] > Subject: RE: table copying/replication > > > Thanks for the reply. > > I didn't explain properly :) > > A diagram should help: > > |--------| > |-----| 1 |----| 2 | |--------| > |stage| --> |prod| --> |-| |--------| > |-----| |----| |-|replicas| > |--------| > > 'stage' is a staging mysql instance, where changes are made all the time. > When the data is ready for production, it needs to be pushed to 'prod', at > which time it will be replicated out to all the slaves. > > Step 2 is covered by the answer to my previous question. > > Step 1 is really my question. My need is that somehow a table already > existing on stage can be copied/replicated/etc over to prod, but _only_ when > requested, and then immediately. > > Any thoughts? > > Thanks, > Ross > > > On Tue, 2003-06-03 at 12:07, Dathan Vance Pattishall wrote: > > -->-----Original Message----- > > -->From: Ross Simpson [mailto:[EMAIL PROTECTED] > > -->Sent: Tuesday, June 03, 2003 10:31 AM > > -->To: [EMAIL PROTECTED] > > -->Subject: table copying/replication > > --> > > -->Hello, > > --> > > -->I have a need for fast copying of a specific table from a master > > mysql > > -->server to a number of slave servers (say 5). > > Create the table on the master and if the master and slave config is > > working then the same table will be on the slave. > > > > > > --> > > -->The database in question could potentially have up to 2000 tables, > > and > > -->at any time, one of those tables would need to be copied to all 5 > > -->of > > the > > -->slaves, upon command of the master. > > Make sure you have enough inodes that's 6000 files that will be > > opened. Also set your ulimit high enough to open all the files. > > > > Replication will perform the same action on the slaves as initiated by > > the master. There is no need for a copy. > > > > -->I also looked at doing table copies (insert into .. select * from > > ..), > > -->but these seem pretty slow. > > It's building the index on the fly as well, if there are indexes on > > the dst table. It does have to scan the src table and for every row > > insert it into the dst table. You can tweak you're my.cnf values to > > make that operation happen faster. > > > > --> > > -->Has anyone solved this problem before? Any ideas that would help > > out? > > --> > > > > Yes, the mysql team with replication. > > > > --> > > -->-- > > -->Ross Simpson <[EMAIL PROTECTED]> > > -->MapQuest.com > > --> > > --> > > -->-- > > -->MySQL General Mailing List > > -->For list archives: http://lists.mysql.com/mysql > > -->To unsubscribe: > > -->http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > Ross Simpson <[EMAIL PROTECTED]> > MapQuest.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Ross Simpson <[EMAIL PROTECTED]> MapQuest.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
