45

I have a SQL file generated by MySQLDump. How can I restore it via command prompt?

Teun Zengerink
  • 4,277
  • 5
  • 30
  • 32
Anuya
  • 8,082
  • 49
  • 137
  • 222

6 Answers6

75

Run this command (if the mysql executable isn't in your PATH, first go to the directory where the MySQL binary is installed, something like \mysql\bin):

mysql -u username -ppassword databasename < file.sql

(Note that there is no space between -p and the password)

Or if the file is gzipped (like my backups mostly are) then something like this:

gunzip file.sql.gz | mysql -u username -ppassword databasename

or on some systems it might be necessary to add the -c flag to gunzip, like so (to force it to output to stdout):

gunzip -c file.sql.gz | mysql -u username -ppassword databasename
Nathan
  • 11,938
  • 12
  • 55
  • 62
49
  1. get to bin directory of mysql using command prompt
  2. log in to mysql
  3. run source command with file parameter

Example :

cd C:\mysql\bin
mysql -u root -p
mysql> source c:\myfile.sql
user207421
  • 305,947
  • 44
  • 307
  • 483
Xorty
  • 18,367
  • 27
  • 104
  • 155
  • 1
    You don't have to be in the MySQL bin directory if your system is sane, and by sane, I mean has the MySQL bin directory in the PATH. – icktoofay Jun 11 '10 at 07:58
  • I am using normal window cmd..And i tried this. But no results and no error..... C:\MySQL\MySQL Server 5.0\bin>mysql -uroot -ppassword mysql > source d:\admindb\ aar12.sql – Anuya Jun 11 '10 at 08:05
  • well in third step, don't write "mysql >" :) – Xorty Jun 11 '10 at 08:14
  • 1
    I am not getting any errors. But there is no update when i see in my query browser. The script works fine when executing manually in query browser. Tried this in cmd : C:\MySQL\MySQL Server 5.0\bin>mysql -uroot -ppassword source d:\admindb\AAR12.sql – Anuya Jun 11 '10 at 09:15
  • My .sql file looks encoded (or in binary form). So the above method gives lots of errors, is there any specific flag which I'll have to use? – Chankey Pathak Apr 17 '13 at 13:14
  • how can i preserve comments while restoring using above command? – Himalaya Garg Jul 23 '19 at 10:09
9
$ mysql database < myfile.sql

OR

$ mysql database
mysql> source myfile.sql
Edward Dale
  • 29,597
  • 13
  • 90
  • 129
6

The syntax is: 

mysql database_name < file.sql

See: Using mysql in Batch Mode

Seth
  • 45,033
  • 10
  • 85
  • 120
  • I am using normal window cmd..And i tried this. But no results and no error..... C:\MySQL\MySQL Server 5.0\bin>mysql -uroot -ppassword mysql > source d:\admindb\ aar12.sql – Anuya Jun 11 '10 at 08:06
  • The syntax is as I wrote above. What you've done there is redirect the output of the `mysql` command a file called `source` in your working directory. (Your redirection arrow is pointing the wrong way, should be `<`, and you have too many parameters). – Seth Jun 11 '10 at 16:47
4

Assume you are going to import /home/abc.sql Run commands below:

cd /home    
mysql -u root -p

If table not exists: 

mysql> create database abc;

Now use database abc and import abc.sql

mysql> use abc;
mysql> set names utf8;
mysql> source abc.sql;
Mashiro
  • 1,032
  • 1
  • 12
  • 24
1

From within MySQL command prompt, type

SOURCE file.sql