0

I have script which backs up mysql database into aws. I need to add a code to the script which deletes the databases after backup is successful onto aws. Can someone guide me with the code. Here is the script

#!/bin/bash

# Basic variables
mysqlpass="mysql"
bucket="s3://akshaysolutionbackup/orbit/"

# Timestamp (sortable AND readable)
stamp=`date +"%d-%m-%Y"`

# List all the databases
databases=`mysql -u root -pmysql -e "SHOW DATABASES;" | tr -d "| " | grep -v "\(Database\|information_schema\|performance_schema\)"`

# Feedback
echo -e "Dumping to \e[1;32m$bucket/$stamp/\e[00m"

# Loop the databases
for db in $databases; do

# Define our filenames
filename="$stamp - $db.sql.gz"
tmpfile="/tmp/$filename"
object="$bucket/$stamp/$filename"

# Feedback
echo -e "\e[1;34m$db\e[00m"

# Dump and zip
echo -e "  creating \e[0;35m$tmpfile\e[00m"
mysqldump -u root -pmysql --force --opt --databases "$db" | gzip -c > $tmpfile"

# Upload
echo -e "  uploading..."
s3cmd put "$tmpfile" "$object"

# Delete
rm -f "$tmpfile"
done;

# Jobs a goodun
echo -e "\e[1;32mJobs a goodun\e[00m"
  • Kindly excuse for typing the entire script as i could't get the link working for the script – darshan krishnaiah Jul 28 '16 at 11:02
  • The link to the script is here gist.github.com/oodavid/2206527#file-s3mysqlbackup-sh – darshan krishnaiah Jul 28 '16 at 11:06
  • 1
    You could just do something like echo "drop database $db" | mysql -u root -pmysql. Is that what you want? – Jos Jul 28 '16 at 11:33
  • I want that to be done by the script itself, once all the backup is successful. Where to add the code? I will be adding the script into crontab. – darshan krishnaiah Jul 28 '16 at 11:37
  • 1
    You can add the command as I gave it to the script, before the done; or you could do another loop, like: for db in databases; do echo "drop database $db" | mysql -u root -pmysql done at the end of the script. – Jos Jul 28 '16 at 11:41
1

The easiest way to delete a database from within a script is:

echo "drop database dbname" | mysql -u username -ppassword

This will start up the mysql command line client and execute the SQL statement that was piped into it by the echo command. In your script, you can either delete the database as soon as it has been backed up:

echo "drop database $db" | mysql -u username -ppassword

(within the do/done loop), or wait until the end of the script, when the whole backup is complete, and then do another loop through the databases and drop all of them:

for db in databases; do
  echo "drop database $db" | mysql -u username -ppassword
done;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.