qubitstream / phpmyadmin_sql_backup

A Python script to automate SQL dumps via phpMyAdmin's web interface

phpmyadmin_sql_backup.py

What is it?

A Python 3 script to automate the download of SQL backups via a phpMyAdmin web interface.

This is useful when your web hosting provider does not grant you access to a console (for mysqldump) but you want to automate the backup of your database (without having to manually use the browser).

It has been tested with Python 3.4+ on Linux and Windows and the following versions of phpMyAdmin: 4.3.x - 4.8.x, 5.0.0

Note: The web interface of phpMyAdmin may change in the future and break this script. Please file a bug report (including your version of phpMyAdmin) if you encounter this issue.

Usage

usage: phpmyadmin_sql_backup.py [-h] [-o OUTPUT_DIRECTORY] [-p]
                                [-e EXCLUDE_DBS] [-s SERVER_NAME]
                                [--compression {none,zip,gzip}]
                                [--basename BASENAME] [--timeout TIMEOUT]
                                [--overwrite-existing]
                                [--prefix-format PREFIX_FORMAT] [--dry-run]
                                [--http-auth HTTP_AUTH]
                                URL USERNAME PASSWORD

Automates the download of SQL dump backups via a phpMyAdmin web interface.

positional arguments:
  URL                   phpMyAdmin login page url
  USERNAME              phpMyAdmin login username
  PASSWORD              phpMyAdmin login password

optional arguments:
  -h, --help            show this help message and exit
  -o OUTPUT_DIRECTORY, --output-directory OUTPUT_DIRECTORY
                        output directory for the SQL dump file (default: the
                        current working directory)
  -p, --prepend-date    prepend current UTC date & time to the filename; see
                        the --prefix-format option for custom formatting
  -e EXCLUDE_DBS, --exclude-dbs EXCLUDE_DBS
                        comma-separated list of database names to exclude from
                        the dump
  -s SERVER_NAME, --server-name SERVER_NAME
                        mysql server hostname to supply if enabled as field on
                        login page
  --compression {none,zip,gzip}
                        compression method for the output file - must be
                        supported by the server (default: none)
  --basename BASENAME   the desired basename (without extension) of the SQL
                        dump file (default: the name given by phpMyAdmin); you
                        can also set an empty basename "" in combination with
                        --prepend-date and --prefix-format
  --timeout TIMEOUT     timeout in seconds for the requests (default: 60)
  --overwrite-existing  overwrite existing SQL dump files (instead of
                        appending a number to the name)
  --prefix-format PREFIX_FORMAT
                        the prefix format for --prepend-date (default:
                        "%Y-%m-%d--%H-%M-%S-UTC_"); in Python's strftime
                        format. Must be used with --prepend-date to be in
                        effect
  --dry-run             dry run, do not actually download any file
  --http-auth HTTP_AUTH
                        Basic HTTP authentication, using format
                        "username:password"

Written by Christoph Haunschmidt et al., version: 2019-05-07.1

Examples

phpmyadmin_sql_backup.py "http://www.example.com/phpmyadmin/" your_user your_password

Downloads a plain text .sql backup of all databases to the current working directory.

---

phpmyadmin_sql_backup.py "http://www.example.com/phpmyadmin/" your_user your_password --exclude-dbs mydb2,mydb4 --prepend-date --basename example_dump --output-directory /tmp --compression zip

Downloads a zipped dump with databases mydb2 & mydb4 excluded, the base name example_dump and a prepended UTC date / time to the directory /tmp, e.g. /tmp/2016-03-11--15-19-04-UTC_example_dump.zip.

Requirements

• A Python 3.4+ installation on your system
• Grab - python web-scraping framework: Install via pip install -U Grab or see the installation instructions if you run into problems.

Note for Windows users: while it is possible to install the requirements natively, it is often easier to use the Windows Subsystem for Linux if you are using Windows 10

Changelog

Currently, there is no changelog; the best option at the moment is to read the commit messages.

License

GNU GPL3

Contributors

• Christoph Haunschmidt (original author)
• Jason Harper (optional mysql server hostname)
• Jonas Bengtsson (older frame-based phpMyAdmin support)
• Benoît Courtine (HTTP Auth)

phpmyadmin_sql_backup's Issues

xyz issue....

upload and restore an sql backup

Hi.
This is not an issue but a feature request.
I am surprised there are so few python scripts for phpmyadmin interaction.

• phpmyadmin-cli (forked from an archived project) did not work for me with phpmyadmin 5
• This blog script doesn't work for me either

This phpmyadmin_sql_backup project is so far the only one which worked for me with phpmyadmin 5.

Unfortunately I am looking for the opposite functionality: I want to automate the daily upload of some (locally updated) tables to a shared php-mysql server ... where I only have a phpmyadmin account.

Would it be possible to add an option for uploading an .sql file, instead of downloading it?
I'm afraid my skills do not permit me to code this on my own.

Thanks a lot for this great script anyway

Syntax Error

Hey, i tryed to use your script but i got this Error:

File "phpmyadmin_sql_backup.py", line 68
file=sys.stderr)
^
SyntaxError: invalid syntax

Hope i could help you with this

"Error: No module named 'pycurl'"

Hello,

FWIW, pycurl is required… but won't install. It's Python 3.8.10 on Windows7 (I know).

phpmyadmin_sql_backup.py etc.
Error: No module named 'pycurl'
=========
pip install -U pycurl
  error: subprocess-exited-with-error

  x python setup.py egg_info did not run successfully.
  ¦ exit code: 10
  ?-> [1 lines of output]
      Please specify --curl-dir=/path/to/built/libcurl
      [end of output]
=========
python -m pip install -U pip setuptools
=========
phpmyadmin_sql_backup.py etc.
Error: No module named 'pycurl'

Cheers,

Doesn't work on Ubuntu 16.04?

On Ubuntu 16.04 (default pyhon 2.7 and _python 3_installed together), running:

python3 phpmyadmin_sql_backup.py "https://myhosting.com/phpmyadmin" USERNAME PASSWORD

with tested URL, USERNAME and PASSWORD, I have this error:

Error: Could not login - did you provide the correct username / password? (Substring not found: server_export.php)

SystemError: PY_SSIZE_T_CLEAN macro must be defined for '#' formats

SystemError: PY_SSIZE_T_CLEAN macro must be defined for '#' formats
Error: ('Failed writing header', error(23, 'Failed writing header'))

getting this error runing python 3.10.6

compression not working

thanks for sharing the script!
I successfully use it to backup my db, which results in a 1Gb sql file when using no compression.
However, --compresion zip results in an empty file.
And --compression gzip results in a file, that once gunziped, it has 626Mb, instead of 1Gb.

Any idea of what can be the problem?
I've tried both in OSX and CentOS release 6.8. Python 3.4, and phpmysql 4.6.6.