Database dump and restore examples

From Meta, a Wikimedia project coordination wiki
Jump to: navigation, search
Ambox outdated content.svg

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

MediaWiki logo


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).

MySQLDump[edit]

Mysqldump is used to dump or create a database backup or to transfer to another SQL Server. It is also used to generate files in CSV, other delimited text or XML format for more details http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Export[edit]

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

"Overwriting"-type Backup[edit]

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.

You can create backup by simply typing:
    mysqldump  -u[username] -p[password] databasename > databasefilename.sql
    mysqldump -uroot -padmin emptable > backupfile.sql

INSERTs Backup[edit]

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[edit]

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[edit]

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[edit]

Export[edit]

You can simply Export sql file into database by typing:

   First Step: create database:
      mysql> create database emptable
      mysql> quit
   Second Step:
     shell> mysql -u[username] -p[password] emptable < databasefilename.sql

Import[edit]

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