--- Gary Thornock <[EMAIL PROTECTED]> wrote: > I have a quick-and-dirty perl script for this. It's a bit > more than I want to try to paste in an email, though. You > can grab it at > > http://thornock.us/files/mysqlcopy.perl.bz2 > > if you're interested. > > Basically it just iterates through the tables in the old > database, creates tables with the same names and structure > in the new database and does INSERT INTO ... SELECT. > > One caveat: CREATE TABLE ... LIKE won't copy foreign key > constraints. If you have any of those, you'll have to > recreate them yourself.
If you were interested in improving upon the script, I can think offhand of a couple of things that would makt it better. (Remember, this is "quick and dirty". There's plenty of room for improvement before it's "good".) - An option to do the copy in a single transaction, or to lock the source tables before copying, would help to ensure that the destination data is consistent even if there are new writes happening on the source while you copy. - You may get some performance improvement if you disable keys on the destination table before doing the inserts and enable them again afterward. That can still take a Long Time (I've seen tables that take hours just to re-enable the keys), but it may still be faster than doing mass inserts with the keys enabled. Because of the way the script copies the data, it's not possible to make it work across two servers. But we have mysqldump for that :) - Gary PGP Key ID: 071B173D Fingerprint: ED30 B048 6833 56B4 28C0 CE52 F12B 884A 071B 173D /* PLUG: http://plug.org, #utah on irc.freenode.net Unsubscribe: http://plug.org/mailman/options/plug Don't fear the penguin. */
