Add to Favourites Add to Favourites    Print this Article Print this Article

How can I backup my database or table ?

Click on a database or table name in the left frame, the properties will be displayed. Then on the menu, click "Export", you can dump the structure, the data, or both. This will generate standard SQL statements that can be used to recreate your database/table.

You will need to choose "Save as file", so that phpMyAdmin can transmit the resulting dump to your station. Depending on your PHP configuration, you will see options to compress the dump. See also the $cfg['ExecTimeLimit'] configuration variable.

For additional help on this subject, look for the word "dump" in this document.

You can also automate this task using a shell script under linux. Here is an example script, which also does basic authentication and ssl handshake using wget to retrieve the dump:

#!/bin/sh

# Retrieve a gzipped mysqdump of $DATABASE using phpMyAdmin 2.11.5.2
# installed on $URL using basic authentication with $USERNAME and $PASSWORD.
# Takes one argument: The $DUMP file where output is stored.

COOKIES="/tmp/phpmyadmin_cookies.txt"
USERNAME="XXXXXXXXXX"
PASSWORD="XXXXXXXXXX"
URL="https://www.XXXXXXXXXXXXXXXX/phpmyadmin/"
DATABASE="DATABASENAME"
DUMP="$1"

[[ "x$1" != "x" ]] || DUMP="/tmp/phpmyadmin_dump_$DATABASE.sql.gz"

[[ ! -f $COOKIES ]] || rm $COOKIES >/dev/null

TOKEN=$(wget -O- --no-check-certificate --http-user="$USERNAME" --http-password="$PASSWORD" --keep-session-cookies --save-cookies "$COOKIES" $URL | grep token | tail -n1 | sed -e "s/.*token=\(.*\)\"/\1/")

PHPSESSID=$(cat "$COOKIES" | grep -E "PHPSESSID" | awk '{ print $7 }')

POSTDATA="phpMyAdmin=$PHPSESSID&token=$TOKEN&export_type=server&phpMyAdmin=$PHPSESSID&phpMyAdmin=$PHPSESSID&db_select%5B%5D
=$DATABASE&what=sql&phpMyAdmin=$PHPSESSID&csv_separator=%3B&csv_enclosed=%26quot%3B&csv_escaped=%5C&csv_terminated
=AUTO&csv_null=NULL&csv_data=&phpMyAdmin=$PHPSESSID&excel_null=NULL&excel_edition=Windows&excel_data=&phpMyAdmin=$PHPSESSID&htmlexcel_null
=NULL&htmlexcel_data=&phpMyAdmin=$PHPSESSID&htmlword_structure=something&phpMyAdmin=$PHPSESSID&htmlword_data=something&htmlword_null
=NULL&phpMyAdmin=$PHPSESSID&latex_caption=something&phpMyAdmin=$PHPSESSID&latex_structure=something&latex_structure_caption
=Structure+of+table+__TABLE__&latex_structure_continued_caption=Structure+of+table+__TABLE__+%28continued%29&latex_structure_label
=tab%3A__TABLE__-structure&latex_comments=something&phpMyAdmin=$PHPSESSID&latex_data=something&latex_columns=something&latex_data_caption
=Content+of+table+__TABLE__&latex_data_continued_caption=Content+of+table+__TABLE__+%28continued%29&latex_data_label
=tab%3A__TABLE__-data&latex_null=%5Ctextit%7BNULL%7D&phpMyAdmin=$PHPSESSID&ods_null=NULL&ods_data=&phpMyAdmin
=$PHPSESSID&phpMyAdmin=$PHPSESSID&odt_structure=something&odt_comments=something&phpMyAdmin=$PHPSESSID&odt_data=something&odt_columns
=something&odt_null=NULL&phpMyAdmin=$PHPSESSID&pdf_report_title=&pdf_data=1&phpMyAdmin=$PHPSESSID&sql_header_comment=&sql_disable_fk
=something&sql_compatibility=NONE&phpMyAdmin=$PHPSESSID&phpMyAdmin=$PHPSESSID&sql_structure=something&sql_drop_table=something&sql_auto_increment
=something&sql_backquotes=something&phpMyAdmin=$PHPSESSID&phpMyAdmin=$PHPSESSID&sql_data=something&sql_columns=something&sql_extended
=something&sql_max_query_size=50000&sql_hex_for_blob=something&sql_type=INSERT&phpMyAdmin=$PHPSESSID&yaml_data=&phpMyAdmin=$PHPSESSID&asfile
=sendit&filename_template=__SERVER__&remember_template=on&compression=gzip&phpMyAdmin=$PHPSESSID"

HEADER="Cookie: pma_server_filename_template=__SERVER__; pma_lang=en-utf-8; pma_charset=iso-8859-1; pmaCookieVer=4; pma_collation_connection=utf8_unicode_ci; phpMyAdmin=$PHPSESSID; pma_theme=original; PHPSESSID=$PHPSESSID"

REFERER="$URL/server_export.php?token=$TOKEN&phpMyAdmin=$PHPSESSID"

wget --no-check-certificate --http-user="$USERNAME" --http-password="$PASSWORD" --load-cookies "$COOKIES" "$REFERER"

wget -O "$DUMP" --no-check-certificate --http-user="$USERNAME" --http-password="$PASSWORD" --load-cookies "$COOKIES" --referer="$REFERER" --post-data="$POSTDATA" --header="$HEADER" "$URL/export.php"

Was this answer helpful?

Also Read