Database dump and restore examples

This page is outdated. Please see Manual:Backing up a wiki on MediaWiki.org for more up to date informations.

The instructions below describe the process for command-line exports/imports.
For very small databases the mySQL admin interface can also be used (but due to size restrictions this will fail with larger dumps).
Version 15+ of MediaWiki also contains maintenance scripts to export/import the database via an XML file: DumpBackup.php & ImportDump.php, but be aware that this will ONLY back up articles, NOT user information!

The examples below assume the following settings:

• Database host: db.mywiki.com ($wgDBserver)
• Name of database: wikidb ($wgDBname)
• Admin username: wikiadmin ($wgDBuser)
• Admin password: wikipw ($wgDBpassword)
• Name of schema: mywiki ($wgDBprefix)

All of the actual settings for your installation of MediaWiki can be found in the LocalSettings.php file (see the respective variable names listed above, in parentheses).

MySQL

Export

Mysqldump saves a dump of the database as a plain-text file in the current folder (via mysqldump).

"Overwriting"-type Backup

The created file contains commands that will delete, recreate, and repopulate the each of the tables associated with the database in bulk.

mysqldump --user=wikiadmin --password=wikipw --host=db.mywiki.com wikidb > wikidb.sql

To prompt the user for the password, use

mysqldump --user=wikiadmin --password --host=db.mywiki.com wikidb > wikidb.sql

This backup does not lend itself to restore individual records.

INSERTs Backup

An alternative backup command is

 mysqldump --user=wikiadmin --password=wikipw --skip-opt  wikidb > wikidb.sql

this version creates a file that creates the tables and INSERTs each record, individually, into them. This may be more useful, since specific records can be GREP'd out.

Compressed Backups

Finally, the resulting file, produced by either method, can be compressed 7:1 or better by running it through Gzip:

 gzip -cq9 wikidb.sql > wikidb.sql.gz

The backup commands above can be altered to "pipe" their output directly to gzip, saving several steps:

 mysqldump --user=wikiadmin --password=wikipw --host=db.mywiki.com wikidb |gzip -cq9 > wikidb.sql.gz

or

 mysqldump --user=wikiadmin --password=wikipw --skip-opt  wikidb | gzip -cq9 > wikidb.sql.gz

Import

Either command, above, generates a valid SQL script that can be executed with mysql.

Assuming the backup file is not compressed, or has been decompressed:

 mysql --user=wikiadmin --password=wikipw wikidb < wikidb.sql

The INSERTs type script file can be easily edited to limit which tables and/or records will be added back into the system.

Postgres

Export

Saves a dump of the database as a plain-text file in the current folder (via pg_dump).

 pg_dump -U wikiadmin -n mywiki wikidb > mywikibackup.sql

User will be prompted for password.

Import

Reads a saved dump, and restores (overwriting any existing data) with the contents of the imported file (via psql).

 psql -U wikiadmin -f mywikibackup.sql wikidb