[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2009-12-29
Medium Priority
Last Modified: 2012-05-08
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.

Question by:ibanja
  • 2
  • 2
  • 2
LVL 24

Expert Comment

ID: 26140598
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.

Author Comment

ID: 26140879
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.

LVL 16

Expert Comment

by:Jon Brelie
ID: 26141019
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.

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.
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

LVL 16

Expert Comment

by:Jon Brelie
ID: 26141025
rats... you beat me to it.  Either way works.
LVL 24

Accepted Solution

mankowitz earned 2000 total points
ID: 26141443


% mysqldump -h hostName -u userName -p databaseName `mysql -h hostName -u userName -p dataBaseName -e "show tables like'phpbb3%'" |grep phpbb` > backupFileName.sql

Author Comment

ID: 26141729
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.


Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month19 days, 7 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question