Joomla database dump in linux terminal

There are many extensions for files and database backup (the most poular, as far I know, is Akeeba Backup). But, to keep a light environment and don't be exposed thorough eventual extensions vulnerabilities, advanced users may want to automate this task in terminal, doing dump extraction.

Basically, a dump is a SQL script that recreate all your tables and their data, being very much useful in environment migrations, restore points, etc. Execute it inan empty database to recreate the original one.

I will present some useful commands in this task and, at the end, a script for Joomla database dump, that reads autimatically the connection data from configuration.php.

Identifying database connection parameters

Edite configuration.php file and indetify the following lines:

public $host = 'pdevdb.mysql.hosting.com.br';
public $user = 'pdevjoomla';
public $password = 'mysecrectpassword';
public $db = 'pdevjoomladb';

This variables have the database connectgion data.

Extract values from configuration.php in terminal

We can extract variables values using the following command:

HOST=$(cat configuration.php | grep -e "\$host[ =]" | sed "s/[\ \t;\"']//g" | awk -F= '{ print $2; }');
echo $HOST;

Taking a dump from a database

Use mysqldump command, this way:

mysqldump -u USER -pPASSWORD -h HOST DATABASE > FILE.sql

There is no space between -p and PASSWORD.

Naming a file based on date and time

This may look unrelated to Joomla databases, but you may need in your automations:

TODAY=$(date +%Y%m%d)
NOW=$(date +%H%M%S) FILE="file_"$TODAY"_"$NOW".txt"

In this example, the resulting file would have the formato file_YYYYMMDD_HHMMSS.txt

Joomla database dump script: dumpj

I named this script dumpj. To use, save it in your hosting as dumpj.sh, and run using the command sh ./dumpj.sh. Edit CONFIGURATION variable (line 3) to point it to the correct file.

Will be created, at the same directory where the script was placed, a file called dump_DATABASE_DATE_TIME.sql, and a ZIP version (if the zip compressor is available).

Where to save dumpj.sh: do not use the published folders for that. Create a folder to store the dumps, outside the published folder, to avoid third person from executing yout script or download your dumps.

To create a password protected ZIP file, using the same database's password, and to delete the .sql file at the end, look for the word EXTRA in script code.

#!/bin/bash
CONFIGURATION="public_html/configuration.php"; # CHANGE HERE!
# ---------------------------------------------------------------
function help() {
cat <<EOF
dumpj: Takes a Joomla database dump and save in script's
Directory.
I recommend to keep this script and dumps in a top level directory,
outside published folders, to avoid access by browsers.
Database parameters are read automatically. To define configuration.php
location, edit this script and change the CONFIGURATION variable, at
the source code begin. The dump is extracted using the mysqldump command, that must be
installed.
If zip package is installed, the dump can be compressed at the end of
the process. This step is highly recommended, because as the dump have
a lot of spaces, zeroes, and other many repeated sequences, the compress
ratio use to reach 80% or more!
To protect the zip file using the database password, and to delete the .sql
file at the end os compression, look for the word EXTRA in this script code.
Script by Paulo Amaral - http://paulodev.com.br/en EOF exit 0; } if [ "$1" == "help" ] || [ "$1" == "-help" ] || [ "$1" == "--help" ] || [ "$1" == "?" ]; then help exit 0 fi # --------------------------------------------------------------- # getConfig (string varname) # echoes a variable value from configuration.php function getConfig() { if [ "$1" == "" ]; then return ""; fi cat $CONFIGURATION | grep -e "\$$1[ =]" | sed "s/[\ \t;\"']//g" | awk -F= '{ print $2; }' } # --------------------------------------------------------------- command -v mysqldump >/dev/null 2>&1 || { echo "Mandatory mysqldump package not found." exit 1 } # Get date and time TODAY=$(date +%Y%m%d) NOW=$(date +%H%M%S) TODAY_FORMAT=$(date +%Y-%m-%d) NOW_FORMAT=$(date +%H:%M:%S) echo "Sump script started - "$TODAY_FORMAT" "$NOW_FORMAT echo "" if [ ! -f $CONFIGURATION ]; then echo "$CONFIGURATION not found." exit 1; fi # Get database connection data HOST=$(getConfig "host"); DATABASE=$(getConfig "db"); USER=$(getConfig "user"); PWD=$(getConfig "password"); if [ "$HOST" == "" ] || [ "$DATABASE" == "" ] || [ "$USER" == "" ] || [ "$PWD" == "" ]; then echo "Unable to identify database parameters." echo "Be sure that configuration.php lines follows the format" echo " public $variable = 'value';" echo "" exit 1; fi echo "Detected connection parameters:" echo "HOST ...... $HOST" echo "DATABASE .. $DATABASE" echo "USUARIO ... $USER" echo "" # Defines base filename FILENAME="dump_"$DATABASE"_"$TODAY"_"$NOW echo "Dump will be saved as $ARQUIVO.sql" echo "" # Get the dump and save to file echo "Getting the dump... Be patient, may take few minutes..." echo "START ..... "$(date +%H:%M:%S) mysqldump -u $USER -p$PWD -h $HOST $DATABASE > $ARQUIVO.sql echo "END ....... "$(date +%H:%M:%S) touch $ARQUIVO.sql # Verify if process have well ended if [ -f $ARQUIVO.sql ]; then echo "$ARQUIVO.sql creation sucessful!" echo "" else echo "Unable to get the dump." echo "" exit 1 fi # if zipis available, zip it command -v zip >/dev/null 2>&1 && { echo "'zip' package detected. Will zip the dump." # zip dump normally zip -r9 $ARQUIVO.zip $ARQUIVO.sql # EXTRA: zip dump using database's password # (comment or delete above line to use this, do not enable both) # zip -r9 -P $PASSWORD $ARQUIVO.zip $ARQUIVO.sql # EXTRA: uncomment line below to delete .sql file at the end # rm $ARQUIVO.sql echo "" } TODAY_FORMAT=$(date +%Y-%m-%d) NOW_FORMAT=$(date +%H:%M:%S) echo "Ended dump - "$TODAY_FORMAT" "$NOW_FORMAT