XAMPP is a very easy to use local webserver. Can be slow sometimes, but hey! it’s for testing purposes.
Ever tried to import a large SQL file? Doing so with phpMyAdmin will cause you headaches when tweaking php.ini to accept large data sets: memory_limit, post_max_size, etc…
Ever considered using the shell option? Once you have started your Apache and mySql module in the control panel, click “Shell”.
A command prompt will open and all you need to do is use this command line:
mysql -u root -p <DB_NAME> < <location of your large sql file
mysql -u root -p test < "d:\sql exports\file.sql"
Use -v to output the import process to screen
mysql -u root -p -v test < "d:\sql exports\file.sql"
Alternative method (no quotations in source command location!)
mysql -u root -p; use <db-name>; source C:\Temp\filename.sql;
Disable indexes before you start importing, re-enable them when completed.
ALTER TABLE `table_name` DISABLE KEYS;
ALTER TABLE `table_name` ENABLE KEYS;
Further reading: https://support.tigertech.net/mysql-large-inserts
ERROR 2006 (HY000) at line 9999: MySQL server has gone away
It’s possible that this error occurs in XAMPP. It’s easily resolved by changing
- my.ini at “\xampp\mysql\bin\”.
- change “max_allowed_packet” to a higher value than the default 1M. E.g. 10 MB
- restart mysql service
4 thoughts on “How to import large sql files in XAMPP?”
The space between “-u” and “root” should be removed. So it should be:
mysql -uroot -p…
I am using Xampp 5.6.8 for Windows 64bit.
Worked like a charm! This is my new way of importing databases! Thanks for posting.