Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
command for dumping multiple (not all) mysql tables ?
For command-line handling of dump files, I’ve looked at the reference manual and know I need to use…
mysqldump [options] db_name [tables]
but what’s the notation for separating multiple tables, comma? So like this…(?)
mysqldump [options] db_name table1,table2,table3
What would be better for a my issue at hand would be a command that says all tables except tableX. Is there a command for indicating what tables to not include so you don’t have to write out the names of the majority you do want?
Thanks in advance.
Offline
#2 2008-12-22 18:02:45
- net-carver
- Archived Plugin Author
- Registered: 2006-03-08
- Posts: 1,648
Re: command for dumping multiple (not all) mysql tables ?
Destry
Use the --ignore-table=table_name option to exclude a single table from the DB. Use the --ignore-table option multiple times if you want to exclude more than one table.
This option was introduced in a recent version of mysqldump but I can’t remember what it was. Give it a shot on your system and you’ll soon find out if it can do exclusions.
— Steve
Offline
Re: command for dumping multiple (not all) mysql tables ?
Sweet! Thanks Steve. I’ll give it a try. The table in question is a slimstat table and it’s like 98% of the size of the entire DB and why phpMyAdmin won’t work for the full file — ridiculous. This is a punBB install, BTW, and I’m struggling with a number of issues in this migration (custom hacks, severely outdated version, etc). * sigh *
Offline
#4 2008-12-23 20:22:43
- els
- Moderator
- From: The Netherlands
- Registered: 2004-06-06
- Posts: 7,458
Re: command for dumping multiple (not all) mysql tables ?
Destry wrote:
The table in question is a slimstat table and it’s like 98% of the size of the entire DB
Destry, have a look at this. I used it on all my slimstat databases and it really helps!
Offline
Re: command for dumping multiple (not all) mysql tables ?
OK, Steve’s tip worked good.
Now I have another situation where I need the opposite (my original question, I think). There’s a single DB with the tables of many systems installed into it. I need to export each system’s set of tables as a unique dump file for importing later into a new (discrete) DB.
What’s the easiest approach? Command-line? phpmyadmin? …
Offline
Re: command for dumping multiple (not all) mysql tables ?
Got this figured out after fighting some “can’t unlock tables” issues. Here’s what worked in case some poor sap ever needs to know.
I had a single database with the tables of several systems installed in it. I needed to parse the individual systems to their own dump files for creating isolated DBs later. There’s seemingly not an easier way to do this than to run a command like following for each system’s set of tables and list each table in the command. The --opt --skip-locked-tables was necessary in my case, perhaps not in yours.
mysqldump -u [db_username] -p[db_password] --opt --skip-lock-tables [db_name] [table_1of8 table_2of8 table_3of8 table_4of8 etc] > /path/to/dump_file.sqlI found it easiest to write all the long commands for each dump in a text file and then systematically copy/paste them into Terminal to run.
Offline