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 “
Here’s what you need to do to restore a MySQL backup on Windows:
- Run the command line tool from the start menu
- Open your backup file in a text editor. Does it start with a command to create or “use” the database? If not
- Create it, if necessary
- Type “use database” filling in your DB name
- Type “source path-to-SQL-file“. BUT, you must follow these rules:
- Use the full source command, not the \. shortcut
- 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. - Do not quote the file name, even if it has spaces. This gave error 22.
- Use forward slashes in the path, e.g., C:/path/to/filename.sql.
Otherwise you’ll get error 2. - Do not end with a semicolon.
Follow all those rules and it should work fine.
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
Thank you so much
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!
Thank you very much, it works!
This was quite helpful. Thanks for the tips.
Exactly what I needed. Thank you.
Nice Explanation…..Thank you very much…..
Thank you!!!
Thanks! No quotes — who would have guessed!
thank you so much for this, I had no idea why my commands didn’t work
it didn’t work for me 🙁
Excellent! thanks a lot
Thanks! 🙂
Thanks! Using quotes caused the error on my occasion.
Morgan
Searching for the problem’s solution, and you rock! thx
I copied the SQL files wherever and then changed to that path and launched MySQL from there. That worked for me.
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!
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!
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.
The one time I use backslashes! Thanks!
Thank you very much.
Thank You so much……….
Thanks for your help
Maaaaaan spending 4 hours because of a space in my path, you ended my suffering!
another reason to why this might happen, is when mysql is already in the process of importing another file. Rarely this happens if you’re trying to play it smart by importing one large file through phpMyAdmin, and in parallel trying to import another via mysql command line through CMD.
Hi there!
I am experiencing exactly the same issue.
Here’s what I have done so far:
– Re-install SQL version 8 developer pack.
– I am using mySQL 8 command line client and applied the following control: ‘source C:\ai\employees.sql’ with forward slashes as well and it gave me the error above
– I have drop the database and created from scratch.
– When I type ‘show tables’ the tables are definitely there but when I use the ‘select’ function it returns ’empty set’
– On the workbench when I right-click on a table for this database and ‘select rows – limit 1000’ there is no content.
I would appreciate some help! I have an assessment with this database for an interview. I am a newbie btw.
Thanks in advance!
Edi
Sorry, I haven’t used MySQL in some years. There are more ideas in the comments and you can try ServerFault. Honestly, I’d ask your interviewer, this should not be a part of your test. Unless the test is to see if you ask for help!
Thanks for this post it was realy useful d’or me?
sorry this wasnt helpful not at all ! im still facing the same problem
It worked with me, thank you.