MySQL db exporter script

Public

This script creates an export from the database. The unnecessary data is removed, so search, cache etc tables are exported without data. The exported file is zipped with gzip and the prefixed with the current date.

Get raw version
bash
  1. #! /bin/bash
  2.  
  3. user="";
  4. pass="";
  5. db="";
  6. host="localhost";
  7. filepath="./";
  8.  
  9. # Store tables
  10. filename=$filepath`date "+%y%m%d_%H%M"`_$db.sql;
  11. datatables=`mysql -ND -h$host -u$user -p$pass $db -e "SHOW TABLES" | egrep -v '(^search.*)|(^cache.*)|^(accesslog|cache|sessions|watchdog)$'`;
  12. cachetables=`mysql -ND -h$host -u$user -p$pass $db -e "SHOW TABLES" | egrep '(^search.*)|(^cache.*)|^(accesslog|cache|sessions|watchdog)$'`;
  13.  
  14. # Create structure and data dumps
  15. echo "Export database...";
  16. mysqldump -h$host -u$user -p$pass --no-data --add-drop-table -e $db $cachetables > $filename;
  17. mysqldump -h$host -u$user -p$pass --add-drop-table -e $db $datatables >> $filename;
  18. # Craete archive
  19. gzip $filename;
  20.  
  21. echo "The database is exported to $filename.gz";
  22. exit 0;