mysqldump - select table if it contains a specific string

Question

I have a pretty big local WordPress Multisite installation, and this is mirrored on a live server, so I can develop a site locally and upload it for a client to see (it's sub-domain based). I have written a custom shell script to upload the database from my local machine to the live server, which uses sed to change all instances of the local domain name to the live domain name. It then commits and pushes it by git and the live server automatically pulls it, takes a backup and applies the new file.

However, this has become problematic in the last month or so. As I've got more client work, and they have been editing their sites live on the server, and I've been pushing new work to the server for other clients to take a look at, I've been overwriting changes on the live database.

What I need is to be able to add a flag or something when I call the shell script file (sh push.sh) that indicates what tables in the database need to be pushed live. Each site in the WordPress Multisite database has a number, e.g network_17_posts. So if I could a table number state when I called my shell script, like sh push.sh --table=17 and it would only upload site 17's data, and not overwrite anything else, that would be awesome. As a bonus, if I could specify multiple numbers, so I could upload multiple sites at a time, that would be amazing!

As a reference, here is my current shell script for pushing the database live (it could probably be 10x better, but I'm primarily a front-end/PHP dev, not shell script!):

rm -rf db_sync.sql
mysqldump -u root -ppassword db_name > db_sync.sql  
sed 's/localdomain.dev/livedomain.com/g' db_sync.sql > new_db_sync.sql
rm -rf db_sync.sql
mv new_db_sync.sql db_sync.sql
git add db_sync.sql
read -p "Enter Commit Message: " commit_message
git commit -m "$commit_message"
git push -u web master

Answer 1

See dumpting a table using mysqldump. Assuming you have the table names, or a list of table names, you could either dump them all with one command or loop:

: > db_sync.sql
for table in tableNames; do
    mysqldump -u root -ppassword db_name table >> db_sync.sql
done

To get the table names:

number=17
echo "select TABLE_NAME from information_schema.tables where TABLE_NAME like '%\_$number\_%' and TABLE_SCHEMA = 'databasename'" | \
    mysql -udbuser -pdbpassword databasename

So, if you have multiple numbers, e.g. as command line arguments, you can put this all together:

sql="select TABLE_NAME from information_schema.tables where TABLE_NAME like '%\_$number\_%' and TABLE_SCHEMA = 'databasename'"
# truncate/create the dump file
: > db_sync.sql
# loop through the command line arguments
for number in $@; do
    # for each tableName from the sql ...
    while read tableName; do
        # append to the dump file
        mysqldump -u root -ppassword db_name $tableName >> db_sync.sql
    done < <( echo $sql | mysql -udbuser -pdbpassword databasename | tail -n +2)
done

This will work if you put the numbers on the command line:

./push.sh 17 18 19

See the getopts tutorial for more information about processing command line arguments if you want to get complicated. Here is a short example of getting multiple arguments in an array:

numbers=()
while getopts ":t:" opt "$@"; do
    case "$opt" in
        t) numbers+=($OPTARG) ;;
    esac
done

for number in ${numbers[@]}; do
    echo $number
done

For example:

$ ./test.sh -t 17 -t 18 -t 19
17
18
19