Import/Restore MySQL Files From the Command Line

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 appropriate MySQL password at the prompt

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.)

Author avatar

About the author

Creator of Surreal CMS and other web things. Follow me for tweets about JavaScript, CSS, and web programming.