Copy mysql database to dev location

![db copy] ({filename}/static/images/db_copy.jpg)

If you are just here for the script copy the script and have fun. If you want a bit more info read on this script is tweakable to some extend.

#! /bin/bash
 
TIMESTAMP=$(date +"%F")
BACKUP_DIR="/tmp/db-dump/$TIMESTAMP"
MYSQL_USER="root"
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD="password"
MYSQLDUMP=/usr/bin/mysqldump

mkdir -p "$BACKUP_DIR"

databases="live_db"
for db in $databases; do
  test_db=$db"_test"
  $MYSQLDUMP --user=$MYSQL_USER -p$MYSQL_PASSWORD $db | gzip > "$BACKUP_DIR/$db.sql.gz"
  mysql --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "DROP DATABASE IF EXISTS $test_db;"
  mysql --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "CREATE DATABASE $test_db;"
  zcat "$BACKUP_DIR/$db.sql.gz" | mysql --user=$MYSQL_USER -p$MYSQL_PASSWORD $test_db
done

rm -rf "$BACKUP_DIR"

This script works single threaded and can take in multiple databases or even all databaeses and copy them to _test databases. If you want it to copy all databases to _test use this line for defining the databases var:

databases=$($MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)")

The biggest issue with this method is that it is slow and you may want to consider skipping disk and pass output of the dump straight into the import with the following:

  test_db=$db"_test"
  mysql --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "DROP DATABASE IF EXISTS $test_db;"
  mysql --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "CREATE DATABASE $test_db;"
  $MYSQLDUMP --user=$MYSQL_USER -p$MYSQL_PASSWORD $db | mysql --user=$MYSQL_USER -p$MYSQL_PASSWORD $test_db

There is a downside to this method, while you are copying the database your data in the destination location is longer unavailable. The database is dropped and then filled as it comes in from the dump instead of being parsed at max read speed of disk or max execute speed of mysql.

The last method is realy good for a seperate dev mysql server but can fill up your ram if the dev mysql server can not keep up with the dump speed.