Automated export of phpMyAdmin SQL download with wget

Question

I'm using the following script to attempt to download an SQL export from a phpMyAdmin installation (adapted from this Stackoverflow question)

read -p "Username: " USERNAME
read -p "Password: " PASSWORD
ADMIN_URL='https://example.com/phpmyadmin'
COOKIEJAR='/tmp/phpmyadmin_cookie'

token=$(
    wget -qO- \
         --http-user="$USERNAME" \
         --http-password="$PASSWORD" \
         --save-cookies "$COOKIEJAR" \
         --keep-session-cookies \
         "$ADMIN_URL" \
        | egrep -o 'token=[[:xdigit:]]+' | head -1
     )

post_data="$token&what=sql&export_type=server"

wget -O export.sql \
     --save-headers \
     --http-user="$USERNAME" \
     --http-password="$PASSWORD" \
     --load-cookies "$COOKIEJAR" \
     --post-data "$post_data" \
     "$ADMIN_URL/export.php"

rm "$COOKIEJAR"

Unfortunately, phpMyAdmin 4.7.9 responds with a Server 500 error:

--2018-03-21 15:27:35--  https://woosh5.nl/phpmyadmin/export.php
Resolving woosh5.nl (woosh5.nl)... 37.97.205.130, 2a01:7c8:aac3:7::11
Connecting to woosh5.nl (woosh5.nl)|37.97.205.130|:443... connected.
HTTP request sent, awaiting response... 401 Unauthorized
Authentication selected: Basic realm="phpMyAdmin localhost"
Reusing existing connection to woosh5.nl:443.
HTTP request sent, awaiting response... 500 Internal Server Error
2018-03-21 15:27:35 ERROR 500: Internal Server Error.

I'm quite certain the authentication succeeds, so there must be something wrong with the POST parameters I'm sending.

How can I automate the downloading of SQL dumps from phpMyAdmin?

Answer 1

There could conceivably be situations where you might want to get at your database this way, but I'm struggling to think of one.

phpMyAdmin is an interactive tool. It is not designed for automation of the sort you want.

If you can access your webserver over ssh, just run mysqldump via ssh, and dump to the ssh client system.

Are you able to run some code on the web server? If so, run mysqldump there, along with whatever code you need to export it to somewhere (e.g. upload it via SCP or FTP, or to an S3 bucket). Or maybe write yourself a little script that checks authentication, then provides the dump file over HTTP.

If you have broader access to the server, run something like backupninja, with config to handle both the dump and offloading to remote storage.

It is possible to configure Phpmyadmin to use HTTP authentication, as your use of --http-user and --http-password implies, but in that case you wouldn't need a cookie jar. It's more common to find it configured to use cookie based authentication, in which case you need to first submit the login form to get the cookie, and then use that cookie in subsequent requests.