asked on

How do I mysqldump a list of tables that start with PHPbb3_

I have a managed server with only one MySql database. So the only way to differentiate tables is to prefix them with the name of the application that is accessing them. For instance all of my PHPbb files are prefixed with phpbb3_.

How can I backup only the phpbb3_ tables without having to list each one. There are a bunch of them. I tried the wildcard approach and it didn't work.

This command:
mysqldump -h hostName -u userName -p databaseName phpbb3_* > backupFileName.sql

got this error:
mysqldump: Couldn't find table: "phpbb3_*"

Is there another way? Maybe piping a mysql command or something.

Any help appreciated.

Thanks,
Frank

mankowitz

Unfortunately, there is no wildcard option for mysqldump. The best option is to move all the phpbb tables into their own database, and then dump the whole database.

Another possibility is to trap the output of a command like this and insert it into the command line
%mysql -uxxx -pyyyy -s DATABASENAME -e"SHOW TABLES LIKE 'php%'"

What platform are you using? php/perl or another scripting language may be useful here.

ibanja

ASKER

I'm on a linux server. It is managed and only has one database.

I was thinking of something along the lines of what you mention.

This worked:

$ mysql -h hostName -u username -p dataBase -e "show tables like'phpbb3%'" > tablesList

followed by:

$ mysqldump -h hostName -u userName -p dataBase $(grep phpbb3_ tablesList) > backupFileName.sql

I'll turn it into a script.

Thanks

Jon Brelie

In addition to Mankowitz's suggestion, you could push the list of tables into a file and use xargs to run the operation on each table.
IE:

less tablelist | xargs -I {} mysqldump -h hostName -u userName -p databaseName {} >> backupFileName.sql

Make sure you append, or you'd only have the last table.

Jon Brelie

rats... you beat me to it. Either way works.

ASKER CERTIFIED SOLUTION

mankowitz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ibanja

ASKER

That's nice and clean. Thanks mankowitz.

Thanks to Enphyniti as well. I went with mankowitz since he got there first and presented a oneliner.

One thing to note, Enphyniti's oneliner meant having to key in the password for each iteration--in this case 62. I know I can use the --password= switch, but I don't like leaving passwords in the command history.

Thanks