This method can be used to set up a time-based backup of a WordPress database on the server
In this example, we willl go over the steps to create an executable shell script that will create a daily backup of the database to a /daily_bu/ directory in our vhost and then save a log of these tasks to /public_html/bu_log.
All of this will be managed by a cron on our server that will automatically run this script daily. When the database files are created and how long they are stored on the server are flexible, in this case we are setting our cron to run daily, and store the backups for 14 days.
Step One, Build the Script.
1. Create a new directory in your project root named tasks. We will add all executable scripts in this folder.
mkdir ~/tasks;
2. Create your new script in this folder and open it in vim to edit it.
* Note: This file does not need a file extension, we will set the syntax with a shebang/hash-bang on the first line of the file.
#!/bin/bash
When a text file with a shebang is used as if it is an executable in a Unix-like operating system, the program loader mechanism parses the rest of the file's initial line as an interpreter directive. The loader executes the specified interpreter program, passing to it as an argument the path that was initially used when attempting to run the script, so that the program may use the file as input data.
vim ~/tasks/daily_bu
3. When your new file is opened in vim, Hit the (i) key to enter input mode then paste the following script:
#!/bin/bash
# Define naming convention for daily backup files
DBFILE="live-"$(date +%Y-%m-%d-%R:%S)".sql"
# Define Root directory of the WordPress installation
WPDIR=$HOME/public_html/
# Define Folder to store daily backups
DAILYBU=$HOME/public_html/daily_bu
# Define log of daily backup events
BU_LOG=$HOME/public_html/bu_log
# Create directory for daily backups if it does not already exist
if [[ ! -e $DAILYBU ]]; then
mkdir $DAILYBU
echo "Directory was created to store daily database backups" >> $BU_LOG
fi
echo "Creating backup of database Process began at:" $(date +%r) >> $BU_LOG
# cd into WordPress root directory so wp cli will work
cd $WPDIR
# use wp cli to create a database backup to daily backups directory
wp db export $DAILYBU"/"$DBFILE >> $BU_LOG
find $DAILYBU/* -mtime +14 -exec rm {} \;
echo "Backup Successfully Created /daily_bu/"$DBFILE " at:" $(date +%r) >> $BU_LOG
4. Save your new file by hitting the (esc) Key they type : wq and Hit (Enter) to write your changes and exit the vim editor.
5. Make your new file executable
chmod +x ~/tasks/daily_bu
6. You may review your file to verify that it is presend and is executable using the ls -la command:
ls -la ~/tasks/
Example output:
-rwxrwxr-x 1 skoulas skoulas 918 Aug 25 22:21 daily_bu
We can see this is now Executable because it contains the -x operator
-rwxrwxr-x <-- Here
Now we have created our script and it is executable. Next we need to test this out.
Setup Your Cron for daily automation
The crontab is a list of commands that you want to run on a regular schedule, and also the name of the command used to manage that list. Crontab stands for “cron table, ” because it uses the job scheduler cron to execute tasks; cron is the system process which will automatically perform tasks for you according to a set schedule. The schedule is called the crontab, which is also the name of the program used to edit that schedule.
Linux Crontab Format
MIN HOUR DOM MON DOW CMD
1. Lets use the crontab command to open the configuration for this user. This will open an editor and let us specify the crontab format and a script.
crontab -e
2. To add a new crontab entry type (i) to enter input mode, then paste this line:
55 23 * * * /var/www/vhosts/skoulasdds.com/tasks/daily_bu
Note: This line must use the full path to your exicutable script, int this example it is
3. After you are finished adding your entry, hit the (esc) key to exit input mode, then type :wq and then hit (enter) to write your change to the crontab and quit edit mode.
This Example set a cron task to run the daily_bu script daily at 11:55PM (23:55) server time.
4. Review the tasks that are currently in your crontab to verify your entry was saved to the crontab by running the crontab command with the -l flag
crontab -l
We Can Repeat this to schedule more autimated scripts in our crontab (1-per-line) as needed.