Download entire database(s) using the PhpMyAdmin web interface. Useful when the hosting provider does not allow remote connections with the database server.

PMAbkp.sh

#!/bin/bash

HOST="example.com"
USER="dbuser"
PASS="pass123"

TARGETDIR="/backup"
TMPFN=".export.tmp"

for DB in "xyz" "pqr"
do

# obtain authorisation token from PhpMyAdmin main page
DNLD="wget --save-cookies ./jp-cookies \
    --keep-session-cookies \
    --http-user=${USER} \
    --http-password=${PASS} \
    -O - \
    -q \
    http://${HOST}/phpmyadmin/index.php?db=${DB}&pma_username=${USER}&pma_password=${PASS}"
TOKEN=$( echo "$( $DNLD ; wait )" | grep --text ' var token =' | cut -d"'" -f 2)

# assemble URL of the database structure frame where we download tables list from
TABLES_URL="http://${HOST}/phpmyadmin/db_structure.php?db=${DB}&token=${TOKEN}"
DNLD="wget --load-cookies ./jp-cookies \
    --keep-session-cookies \
    --http-user=${USER} \
    --http-password=${PASS} \
    -O - \
    -q \
    ${TABLES_URL}"

# parse table list and assemble corresponding post data
TBLNAMES=$( echo "$( $DNLD ; wait )" | \
    xmlstarlet sel -N x="http://www.w3.org/1999/xhtml" -t \
    -m "/x:html/x:body/x:form[@id='tablesForm']/x:table/x:tbody/x:tr/x:th/x:label/text()" -c . -n )
TABLES=""
for t in $TBLNAMES
do
  TABLES="${TABLES}&table_select%5B%5D=${t}"
done

# download the database dump
EXPORT_URL="http://${HOST}/phpmyadmin/export.php"

POST="db=${DB}&token=${TOKEN}&export_type=database${TABLES}&what=sql&\
sql_header_comment=&sql_disable_fk=something&sql_compatibility=NONE&\
sql_structure=something&sql_drop_table=something&sql_if_not_exists=something&\
sql_auto_increment=something&sql_backquotes=something&sql_data=something&\
sql_columns=something&sql_extended=something&sql_max_query_size=750000&\
sql_hex_for_blob=something&sql_type=INSERT&asfile=sendit&\
filename_template=__DB__.production.%25Y%25m%25d-%25H%25M%25S&\
remember_template=on&compression=none"

wget --load-cookies ./jp-cookies \
    --keep-session-cookies \
    --http-user=${USER} \
    --http-password=${PASS} \
    --post-data=${POST} \
    --save-headers \
    --output-document=${TMPFN} \
    "${EXPORT_URL}"
wait

# find the 'Content-disposition' header and extract target file name
TARGETFN=$( egrep -i -m 1 '^Content-Disposition' ${TMPFN} | cut -d'"' -f 2 )

# find first empty line delimited by CR+LF = end of headers
LTS=$( egrep -m 1 -n '^[[:space:]]$' ${TMPFN} | cut -d':' -f 1 )

# start at the next line
(( LTS += 1 ))

# output all lines below headers to target file
tail -n +${LTS} ${TMPFN} > ${TARGETDIR}/${TARGETFN}

rm ${TMPFN}

echo "Database dump saved to ${TARGETFN}"

done

exit 0