Mac OS X (10.6.8), CentOS, Linux
Do you perform regular database backups? Maybe you take copies of your live database and push that data somewhere else (like, your staging site)? And maybe you’ve accidentally overwritten your live database coz you typed something wrong?
Yeah, well I have. And after the second time, I decided to write a little bash script to prevent it happening again! Typically, you would run this script on your local machine.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | #!/bin/bash # check for empty variables echo "Choose a host to dump from: " select host in local staging live do echo "Using" $host break; done if [ -z "$1" ] then echo "Database name: " read dbname fi # variables date=$( date +%Y-%m-%d_%H%M ) backupPath="/Users/jason/Sites/db_backups" # create dir if not found [ ! -d "$backupPath" ] && mkdir -p $backupPath # which host are we going to [ $host == "local" ] && server="127.0.0.1" && dbuser="root" && dbpass="root" [ $host == "staging" ] && server="192.168.0.1" && dbuser="jason" && dbpass="password" [ $host == "live" ] && server="192.168.0.2" && dbuser="jason" && dbpass="password" # dump echo "starting..." sudo mysqldump -h ${server} -u ${dbuser} -p${dbpass} -B ${dbname} --lock-tables=false > ${backupPath}/${host}_db_${date}.sql echo "Success. Created" ${backupPath}/${host}_db_${date}.sql |
That will grab the database from your host and create a backup on your local machine. It needs your own configuration variables (host names, IP addresses, etc), but the rough outline is there.
Now, what to do if we need to upload this to another server? If only someone wrote a handy little script…
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | #!/bin/bash # check for empty variables echo "Choose a host to upload to: " select host in local staging do echo "Using" $host break; done if [ -z "$1" ] then echo "Database name: " read dbname fi echo "Choose a file to upload: " select db in $(ls /Users/jason/Sites/db_backups); do echo "Uploading" $db break; done # which host are we going to [ $host == "local" ] && server="127.0.0.1" && dbuser="root" && dbpass="root" [ $host == "staging" ] && server="192.168.0.1" && dbuser="jason" && dbpass="password" # confirm message echo "Upload" ${db} "to" ${server} "... Are you sure?" select confirm in Yes No do echo $confirm break; done # upload if [ $confirm == "Yes" ] then echo "starting..." sudo mysql -h ${server} -u ${dbuser} -p${dbpass} -B ${dbname} < /Users/jason/Sites/db_backups${db} echo "Success! Database uploaded to" ${host} fi |
Create or install these scripts wherever you like. Then add a link to your PATH and you’re ready for some db-dumping mayhem!
Optional hint: Add Gzip for compression on download if you like by piping (|) the mysqldump into gzip before writing it to your local file.
Adding Gzip Compression
Change this line…
1 | sudo mysqldump -h ${server} -u ${dbuser} -p${dbpass} -B ${dbname} --lock-tables=false > ${backupPath}/${host}_db_${date}.sql |
To this…
1 | sudo mysqldump -h ${server} -u ${dbuser} -p${dbpass} -B ${dbname} --lock-tables=false | gzip > ${backupPath}/${host}_db_${date}.sql.gz |
And you are all set for some backups!