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.<\/p>\n
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.<\/p>\n
Anyhow, here it is for anyone’s use:<\/p>\n
[code lang=”Bash”]
\n#!\/bin\/bash<\/p>\n
#—————————————–#
\n# Ammon Shepherd #
\n# 09.05.07 #
\n# Dump a database with only the tables #
\n# containing the prefix given. #
\n#—————————————–#<\/p>\n
echo “This will dump just the tables with the specified prefix from the specified database.”<\/p>\n
echo -n “Enter the database name: ”
\nread dbase<\/p>\n
echo -n “Enter the table prefix: ”
\nread prefix<\/p>\n
echo -n “The mysql user: ”
\nread sqluser
\necho -n “The mysql pass: ”
\nread -s sqlpass<\/p>\n
# Get list of tables with the desired prefix
\nlist=( $(mysql -u$sqluser -p$sqlpass $dbase –raw –silent –silent –execute=”SHOW TABLES;”) )<\/p>\n
for tablename in ${list[@]}
\ndo
\n if [[ “$tablename” =~ $prefix ]]; then
\n tablelist+=”$tablename ”
\n fi
\ndone<\/p>\n
`mysqldump -u$sqluser -p$sqlpass –opt $dbase $tablelist > $dbase.$prefix.bak.sql`<\/p>\n
echo<\/p>\n
exit 0<\/p>\n
[\/code]<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading Tabledump<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[14],"tags":[23,63],"class_list":["post-83","post","type-post","status-publish","format-standard","hentry","category-computer-history","tag-bash-code","tag-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9wosP-1l","_links":{"self":[{"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/posts\/83"}],"collection":[{"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/comments?post=83"}],"version-history":[{"count":0,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/posts\/83\/revisions"}],"wp:attachment":[{"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/media?parent=83"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/categories?post=83"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mossiso.com\/wp-json\/wp\/v2\/tags?post=83"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}