Post has moved to https://manios.org/2013/03/19/import-a-large-sql-dump-file-to-a-mysql-database-from-command-line
I have read many articles for importing large dbs in mysql…u made my day..THNX!!!
Thanks for the article – maybe you can help me out. I’ve taken the steps above and I’m getting an error 2006 (MySQL server has gone away) when trying to import a large file. Any ideas why this might be?
Thank you very much, your post saved my day. I was having errors when importing large files, your instructions worked like a charm.
I have followed your recommendations, but on Set max_allowed_packet and net_buffer_length I got 1 warning in the query and I don’t know what is it. However, the performance is not increased.
Could you please post the warning message;
Reblogged this on Birebir Linux.
nice guid. thanks, but you had forgotten ‘use databansename;’.
You are welcome! We assume that database is created inside the large sql dump file.
This is simply incredible! Many thanks!
This worked perfectly for me .. Thank you Very Much!
Thanks, it helped me a lot.
Didn’t work with me . “No database selected”
oops , sorry ma bad
everything is fine ^_^
Reblogged this on Imran Kabir.
This is so helpful. Reduced my import time from ~1 hour to 4 minutes. Thank you very much.
Thank for you article. But how long does it take to import 6GB db? I looking for a solution to import very big db, around 150 million record everday to mysql. Time is very important.
@Azure: In my situation it took about from 45 minutes to 2 hours to import a 6GB file. This file contained tables with 20-30 million rows with many indexes. Although I am not a MySQL expert, I noticed that you can achieve better performance if you tune your memory settings (like tmp_table_size,max_heap_table_size,max_binlog_size) in my.cnf file.
Hi there, thank you for this article. It was really helpful for me.
Thanks for putting this together as a script. Does anyone have an idea how I could combine this with dump files that are compressed? I used to import these databases like this:
gunzip < "dbdump.sql.gz" | mysql -u xyz -p'..' database
For small dumps, extracting them before is fine, for large ones this seems somewhat problematic as it requires a lot of spare disk space.
imeron? Timer On?
Hello mortona42! imeron and imeron2 are just two variables to record start and end datetime of import process. See the updated script.
Dont forget to select the database before you use your dump.
Thanks for the suggestion! I implied that database is created inside the dump file. I updated the script.
ghanu saras che aa post
WORKED ! , Thanks DUDE !!
The script you provided works great on mysql 5.5. Thank you. However I recently received a newer PC at work and upgraded the local mysql server from 5.5.37 to 5.7.13-0ubuntu0.16.04.2. The import process duration increased from 5 minutes to 25 minutes. The import file is only 110MB. I see in this post (http://stackoverflow.com/questions/37530126/mysql-5-5-faster-on-database-import-and-drop-than-5-7) that 5.7 is slower. I’ve tried changing several performance tuning settings but with no luck.
Not working in Windows 10 64bit. Works perfectly on Windows 7. I am using Wamp Server on both the machines.
Windows 7 takes 30 min to import the database and Window 10 takes 1.5 hours for the same database.
You might have to check MySql configuration in my.ini as WAMP Server propably comes with a predefined and preconfigured MySQL installation which might not be optimised for high loads. Some times it may help to tweek innodb_buffer_pool_size parameter.
I always use like this :-), that work!
mysql -u[username] -p[password] database_name < file.sql
Hi, Can we use this above method for INNODB as well? I was trying to disable indexing. Not sure if setting foreign key check to 0 would be any helpful. Could you please help me out? Sorry, i am a novice in database stuff. Thanks
All these examples have been used in production databases which use InnoDB as storage engine. MyISAM engine does not have foreign key checks , as far as I am concerned.
but where i put this script? Thanks
This is a Linux bash shell script. You can execute it wherever you like in a Linux OS, providing that you have MySQL client installed and have the correct credemtials and permissions to a MySQL database.
Fill in your details below or click an icon to log in:
You are commenting using your WordPress.com account.
( Log Out /
You are commenting using your Google+ account.
( Log Out /
You are commenting using your Twitter account.
( Log Out /
You are commenting using your Facebook account.
( Log Out /
Connecting to %s
Notify me of new comments via email.