scripts.mit.edu

How can I backup and restore MySQL data?

You can back up your SQL data in several ways.

One easy way to backup your data is using phpMyAdmin:

1. Log in to phpMyAdmin with your SQL password.
2. Select the database or table you want to export (if you want to export just one database or table), and click the link at the top-right labeled Export.
3. If a list of databases or tables displays at the top, select the databases or tables you want to export: either use “Select All”, or Ctrl-click / Cmd-click to select multiple items. (There is no need to backup information_schema, since this is a read-only system table.)
4. Ensure the export type is “SQL”. Check the “Save as file” box near the bottom, and select “gzipped” if you want to compress your exported data.

1. Click Go and save the downloaded file somewhere safe.

To restore your data using phpMyAdmin:

1. Log in to phpMyAdmin with your SQL password.
2. If you want to import a single database, choose that database from the left menu.
3. Click Import at the top.

1. Select the backup you downloaded previously (or any SQL-formatted database dump) and click Go.

A common issue with phpMyAdmin is that it cannot import databases beyond a certain size. To get around this limitation, you can import and export databases using the command-line. First, ssh to scripts.mit.edu. Then, when exporting a database, run

mysqldump user+database > filename.sql

To import a database dump, make sure the database exists (you may need to create it from sql.mit.edu) and run the command

mysql user+database < filename.sql