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

Posted on 2009-12-29
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
    LVL 24

    Expert Comment

    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

    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

    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.
    LVL 16

    Expert Comment

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

    Accepted Solution



    % 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

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now