Daily DB export and local download

This allows to create a export script to save the current state of your database
-> This is schedule each day so you always have access to a recent backup of your database
-> This can be run manually: In the advent you want to diagnose an issue on the server you can export the current state, then download to your computer for local testing
-> The database is stored in a secure location on your server that is NOT accessible via your domain (only SSH connections can download)

# 1 > Navigate to your PRIVATE server location

We are creating scripts to export your database
-> This is added to our PRIVATE server location, meaning it is NOT accessible by the outside internet at all (only via control panel / SSH connections)
-> Navigate -> Control Panel -> File Manager -> Click on breadcrumbs (username of server) -> Private 
-> Click 'Create file'

# 2 > Create export script

Paste this into your script: IMPORTANT: EACH $...$ YOU ARE REPLACING WITH YOUR SERVER INFO, other info you can leave as is
eg $PASS$ will be replaced with 1234 if your password was 1234



do :

echo "Dump structure"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} --single-transaction --no-data ${DATABASE} > ${DB_FILE}

echo "Dump content"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} ${DATABASE} --no-create-info ${IGNORED_TABLES_STRING} >> ${DB_FILE}

# 3 > Create CRONJOB

We are now going to create a cronjob / crontab that will automatically run this script so each day you will have fresh information from your server

Control panel -> HOME
-> Click CRON JOBS
-> Click the icon on 'run sript...' right side
-> Navigate to 'private' -> and choose the file you specific in your script - select
-> Click the dropdown 'choose start time' to the frequency you want
-> click 'Set Cron'

# 4 > View CRON JOB

You will now see your cronjob setup and active and the time it will run
-> You can click 'RUN' and test and ensure you have setup everything correctly
-> EDIT, if you want to change the frequency
-> DELETE if you want to remove the cron and not run anymore

# 5 > Local - download into docker

Now we want to create a file so we can export our file from our server and save it into our local docker structure
-> After you create it, you can just right click on that file in PHP-STORM and choose RUN (without going into terminal)
-> you will need to manually delete any other db files you had in your docker or docker will NOT start 


#!/usr/bin/env bash
# Get the file that is located in the private folder on the server and copy to our sql directory with today's date
TODAY=$(date +"%Y%m%d")
sshpass -p "1234" rsync -av --progress $USERNAME$@$SERVERNAME$.com:/home/$USERNAME$/private/$FILENAME$.sql sql/$USERNAME$-$TODAY.sql