The fine fine art of command line programming keeps suprising me, it’s brilliant what you can acomplish with a few lines of commands all mixed up in grep and other linux commands.I’m working on a huge wordpress mu project and have run into problems with upgrading from version 1.1.1 to the latest version (currently 2.7), so now I decided to move some of the data to my laptop and try to recreate the problems. But I do not want to move all ~20 GB data since I only need a few blogs for testing.
I googled the task and found a mix of options, after a few minutes of testing I came up with this lovely little commandline:
mysql databasename -u [root] -p[password] -e 'show tables like "wp_153_%"' | grep -v Tables_in | xargs mysqldump [databasename] -u [root] -p[password] > [target_file]
Beauty full isn’t it :)
It will grap all tables containing “wp_153_*” and dump this into your target file.
Awesome man! Indeed, it’s beatiful.
This was really beautiful! This saved my day. Thank you.
Pingback: Några tips för att flytta och byta url på en Wordpress-installationU - Swedmedia Utveckling | Internet, trender & analyser
Hi
Can u please tell me how do i restore selected tables only from a mysqldump file?
Regards
Manoj Kumar V
Hi, have you tried using the source command within the command line?
As far as I know mysqldump supports multiple tables dump like so:
mysqldump -u{user} -p {database} {table1} {table2} {table3} > destination
Way easier ,no ?
Not easier, what if there are dozens or hundreds of tables under a specific prefix. Manually typing them would increase the likelihood of missing one or repeatedly fixing spelling mistakes.
Good in connection with PHP !
You fix the errors and try again until no errors found.
xargs is my favorite
Thanks! Exactly what I was looking for.
Thanks a lot. I am new to sql , please explain the syntax
That’s cool, thanks
mysql databasename -u [root] -p[password] —disable-column-names -e ‘show tables like “wp_153_%”‘ | xargs mysqldump [databasename] -u [root] -p[password] > [target_file]
if use “—disable-column-names” – “grep -v Tables_in” is not needed
im getting the “xargs: mysqldump: No such file or directory” error. Here is my code…
./mysql sfc_current -u root -p -e ‘show tables like “civicrm%”‘ | grep -v Tables_in | xargs mysqldump sfc_current -u root -p > /Users/work/Desktop/civicrm_dump1.sql
nice site, spread the truth about the Olympic 2012 false-flag terror attacks due to take place in london because US/UK/EU/Isreal is running out of excuses to attack Iran/Syria. Thanks mate. Appreciate it =)
You are awesome!
How to take selected column in a table using Mysql
For reference, this may be help someone:
The “-B” or “–batch” option will force the output to be TAB delimited no matter where the output is going. The “-N” option will turn off column names in the output.
via noah.org
I really had been researching for tips for my personal blog site and came across
your own posting, “Mysqldump selected tables | Kristian Nissen”,
will you mind in cases where I make use of some of your ideas?
I am grateful -Tiffani