MySQL Failed to open file – error 2 and 22 on Windows

I recently had to restore a MySQL backup. It had been a while since I used MySQL, having switched to Postgresql sometime back. First thing I noticed was the lack of a GUI admin tool that PG spoils you with. Making matters worse, the command line tool isn’t even added to your path; you can’t just run “mysql” like you can in a Unix shell, much less use the redirect shortcut. And I was getting errors.

Here’s what you need to do to restore a MySQL backup on Windows:

  1. Run the command line tool from the start menu
  2. Open your backup file in a text editor. Does it start with a command to create or “use” the database? If not
    1. Create it, if necessary
    2. Type “use database” filling in your DB name
  3. Type “source path-to-SQL-file“. BUT, you must follow these rules:
    1. Use the full source command, not the \. shortcut
    2. Have no spaces in your path. I copied mine to a root of a drive. Note that spaces in theĀ file name is OK, just not the path.
    3. Do not quote the file name, even if it has spaces. This gave error 22.
    4. Use forward slashes in the path, e.g., C:/path/to/filename.sql. Otherwise you’ll get error 2.
    5. Do not end with a semicolon.

Follow all those rules and it should work fine.