UPDATE: See updated code on github: https://github.com/chnm/tabledump
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.
Here it is for anyone’s use. Let me know if it’s useful or if you have any changes or improvements.
Updated August 3, 2010
#!/bin/bash #-----------------------------------------# # Ammon Shepherd # # 08.03.10 # # Dump a database with only the tables # # containing the prefix given. # #-----------------------------------------# set -e # End the script if any statement returns a non-true return value set -u # End script if an unset variable is encountered. WD=`pwd` CURDATE=`date +%F` 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 echo echo "Here are the tables to be dumped: $tablelist" echo echo -n "Continue [y/n]:" read cont if [ "y" == $cont ]; then `mysqldump -u$sqluser -p$sqlpass --opt $dbase $tablelist > ${dbase}_${prefix}${CURDATE}_BACKUP.sql` echo -e "\nBackup file created at ${WD}/${dbase}_${prefix}${CURDATE}_BACKUP.sql" else exit 1 fi echo exit 0