Apache Friends Support Forum

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

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

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"

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 wrote:Is there a way to get remote host file?

What is a "remote host file"??

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.

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.

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 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

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