• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 797
  • Last Modified:

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
0
ibanja
Asked:
ibanja
  • 2
  • 2
  • 2
1 Solution
 
mankowitzCommented:
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.
0
 
ibanjaAuthor Commented:
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
0
 
Jon BrelieSystem ArchitectCommented:
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.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Jon BrelieSystem ArchitectCommented:
rats... you beat me to it.  Either way works.
0
 
mankowitzCommented:
great.

or

% mysqldump -h hostName -u userName -p databaseName `mysql -h hostName -u userName -p dataBaseName -e "show tables like'phpbb3%'" |grep phpbb` > backupFileName.sql
0
 
ibanjaAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now