Database dump and restore examples

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

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

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

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

mysqldump --user=wikiadmin --password --host=db.mywiki.com --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

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

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

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