Import/restore MySQL files from the command line

• 1 min read
A drawing of a cartoon man pointing upwards

Heads up! This post was written in 2008, so it may contain information that is no longer accurate. I keep posts like this around for historical purposes and to prevent link rot, so please keep this in mind as you're reading.

— Cory

I don't use this very often, but when I do I always spend a decent amount of time trying to figure it out online. Here are the steps to import a large SQL file from the command line.

  1. Upload the SQL file to the server that your MySQL database is on
  2. Execute the following command from the terminal (command line):
    mysql -u [username] -p [database name] < /path/to/file/data.sql
  3. Enter the database password when prompted

Since anything over about 2MB won't paste gracefully into a textarea, this works really well with large SQL files generated by phpMyAdmin. Remember to truncate data and/or drop tables where necessary before importing, otherwise the restore may not work as intended. (For example, CREATE TABLE will not work if the table already exists and INSERT statements will fail if a primary key already exists.)

development mysql tips