How can I automate the exportation of a mySQL data

Question

Is it possible to automate the exportation of a single db using phpMyAdmin?

When I try creating the .sql file from an external script, like php, the resulting .sql file looks clean, but when imported causes problems in the application.

When I use phpMyAdmin to create a similar .sql file by using Export, the resulting .sql file Imports without causing any issues to my application.

The application is moodle 2.2, the MySQL version is 5.5, the phpMyAdmin version is 3.4.10.

Answer 1

If you can't connect directly to the database with mysqldump, you can use this shell script (requires curl):

#!/bin/sh
ADMIN_URL='https://phpmyadmin.example.com'
USERNAME='my_username'
PASSWORD='my_password'
COOKIEJAR='/tmp/my_cookiejar'
token=`curl -s -o - -d "pma_username=$USERNAME" -d "pma_password=$PASSWORD" -c "$COOKIEJAR" "$ADMIN_URL/" | grep -o -m 1 "token=.*'" | sed "s/'//"`
curl -o - -b "$COOKIEJAR" -d what=sql -d export_type=server -d sql_structure_or_data=structure_and_data -d "$token" "$ADMIN_URL/export.php"
rm "$COOKIEJAR"

Then put it in your crontab:

22      3   *   *   *   someuser    /path/to/script.sh > /path/to/backup/db.sql

to get a backup at 3:22 every night.

Only tested with phpMyAdmin 3.4.10.1 and might not work in other (major) versions.

Answer 2

Use this script: https://github.com/qubitstream/phpmyadmin_sql_backup

phpmyadmin_sql_backup.py

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 the the 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 phpMyAdmin 4.3.6, 4.5.4.1 and 4.7.0-dev

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.

Example:

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

other options available.

Answer 3

The best option is to make a script that executes database export with mysqldump and after that a cronjob to execute that script.