14

I have created a file named ab.sql using the mysqldump utility of a database named library. It worked fine. Now i am trying to restore it using mysqlimport. My database already exists. But i want to override it. I am using the command 

mysqlimport -uroot -p**** library D:/ab.sql

in the command line but it gives an error that says,

mysqlimport: Error: 1146, Table 'library.ab' doesn't exist, when using table: ab

desperately need help.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Suman.hassan95
  • 3,895
  • 8
  • 26
  • 24

2 Answers2

26

mysqlimport reads rows from a text file into a database. mysqldump outputs a file full of SQL statements, not simple rows. You can run those SQL statements using:

mysql -u root < D:/ab.sql

Depending on your mysqldump options, this may delete existing data in your database. If you're unsure, I'd grep for "drop" and "delete" to make sure it looks OK.

Potherca
  • 13,207
  • 5
  • 76
  • 94
froody
  • 371
  • 3
  • 3
  • i have tried doing this too in the cmd prompt. but it says, "'mysql' is not recognized as an internal or external command, operable program or batch file." – Suman.hassan95 Mar 03 '11 at 21:33
  • can we backup the entire database using mysqlimport. i just read that it can restore only a particular table for which the table name and .sql file name must be same. how can i restore entire database...?? – Suman.hassan95 Mar 03 '11 at 21:41
  • 1
    mysqldump is intended for backing up a database. It can dump a table, a database, or all databases. Its output is a plaintext file which contains all the necessary SQL queries to reconstruct the dumped data. You use the mysql monitor to "import" the dump. Given your "not recognized" error, either it's not installed, or not in your path. – Marc B Mar 03 '11 at 21:44
14

Marc B commented: Given your "not recognized" error, either it's not installed, or not in your path.

How to "Recognize" mysql

If you do have MySQL installed, say XAMPP 1.7.3 on Windows 7, installed to C:\xampp, you would need to open the Windows command line (cmd.exe) and change your path so that MySQL is included:

cd C:\xampp\mysql\bin

You would then add the command from froody's answer or some variant like:

C:\xampp\mysql\bin>mysql -u {DB_USER} -p {DB_NAME} < path/to/file/ab.sql

If you move the database file (i.e. the one you initially exported using mysqldump) to C:\xampp\mysql\bin before running the above command, you can leave off the path to the database file, leaving only:

C:\xampp\mysql\bin>mysql -u {DB_USER} -p {DB_NAME} < ab.sql

You will then be asked for the password of the database user. Then the command should execute. You should see something like this when it's finished:

windows cmd mysql import

Hope this helps and is accurate, with a little help from StackOverflow, this is how it worked for me. Good luck!

Community
  • 1
  • 1
joelhaus
  • 903
  • 1
  • 10
  • 18
  • Is approach that you are suggesting more efficient than running `mysqlimport`? If yes, how fast would this approach complete against if we ran `mysqimport`? – Rachel Oct 18 '12 at 14:11
  • Please take this with a grain of salt, but in my experience, usage of `mysqlimport` only comes into play when I am importing a data file which is NOT in `.sql` format. For instance, importing a `.csv` file into your database with the `mysqlimport` client would be the appropriate use case. As for speed, I can not offer any evidence, but I am inclined to believe that a `.sql` file would import faster than an identical file of data in `.csv` format using `mysqlimport`. – joelhaus Oct 22 '12 at 19:54
  • mysqlimport can import 40 millions of rows for about 1 hour. .sql is much slower. – CappY Oct 16 '15 at 14:17
  • Just a clarification: this needs to be done in the windows cmd prompt. Didn't work for me in Git Bash prompt. – Ozh Apr 23 '20 at 09:46