Copy/clone/duplicate a mysql database script

TAGS:This is a simple script that duplicates your entire database. There are many ways in which you can take advantage of having an exact replica of your production database.

In short, the behaviour of the script is the following (in this order, all piped):

  1. Delete the COPY database if possible to start with a fresh one
  2. Create the COPY database
  3. Dump the PRODUCTION database
  4. Inject the output of the dump into the COPY database

Any errors during the process will be logged to the file defined as $ERROR. Be aware that the user you you choose to connect to mysql needs CREATE and DROP database permissions. Note that there are some --force parameters. That will make the script continue even if something fails. The first time you launch the script you'll see that cannot drop the copy database. 

Copy & paste the following into a new file ~/duplicate_database.sh, and modify the credentials: 

#!/bin/bash
# Albert Lombarte
# Docs: http://www.harecoded.com/copycloneduplicate-mysql-database-script-2184438

PRODUCTION_DB=myapp
# The following database will be DELETED first:
COPY_DB=myapp_copy
USER=root
PASS="yourP4ssw0rd"
ERROR=/tmp/duplicate_mysql_error.log
echo "Droping '$COPY_DB' and generating it from '$PRODUCTION_DB' dump"
mysql -u$USER -p$PASS -e "drop database $COPY_DB;" --force && mysql -u$USER -p$PASS -e "create database $COPY_DB;" && mysqldump --force --log-error=$ERROR -u$USER -p$PASS $PRODUCTION_DB | mysql -u$USER -p$PASS $COPY_DB

And launch it: 

bash ~/duplicate_database.sh

If you want to do it on a daily basis, you can add it in your crontab. Type crontab -e and write something like this: 

# At 7am clone the production database
7 0 * * * bash /home/myusername/duplicate_database.sh

If you want to make this copy in another host you have to add the SSH connection with ssh -C "command" in the first and last mysqlcommands

Like: 

ssh -C user@remotehost "mysql -u root -p -e 'create database ...... | ssh -C user@remotehost "mysql -u"