I had the need once again to dump only certain tables from a database, instead of all 100+ tables. This was where I had a database with about 5-8 wordpress installs. I wanted to backup all of the tables for only one install. There is a way with mysqldump to do this, by listing out all of the tables you want to dump. So I just wrote a bash script to take care of making the list of tables to dump.
It has an array of database table names (without the common prefix) in the script. Then it prompts for the mysql user, database, and prefix. It could be changed to prompt for a file that contains a list or array of table names.
Anyhow, here it is for anyone’s use:
[code lang=”Bash”]
#!/bin/bash
#—————————————–#
# Ammon Shepherd #
# 09.05.07 #
# Dump a database with only the tables #
# containing the prefix given. #
#—————————————–#
echo “This will dump just the tables with the specified prefix from the specified database.”
echo -n “Enter the database name: ”
read dbase
echo -n “Enter the table prefix: ”
read prefix
echo -n “The mysql user: ”
read sqluser
echo -n “The mysql pass: ”
read -s sqlpass
# Get list of tables with the desired prefix
list=( $(mysql -u$sqluser -p$sqlpass $dbase –raw –silent –silent –execute=”SHOW TABLES;”) )
for tablename in ${list[@]}
do
if [[ “$tablename” =~ $prefix ]]; then
tablelist+=”$tablename ”
fi
done
`mysqldump -u$sqluser -p$sqlpass –opt $dbase $tablelist > $dbase.$prefix.bak.sql`
echo
exit 0
[/code]
Updated the code to prompt for the table prefix, then the code finds those tables by itself. You no longer need an array specifying the tables to backup.