I'm trying to setup a relatively short shell script called backup_extract.sh which will essentially unzip a zip file, drop a database, create a database of the same name, then import an sql file that was created from the zip file.
This zip file is created on a separate server, then transferred to this server every day - it's a simple database backup of a wordpress website & this script is intended to restore that database on the new server to serve as a 1:1 clone of the other website for backup purposes.
The problem I'm having is when I just bash run the script it works fine, but when I let cron take over nothing seems to happen.
Code looks a bit like this (obviously omitting mysql user & password, as well as full urls but I assure you i am using the exact path to the files as if they were from the root)
cd /var/www/html/backups/database
mkdir /var/www/html/backups/database/$(date +%Y%m%d)
unzip /var/www/html/backups/database/*.zip -d /var/www/html/backups/database/$(date +%Y%m%d)
mv /var/www/html/backups/database/*.zip /var/ww/html/backups/database/$(date +%Y%m%d)
cd /var/www/html/backups/database/$(date +%Y%m%d)/dup-installer
mv /var/www/html/backups/database/$(date +%Y%m%d)/dup-installer/*.sql /var/www/html/backups/database/$(date +%Y%m%d)/dup-installer/database_$(date +%Y%m%d).sql
mysql -u USERNAME -p'PASSWORD' database -e \ 'DROP DATABASE database';
mysql -u USERNAME -p'PASSWORD' -e \ 'CREATE DATABASE database';
mysql -u USERNAME -p'PASSWORD' database < /var/www/html/backups/database/$(date +%Y%m%d)/dup-installer/database_$(date +%Y%m%d).sql
echo "Backup completed" >> /var/www/html/backups/database/$(date +%Y%m%d)/backup-status.txt
Now this is my first time writing a script like this and I'm sure I'm doing some things wrong. But let me explain a few things about what this script is doing.
First I have no idea if the cd commands do anything at all when the crontab is involved, but they're there just in case. if i don't need them great.
2nd a directory is created using the current date
3rd a zip file is unzipped into the folder we just created - this is where it gets complicated. every day a new zip file will be moved to this folder, however the name is procedurally generated by a different server & there's no way to predict EXACTLY what the zip file's name will be so I need a way to unzip any zip file that exists in this folder - it will be moved once it is unzipped so there's no worry about unzipping several zip files at once.
4th the zip file is moved to the folder we created on line 2
5th we cd into a folder that was unzipped from the zip file
6th we rename any sql file in here (there will only ever be 1) to a name that is more predictable (same issue with this as there was with the zip file, the sql filename is unpredictable & therefore hard to lock down so I used wildcard to target any sql files in that folder.
7th we login to MySQL & drop the database
8th we login to MySQL & re-create the database with the same name as the one we just deleted
9th we login to MySQL & Import the sql file we renamed earlier
10th we create an arbitrary text file that just says backup completed
I've tested the shell script on its own using bash -x several times & it seems to be working fine. there are a few issues like it doesn't actually seem to care what folder the .zip file is in & wants to unzip EVERY zip file on the server which isn't good, but the core issue is that crontab isn't working. my crontab line is this:
00 00 * * * /var/www/html/(actual filepath)/backups/backup_extract.sh
I've been changing the times while testing, but generally that's what it looks.
When I check/var/log/cron it acts like it has run the script, but in actuality it has done nothing.
I've even tried creating a simple crontest.sh file that just creates a crontest.txt file & tested that both using bash & using cron - bash works fine but cron does not.
00 00 * * * /var/www/html/(actual filepath)/backups/backup_extract.sh 2>&1 >> /path/to/log/file.txt