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):
- Delete the COPY database if possible to start with a fresh one
- Create the COPY database
- Dump the PRODUCTION database
- 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:
If you want to do it on a daily basis, you can add it in your
crontab -e and write something like
# 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
ssh -C user@remotehost "mysql -u root -p -e 'create database ...... | ssh -C user@remotehost "mysql -u"