Database dump and restore examples

From Meta, a Wikimedia project coordination wiki
Jump to navigation Jump to search

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: ($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 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


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 wikidb > wikidb.sql

To prompt the user for the password, use

mysqldump --user=wikiadmin --password wikidb > wikidb.sql

To back up all databases on the server, add the --all-databases switch

mysqldump --user=wikiadmin --password --all-databases > 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 wikidb |gzip -cq9 > wikidb.sql.gz


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


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.



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


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