Drupal backup and reinstall shell script

I've been playing around with creating a kind of all-in-one, "loaded for bear" Drupal 10 configuration, with a coherent set of compatible and cooperative modules. My goal is to make deployment and expansion simple regardless of your goals. Some of my goals include support for ECA with BPMN, some kind of hierarchical permissions assignment system, geolocation and mapping, CRM, and easy reporting. I have all but the latter of these accomplished, although I have identified a good collection of modules which do help.

In the process, I've needed to refresh my test installation a number of times, as numerous modules have been updated and so on. I've added and removed whole families of modules for testing, and in the process I came up with a fairly simple flow of operations:

Back up all of the files with tar and pigz
Back up the database with mysqldump (And pigz)
Wipe the tables from the database
Grant write permissions to the config file and directory
Remove all of the files
Copy a new private directory with .htaccess from a skel directory
Run composer i in order to reinstall whatever is currently in composer.json

What wasn't deleted besides composer.json and composer.lockIt's really a combination lock file and cache file, I propose it should have been "composer-state". I don't back it up since it's a) large, b) regenerated when you reinstall, c) I'm not deleting it, and d) I'm backing up everything that was installed from it, and e) I'd rather have newer versions if they are available after restoring my backup anyway. was a scripts directory I originally made to hold one script, which removed some unneeded files installed with charts.js. If I wanted to return to a specific composer.json, I could copy it to skel. Anywho, why not add some more scripts there to make this easier? The script itself has some hardcoded values in it so it's not really ready for prime time, but I did want to save myself some notes here about what I'd done in case it went missing at some point, so here is reinstall.sh anyway:

BACKUP="composer.json libraries patches scripts skel vendor web"
COMPRESS="pigz"
DATE=`date +"%Y%m%d"`

if [ "x`basename $0`x" == "xreinstall.shx" ]; then
        PREFIX="reinstall-"
fi

# back up files
tar -cf - $BACKUP | $COMPRESS > "backups/${PREFIX}drupal-${DATE}.tar.gz"

# back up database
mysqldump drupal | $COMPRESS > "backups/${PREFIX}drupal-${DATE}.sql.gz"

if [ "x`basename $0`x" == "xbackup.shx" ]; then
        exit 0
fi

# clear all tables from database
mysql -sr < scripts/droptables.sql | mysql drupal

# remove files
chmod +w web/sites/default/
chmod +w web/sites/default/settings.php
rm -rf libraries/ private/ vendor/ web/

# reinstall from composer.json
cp -R skel/* .
composer i

# launch browser
xdg-open "http://drupal/"

As you might have figured out, it's also linked to backup.sh, and when run that way it just backs up the files. No sense in having two scripts, right?

www-data:~/sites/drupal$ ls -l scripts/
total 21
lrwxrwxrwx 1 www-data www-data   12 May  5 13:41 backup.sh -> reinstall.sh
-rwxr-xr-x 1 www-data www-data 1312 Apr 22 17:10 clean-chartjs.sh
-rw-r--r-- 1 www-data www-data  120 May  4 16:28 droptables.sql
-rw-r--r-- 1 www-data www-data  158 May  4 15:40 grantaccess.sql
-rwxr-xr-x 1 www-data www-data 1150 May  5 13:45 reinstall.sh

You will also need droptables.sql:

SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'drupal';

And you might benefit from grantaccess.sql:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
CREATE TEMPORARY TABLES ON drupal.*
TO 'drupal'@'localhost' IDENTIFIED BY 'Your Database Password';

You will only need to use that once though (using e.g. mysql drupal < scripts/grantaccess.sql) because we are deleting the database tables instead of dropping and recreating the database. You could run it every time, it wouldn't hurt anything.

Here is what I had to do in order to grant passwordless access to my apache user (www-data) to my drupal database, which I did as the user mysql:

grant all privileges on drupal.* to 'www-data'@'localhost'
IDENTIFIED VIA mysql_native_password
USING 'invalid' OR unix_socket WITH GRANT OPTION;

I have been creating a separate database for each drupal instance, but I am also using a database prefix (just drupal again) for two reasons. One, Views Database Connector (which lets you use Drupal as an interface to a non-Drupal database) cannot access tables with the same names as Drupal tables, and I might want to use it to view some tables in a foreign Drupal database; And two, I might want to make sub-sites on the same install, and put them in the same database.

Using the options -s and -r to mysql suppresses the border around the results. I should perhaps echo, printf, or otherwise produce the SQL statements from inside of the script, rather than using separate files, but this way they are also available for other uses. In actual use you should not leave your database password lying around like this, but this is on my development machine and it's not hurting anybody. This is obviously mysql-specific, so if you're using anything but MySQL or MariaDB then it will require some further refinement. So many Drupal modules work best (or only!) with MySQL, I gave up on using anything else.