Import and Export MySQL and PostgreSQL

There are a number of ways to export and import data from a database. You can use a Mac client like Sequal Pro for MySQL, for example. You can do the same for PostgreSQL, though I have not settled on a Mac client for PostgreSQL.

Mac clients aside, you need can import and export your database SQL from the command line or from a bash script on your server as well. I use the following commands to handle importing and exporting SQL on my servers and development machines.

Export

If you want to export PostgreSQL:

pg_dump -U [username] [database] -f [filepath]

If you want to export MySQL:

mysqldump -u [username] -p[root_password] [database] > [filepath]

Note that there is no space after -p in the MySQL command.

Import

If you want to import PostgreSQL:

psql [database] < [filepath]

If you want to import MySQL:

mysql -u [username] -p[root_password] [database] < [filepath]

Documentation

You can read about the backup process for PostgreSQL on their website for more details.

For MySQL, the documentation is broken up into multiple pages. One for exporting or dumping your database and another for importing your exported database.

 

Author: Kevin

Howdy, I’m Kevin Dees. I’m the founder of Robojuice. It is a web strategy company focused on delivering a refined experience to partners. I’m also the creator of TypeRocket for WordPress.