How to Import Large SQL file Using MySQL command line

Import Large SQL file Using MySQL command line

When you want to restore database from a SQL backup file to your MySQL server on web server, you can simply restore it using phpMyAdmin which is fast and easy.
However, if the backup file is a large SQL dump file, let’s say 50 MB, you can’t use phpMyAdmin because most server configuration haven’t been configured for large file uploading.
phpMyAdmin Import Page

Solution

The best solution for import large SQL file to database is to use command line in MySQL. By importing database from command line, you won’t have to worry with any PHP restriction, such as, upload_max_filesize, post_max_size, maximum execution time, etc, which usually are limited to some value in PHP configuration.
MySQL command line

To execute MySQL command line for import SQL file, the syntax is:

mysql -u user_name -p -h host_name database_name < dump_file.sql

Where

  • user_name is username to execute this import operation.
  • host_name is target database hostname.
  • database_name is target database name.
  • dump_file.sql is SQL file.

Note: The syntax is the same for both Windows and Linux platforms.

Let’s see some example

mysql -u root -p -h localhost stock < stock.sql

The above command will import database from a file “stock.sql” to the server “localhost” on database “stock” using user “root”. You will be asked to enter password for the user once you execute the command.
If there is any error occurs while importing, it will display on the screen.
MySQL Import Database on Command Line

Once the importing process is finished without any error, you can use the data as you want.
The Imported Tables on phpMyAdmin

Leave a Reply

%d bloggers like this: