Exporting / Importing a MySQL & PostgreSQL database

I love phpmyadmin & phppgadmin! They are such a valuable tools, especially when moving a website's database from one environment to another. It can be secured so there is no problem with having such a powerful database tool running on a production web server, just be careful.

If your exporting from phpmyadmin ensure you have the script include the DROP TABLE so you don't get annoying errors when importing into an existing database. If your doing this from the command line, as best practice, use the following:

mysqldump -u username -p --complete-insert --add-drop-table --default-character-set=utf8 database &gt; backup.sql

To import the database create it first then through the commandline use the following:

mysql -u username -p --default-character-set=utf8 database < backup.sql

To import a database into your PostgreSql database from the command line you can use the following:

psql -h localhost -d database -U username > backup.sql

Using the command line is the best option for large database files as you have no limitations for post sizes as you can run into with phpmyadmin and phppgadmin.

Sometimes you need to be more technical with your database dumps so the following example shows how to dump a postgresql database with custom format.

pg_dump -Fc -U username database > backup.sql

And then to restore this at the speed of lightening, try this:

pg_restore -U username -d database backup.sql

Published on Monday, December 15th, 2008 at 06:36 pm by jon