Import/export large MySQL databases under XAMPP


When importing large amounts of data under XAMPP ramming through phpMyAdmin quickly limits the script runtime or performance limits:

Fatal error: Maximum execution time exceeded

This can be adapted while, but such an import can take hours. The import of the console is by a factor of 10 faster. So do you need just a few minutes instead of an hour and has no problems with the script runtime or similar. The quickest and easiest is via the MySQL console import:

  • just in the XAMPP Control Panel on Shell Click and then type the following commands in the console:
mysql -u root -p

Enter the password or einfachmit ENTER confirm, If no password has been set. Root is the user of the database, may differ. Then select the database, that is to be imported, GGF. before applying, If these non-existent.

use meineDatenbank;

Then start the import:

source C:\data\mysqldump\db.sql

This works for importing files in the SQL format.

Import CSV files via the MySQL console

An import file in the following csv format:

Blalabl Copyright Zeug
Spalte1,Spalte2,Spalte3
"123","12432","22343"
"1223","12432","2343"
"1223","12432","23243"

must one proceed the following masses:

  1. Creating the table “test2″ with 3 Columns of data type int.. It should be noted, that the range of INT value only goes. Larger numbers you have to change either UNSIGNED INT or BIGINT, see MySQL Reference.
  2. Import with the following SQL statement:
LOAD DATA INFILE  'C:/archiv/data.csv'  INTO TABLE mytable
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 2 LINES;

Note: Windows path name must use forward slashes (instead of backslash) to be specified. If you backslash (Backslashes) use, must you specify it twice. (see MySQL Reference)

Export MySQL databases via the MySQL console

Goes easily with the following command:

mysqldump -u root -p db_name> D:\export.sql

Note: You may be not using the MySQL CLI engeloggt.

More information there to mysqldump here.

Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

  1. Hello and thank you for the article, After looking for a while. Unfortunately, already the first input in the XAMPP Shell works
    MySQL-u root-p does not :-( It is the error message, d:/XAMPP/mysql.exe is not a valid Win32 application, What now?

    Greeting Helmut

  2. Brilliant! Even after two years, this article is my salvation. For hours, I'm trying to move to Magento, without success, but with the more error messages.

    Everything is finally perfectly after this shell-DB-import.
    THANKS!!!!

    Greetings
    Angela