A Little Background
I have two computers running MySQL database server software. One on my development laptop and one on my web server. When I have made changes to my development database, I need the changes to be replicated onto the web server. I leave this task to an automated cron script that runs at night whilst I’m asleep.
So that the first script does not need to supply passwords to copy the backup files to the remote web server or execute the remote script, I use a Secure Shell (ssh) tunnel between the two computers with a public/private key setup (You can find details on how to setup a password-less ssh public/private key by searching the all knowing oracle that is Google).
The Scripts
Here is the script that runs on my laptop…
#!/bin/bash
echo “+————————————————————————+”
echo “| Taking backups of local databases… |”
echo “+————————————————————————+”
echo “Backing up…”
mysqldump -h localhost -u username -ppassword databaseName > ~/mysqlbackups/databaseName.sql
echo “+————————————————————————+”
echo “| Copying Database backups to desktop computer |”
echo “+————————————————————————+”
echo “Copying backup file to server…”
scp ~/mysqlbackups/*.sql username@servername:/home/user/mysqlbackups/
echo “+————————————————————————+”
echo “| Applying the MySQL backups to the remote Database |”
echo “+————————————————————————+”
echo “Applying backup file to server…”
ssh user@server /home/user/bin/mysql_apply
The first line of interest here, starting “mysqldump -h localhost…”, exports a complete copy of the specified database into a plain text file, in this case database.sql.
The second line, starting “scp ~/mysqlbackups…” copies the exported file to the remote server.
The last line “ssh user@sever…” executes the second script, this time on the remote web server…
#!/bin/bash
echo “+————————————————————————+”
echo “| Applying .sql backups to local databases… |”
echo “+————————————————————————+”
echo “Applying backup to server…”
echo “————————-“
mysql -h localhost -u username -ppassword databaseName < ~/mysqlbackups/databaseName.sql
This script uses the command line to apply the exported file to the web servers MySQL database.