--- Dave Smith <[EMAIL PROTECTED]> wrote: > I need to copy a MySQL database fast. Let's pretend it's for > backup purposes, and the user is impatient. The database has > about 15,000,000 rows spread non-uniformly across 30 InnoDB > tables. I've tried mysqldump, like so: > > mysql -u root -e "create database new_db" dump -u root > mysqlold_db | mysql -u root new_db > > But it takes about an hour. > > Unfortunately, mysqlhotcopy is not an option either since it > only works with ISAM and MyISAM tables. > > Any other ideas? I need some speeeeeed.... > > --Dave
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. - Gary /* PLUG: http://plug.org, #utah on irc.freenode.net Unsubscribe: http://plug.org/mailman/options/plug Don't fear the penguin. */
