Sunday, February 28, 2016

How to import large data on MySQL

If you have a large data, and you want to import that data to let's say an empty database, you will have a hard time doing it with phpmyadmin or mysql workbench.

The best way to import large data on mysql is using mysql command line tool, so basically you need to use mysql client on command line mode.

Let's say you have sql file (.sql) called mydata.sql, which contains millions of data that you want to import, you can import using this command:
mysql -u root -h localhost -p campaign_db < mydata.sql
Campaign_db is the name of database, it's an empty database, no table yet, because on mydata.sql there is a command that will create the tables and insert the data.

If you have very large data on the sql file, you will need to wait little longer, while waiting you can open another command line and login to mysql, like this:

mysql -u root -h localhost

And then once you are inside, run this command to show what kind of process that's currently running on mysql database.

SHOW processlist;

You should be able to see the import process of mydata.sql is taking place right now, and when that process is gone, means the importing data is finish.

No comments:

Post a Comment