CODEWITHSUNDEEP
mysqlxampp
Jeaf Gilbert
Jeaf Gilbert

Reputation: 11991

Restore MYSQL Dump File with Command Line

I have file.sql, and I want to restore it. I found this command:

mysql -u username -p database_name < file.sql

Where should I put 'file.sql' in file system? I'm using Windows and XAMPP. Thank you.

*) The dump file is 3GB++

Upvotes: 23

Views: 74444

Answers (9)

user3556600
user3556600

Reputation: 107

Type the following command to import sql data file:

$ mysql -u username -p -h localhost DATA-BASE-NAME < data.sql

In this example, import 'data.sql' file into 'blog' database using vivek as username:

$ mysql -u vivek -p -h localhost blog < data.sql

If you have a dedicated database server, replace localhost hostname with with actual server name or IP address as follows:

$ mysql -u username -p -h 202.54.1.10 databasename < data.sql

OR use hostname such as mysql.cyberciti.biz

$ mysql -u username -p -h mysql.cyberciti.biz database-name < data.sql

If you do not know the database name or database name is included in sql dump you can try out something as follows:

$ mysql -u username -p -h 202.54.1.10 < data.sql

Refer: http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

Upvotes: 6

Pritam Chaudhari
Pritam Chaudhari

Reputation: 819

Import Database
1. Go to drive
command: d:
2. Mysql login
command: c:\xampp\mysql\bin\mysql -u root -p
3. Will ask for pwd: enter it
pwd
4. Select db
use DbName;
5. Provide file name
\. G:DbName.sql

Upvotes: 3

Deepak Saxena
Deepak Saxena

Reputation: 1

You can restore the dump using phpmyadmin as well but you have to sure that it must be .sql file.

The second way is if you are linux user than use the command line to restore the dump file.

mysql> mysql -uuser_name -ppassword table_name < /path/to/dump_file.sql

Upvotes: -1

Jeaf Gilbert
Jeaf Gilbert

Reputation: 11991

When I execute the answers of this thread, it returns 'mysql' is not recognized as an internal or external command, operable program or batch file..

Turned out that I have to execute mysql.exe first in xampp/mysql/bin folder, and the dump file path is relative to this bin folder. I put mine in there, and it worked.

Thanks all.

Upvotes: 2

Gajendra Bang
Gajendra Bang

Reputation: 3583

The best option will be

1) open the command prompt by Start -> Run -> Command or anyway 2) change you directory where the file.sql is saved say C:\temp\file.sql so your command prompt should look like

C:\temp>

3) now try these command

mysql -u root -p database_name < file.sql

Upvotes: 3

Devart
Devart

Reputation: 122032

As an alternate, try to restore the dump with a Restore or with an Execute Large Script Wizard Wizard.

There is a command-line support.

Backup or restore

Upvotes: 0

Peter Lindqvist
Peter Lindqvist

Reputation: 10210

You can put it anywhere, where there is enough diskspace available of course.

A good place for this would be either /tmp (on linux or similar) or c:\temp (on windows or similar)

But assuming you use that exact line you need to change your working directory to that which holds the file.

cd /path/where/sql/file/is

And then

mysql -u username -p database_name < file.sql

If you don't have the mysql bin in your PATH you might want to run

/path/to/mysql/bin/mysql -u username -p database_name < file.sql

Or temporarily put the file.sql in the mysql bin directory (not recommended)

Another possible scenario is to point to the file in the filesystem like this

mysql -u username -p database_name < /path/where/sql/file/is/file.sql

PS. If you're on windows you might wanna change the forward slashes to backslashes.

Upvotes: 23

Shakti Singh
Shakti Singh

Reputation: 86446

You can put anywhere in the system but consider to change the command also

mysql -u username -p database_name < /somepath/file.sql

Upvotes: 5

Matteo Alessani
Matteo Alessani

Reputation: 10422

you need to point to the location of your file using:

mysql -u username -p database_name < drive:\\path_to_your_file\file.sql

Upvotes: 1

Related Questions