Cloning big databases - streamlining the process?

I'm copying the latest incarnation of my database onto my test platform so that I can run a test upgrade to the newest vB. The mysql import of the data has been running for almost 2 hours and it's still working on the post table. (ugh!)

Is there a better/easier way to go about this? I'd LOVE to just drop all the posts and copy everything else over, since I really don't need a copy of 1 million posts on my test platform. And if I hose this DB (which I very well might, since I'll be fiddling around) doing it all over again would really not make my day.

[EricGT]

For something like that, I'll usually turn off the primary database long enough to rsync the database binaries to the new location. It is much faster. Once a copy of the binaries have been made, you can overwrite the test DB over and over, as needed, and do so very quickly. If you are doing a DB dump, don't include the searchindex/postindex table and the attachments table, if you are storing attachments in the database. You probably won't need this info for an upgrade attempt. When I was still storing attachments in the DB, these two tables alone accounted for about 61% of my total DB size. Eric

I do everything from command line, so when I dump my DB it's something like mysqldump (mydb) > dump.sql. Is there a way to skip the postindex/searchindex tables via command line? Or are you doing it with phpMyAdmin?

[high]* Guest210212002 is intrigued
[/high]

I'm not moving servers, just making a clone of my current site on the same box under a private subdomain so that I can run the 3.7 installer on it and see what breaks.

[EricGT]

Originally Posted by Chris-777

I do everything from command line, so when I dump my DB it's something like mysqldump (mydb) > dump.sql. Is there a way to skip the postindex/searchindex tables via command line? Or are you doing it with phpMyAdmin?

[high]* Chris-777 is intrigued
[/high]

I'm not moving servers, just making a clone of my current site on the same box under a private subdomain so that I can run the 3.7 installer on it and see what breaks.

Hello. You can use: '--ignore-table=name' to ignore a table in a DB dump. If you want to ignore multiple tables, you would use the directive multiple times. Run 'man mysqldump' or 'mysqldump --help' for more information. Eric

[EricGT]

Originally Posted by EricGT

Hello. You can use: '--ignore-table=name' to ignore a table in a DB dump. If you want to ignore multiple tables, you would use the directive multiple times. Run 'man mysqldump' or 'mysqldump --help' for more information. Eric

Sorry, the syntax is actually: ' --ignore-table=database.name '. I forgot to put the DB name in there. I was just backing up my database and realized I had forgotten that. Eric