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.

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

  1. Thanks so much for this info. You where a savior to me this night. In my work for a project for this my site,Code Messiah, I run into an issue when I have to migrate some project in .net to MySQL. Thanks you for this guide. Its short but henvy

  2. Hi, just a heads up;
    You can use backslashes but you’ll have to escape them (“\\”); and you can use semicolons, it worked as well. However I wouldn’t have figured out that the problem was the spaces in the path, that’s the main issue! So thanks for the article!

  3. I copied the SQL files wherever and then changed to that path and launched MySQL from there. That worked for me.

  4. I suppose the origins of MySQL in a Unix-like or Linux environment probably resulted in the quirks you described in this post. I received both the error 22 and the error 2 in Windows. I am far from a MySQL expert, and I probably would never have figured this out without your help. Your explanation got me unstuck immediately! Thank you!

  5. I wanted to thank you as well. I had a huge file to import and couldn’t get it to work. My background is not MySQL and I was proverbially pounding my head against the wall. I was getting both the 22 and 2 errors and found your simple explanation exactly what was holding me up. Thanks!

  6. I was getting the error 22 and it has been solved through changing the file name ‘a.sql’ to ‘111.sql’,I don’t know the real reason,but
    I use backslashes and never to escape them (β€œ\\”),thanks anyway.

Leave a Reply

Your email address will not be published. Required fields are marked *