mysql

Tabledump

Wednesday, September 5th, 2007 | computer history | No Comments

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:

  1. #!/bin/bash
  2.  
  3. #—————————————–#
  4. #           Ammon Shepherd                #
  5. #              09.05.07                   #
  6. #  Dump a database with only the tables   #
  7. #   containing the prefix given.          #
  8. #—————————————–#
  9.  
  10. #This is the list of tables in a wordpress install version 2.0
  11. #tablearray=( categories comments linkcategories links options post2cat postmeta posts usermeta users )
  12.  
  13. #THis is the list of tables for wordpress version 1.5.1.2
  14. #tablearray=( categories comments linkcategories links options post2cat postmeta posts users )
  15.  
  16. # This is the array of tables for wordpress version 2.2.2
  17. tablearray=( categories comments link2cat links options post2cat postmeta posts usermeta users )
  18.  
  19. # This array is for wikimedia tables version 1.5.1
  20. #tablearray=( archive categorylinks hitcounter image imagelinks interwiki ipblocks logging math objectcache oldimage page pagelinks querycache recentchanges revision searchindex site_stats text trackbacks user user_groups user_newtalk validate watchlist )
  21.  
  22. #tablearray=( mw_archive mw_blobs mw_brokenlinks mw_categorylinks mw_cur mw_hitcounter mw_image mw_imagelinks mw_interwiki mw_ipblocks mw_links mw_linkscc mw_logging mw_math mw_objectcache mw_old mw_oldimage mw_querycache mw_recentchanges mw_searchindex mw_site_stats mw_user mw_user_newtalk mw_user_rights mw_validate mw_watchlist )
  23.  
  24. echo “This will dump just the tables with the specified prefix from the specified database.”
  25.  
  26. echo -n “Enter the database: “
  27. read dbase
  28.  
  29. echo -n “Enter the table prefix: “
  30. read prefix
  31.  
  32. echo -n “The mysql user: “
  33. read user
  34.  
  35. for tablename in ${tablearray[@]}
  36. do
  37.     tablelist+=“$prefix$tablename “
  38. done
  39.  
  40. `mysqldump -u $user -p –opt $dbase $tablelist > $dbase.$prefix.bak.sql`
  41.  
  42. exit 0

Popularity: 8% [?]

Tags: ,

Search

Categories