How to import database file with command prompt

Problems with the Windows version of XAMPP, questions, comments, and anything related.

How to import database file with command prompt

Postby Naitik_Mistry » 19. June 2013 06:05

Hello Friends,

In Magento E-Commerce whenever you have to import and export large sql file then apply following things:-

1>First of all open command prompt

2>Then
Example:- D:/xampp/mysql/bin/mysql.exe

3>Syntax:-

D:/xampp/mysql/bin/mysql - u root -p databasename < D:/test.sql (sql file name).

And Same thing apply when you have to use export database then apply following things:-

1> D:/xampp/mysql/bin/mysqldump -u root -p databasename > D:/text.sql(sql file name).

So, You have use when import and export sql file with command prompt this method would very much helpful to you.

Regards,
Niks
:roll:
Naitik_Mistry
 
Posts: 2
Joined: 12. June 2013 05:47
Operating System: Microsoft Windows XP

Re: How to import database file with command prompt

Postby KeepSmiling » 03. August 2013 20:13

Thanks for this input. I seem to have stumbled across this exact problem of needing to import a database over 2M.

Does this just overwrite an existing database, or does it append or combine anything?

In trying to do a mysqldump, I had an error indicating I had a duplicate entry for a primary key value at line 76. How do I find what line 76 the command prompt is talking about? I checked every table and there are no duplicate entries for any key value.

I once saw a line of code to take one database from one xampp install and transfer it to another in just one line of code, but I can't find it again. Do you know how to do that? This might allow synchronization between a laptop/desktop installation and a portable on a thumb drive.

Found it under:
http://dev.mysql.com/doc/refman/5.5/en/mysqldump-copying-to-other-server.html
Posted by Keith Jolley:
"""""you can also do this is one step:

local_server% mysqldump -u mysqluser --password=mysqlpasswd --databases db1 | ssh remote_server /usr/local/bin/mysql -u mysqluser -p

note that on a multi-user system you will NOT want to put in the passwords as shown because anyone on the system will be able to see them. in that case you can use the following, less convenient command. it will ask you for your password twice, once for the local server and once for the remote server:

local_server% mysqldump -u mysqluser --password=mysqlpasswd --databases db1 | ssh remote_server /usr/local/bin/mysql -u mysqluser -p"""""

Thanks again :D
Last edited by KeepSmiling on 04. August 2013 00:55, edited 4 times in total.
KeepSmiling
 
Posts: 10
Joined: 04. May 2013 05:05
Operating System: Vista

Re: How to import database file with command prompt

Postby Altrea » 03. August 2013 21:03

Dumping a database:
Code: Select all
mysqldump.exe --user=root --password= --max_allowed_packet=1G --host=localhost --port=3306 --default-character-set=utf8 "databasename" > "c:\path\to\dump\filename.sql"


Importing a Database
Code: Select all
mysql.exe --user=root --password= --max_allowed_packet=1G --host=localhost --port=3306 --default-character-set=utf8 "databasename" < "c:\path\to\dump\filename.sql"
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: How to import database file with command prompt

Postby IamJim » 26. December 2013 00:53

Thank you for this tip !! I have reloaded XAMPP 2 times today due to erros.

Is there a way to get remote host file?

Thanks...Jim
IamJim
 
Posts: 56
Joined: 27. April 2009 03:01

Re: How to import database file with command prompt

Postby Nobbie » 27. December 2013 00:36

IamJim wrote:Is there a way to get remote host file?


What is a "remote host file"??
Nobbie
 
Posts: 13165
Joined: 09. March 2008 13:04

Re: How to import database file with command prompt

Postby Altrea » 27. December 2013 01:14

If you mean a .sql file from a shared network folder, than the answer is yes (assumed the os user the process is running with has access to that shared folder).
you can use UNC paths for the source file path.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: How to import database file with command prompt

Postby IamJim » 30. December 2013 16:27

Altrea wrote:If you mean a .sql file from a shared network folder, than the answer is yes (assumed the os user the process is running with has access to that shared folder).
you can use UNC paths for the source file path.


Not from a `local` network, but a box not networked thru my router.

From my remote Linux servers using DOS Command prompts. As far as I have got, is to simply download WGET for Windows...but have not tried it.
IamJim
 
Posts: 56
Joined: 27. April 2009 03:01

Re: How to import database file with command prompt

Postby IamJim » 31. December 2013 00:10

FAst question...been awaiting to check...but I have it loacl on USB Drive and want to goto another USB drive..

In your command, it says > --max_allowed_packet=1G

My DB is 4.3 gig..

Do I leave this? > --max_allowed_packet=1G

As mentioned, way too many installs "smile" of xampp so far as trying to get it tweaked for InnoDB use and large DB's such as this.

Thanks !!

Jim
IamJim
 
Posts: 56
Joined: 27. April 2009 03:01

Re: How to import database file with command prompt

Postby Altrea » 31. December 2013 00:30

IamJim wrote:In your command, it says > --max_allowed_packet=1G

My DB is 4.3 gig..

Do I leave this? > --max_allowed_packet=1G

Read the official explaination of this setting in the manual to understand what it is doing and that your DB size will very common not be a problem
http://dev.mysql.com/doc/refman/5.6/en/ ... wed_packet
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: How to import database file with command prompt

Postby IamJim » 31. December 2013 13:56

Thank you for the reply and the link,

re: that your DB size will very common not be a problem

Well with the CORE new install and using the supplied my.ini only 1,159,168 KB of data went before it stopped and not really sure why.

I am now going to try using my-huge-ini again, as before I tried importing it I feel was doing well.

Also, I have set > #innodb_data_home_dir = C:\\mysql\\data\\

to: #innodb_data_home_dir = E:\\xampp\\mysql\\data\\

As well as: #innodb_log_group_home_dir = C:\\mysql\\data\\

to> #innodb_log_group_home_dir = E:\\xampp\\mysql\\data\\

As my xampp resides on USB

I will hopefully just have to truncate tables; and try again.

-----------------------------------------
UPDATE huge or large Will not start after uncommenting for use of innoDB

131231 7:12:38 [Note] Plugin 'FEDERATED' is disabled.
131231 7:12:39 InnoDB: The InnoDB memory heap is disabled
131231 7:12:39 InnoDB: Mutexes and rw_locks use Windows interlocked functions
131231 7:12:39 InnoDB: Compressed tables use zlib 1.2.3
131231 7:12:40 InnoDB: Initializing buffer pool, size = 384.0M
131231 7:12:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: data file E:\xampp\mysql\data\ibdata1 is of a different size
InnoDB: 1152 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 128000 pages!
131231 7:12:41 InnoDB: Could not open or create data files.
131231 7:12:41 InnoDB: If you tried to add new data files, and it failed here,
131231 7:12:41 InnoDB: you should now edit innodb_data_file_path in my.cnf back
131231 7:12:41 InnoDB: to what it was, and remove the new ibdata files InnoDB created
131231 7:12:41 InnoDB: in this failed attempt. InnoDB only wrote those files full of
131231 7:12:41 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
131231 7:12:41 InnoDB: remove old data files which contain your precious data!
131231 7:12:41 [ERROR] Plugin 'InnoDB' init function returned error.
131231 7:12:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
131231 7:12:41 [ERROR] Unknown/unsupported storage engine: InnoDB
131231 7:12:41 [ERROR] Aborting

131231 7:12:42 [Note] E:\xampp\mysql\bin\mysqld.exe: Shutdown complete

131231 7:21:13 [Note] Plugin 'FEDERATED' is disabled.
131231 7:21:13 InnoDB: The InnoDB memory heap is disabled
131231 7:21:13 InnoDB: Mutexes and rw_locks use Windows interlocked functions
131231 7:21:13 InnoDB: Compressed tables use zlib 1.2.3
131231 7:21:14 InnoDB: Initializing buffer pool, size = 256.0M
131231 7:21:14 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file E:\xampp\mysql\data\ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
131231 7:21:14 [ERROR] Plugin 'InnoDB' init function returned error.
131231 7:21:14 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
131231 7:21:14 [ERROR] Unknown/unsupported storage engine: InnoDB
131231 7:21:14 [ERROR] Aborting

131231 7:21:14 [Note] E:\xampp\mysql\bin\mysqld.exe: Shutdown complete
----------------------------------------

For others and for reference, one may find this article handy:
http://www.debianhelp.co.uk/mysqlperformance.htm
IamJim
 
Posts: 56
Joined: 27. April 2009 03:01


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 112 guests