Data Management

Tech Tip: Back up PostgreSQL and MySQL databases

Learn how to back up PostgreSQL and MySQL databases.

You should back up your SQL database—whether it's MySQL or PostgreSQL—on a weekly, if not daily, basis. For many organizations, this information represents the content for Web sites and other important data, so retaining proper backups is vital.

Fortunately, both MySQL and PostgreSQL provide the tools required to dump databases to flat-text files that you can use to import to other systems or retain as backups.

To back up a MySQL database, use the mysqldump utility. For instance, to back up a database called data, as the user webuser, execute the following:

$ mysqldump -u webuser —password=pass data >data.dump

Because mysqldump normally writes to standard output, you should redirect the standard output to a file (in this case, data.dump). The only disadvantage to an automated backup routine is specifying the password on the command line.

To back up a PostgreSQL database, use the pg_dump tool. A user with access to the database needs to run this tool. Depending on host-based access rules defined in PostgreSQL, you may not need to present a password to the database to get the dump of the data it contains. Execute the following:

$ pg_dump data >data.dump

This is effectively the same as the mysqldump utility, but it doesn't require username or password information since you're running the tool as the user who has access to the database.

In both cases, the generated output file offers instructions on re-creating the entire database. So, this file contains both the data and the table structures necessary to create the database should you need to re-create it.

With both commands, you can use additional command-line switches to obtain the data you want to save. For both commands, read the man pages for complete information on the different switches you can use to customize your backups.

Editor's Picks

Free Newsletters, In your Inbox