mysqldump with wildcard table matching

Ever wanted to use mysqldump to dump tables that match a wildcard pattern? I have. It’s not currently supported as an option but can be achieved with a little bash magic. Here’s how:

#!/bin/bash
if [ $# -lt 2 ]
then
    echo "Usage: `basename $0` database wildcardpattern"
    echo "Eg: `basename $0` mydatabase App_%"
    exit 1
fi
database=$1
pattern=$2
mysqldump $database `mysql -ND $database -e "SHOW TABLES LIKE '$pattern'" | awk '{printf $1" "}'`

This uses a simple SQL query to extract all the table names that match the pattern and concatenate them in the format that mysqldump expects. Note that you’ll need your ~/.my.cnf set up correctly to allow the connections to MySQL to happen without a authentication prompt.

——————

Something wrong? Suggest an improvement or add a comment (see article history)
Tagged with: mysql, commandlinefu
Filed in: tips

Previous: How to sync a MySQL table between two remote databases.
Next: nurl - an immutable URL object for node.js

Copyright © 2005-2023 David Winterbottom
Content licensed under CC BY-NC-SA 4.0.